restrict access to database: following

Showing posts with label following. Show all posts

Friday, March 30, 2012

How to Schedule Backup for SQL Server 2005 Express

How do you schedule to backup database in SQL Server 2005 Express? I tried
following the steps mentioned at http://support.microsoft.com/kb/q241397, but
there are errors .
Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
myBackupScript.sql -n, I received timeout error, telling me that it might be
that my server doesn't allow remote access. So, I open SQL Client Tools, and
run the SQL statements. Then it told me that all of the mentioned stored
procedures does not exist.
Please help> Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
> myBackupScript.sql -n, I received timeout error, telling me that it might be
> that my server doesn't allow remote access.
Use Surface Area Configuration to enable remote access.
> So, I open SQL Client Tools, and
> run the SQL statements. Then it told me that all of the mentioned stored
> procedures does not exist.
The procedures are in the msdb database, so you need to be there to run them, or qualifying the proc
name.
But Express doesn't come with Agent, so the steps in this KB doesn't make sense to me for 2005
Express.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:F0CD668A-70C8-43DE-B93B-76E709FE8152@.microsoft.com...
> How do you schedule to backup database in SQL Server 2005 Express? I tried
> following the steps mentioned at http://support.microsoft.com/kb/q241397, but
> there are errors .
> Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
> myBackupScript.sql -n, I received timeout error, telling me that it might be
> that my server doesn't allow remote access. So, I open SQL Client Tools, and
> run the SQL statements. Then it told me that all of the mentioned stored
> procedures does not exist.
> Please help|||Then is there no way to schedule backup? If that's so the log file will very
soon reach its limit and everyone won't be able to use the database, am I
right?
"Tibor Karaszi" wrote:
> > Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
> > myBackupScript.sql -n, I received timeout error, telling me that it might be
> > that my server doesn't allow remote access.
> Use Surface Area Configuration to enable remote access.
>
> > So, I open SQL Client Tools, and
> > run the SQL statements. Then it told me that all of the mentioned stored
> > procedures does not exist.
> The procedures are in the msdb database, so you need to be there to run them, or qualifying the proc
> name.
> But Express doesn't come with Agent, so the steps in this KB doesn't make sense to me for 2005
> Express.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "wrytat" <wrytat@.discussions.microsoft.com> wrote in message
> news:F0CD668A-70C8-43DE-B93B-76E709FE8152@.microsoft.com...
> > How do you schedule to backup database in SQL Server 2005 Express? I tried
> > following the steps mentioned at http://support.microsoft.com/kb/q241397, but
> > there are errors .
> >
> > Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
> > myBackupScript.sql -n, I received timeout error, telling me that it might be
> > that my server doesn't allow remote access. So, I open SQL Client Tools, and
> > run the SQL statements. Then it told me that all of the mentioned stored
> > procedures does not exist.
> >
> > Please help
>
>|||Hi,
To allow remote access remove the database name from namedpipes in
network configuration.All the stored procedure exists in master
database.
U can schedule them as u schedule it in sql server2000. butu have to
use master.storedprocedure name.
HTH
from
Doller|||>remove the database name from namedpipes in network configuration?
Under NamedPipes, it's either enable it or disable it. What do you mean by
removing the name?
"doller" wrote:
> Hi,
> To allow remote access remove the database name from namedpipes in
> network configuration.All the stored procedure exists in master
> database.
> U can schedule them as u schedule it in sql server2000. butu have to
> use master.storedprocedure name.
> HTH
> from
> Doller
>|||> U can schedule them as u schedule it in sql server2000. butu have to
> use master.storedprocedure name.
I run sql statements at master, and msdb, but it returns me this message,
"SQLServerAgent is not currently running so it cannot be notified of this
action." But there is no SQLServerAgent for this version.
"doller" wrote:
> Hi,
> To allow remote access remove the database name from namedpipes in
> network configuration.All the stored procedure exists in master
> database.
> U can schedule them as u schedule it in sql server2000. butu have to
> use master.storedprocedure name.
> HTH
> from
> Doller
>|||> U can schedule them as u schedule it in sql server2000. butu have to
> use master.storedprocedure name.
No! Express doesn't come with Agent.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"doller" <sufianarif@.gmail.com> wrote in message
news:1145431836.282134.66530@.v46g2000cwv.googlegroups.com...
> Hi,
> To allow remote access remove the database name from namedpipes in
> network configuration.All the stored procedure exists in master
> database.
> U can schedule them as u schedule it in sql server2000. butu have to
> use master.storedprocedure name.
> HTH
> from
> Doller
>|||> Then is there no way to schedule backup?
You can use the scheduler that comes with Windows. Together with SQLCMD.EXE, for instance.
> If that's so the log file will very
> soon reach its limit and everyone won't be able to use the database, am I
> right?
Well, you can set the database to simple recovery. But of course, there are other more important
reasons to do backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:58C5672C-ABC2-4EFA-BB1C-AD5232648F20@.microsoft.com...
> Then is there no way to schedule backup? If that's so the log file will very
> soon reach its limit and everyone won't be able to use the database, am I
> right?
> "Tibor Karaszi" wrote:
>> > Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
>> > myBackupScript.sql -n, I received timeout error, telling me that it might be
>> > that my server doesn't allow remote access.
>> Use Surface Area Configuration to enable remote access.
>>
>> > So, I open SQL Client Tools, and
>> > run the SQL statements. Then it told me that all of the mentioned stored
>> > procedures does not exist.
>> The procedures are in the msdb database, so you need to be there to run them, or qualifying the
>> proc
>> name.
>> But Express doesn't come with Agent, so the steps in this KB doesn't make sense to me for 2005
>> Express.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "wrytat" <wrytat@.discussions.microsoft.com> wrote in message
>> news:F0CD668A-70C8-43DE-B93B-76E709FE8152@.microsoft.com...
>> > How do you schedule to backup database in SQL Server 2005 Express? I tried
>> > following the steps mentioned at http://support.microsoft.com/kb/q241397, but
>> > there are errors .
>> >
>> > Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
>> > myBackupScript.sql -n, I received timeout error, telling me that it might be
>> > that my server doesn't allow remote access. So, I open SQL Client Tools, and
>> > run the SQL statements. Then it told me that all of the mentioned stored
>> > procedures does not exist.
>> >
>> > Please help
>>|||Here are some links you might find useful:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sseoverview.asp
Express Overview
http://msdn2.microsoft.com/en-us/library/ms165672.aspx Comparing Express
with MSDE
http://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsse/html/sqlexpuserinst.asp
User Instances
http://www.datamasker.com/SSE2005_NetworkCfg.htm Configuring Express
for Remote Access
https://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx
Configuring Remote Access
http://www.kbalertz.com/Feedback_914277.aspx Configuring SQL2005 for
remote access
http://msdn.microsoft.com/sql/express/default.aspx?pull=/library/en-us/dnsse/html/emsqlexcustapp.asp
Embedding Express in Apps
--
Andrew J. Kelly SQL MVP
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:F0CD668A-70C8-43DE-B93B-76E709FE8152@.microsoft.com...
> How do you schedule to backup database in SQL Server 2005 Express? I tried
> following the steps mentioned at http://support.microsoft.com/kb/q241397,
> but
> there are errors .
> Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
> myBackupScript.sql -n, I received timeout error, telling me that it might
> be
> that my server doesn't allow remote access. So, I open SQL Client Tools,
> and
> run the SQL statements. Then it told me that all of the mentioned stored
> procedures does not exist.
> Please help|||Automating Database maintenance in SQL 2005 Express Edition Part I
http://www.sqldbatips.com/showarticle.asp?ID=27
Automating Database maintenance in SQL 2005 Express Edition Part II
http://www.sqldbatips.com/showarticle.asp?ID=29
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:F0CD668A-70C8-43DE-B93B-76E709FE8152@.microsoft.com...
> How do you schedule to backup database in SQL Server 2005 Express? I tried
> following the steps mentioned at http://support.microsoft.com/kb/q241397,
> but
> there are errors .
> Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
> myBackupScript.sql -n, I received timeout error, telling me that it might
> be
> that my server doesn't allow remote access. So, I open SQL Client Tools,
> and
> run the SQL statements. Then it told me that all of the mentioned stored
> procedures does not exist.
> Please help|||Cool, Jasper!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:uBvSyh6YGHA.1888@.TK2MSFTNGP02.phx.gbl...
> Automating Database maintenance in SQL 2005 Express Edition Part I
> http://www.sqldbatips.com/showarticle.asp?ID=27
> Automating Database maintenance in SQL 2005 Express Edition Part II
> http://www.sqldbatips.com/showarticle.asp?ID=29
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>|||Way cool. If you want to combine this with internal scheduling that doesn't
use the Windows scheduler, you could replace the brain-dead backup command
with your SP in this:
http://blogs.msdn.com/rogerwolterblog/archive/2006/04/13/575974.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uJaD788YGHA.1220@.TK2MSFTNGP02.phx.gbl...
> Cool, Jasper!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:uBvSyh6YGHA.1888@.TK2MSFTNGP02.phx.gbl...
>> Automating Database maintenance in SQL 2005 Express Edition Part I
>> http://www.sqldbatips.com/showarticle.asp?ID=27
>> Automating Database maintenance in SQL 2005 Express Edition Part II
>> http://www.sqldbatips.com/showarticle.asp?ID=29
>> --
>> HTH,
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com|||Express databases by default use the SIMPLE recovery model so there
shouldn't be a problem with log files filling up unless you change this.
This DOESN'T mean you don't have to back up SQL Express databases - only
that log file backups probably aren't an issue.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:58C5672C-ABC2-4EFA-BB1C-AD5232648F20@.microsoft.com...
> Then is there no way to schedule backup? If that's so the log file will
> very
> soon reach its limit and everyone won't be able to use the database, am I
> right?
> "Tibor Karaszi" wrote:
>> > Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
>> > myBackupScript.sql -n, I received timeout error, telling me that it
>> > might be
>> > that my server doesn't allow remote access.
>> Use Surface Area Configuration to enable remote access.
>>
>> > So, I open SQL Client Tools, and
>> > run the SQL statements. Then it told me that all of the mentioned
>> > stored
>> > procedures does not exist.
>> The procedures are in the msdb database, so you need to be there to run
>> them, or qualifying the proc
>> name.
>> But Express doesn't come with Agent, so the steps in this KB doesn't make
>> sense to me for 2005
>> Express.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "wrytat" <wrytat@.discussions.microsoft.com> wrote in message
>> news:F0CD668A-70C8-43DE-B93B-76E709FE8152@.microsoft.com...
>> > How do you schedule to backup database in SQL Server 2005 Express? I
>> > tried
>> > following the steps mentioned at
>> > http://support.microsoft.com/kb/q241397, but
>> > there are errors .
>> >
>> > Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
>> > myBackupScript.sql -n, I received timeout error, telling me that it
>> > might be
>> > that my server doesn't allow remote access. So, I open SQL Client
>> > Tools, and
>> > run the SQL statements. Then it told me that all of the mentioned
>> > stored
>> > procedures does not exist.
>> >
>> > Please help
>>

Monday, March 26, 2012

How to run the snapshot agent using the -PublisherLogin ?

I am Merge replicating from MS SQL Server 2000 SP4 to a MS SQL Server 2000
SP3 box.
Get the following error while running the Merge Agent:
Error Message : The process could not log conflict information.
Error Details : The process could not log conflict information. (Source:
Merge Replication Provider (Agent); Error number: -2147200992)
Could not find stored procedure ''.
(Source: <publishing servername> (Data source); Error number: 2812)
On the Microsoft support website
http://support.microsoft.com/default...b;en-us;308743
it says this happens when 'If Merge Replication is configured so that the
Snapshot Agents connect to the Publisher by using a login that is defined as
a member of db_owner (and not a system administrator) in a database that is
merge published, if conflicts are detected during the Merge Process the Merge
Agent fails with this error message'
They offer a workaround which is :
To work around this behavior, run the Snapshot Agent with the
-PublisherLogin parameter and specify a login. For example, use the sa login,
which is a system administrator on the Publisher to generate a new snapshot.
After the snapshot completes, SQL Server creates the conflict stored
procedure. Then, you can rerun the Merge Agent so that SQL Server can log the
conflicts. Because you are not reinitializing the subscription, SQL Server
does not apply the new snapshot to the subscribers.
How do I run the Snapshot Agent with the -PublisherLogin parameter ?
On your publisher server there's a job which creates the snapshot. The
name of the job varies but you can recognize it by the category
REPL-Snapshot.
When you open the properties of the job, go to to Steps tab and edit
the "run agent" step by adding the parameter.
M

Monday, March 19, 2012

How to return time & number format that has set in the regional setting using stored proce

How to return time & number format that has set in the regional setting using stored procedure.

Following is my sp for getting current date format from Sql Server.

if exists

(select*fromsysobjectswhereid =object_id(N'[HSP_GetDateFormat]')andOBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure[HSP_GetDateFormat]

create procedure

HSP_GetDateFormat

(

@.strDateFormat nvarchar(64) out,

@.iErr

intout

)

begin

set nocount on

set@.strDateFormat = (selectdateformat frommaster..syslanguageswherelangid = (selectvaluefrommaster..sysconfigureswherecomment = 'default language'))

set@.iErr = @.@.Error

set nocount off

end

Now, I want to know what would I write if I want to get currenttime &numberformat from Sql Server.

Hi,

From your description, it seems that you want to get the current date time in stored procedure, right?

TSQL has provided several built-in functions which is used for datetime and datetiem calculating. You can get the current date and time by using GetDate() function, also, you can use such functions like DATEADD,DATEDIFF to calculate the date and time. See the following codes:

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE() ))

Besides, if you want to output the value, just create a datetime typed variable and set it as OUTPUT, and then create a parameter in .NET side, which shares the same type, set the direction as OUTPUT, then you can receive the datetime from your stored procedure. As for the number type, you can use INT,FLOAT,DOUBLE and etc in your stored procedure.

Thanks.

How to return the result (XML) with FOR XML EXPLICIT

Dear SQLXML specialists and other animals,
I got the following SP, that runs a SQL statement with FOR XML
EXPLICIT:
**** SQL STATEMENT *****
CREATE PROCEDURE SP_F06_GetActions
(
@.MSISDN as varchar(20),
@.SessionId as varchar(64)
)
AS
BEGIN
SELECT
1 as tag
,NULL as parent
,'F06' as [RESPONSE!1!Flow_Name!Element]
,[MSISDN] as [RESPONSE!1!MSISDN!Element]
,getdate() as [RESPONSE!1!Timestamp!Element]
,@.SessionId as [RESPONSE!1!SessionId!Element]
,0 as [RESPONSE!1!Status!Element]
,NULL as [Actions!2!NumOfActions!Element]
,NULL as [Action!3!Id]
,NULL as [Action!3!ActionId!Element]
,NULL as [Action!3!ActionName!Element]
,NULL as [Action!3!ActionType!Element]
,NULL as [Action!3!From!Element]
,NULL as [Action!3!To!Element]
,NULL as [Action!3!ActionStart!Element]
,NULL as [Action!3!ActionStop!Element]
,NULL as [Action!3!ActionDurationThreshold!Elemen
t]
,NULL as [Action!3!ActionVideoURL!Element]
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
2
,1
,NULL
,NULL
,NULL
,NULL
,NULL
, dbo.getNumOfActionsByMSISDN(MSISDN)
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
3
,2
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,[ActionId]
,[ActionId]
,[ActionName]
,[ActionType]
,[From]
,[To]
,[ActionStart]
,[ActionStop]
,[ActionDurationThreshold]
,[ActionVideoURL]
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
WHERE [MSISDN]=@.MSISDN
--ORDER BY 3, 1
FOR XML EXPLICIT
RETURN
END
GO
**** END OF SQL ***********
Well, it returns the following XML:
***** XML *********************
<RESPONSE>
<Flow_Name>F06</Flow_Name>
<MSISDN>9053*********</MSISDN>
<Timestamp>2007-11-15T14:37:24.467</Timestamp>
<SessionId>45717B60-0A0F-434B-9FB9-CC8F647BFEA7</SessionId>
<Status>0</Status>
<Actions>
<NumOfActions>3</NumOfActions>
<Action Id="10001">
<ActionId>10001</ActionId>
<ActionName>Acme</ActionName>
<ActionType>1</ActionType>
<From>00:00:00</From>
<To>12:00:00</To>
<ActionStart>2007-11-15T09:52:56.513</ActionStart>
<ActionStop>2007-11-25T09:52:56.513</ActionStop>
<ActionDurationThreshold>8</ActionDurationThreshold>
<ActionVideoURL>http://www.dynu.com/media/video/video1.g3p</
ActionVideoURL>
</Action>
<Action Id="10002">
<ActionId>10002</ActionId>
<ActionName>Pepsi</ActionName>
<ActionType>1</ActionType>
<From>12:00</From>
<To>16:00:00</To>
<ActionStart>2007-11-15T09:53:17.640</ActionStart>
<ActionStop>2007-11-25T09:53:17.640</ActionStop>
<ActionDurationThreshold>7</ActionDurationThreshold>
<ActionVideoURL>http://www.dynu.com/media/video/video2.g3p</
ActionVideoURL>
</Action>
<Action Id="10003">
<ActionId>10003</ActionId>
<ActionName>Momo Deterjan</ActionName>
<ActionType>1</ActionType>
<From>18:00:00</From>
<To>22:00:00</To>
<ActionStart>2007-11-15T09:56:32.950</ActionStart>
<ActionStop>2007-11-25T09:56:32.950</ActionStop>
<ActionDurationThreshold>6</ActionDurationThreshold>
<ActionVideoURL>http://www.dynu.com/media/video/video2.g3p</
ActionVideoURL>
</Action>
</Actions>
</RESPONSE>
**** END OF XML ************
Therefore, what I want is to assign this XML to a variable. To do
that, I tried the following (but failed):
Exec @.RC=SP_F06_GetActions
@.MSISDN,
@.SessionId
So, my QUESTION is HOW CAN I RETRIEVE THIS XML INTO A VARIABLE?
Thanks in advance for your help and interest.
Regards,
Ali<ali.koyuncu@.gmail.com> wrote in message
news:ea5805dd-f725-49c8-a641-68f2ea767a14@.f13g2000hsa.googlegroups.com...
> Dear SQLXML specialists and other animals,
> I got the following SP, that runs a SQL statement with FOR XML
> EXPLICIT:
> **** SQL STATEMENT *****
> CREATE PROCEDURE SP_F06_GetActions
> (
> @.MSISDN as varchar(20),
> @.SessionId as varchar(64)
> )
> AS
> BEGIN
> SELECT
> 1 as tag
> ,NULL as parent
> ,'F06' as [RESPONSE!1!Flow_Name!Element]
> ,[MSISDN] as [RESPONSE!1!MSISDN!Element]
> ,getdate() as [RESPONSE!1!Timestamp!Element]
> ,@.SessionId as [RESPONSE!1!SessionId!Element]
> ,0 as [RESPONSE!1!Status!Element]
> ,NULL as [Actions!2!NumOfActions!Element]
> ,NULL as [Action!3!Id]
> ,NULL as [Action!3!ActionId!Element]
> ,NULL as [Action!3!ActionName!Element]
> ,NULL as [Action!3!ActionType!Element]
> ,NULL as [Action!3!From!Element]
> ,NULL as [Action!3!To!Element]
> ,NULL as [Action!3!ActionStart!Element]
> ,NULL as [Action!3!ActionStop!Element]
> ,NULL as [Action!3!ActionDurationThreshold!Elemen
t]
> ,NULL as [Action!3!ActionVideoURL!Element]
> FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
> UNION
> SELECT
> 2
> ,1
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> , dbo.getNumOfActionsByMSISDN(MSISDN)
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
> UNION
> SELECT
> 3
> ,2
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,[ActionId]
> ,[ActionId]
> ,[ActionName]
> ,[ActionType]
> ,[From]
> ,[To]
> ,[ActionStart]
> ,[ActionStop]
> ,[ActionDurationThreshold]
> ,[ActionVideoURL]
> FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
> WHERE [MSISDN]=@.MSISDN
> --ORDER BY 3, 1
> FOR XML EXPLICIT
> RETURN
> END
> GO
> **** END OF SQL ***********
> Well, it returns the following XML:
> ***** XML *********************
> <RESPONSE>
> <Flow_Name>F06</Flow_Name>
> <MSISDN>9053*********</MSISDN>
> <Timestamp>2007-11-15T14:37:24.467</Timestamp>
> <SessionId>45717B60-0A0F-434B-9FB9-CC8F647BFEA7</SessionId>
> <Status>0</Status>
> <Actions>
> <NumOfActions>3</NumOfActions>
> <Action Id="10001">
> <ActionId>10001</ActionId>
> <ActionName>Acme</ActionName>
> <ActionType>1</ActionType>
> <From>00:00:00</From>
> <To>12:00:00</To>
> <ActionStart>2007-11-15T09:52:56.513</ActionStart>
> <ActionStop>2007-11-25T09:52:56.513</ActionStop>
> <ActionDurationThreshold>8</ActionDurationThreshold>
> <ActionVideoURL>http://www.dynu.com/media/video/video1.g3p</
> ActionVideoURL>
> </Action>
> <Action Id="10002">
> <ActionId>10002</ActionId>
> <ActionName>Pepsi</ActionName>
> <ActionType>1</ActionType>
> <From>12:00</From>
> <To>16:00:00</To>
> <ActionStart>2007-11-15T09:53:17.640</ActionStart>
> <ActionStop>2007-11-25T09:53:17.640</ActionStop>
> <ActionDurationThreshold>7</ActionDurationThreshold>
> <ActionVideoURL>http://www.dynu.com/media/video/video2.g3p</
> ActionVideoURL>
> </Action>
> <Action Id="10003">
> <ActionId>10003</ActionId>
> <ActionName>Momo Deterjan</ActionName>
> <ActionType>1</ActionType>
> <From>18:00:00</From>
> <To>22:00:00</To>
> <ActionStart>2007-11-15T09:56:32.950</ActionStart>
> <ActionStop>2007-11-25T09:56:32.950</ActionStop>
> <ActionDurationThreshold>6</ActionDurationThreshold>
> <ActionVideoURL>http://www.dynu.com/media/video/video2.g3p</
> ActionVideoURL>
> </Action>
> </Actions>
> </RESPONSE>
> **** END OF XML ************
> Therefore, what I want is to assign this XML to a variable. To do
> that, I tried the following (but failed):
> Exec @.RC=SP_F06_GetActions
> @.MSISDN,
> @.SessionId
> So, my QUESTION is HOW CAN I RETRIEVE THIS XML INTO A VARIABLE?
> Thanks in advance for your help and interest.
> Regards,
> Ali
Which version of SQL Server? In 2000 you can't do what you want, in 2005 you
need to use the TYPE directive.
http://technet.microsoft.com/en-us/...y/ms345110.aspx
Joe Fawcett (MVP - XML)
http://joe.fawcett.name|||I use SQL Server 2005.
How to use TYPE? Can you give me simple example?
Thanks,
Ali
On 15 Kas=FDm, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote:
> <ali.koyu...@.gmail.com> wrote in message
> news:ea5805dd-f725-49c8-a641-68f2ea767a14@.f13g2000hsa.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Which version of SQL Server? In 2000 you can't do what you want, in 2005 y=[/color
]
ou
> need to use the TYPE directive.http://technet.microsoft.com/en-us/library/=[/color
]
ms345110.aspx
> --
> Joe Fawcett (MVP - XML)
> http://joe.fawcett.name|||I use SQL Server 2005.
How to use TYPE? Can you give me simple example?
Thanks,
Ali
On 15 Kas=FDm, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote:
> <ali.koyu...@.gmail.com> wrote in message
> news:ea5805dd-f725-49c8-a641-68f2ea767a14@.f13g2000hsa.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Which version of SQL Server? In 2000 you can't do what you want, in 2005 y=[/color
]
ou
> need to use the TYPE directive.http://technet.microsoft.com/en-us/library/=[/color
]
ms345110.aspx
> --
> Joe Fawcett (MVP - XML)
> http://joe.fawcett.name|||I use SQL Server 2005.
How to use TYPE? Can you give me simple example?
Thanks,
Ali
On 15 Kas=FDm, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote:
> <ali.koyu...@.gmail.com> wrote in message
> news:ea5805dd-f725-49c8-a641-68f2ea767a14@.f13g2000hsa.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Which version of SQL Server? In 2000 you can't do what you want, in 2005 y=[/color
]
ou
> need to use the TYPE directive.http://technet.microsoft.com/en-us/library/=[/color
]
ms345110.aspx
> --
> Joe Fawcett (MVP - XML)
> http://joe.fawcett.name|||When I use TYPE, it gave me the following error :( *damn*
Msg 1086, Level 15, State 1, Line 72
The FOR XML clause is invalid in views, inline functions, derived
tables, and subqueries when they contain a set operator. To work
around, wrap the SELECT containing a set operator using derived table
syntax and apply FOR XML on top of it.
On 15 Kas=C4=B1m, 16:08, ali.koyu...@.gmail.com wrote:
> I use SQL Server 2005.
> How to use TYPE? Can you give me simple example?
> Thanks,
> Ali
> On 15 Kas=C3=BDm, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote=[/color
]
:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
you
y/ms345110.aspx
>
>
>|||At first, many thans to Joe...
then YAY! I solved. Just changed my SP as follows:
ALTER PROCEDURE SP_F06_GetActions
(
@.MSISDN as varchar(20),
@.SessionId as varchar(64),
@.ResultXML as xml=3DNULL output --NEW!
)
AS
BEGIN
SELECT
1 as tag
,NULL as parent
,'F06' as [RESPONSE!1!Flow_Name!Element]
,[MSISDN] as [RESPONSE!1!MSISDN!Element]
,getdate() as [RESPONSE!1!Timestamp!Element]
,@.SessionId as [RESPONSE!1!SessionId!Element]
,0 as [RESPONSE!1!Status!Element]
,NULL as [Actions!2!NumOfActions!Element]
,NULL as [Action!3!Id]
,NULL as [Action!3!ActionId!Element]
,NULL as [Action!3!ActionName!Element]
,NULL as [Action!3!ActionType!Element]
,NULL as [Action!3!From!Element]
,NULL as [Action!3!To!Element]
,NULL as [Action!3!ActionStart!Element]
,NULL as [Action!3!ActionStop!Element]
,NULL as [Action!3!ActionDurationThreshold!Elemen
t]
,NULL as [Action!3!ActionVideoURL!Element]
INTO #TempTable -- DEFINING A TEMP TABLE!
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
2
,1
,NULL
,NULL
,NULL
,NULL
,NULL
, dbo.getNumOfActionsByMSISDN(MSISDN)
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
3
,2
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,[ActionId]
,[ActionId]
,[ActionName]
,[ActionType]
,[From]
,[To]
,[ActionStart]
,[ActionStop]
,[ActionDurationThreshold]
,[ActionVideoURL]
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
WHERE [MSISDN]=3D@.MSISDN
SET @.ResultXML=3D(SELECT * FROM #TempTable FOR XML EXPLICIT, TYPE)
DROP TABLE #TempTable
RETURN
END
GO
On 15 Kas=C4=B1m, 16:08, ali.koyu...@.gmail.com wrote:
> I use SQL Server 2005.
> How to use TYPE? Can you give me simple example?
> Thanks,
> Ali
> On 15 Kas=C3=BDm, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote=[/color
]
:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
you
y/ms345110.aspx
>
>
>|||At first, many thans to Joe...
then YAY! I solved. Just changed my SP as follows:
ALTER PROCEDURE SP_F06_GetActions
(
@.MSISDN as varchar(20),
@.SessionId as varchar(64),
@.ResultXML as xml=3DNULL output --NEW!
)
AS
BEGIN
SELECT
1 as tag
,NULL as parent
,'F06' as [RESPONSE!1!Flow_Name!Element]
,[MSISDN] as [RESPONSE!1!MSISDN!Element]
,getdate() as [RESPONSE!1!Timestamp!Element]
,@.SessionId as [RESPONSE!1!SessionId!Element]
,0 as [RESPONSE!1!Status!Element]
,NULL as [Actions!2!NumOfActions!Element]
,NULL as [Action!3!Id]
,NULL as [Action!3!ActionId!Element]
,NULL as [Action!3!ActionName!Element]
,NULL as [Action!3!ActionType!Element]
,NULL as [Action!3!From!Element]
,NULL as [Action!3!To!Element]
,NULL as [Action!3!ActionStart!Element]
,NULL as [Action!3!ActionStop!Element]
,NULL as [Action!3!ActionDurationThreshold!Elemen
t]
,NULL as [Action!3!ActionVideoURL!Element]
INTO #TempTable -- DEFINING A TEMP TABLE!
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
2
,1
,NULL
,NULL
,NULL
,NULL
,NULL
, dbo.getNumOfActionsByMSISDN(MSISDN)
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
3
,2
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,[ActionId]
,[ActionId]
,[ActionName]
,[ActionType]
,[From]
,[To]
,[ActionStart]
,[ActionStop]
,[ActionDurationThreshold]
,[ActionVideoURL]
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
WHERE [MSISDN]=3D@.MSISDN
SET @.ResultXML=3D(SELECT * FROM #TempTable FOR XML EXPLICIT, TYPE)
DROP TABLE #TempTable
RETURN
END
GO
On 15 Kas=C4=B1m, 16:08, ali.koyu...@.gmail.com wrote:
> I use SQL Server 2005.
> How to use TYPE? Can you give me simple example?
> Thanks,
> Ali
> On 15 Kas=C3=BDm, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote=[/color
]
:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
you
y/ms345110.aspx
>
>
>|||At first, many thans to Joe...
then YAY! I solved. Just changed my SP as follows:
ALTER PROCEDURE SP_F06_GetActions
(
@.MSISDN as varchar(20),
@.SessionId as varchar(64),
@.ResultXML as xml=3DNULL output --NEW!
)
AS
BEGIN
SELECT
1 as tag
,NULL as parent
,'F06' as [RESPONSE!1!Flow_Name!Element]
,[MSISDN] as [RESPONSE!1!MSISDN!Element]
,getdate() as [RESPONSE!1!Timestamp!Element]
,@.SessionId as [RESPONSE!1!SessionId!Element]
,0 as [RESPONSE!1!Status!Element]
,NULL as [Actions!2!NumOfActions!Element]
,NULL as [Action!3!Id]
,NULL as [Action!3!ActionId!Element]
,NULL as [Action!3!ActionName!Element]
,NULL as [Action!3!ActionType!Element]
,NULL as [Action!3!From!Element]
,NULL as [Action!3!To!Element]
,NULL as [Action!3!ActionStart!Element]
,NULL as [Action!3!ActionStop!Element]
,NULL as [Action!3!ActionDurationThreshold!Elemen
t]
,NULL as [Action!3!ActionVideoURL!Element]
INTO #TempTable -- DEFINING A TEMP TABLE!
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
2
,1
,NULL
,NULL
,NULL
,NULL
,NULL
, dbo.getNumOfActionsByMSISDN(MSISDN)
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
3
,2
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,[ActionId]
,[ActionId]
,[ActionName]
,[ActionType]
,[From]
,[To]
,[ActionStart]
,[ActionStop]
,[ActionDurationThreshold]
,[ActionVideoURL]
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
WHERE [MSISDN]=3D@.MSISDN
SET @.ResultXML=3D(SELECT * FROM #TempTable FOR XML EXPLICIT, TYPE)
DROP TABLE #TempTable
RETURN
END
GO
On 15 Kas=C4=B1m, 16:08, ali.koyu...@.gmail.com wrote:
> I use SQL Server 2005.
> How to use TYPE? Can you give me simple example?
> Thanks,
> Ali
> On 15 Kas=C3=BDm, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote=[/color
]
:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
you
y/ms345110.aspx
>
>
>|||If you're using SQL 2005, how about using FOR XML PATH instead? Much
simpler.
<ali.koyuncu@.gmail.com> wrote in message
news:ea5805dd-f725-49c8-a641-68f2ea767a14@.f13g2000hsa.googlegroups.com...
> Dear SQLXML specialists and other animals,
> I got the following SP, that runs a SQL statement with FOR XML
> EXPLICIT:
> **** SQL STATEMENT *****
> CREATE PROCEDURE SP_F06_GetActions
> (
> @.MSISDN as varchar(20),
> @.SessionId as varchar(64)
> )
> AS
> BEGIN
> SELECT
> 1 as tag
> ,NULL as parent
> ,'F06' as [RESPONSE!1!Flow_Name!Element]
> ,[MSISDN] as [RESPONSE!1!MSISDN!Element]
> ,getdate() as [RESPONSE!1!Timestamp!Element]
> ,@.SessionId as [RESPONSE!1!SessionId!Element]
> ,0 as [RESPONSE!1!Status!Element]
> ,NULL as [Actions!2!NumOfActions!Element]
> ,NULL as [Action!3!Id]
> ,NULL as [Action!3!ActionId!Element]
> ,NULL as [Action!3!ActionName!Element]
> ,NULL as [Action!3!ActionType!Element]
> ,NULL as [Action!3!From!Element]
> ,NULL as [Action!3!To!Element]
> ,NULL as [Action!3!ActionStart!Element]
> ,NULL as [Action!3!ActionStop!Element]
> ,NULL as [Action!3!ActionDurationThreshold!Elemen
t]
> ,NULL as [Action!3!ActionVideoURL!Element]
> FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
> UNION
> SELECT
> 2
> ,1
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> , dbo.getNumOfActionsByMSISDN(MSISDN)
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
> UNION
> SELECT
> 3
> ,2
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,[ActionId]
> ,[ActionId]
> ,[ActionName]
> ,[ActionType]
> ,[From]
> ,[To]
> ,[ActionStart]
> ,[ActionStop]
> ,[ActionDurationThreshold]
> ,[ActionVideoURL]
> FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
> WHERE [MSISDN]=@.MSISDN
> --ORDER BY 3, 1
> FOR XML EXPLICIT
> RETURN
> END
> GO
> **** END OF SQL ***********
> Well, it returns the following XML:
> ***** XML *********************
> <RESPONSE>
> <Flow_Name>F06</Flow_Name>
> <MSISDN>9053*********</MSISDN>
> <Timestamp>2007-11-15T14:37:24.467</Timestamp>
> <SessionId>45717B60-0A0F-434B-9FB9-CC8F647BFEA7</SessionId>
> <Status>0</Status>
> <Actions>
> <NumOfActions>3</NumOfActions>
> <Action Id="10001">
> <ActionId>10001</ActionId>
> <ActionName>Acme</ActionName>
> <ActionType>1</ActionType>
> <From>00:00:00</From>
> <To>12:00:00</To>
> <ActionStart>2007-11-15T09:52:56.513</ActionStart>
> <ActionStop>2007-11-25T09:52:56.513</ActionStop>
> <ActionDurationThreshold>8</ActionDurationThreshold>
> <ActionVideoURL>http://www.dynu.com/media/video/video1.g3p</
> ActionVideoURL>
> </Action>
> <Action Id="10002">
> <ActionId>10002</ActionId>
> <ActionName>Pepsi</ActionName>
> <ActionType>1</ActionType>
> <From>12:00</From>
> <To>16:00:00</To>
> <ActionStart>2007-11-15T09:53:17.640</ActionStart>
> <ActionStop>2007-11-25T09:53:17.640</ActionStop>
> <ActionDurationThreshold>7</ActionDurationThreshold>
> <ActionVideoURL>http://www.dynu.com/media/video/video2.g3p</
> ActionVideoURL>
> </Action>
> <Action Id="10003">
> <ActionId>10003</ActionId>
> <ActionName>Momo Deterjan</ActionName>
> <ActionType>1</ActionType>
> <From>18:00:00</From>
> <To>22:00:00</To>
> <ActionStart>2007-11-15T09:56:32.950</ActionStart>
> <ActionStop>2007-11-25T09:56:32.950</ActionStop>
> <ActionDurationThreshold>6</ActionDurationThreshold>
> <ActionVideoURL>http://www.dynu.com/media/video/video2.g3p</
> ActionVideoURL>
> </Action>
> </Actions>
> </RESPONSE>
> **** END OF XML ************
> Therefore, what I want is to assign this XML to a variable. To do
> that, I tried the following (but failed):
> Exec @.RC=SP_F06_GetActions
> @.MSISDN,
> @.SessionId
> So, my QUESTION is HOW CAN I RETRIEVE THIS XML INTO A VARIABLE?
> Thanks in advance for your help and interest.
> Regards,
> Ali

How to return the result (XML) with FOR XML EXPLICIT

*damn*
Msg 1086, Level 15, State 1, Line 72
The FOR XML clause is invalid in views, inline functions, derived
tables, and subqueries when they contain a set operator. To work
around, wrap the SELECT containing a set operator using derived table
syntax and apply FOR XML on top of it.
On 15 Kas?m, 16:08, ali.koyu...@.gmail.com wrote:[vbcol=seagreen]
> I use SQL Server 2005.
> How to use TYPE? Can you give me simple example?
> Thanks,
> Ali
> On 15 Kasym, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote:
>
>
>
>
>
>
>
>
>
>
>
|||At first, many thans to Joe...
then YAY! I solved. Just changed my SP as follows:
ALTER PROCEDURE SP_F06_GetActions
(
@.MSISDNas varchar(20),
@.SessionIdas varchar(64),
@.ResultXMLas xml=NULL output --NEW!
)
AS
BEGIN
SELECT
1 as tag
,NULL as parent
,'F06' as [RESPONSE!1!Flow_Name!Element]
,[MSISDN] as [RESPONSE!1!MSISDN!Element]
,getdate() as [RESPONSE!1!Timestamp!Element]
,@.SessionId as [RESPONSE!1!SessionId!Element]
,0 as [RESPONSE!1!Status!Element]
,NULL as [Actions!2!NumOfActions!Element]
,NULL as [Action!3!Id]
,NULL as [Action!3!ActionId!Element]
,NULL as [Action!3!ActionName!Element]
,NULL as [Action!3!ActionType!Element]
,NULL as [Action!3!From!Element]
,NULL as [Action!3!To!Element]
,NULL as [Action!3!ActionStart!Element]
,NULL as [Action!3!ActionStop!Element]
,NULL as [Action!3!ActionDurationThreshold!Element]
,NULL as [Action!3!ActionVideoURL!Element]
INTO #TempTable -- DEFINING A TEMP TABLE!
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
2
,1
,NULL
,NULL
,NULL
,NULL
,NULL
, dbo.getNumOfActionsByMSISDN(MSISDN)
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
3
,2
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,[ActionId]
,[ActionId]
,[ActionName]
,[ActionType]
,[From]
,[To]
,[ActionStart]
,[ActionStop]
,[ActionDurationThreshold]
,[ActionVideoURL]
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
WHERE [MSISDN]=@.MSISDN
SET @.ResultXML=(SELECT * FROM #TempTable FOR XML EXPLICIT, TYPE)
DROP TABLE #TempTable
RETURN
END
GO
On 15 Kas?m, 16:08, ali.koyu...@.gmail.com wrote:[vbcol=seagreen]
> I use SQL Server 2005.
> How to use TYPE? Can you give me simple example?
> Thanks,
> Ali
> On 15 Kasym, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote:
>
>
>
>
>
>
>
>
>
>
>
|||At first, many thans to Joe...
then YAY! I solved. Just changed my SP as follows:
ALTER PROCEDURE SP_F06_GetActions
(
@.MSISDNas varchar(20),
@.SessionIdas varchar(64),
@.ResultXMLas xml=NULL output --NEW!
)
AS
BEGIN
SELECT
1 as tag
,NULL as parent
,'F06' as [RESPONSE!1!Flow_Name!Element]
,[MSISDN] as [RESPONSE!1!MSISDN!Element]
,getdate() as [RESPONSE!1!Timestamp!Element]
,@.SessionId as [RESPONSE!1!SessionId!Element]
,0 as [RESPONSE!1!Status!Element]
,NULL as [Actions!2!NumOfActions!Element]
,NULL as [Action!3!Id]
,NULL as [Action!3!ActionId!Element]
,NULL as [Action!3!ActionName!Element]
,NULL as [Action!3!ActionType!Element]
,NULL as [Action!3!From!Element]
,NULL as [Action!3!To!Element]
,NULL as [Action!3!ActionStart!Element]
,NULL as [Action!3!ActionStop!Element]
,NULL as [Action!3!ActionDurationThreshold!Element]
,NULL as [Action!3!ActionVideoURL!Element]
INTO #TempTable -- DEFINING A TEMP TABLE!
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
2
,1
,NULL
,NULL
,NULL
,NULL
,NULL
, dbo.getNumOfActionsByMSISDN(MSISDN)
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
3
,2
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,[ActionId]
,[ActionId]
,[ActionName]
,[ActionType]
,[From]
,[To]
,[ActionStart]
,[ActionStop]
,[ActionDurationThreshold]
,[ActionVideoURL]
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
WHERE [MSISDN]=@.MSISDN
SET @.ResultXML=(SELECT * FROM #TempTable FOR XML EXPLICIT, TYPE)
DROP TABLE #TempTable
RETURN
END
GO
On 15 Kas?m, 16:08, ali.koyu...@.gmail.com wrote:[vbcol=seagreen]
> I use SQL Server 2005.
> How to use TYPE? Can you give me simple example?
> Thanks,
> Ali
> On 15 Kasym, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote:
>
>
>
>
>
>
>
>
>
>
>
|||At first, many thans to Joe...
then YAY! I solved. Just changed my SP as follows:
ALTER PROCEDURE SP_F06_GetActions
(
@.MSISDNas varchar(20),
@.SessionIdas varchar(64),
@.ResultXMLas xml=NULL output --NEW!
)
AS
BEGIN
SELECT
1 as tag
,NULL as parent
,'F06' as [RESPONSE!1!Flow_Name!Element]
,[MSISDN] as [RESPONSE!1!MSISDN!Element]
,getdate() as [RESPONSE!1!Timestamp!Element]
,@.SessionId as [RESPONSE!1!SessionId!Element]
,0 as [RESPONSE!1!Status!Element]
,NULL as [Actions!2!NumOfActions!Element]
,NULL as [Action!3!Id]
,NULL as [Action!3!ActionId!Element]
,NULL as [Action!3!ActionName!Element]
,NULL as [Action!3!ActionType!Element]
,NULL as [Action!3!From!Element]
,NULL as [Action!3!To!Element]
,NULL as [Action!3!ActionStart!Element]
,NULL as [Action!3!ActionStop!Element]
,NULL as [Action!3!ActionDurationThreshold!Element]
,NULL as [Action!3!ActionVideoURL!Element]
INTO #TempTable -- DEFINING A TEMP TABLE!
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
2
,1
,NULL
,NULL
,NULL
,NULL
,NULL
, dbo.getNumOfActionsByMSISDN(MSISDN)
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
3
,2
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,[ActionId]
,[ActionId]
,[ActionName]
,[ActionType]
,[From]
,[To]
,[ActionStart]
,[ActionStop]
,[ActionDurationThreshold]
,[ActionVideoURL]
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
WHERE [MSISDN]=@.MSISDN
SET @.ResultXML=(SELECT * FROM #TempTable FOR XML EXPLICIT, TYPE)
DROP TABLE #TempTable
RETURN
END
GO
On 15 Kas?m, 16:08, ali.koyu...@.gmail.com wrote:[vbcol=seagreen]
> I use SQL Server 2005.
> How to use TYPE? Can you give me simple example?
> Thanks,
> Ali
> On 15 Kasym, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote:
>
>
>
>
>
>
>
>
>
>
>
|||If you're using SQL 2005, how about using FOR XML PATH instead? Much
simpler.
<ali.koyuncu@.gmail.com> wrote in message
news:ea5805dd-f725-49c8-a641-68f2ea767a14@.f13g2000hsa.googlegroups.com...
> Dear SQLXML specialists and other animals,
> I got the following SP, that runs a SQL statement with FOR XML
> EXPLICIT:
> **** SQL STATEMENT *****
> CREATE PROCEDURE SP_F06_GetActions
> (
> @.MSISDN as varchar(20),
> @.SessionId as varchar(64)
> )
> AS
> BEGIN
> SELECT
> 1 as tag
> ,NULL as parent
> ,'F06' as [RESPONSE!1!Flow_Name!Element]
> ,[MSISDN] as [RESPONSE!1!MSISDN!Element]
> ,getdate() as [RESPONSE!1!Timestamp!Element]
> ,@.SessionId as [RESPONSE!1!SessionId!Element]
> ,0 as [RESPONSE!1!Status!Element]
> ,NULL as [Actions!2!NumOfActions!Element]
> ,NULL as [Action!3!Id]
> ,NULL as [Action!3!ActionId!Element]
> ,NULL as [Action!3!ActionName!Element]
> ,NULL as [Action!3!ActionType!Element]
> ,NULL as [Action!3!From!Element]
> ,NULL as [Action!3!To!Element]
> ,NULL as [Action!3!ActionStart!Element]
> ,NULL as [Action!3!ActionStop!Element]
> ,NULL as [Action!3!ActionDurationThreshold!Element]
> ,NULL as [Action!3!ActionVideoURL!Element]
> FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
> UNION
> SELECT
> 2
> ,1
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> , dbo.getNumOfActionsByMSISDN(MSISDN)
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
> UNION
> SELECT
> 3
> ,2
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,[ActionId]
> ,[ActionId]
> ,[ActionName]
> ,[ActionType]
> ,[From]
> ,[To]
> ,[ActionStart]
> ,[ActionStop]
> ,[ActionDurationThreshold]
> ,[ActionVideoURL]
> FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
> WHERE [MSISDN]=@.MSISDN
> --ORDER BY 3, 1
> FOR XML EXPLICIT
> RETURN
> END
> GO
> **** END OF SQL ***********
> Well, it returns the following XML:
> ***** XML *********************
> <RESPONSE>
> <Flow_Name>F06</Flow_Name>
> <MSISDN>9053*********</MSISDN>
> <Timestamp>2007-11-15T14:37:24.467</Timestamp>
> <SessionId>45717B60-0A0F-434B-9FB9-CC8F647BFEA7</SessionId>
> <Status>0</Status>
> <Actions>
> <NumOfActions>3</NumOfActions>
> <Action Id="10001">
> <ActionId>10001</ActionId>
> <ActionName>Acme</ActionName>
> <ActionType>1</ActionType>
> <From>00:00:00</From>
> <To>12:00:00</To>
> <ActionStart>2007-11-15T09:52:56.513</ActionStart>
> <ActionStop>2007-11-25T09:52:56.513</ActionStop>
> <ActionDurationThreshold>8</ActionDurationThreshold>
> <ActionVideoURL>http://www.dynu.com/media/video/video1.g3p</
> ActionVideoURL>
> </Action>
> <Action Id="10002">
> <ActionId>10002</ActionId>
> <ActionName>Pepsi</ActionName>
> <ActionType>1</ActionType>
> <From>12:00</From>
> <To>16:00:00</To>
> <ActionStart>2007-11-15T09:53:17.640</ActionStart>
> <ActionStop>2007-11-25T09:53:17.640</ActionStop>
> <ActionDurationThreshold>7</ActionDurationThreshold>
> <ActionVideoURL>http://www.dynu.com/media/video/video2.g3p</
> ActionVideoURL>
> </Action>
> <Action Id="10003">
> <ActionId>10003</ActionId>
> <ActionName>Momo Deterjan</ActionName>
> <ActionType>1</ActionType>
> <From>18:00:00</From>
> <To>22:00:00</To>
> <ActionStart>2007-11-15T09:56:32.950</ActionStart>
> <ActionStop>2007-11-25T09:56:32.950</ActionStop>
> <ActionDurationThreshold>6</ActionDurationThreshold>
> <ActionVideoURL>http://www.dynu.com/media/video/video2.g3p</
> ActionVideoURL>
> </Action>
> </Actions>
> </RESPONSE>
> **** END OF XML ************
> Therefore, what I want is to assign this XML to a variable. To do
> that, I tried the following (but failed):
> Exec @.RC=SP_F06_GetActions
> @.MSISDN,
> @.SessionId
> So, my QUESTION is HOW CAN I RETRIEVE THIS XML INTO A VARIABLE?
> Thanks in advance for your help and interest.
> Regards,
> Ali

Monday, March 12, 2012

How to return a varchar from sproc on remote Linked Server?

The following T-SQL 2005 snippet executes a remote sproc on an Oracle database.

DECLARE@.OutParamVARCHAR(5)

EXEC('{Call getnextpin(1)}')ATALTTEST

GetNextPin takes a single input parameter.

I need to modify the above code snippet to return the next PIN number into @.OutParam.

Any ideas?

Any help appreciated.

Regards,

Paul.

Hi wadep,

Try the following code and see if it solves your problem ( i suppose your table has a primary key named "number_index" and your table is ordered well. If not, you need to use cursor which will be a little complicated)

create procedure getnextpin@.indexint,@.numberint outputasselect top 1 @.number=pin_number from your_table_namewhere number_index>@.indexreturn

-------- call proceduredeclare @.indexint, @.numberintset @.index=whatever_you_wantexecute getnextpin @.index,@.number outputprint @.number -- see if your number can be printed here

Hope my suggestion helps

Friday, March 9, 2012

How To Return a Static Value?

I have the following stored procedure...

CREATE Procedure UserGetInfo2
(
@.UserID int, @.SystemTimePeriodID int
)
As
set nocount on
SELECT Users.UserId as UserID, Users.UserName as UserName, Users.RealName as RealName, UserTimePeriod.BudgetCode as BudgetCode, UserTimePeriod.SystemTimePeriodID as SystemTimePeriodID, Users.Password as Password, Users.SSN as SSN, Users.Location as Location, Users.ScheduleType as ScheduleType, Users.EmployeeType as EmployeeType, Users.TimeAccounted as TimeAccounted
FROM Users INNER JOIN UserTimePeriod ON Users.UserId = UserTimePeriod.UserID
WHERE (users.userID= @.UserID) AND (UserTimePeriod.SystemTimePeriodID = @.SystemTimePeriodID)
return
GO

The problem lies in that when a person has a SystemTimePeriodID over a certain value, there is no UserTimePeriod record since it has not been created yet.

Obviously, I need to wrap this in an IF...EXISTS

IF EXISTS (SELECT UserTimePeriodID FROM UserTimePeriod WHERE (SystemTimePeriodID = @.SystemTimePeriodID) AND (UserID = @.UserID))

(the SELECT above, since that's what needs to come back if the data exists)

ELSE

Do the same select but put in a static value for BudgetCode, like '0000'

How could I do the part where the IF...EXISTS fails?

I'm... not sure I can use RETURNS, since it feeds into this recordset:

rstUserInfo2.Open "UserGetInfo2 " & Request("UserID") & ", " & Request("SYSTIMEPERIODID")

and later uses values from that RecordSet, such as <td><%=rstUserInfo("BudgetCode") & ""%></td>

Hi friend, I dont know if I understand you in the right way. Try this:

CREATE Procedure UserGetInfo2 (@.UserIDint, @.SystemTimePeriodIDint )As set nocount onreturnGOIFNOT EXISTS (SELECT UserTimePeriodIDFROM UserTimePeriodWHERE (SystemTimePeriodID = @.SystemTimePeriodID)AND (UserID = @.UserID))SET @.SystemTimePeriodID = 0;SELECT Users.UserIdas UserID, Users.UserNameas UserName, Users.RealNameas RealName, UserTimePeriod.BudgetCodeas BudgetCode, UserTimePeriod.SystemTimePeriodIDas SystemTimePeriodID, Users.Passwordas Password, Users.SSNas SSN, Users.Locationas Location, Users.ScheduleTypeas ScheduleType, Users.EmployeeTypeas EmployeeType, Users.TimeAccountedas TimeAccountedFROM UsersINNERJOIN UserTimePeriodON Users.UserId = UserTimePeriod.UserIDWHERE (users.userID= @.UserID)AND (UserTimePeriod.SystemTimePeriodID = @.SystemTimePeriodID)

Hope this helps.

|||No, I would need to set the outgoing BudgetCode to something, not the incoming SystemTimePeriodID.|||

It's as easy as:

Select..... , '0000' as BudgetCode, ...

|||I knew I had seen that done somewhere. Thank you for clearing up my brain's log jam.

How to retrieve value from Exec stmt?

hi, all..
the following is part of my sp
I want to know how to assign result of stmt.1 to @.tp

declare @.tp datetime
declare @.tableName varchar(100)
SET @.tableName = 'tblState'
Exec ('SELECT MAX(UpdateTime) FROM ' + @.tableName) -- stmt.1

thank you..Try putting everything in an EXEC statement:

EXEC('
declare @.tp datetime
declare @.tableName varchar(100)
SET @.tableName = ''tblState''
SELECT MAX(UpdateTime) FROM @.tableName
')

I can't say this is the optimal way to do it, but it should work. Note: Those are two single quotes around tblState, not double quotes.|||declare @.tp datetime
declare @.tableName varchar(100)
SET @.tableName = 'tblState'
EXEC sp_executesql 'SELECT @.tp=MAX(UpdateTime) FROM ' + @.tableName, N'@.tp datetime OUTPUT',@.tp OUTPUT

select @.tp

it should give u the exact result.|||Thank you for reply,
however it gives following err...
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '+'.|||Modifying the previous example, I'd use:declare @.sql VARCHAR(250)declare @.tp datetime
declare @.tableName varchar(100)
SET @.tableName = 'tblState'
SET @.sql = 'SELECT @.tp=MAX(UpdateTime) FROM ' + @.tableName

EXEC sp_executesql @.sql, N'@.tp datetime OUTPUT',@.tp OUTPUT

select @.tp-PatP|||USE Northwind
GO

SET NOCOUNT ON

DECLARE @.tp datetime, @.TABLE_NAME sysname, @.COLUMN_NAME sysname, @.SQL varchar(8000)

SELECT @.TABLE_NAME = 'Orders', @.COLUMN_NAME = 'OrderDate'

CREATE TABLE #x(tp datetime)

SET @.SQL = 'INSERT INTO #x(tp) SELECT MAX('+@.COLUMN_NAME+') FROM '+@.TABLE_NAME

EXEC(@.SQL)

SELECT @.tp = tp FROM #x

SELECT @.tp

DROP TABLE #x

SET NOCOUNT OFF
GO|||Thank you all..
All are good hint..
Now it works..

my version is following..

DECLARE @.SQLString NVARCHAR(500), @.tbl nvarchar(100)
DECLARE @.ParmDefinition NVARCHAR(500)
declare @.tp datetime
SET @.tbl = 'tblState'
SET @.SQLString = N'SELECT @.tp = MAX(UpdateTime) FROM ' + @.tbl
SET @.ParmDefinition = N'@.tp datetime OUTPUT'
EXECUTE sp_executesql @.SQLString, @.ParmDefinition
,@.tp OUTPUT
Select @.tp as UpdateTime

Wednesday, March 7, 2012

How to retrieve the actions using MDSCHEMA_ACTIONS?

Hello,

When I run the following code, I get an empty schema.

Dim drAction As DataRow

Dim dsActions As DataSet

dsActions = MyConnection.GetSchemaDataSet(AdomdSchemaGuid.Actions, _

New Object() {szDatabase, Nothing, szCubeName, Nothing, Nothing, szCubeName, 1})

The result:

dsActions.Tables.Count = 1 but dsActions.Tables(0).Rows.Count = 0

Even though my cube contains two actions as it is shown in the following:

<ID>Drillthrough Action</ID>

<Name>Drillthrough Action</Name>

<TargetType>Cells</TargetType>

<Target>MeasureGroupMeasures("Values Measure")</Target>

<Type>DrillThrough</Type>

</Action>

<ID>Drillthrough Action 1</ID>

<Name>Drillthrough Action 1</Name>

<TargetType>Cells</TargetType>

<Target>MeasureGroupMeasures("Values Measure")</Target>

<Type>DrillThrough</Type>

</Action>

</Actions>

Thanks,

yones

hello Yones,

i think the problem is with the restrictions you specify. I think coordinate and coordinate type in this case should be: Coordinate type should be Cell (6) and Coordinate should be a tuple defining your cell. (right now it looks like they are for cube object). So for example the query could look like (for Adventure Works)

dsActions = MyConnection.GetSchemaDataSet(AdomdSchemaGuid.Actions, _

New Object() {

"Adventure Works DW", // CATALOG_NAME

Nothing, // SCHEMA_NAME

"Adventure Works", // CUBE_NAME

Nothing, // ACTION_NAME

Nothing, // ACTION_TYPE

"([Customer].[Customer Geography].[Country].&[Canada],[Measures].[Internet Extended Amount])", // COORDINATE

6}) // COORDINATE_TYPE : MDACTION_COORDINATE_CELL (6)

hope this helps,

|||

Hello Mary,

First thank you very much for your help.

I tried Cell actions instead of Cube actions as it is shown in the following code:

dsActions = MyConnection.GetSchemaDataSet(AdomdSchemaGuid.Actions, _

New Object() {"AmoAdventureWorks", Nothing, "Adventure Works", Nothing, Nothing, "[Date].[Calendar Month Name].[All Periods],[Customer].[City].[All Customers],[Measures].[Reseller Sales Amount]", 6})

But the result was the same:

dsActions.Tables.Count = 1

dsActions.Tables(0).Rows.Count = 0

And my cube contains the following action:

<ID>Drillthrough Action 1</ID>

<Name>Drillthrough Action</Name>

<TargetType>Cells</TargetType>

<Target>MeasureGroupMeasures("Reseller Sales")</Target>

<Type>DrillThrough</Type>

</Action>

</Actions>

Please let me know if you notice any wrong in my code.

Again thank you

Yones|||

hello Yones,

i think you might be missing () inside a coordinate. I.e. i think it should be "([Date].[Calendar Month Name].[All Periods],[Customer].[City].[All Customers],[Measures].[Reseller Sales Amount])".

hope this helps,

How to retrieve the actions using MDSCHEMA_ACTIONS?

Hello,

When I run the following code, I get an empty schema.

Dim drAction As DataRow

Dim dsActions As DataSet

dsActions = MyConnection.GetSchemaDataSet(AdomdSchemaGuid.Actions, _

New Object() {szDatabase, Nothing, szCubeName, Nothing, Nothing, szCubeName, 1})

The result:

dsActions.Tables.Count = 1 but dsActions.Tables(0).Rows.Count = 0

Even though my cube contains two actions as it is shown in the following:

<ID>Drillthrough Action</ID>

<Name>Drillthrough Action</Name>

<TargetType>Cells</TargetType>

<Target>MeasureGroupMeasures("Values Measure")</Target>

<Type>DrillThrough</Type>

</Action>

<ID>Drillthrough Action 1</ID>

<Name>Drillthrough Action 1</Name>

<TargetType>Cells</TargetType>

<Target>MeasureGroupMeasures("Values Measure")</Target>

<Type>DrillThrough</Type>

</Action>

</Actions>

Thanks,

yones

hello Yones,

dsActions = MyConnection.GetSchemaDataSet(AdomdSchemaGuid.Actions, _

New Object() {

"Adventure Works DW", // CATALOG_NAME

Nothing, // SCHEMA_NAME

"Adventure Works", // CUBE_NAME

Nothing, // ACTION_NAME

Nothing, // ACTION_TYPE

"([Customer].[Customer Geography].[Country].&[Canada],[Measures].[Internet Extended Amount])", // COORDINATE

6}) // COORDINATE_TYPE : MDACTION_COORDINATE_CELL (6)

hope this helps,

|||

Hello Mary,

First thank you very much for your help.

I tried Cell actions instead of Cube actions as it is shown in the following code:

dsActions = MyConnection.GetSchemaDataSet(AdomdSchemaGuid.Actions, _

But the result was the same:

dsActions.Tables.Count = 1

dsActions.Tables(0).Rows.Count = 0

And my cube contains the following action:

<ID>Drillthrough Action 1</ID>

<Name>Drillthrough Action</Name>

<TargetType>Cells</TargetType>

<Target>MeasureGroupMeasures("Reseller Sales")</Target>

<Type>DrillThrough</Type>

</Action>

</Actions>

Please let me know if you notice any wrong in my code.

Again thank you

Yones|||

hello Yones,

i think you might be missing () inside a coordinate. I.e. i think it should be "([Date].[Calendar Month Name].[All Periods],[Customer].[City].[All Customers],[Measures].[Reseller Sales Amount])".

hope this helps,

How to retrieve SqlDbType.Bit in SQL server?

Hi,

My checkbox status is saved as SqlDbType.Bit in MS SQl Server database.
When I use the following code to retrieve it, it does not work.

myRow[myColumn].ToString()=="1"

I appreciate your kind assistance.

Thank you.Just use

myRow[myColumn] == true

Sunday, February 19, 2012

How to restore Values to Table based on some Query

Hi,
I have a table called A and another Table Called B.
A table has the following Column and Values
ID One Two Three
---
10 test01 test02 test03
11 test11 test12 test13
12 test21 test22 test23
13 test31 test32 test33
B table has the following Column and Values
ID One Two Three
---
10 abcd01 abcd02 abcd03
11 abcd11 abcd12 abcd13
12 abcd21 abcd22
13 abcd31 abcd32 abcd33
I want to take a backup of Column Three where ID in(11,12,13). I know
using BCP utility i can take a backup of Table A of Column Three.
Following is the Query
"select Three,ID from [Test].dbo.A where ID in(11,12,13)" queryout
c:\try.txt -U sa -P ***
and then i want to restore it in to the Table B's Column Three by
specifying some Query. Meaning i want to restore the values to the
Table B's Column Three where ID is the ID which is obtained in
Backup[Table A]. How can i do this? If anybody knows the solution
Please Let me know to solve it.
Thanks,
Vinoth
Vinoth@.gsdindia.comYou don't need to extract it to a file. If you want to update B from A,
just do:
update B
set
Three = (select A.Three from A where A.ID = B.ID)
where
ID in (11, 12, 13)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131797987.096680.189410@.g14g2000cwa.googlegroups.com...
> Hi,
>
> I have a table called A and another Table Called B.
>
> A table has the following Column and Values
>
> ID One Two Three
> ---
> 10 test01 test02 test03
> 11 test11 test12 test13
> 12 test21 test22 test23
> 13 test31 test32 test33
>
> B table has the following Column and Values
>
> ID One Two Three
> ---
> 10 abcd01 abcd02 abcd03
> 11 abcd11 abcd12 abcd13
> 12 abcd21 abcd22
> 13 abcd31 abcd32 abcd33
>
> I want to take a backup of Column Three where ID in(11,12,13). I know
> using BCP utility i can take a backup of Table A of Column Three.
> Following is the Query
>
> "select Three,ID from [Test].dbo.A where ID in(11,12,13)" queryout
> c:\try.txt -U sa -P ***
>
> and then i want to restore it in to the Table B's Column Three by
> specifying some Query. Meaning i want to restore the values to the
> Table B's Column Three where ID is the ID which is obtained in
> Backup[Table A]. How can i do this? If anybody knows the solution
> Please Let me know to solve it.
>
> Thanks,
> Vinoth
> Vinoth@.gsdindia.com
>|||Thanks for ur reply,
The table A will be in One database of Some Server and Table B will
be in Some other Database of Some other Server. How can i do it now?
Thanks,
Vinoth|||Use 4-part naming (after adding a linked server):
update B
set
Three = (select A.Three from OtherServer.OtherDB.dbo.A as A where A.ID =
B.ID)
where
ID in (11, 12, 13)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131799665.556244.160530@.g44g2000cwa.googlegroups.com...
> Thanks for ur reply,
>
> The table A will be in One database of Some Server and Table B will
> be in Some other Database of Some other Server. How can i do it now?
> Thanks,
> Vinoth
>|||Hi,
I cant use the above query. Because i dont have the A and B table on
the same time. A table will be deleted. I want to restore the deleted A
table values[ This values wil be kept in a text file using BCP utility]
to the B table. Now could you please tell me how can i do this?
Thanks,
Vinoth
Vinoth@.gsdindia.com|||I still don't understand. You could simply create a new table, say CopyOfA
and put the old data there.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131802194.908738.97710@.g14g2000cwa.googlegroups.com...
> Hi,
>
> I cant use the above query. Because i dont have the A and B table on
> the same time. A table will be deleted. I want to restore the deleted A
> table values[ This values wil be kept in a text file using BCP utility
]
> to the B table. Now could you please tell me how can i do this?
> Thanks,
> Vinoth
> Vinoth@.gsdindia.com
>

How to restore Values to Table based on some Query

Hi,
I have a table called A and another Table Called B.
A table has the following Column and Values
ID One Two Three
---
10 test01 test02 test03
11 test11 test12 test13
12 test21 test22 test23
13 test31 test32 test33
B table has the following Column and Values
ID One Two Three
---
10 abcd01 abcd02 abcd03
11 abcd11 abcd12 abcd13
12 abcd21 abcd22
13 abcd31 abcd32 abcd33
I want to take a backup of Column Three where ID in(11,12,13). I know
using BCP utility i can take a backup of Table A of Column Three.
Following is the Query
"select Three,ID from [Test].dbo.A where ID in(11,12,13)" queryout
c:\try.txt -U sa -P ***
and then i want to restore it in to the Table B's Column Three by
specifying some Query. Meaning i want to restore the values to the
Table B's Column Three where ID is the ID which is obtained in
Backup[Table A]. How can i do this? If anybody knows the solution
Please Let me know to solve it.
Thanks,
Vinoth
Vinoth@.gsdindia.comYou don't need to extract it to a file. If you want to update B from A,
just do:
update B
set
Three = (select A.Three from A where A.ID = B.ID)
where
ID in (11, 12, 13)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131797987.096680.189410@.g14g2000cwa.googlegroups.com...
> Hi,
>
> I have a table called A and another Table Called B.
>
> A table has the following Column and Values
>
> ID One Two Three
> ---
> 10 test01 test02 test03
> 11 test11 test12 test13
> 12 test21 test22 test23
> 13 test31 test32 test33
>
> B table has the following Column and Values
>
> ID One Two Three
> ---
> 10 abcd01 abcd02 abcd03
> 11 abcd11 abcd12 abcd13
> 12 abcd21 abcd22
> 13 abcd31 abcd32 abcd33
>
> I want to take a backup of Column Three where ID in(11,12,13). I know
> using BCP utility i can take a backup of Table A of Column Three.
> Following is the Query
>
> "select Three,ID from [Test].dbo.A where ID in(11,12,13)" queryout
> c:\try.txt -U sa -P ***
>
> and then i want to restore it in to the Table B's Column Three by
> specifying some Query. Meaning i want to restore the values to the
> Table B's Column Three where ID is the ID which is obtained in
> Backup[Table A]. How can i do this? If anybody knows the solution
> Please Let me know to solve it.
>
> Thanks,
> Vinoth
> Vinoth@.gsdindia.com
>|||Thanks for ur reply,
The table A will be in One database of Some Server and Table B will
be in Some other Database of Some other Server. How can i do it now?
Thanks,
Vinoth|||Use 4-part naming (after adding a linked server):
update B
set
Three = (select A.Three from OtherServer.OtherDB.dbo.A as A where A.ID =B.ID)
where
ID in (11, 12, 13)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131799665.556244.160530@.g44g2000cwa.googlegroups.com...
> Thanks for ur reply,
>
> The table A will be in One database of Some Server and Table B will
> be in Some other Database of Some other Server. How can i do it now?
> Thanks,
> Vinoth
>|||Hi,
I cant use the above query. Because i dont have the A and B table on
the same time. A table will be deleted. I want to restore the deleted A
table values[ This values wil be kept in a text file using BCP utility]
to the B table. Now could you please tell me how can i do this?
Thanks,
Vinoth
Vinoth@.gsdindia.com|||I still don't understand. You could simply create a new table, say CopyOfA
and put the old data there.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131802194.908738.97710@.g14g2000cwa.googlegroups.com...
> Hi,
>
> I cant use the above query. Because i dont have the A and B table on
> the same time. A table will be deleted. I want to restore the deleted A
> table values[ This values wil be kept in a text file using BCP utility]
> to the B table. Now could you please tell me how can i do this?
> Thanks,
> Vinoth
> Vinoth@.gsdindia.com
>

Friday, March 30, 2012

How to Schedule Backup for SQL Server 2005 Express

Monday, March 26, 2012

How to run the snapshot agent using the -PublisherLogin ?

Monday, March 19, 2012

How to return time & number format that has set in the regional setting using stored proce

How to return the result (XML) with FOR XML EXPLICIT

How to return the result (XML) with FOR XML EXPLICIT

Monday, March 12, 2012

How to return a varchar from sproc on remote Linked Server?

Friday, March 9, 2012

How To Return a Static Value?

How to retrieve value from Exec stmt?

Wednesday, March 7, 2012

How to retrieve the actions using MDSCHEMA_ACTIONS?

How to retrieve the actions using MDSCHEMA_ACTIONS?

How to retrieve SqlDbType.Bit in SQL server?

Sunday, February 19, 2012

How to restore Values to Table based on some Query

How to restore Values to Table based on some Query

How to restore Values to Table based on some Query

restrict access to database

Blog Archive

About Me