How to Schedule MSDE Backup

Hi guys,
I have an application that runs on MSDE. I need to back up the database on a
dialy basis. The backup file gets written to this folder (C:\backup) and
then I have found a backup software that will automatically write it to a
DVD+RW, overwriting the previous backup file.
To make the schedule easy I will use 31 DVD+RWs, one for each day of the
month. That way I will always have at least a month worth of backups.
I was hoping that somebody could help me with a small SQL script that will
backup my database from Monday thru Saturday, automatically. Also it'd be
great if the backup file would contain a name and the date of that day.
Thanks a lot for your help.
Hi,
MSDE will not come with GUI. So u have to use SQLMAINT.exe. See the below
URL for more info.
http://msdn.microsoft.com/library/de...maint_19ix.asp
Thanks
Hari
SQL Server MVP
"Tom Bombadill" <Genius_poster@.yahoo.com> wrote in message
news:%23D3lQ7zsFHA.2792@.tk2msftngp13.phx.gbl...
> Hi guys,
> I have an application that runs on MSDE. I need to back up the database on
> a dialy basis. The backup file gets written to this folder (C:\backup) and
> then I have found a backup software that will automatically write it to a
> DVD+RW, overwriting the previous backup file.
> To make the schedule easy I will use 31 DVD+RWs, one for each day of the
> month. That way I will always have at least a month worth of backups.
> I was hoping that somebody could help me with a small SQL script that will
> backup my database from Monday thru Saturday, automatically. Also it'd be
> great if the backup file would contain a name and the date of that day.
> Thanks a lot for your help.
>
|||Hi Hari,
Thanks for your attempt to help me. Unfortunately for your suggestion to
work, I have to install SP3a and for some reason I cannot successfully apply
that. I constantly get the "The instance name specified is invalid" error,
even though I did not specify an instance name and there's only one
installaiton of MSDE on my machine.
What do you suggest?
|||hi Tom,
Tom Bombadill wrote:
> Hi Hari,
> Thanks for your attempt to help me. Unfortunately for your suggestion
> to work, I have to install SP3a and for some reason I cannot
> successfully apply that. I constantly get the "The instance name
> specified is invalid" error, even though I did not specify an
> instance name and there's only one installaiton of MSDE on my machine.
> What do you suggest?
you can have a look at a free prj of mine, at the link following my sign,
qhich implements a UI similar to Enterprise Manager where you can define a
SQL Server job to backup your db and of course define your schedules as
desired...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||>
> you can have a look at a free prj of mine, at the link following my
> sign, qhich implements a UI similar to Enterprise Manager where you
> can define a SQL Server job to backup your db and of course define
> your schedules as desired...
alternatively, you can prepare a cmd file like
<-->
OSQL -Usa -Pyour_pwd -Q"BACKUP DATABASE pubs TO DISK = 'C:\Pubs.bak' WITH
INIT" >c:\err.txt
<-->
and schedule it via standard OS AT or SCHTASKS, the native Win scheduler...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea,
You're an angel, thanks a lot!
I downloaded your interface and installed it. I scheduled a backup and will
be testing this thoroughly.
A couple of questions re the interface if you don't mind:
1- I don't want to accumulate backups to the same file, since I will be
taking a fresh full backup on a separate DVD+RW everyday. How do I make sure
the backups do not keep appending to the same file day after day? Do I do
that just by leaving the ADD box unchecked, in the backup properties?
2- How can make it so that the backup file contains the day of the backup?
Thanks again,
|||hi Tom,
Tom Bombadill wrote:
> Andrea,
> You're an angel, thanks a lot!
you are wellcome.. thank you :D

> I downloaded your interface and installed it. I scheduled a backup
> and will be testing this thoroughly.
> A couple of questions re the interface if you don't mind:
> 1- I don't want to accumulate backups to the same file, since I will
> be taking a fresh full backup on a separate DVD+RW everyday. How do I
> make sure the backups do not keep appending to the same file day
> after day? Do I do that just by leaving the ADD box unchecked, in the
> backup properties?
leaving the "add to media" check box unchecked will add the "WITH INIT"
statement to the full backup statement, so that every backup set will be
initialized..

> 2- How can make it so that the backup file contains the day of the
> backup?
can you please expand on this? I'm sorry but I did not understand your
requirement..
and for DbaMgr2k related questions please feel free to contact me directly,
in order not to be OT on this public microsoft NG...
thank you
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
[vbcol=seagreen]
What I mean by that is to have the backup file name include the date the
backup was performed. I'll give you an example:
For today, it would be called "SJDB 09072005.bak"
For tomorrow, "SJDB 09082005.bak"
For the day after tomorrow, "SJDB 09092005.bak"
And so on...
I hope that makes sense!
Andrea, once again thank you for your service to the community and sharing
your hard work with others.
|||Also Andrea,
Do you know how long it would take for a full backup to run for a full size
MSDE database (2048 MB)?
I ask because I wanted to know how much time to allocate before I schedule
the copy of the backup file to DVD.
Thanks again,
|||hi Tom,
Tom Bombadill wrote:
> Hi Andrea,
>
> What I mean by that is to have the backup file name include the date
> the backup was performed. I'll give you an example:
> For today, it would be called "SJDB 09072005.bak"
> For tomorrow, "SJDB 09082005.bak"
> For the day after tomorrow, "SJDB 09092005.bak"
> And so on...
> I hope that makes sense!

ok, you have to "edit" the backup statement of the generated job's step ...
assuming you are backing up pubs database to C:\ (replace C:\ with the
existing folder you want to backup to), you can provide the file name to
include the current date casting (
http://msdn.microsoft.com/library/de...ca-co_2f3o.asp )
GETDATE() function result as required..
the final statement will look like:
DECLARE @.FileName nvarchar(25)
SET @.FileName = 'C:\' + 'pubs ' + CONVERT(varchar(10) , GETDATE() , 110 ) +
'.bak'
BACKUP DATABASE [pubs] TO DISK = @.FileName WITH INIT ,
NOUNLOAD ,
NAME = N'pubs BackUp',
NOSKIP ,
STATS = 10,
NOFORMAT
as regard your date format, I'd prefer the standard ISO format, thats to say
YYYYMMDD (CONVERT using 112 style), as this data format , 20050908 (for
today) is more readable and sorts better then 09082005..
you can go further, in the "advanced tab", and specify you want to "output"
the result of the execution (like standard messages and/or errors, if any)
to a text file.. if you want to, specify a file name (a text file) in the
"Transact-SQL script command options" ->output file
this will output, in case of success, something like
<-->
Job 'BackUp DB ['pubs'] - #08/09/2005 12.25.12#' : Step 1, 'BackUp DB
['pubs']' : Began Executing 2005-09-08 12:37:16
53 percent backed up. [SQLSTATE 01000]
99 percent backed up. [SQLSTATE 01000]
Processed 224 pages for database 'pubs', file 'pubs' on file 1. [SQLSTATE
01000]
100 percent backed up. [SQLSTATE 01000]
Processed 1 pages for database 'pubs', file 'pubs_log' on file 1. [SQLSTATE
01000]
BACKUP DATABASE successfully processed 225 pages in 0.242 seconds (7.586
MB/sec). [SQLSTATE 01000]
<-->
or the problem found, if any, you can inspect...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

How to schedule an SSIS package that invokes a web service

Hey,

I have an SSIS package that invokes a web service and then updates a table. It runs fine as long as I am running it on the local machine. However, as soon as I save this package to the sql server, and try to schedule this as a job, it starts to fail. Now, the web service writes to an xml file and also uses an xsd and and an xsl file. When I save a dts package to the sql server, whats the proper way of referencing these files? I think this probably is what is making the package to fail, ut I am not sure.

Any help is greatly appreciated!!

Thanks!

You should use a configuration (right-click in the package and choose configurations) to set the ConnectionString property of the connection managers for the files. Or you could use expressions to set the connection strings (paths and filenames) based on variables. The variables can be set at runtime using the /SET option of DTEXEC.|||Also make sure you've configured SSIS logging, so you can find out why the package fails now or (once you fix the problem and go to production) if something goes wrong with scheduled package in production.|||That depends on where you want to keep them. I prefer to keep them in files on the disk. If your package is in SQL and you prefer to avoid the disk entirely, you can keep them in the database and just load them into variables via the Execute SQL task. The XML task and the XML Source component support receiving the XSD/XSLT from variables.
|||Thanks a lot for the suggestions. I will try them out and see how it works.|||

Hey,

Sorry for this delayed reply. Since I posted this question a lot of issues cropped up with my SQL server which eventually led to a total reinstallation of all apps on my pc. Anyway, I discovered that the problem I have been having was because of permission issues. I was able to fix that problem and just when I thought that I had everything going, I came across a new problem. After I save the SSIS package in sql server and create a job, the job starts failing. This is the error that I am getting:

-1073548540,0x,An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: The Web Service threw an error during method execution. The error is: Unable to connect to the remote server.

Any suggestions would be very helpful.

Thanks!

|||Could it be a authentication issue? Is there any security on the web service?|||

Guys!!Thanks a lot for all the suggestions. Really appreciate your help. Problem was a combination of many issues. One was related to 32bit/64bit differences, the other was authentication, and finally some syntax problems when invoking the web service. Seems it is working really well now.

Thanks again!

how to run sql log shipping manually

Hi everyone

In my project, sometimes, I need to run log shipping manually rather that it runs automally when the schedule occured.

manually run log shipping means call the log shipping in code.

Thanks.

Are you using SQL2000 or SQL2005?

====================
For SQL2000, log shipping usesSqlmaint.exe to back up and to restore databases. When SQL Server creates a transaction log backup as part of a log shipping setup, Sqlmaint.exe connects to the monitor server and updates the log_shipping_primaries table with the last_backup_filename information. Similarly, when you run a Copy or a Restore job on a secondary server, Sqlmaint.exe connects to the monitor server and updates the log_shipping_secondaries table.

====================

Fro SQL2005,?after we enable and configure log shipping for the specific database in SQL Server 2005, several agent jobs are created to maintain a series of steps for log shipping. Generally, this includes a backup job for primary server, a copy and a restore job for secondary server and an alert job for monitor server.

While SQL Agent is running, the job invocation engine monitors the schedule and prepares to launch the job next time. Then, the job manager fetches the detailed steps of the active job from msdb tables and executes it. As for the backup/copy/restore jobs, they involve cmdshell subsystem to launch the external executable sqllogship.exe (SMO application) to finish the specific tasks. Sqllogship.exe will update the information in log_shipping_monitor_primary, log_shipping_monitor_secondary, log_shipping_monitor_history_detail, and log_shipping_monitor_error_detail tables on primary or secondary server. This is implemented by running some undocumented log shipping stored procedures in master database, including:

sp_MSadd_log_shipping_history_detail

sp_MSadd_log_shipping_error_detail

and directly modify those correlated columns in log_shipping_monitor_primary, log_shipping_monitor_secondary tables.

At the end of the specific task in each sqllogship.exe execution, SQL Server will issue a series of steps to synchronize the information between primary/secondary server and the monitor server. This is also implemented by running some undocumented log shipping stored procedures in master database, including:

sp_processlogshippingmonitorprimary

sp_processlogshippingmonitorsecondary

sp_processlogshippingmonitorhistory

sp_processlogshippingretentioncleanup

If the monitor server is a remote machine to the SQL box where the sqllogship.exe is running, a distributed query for these sprocs with linked server built in-house is being used to update the info on the remote monitor server.

Next, when each job is finished, the correlated job history info is recorded in the msdb tables by the SQL Agent.|||your answer is very nice, thank you very much !|||I'm glad that I can help Happy new year my friend!sql

How to run an Access file from a SQL job?

I have an Access file that I'm using to move some data around. I want to run it from a SQL job. How do I do that? The Access file runs a command using AutoExec, so it doesn't have any interaction or anything. It just runs and closes itself. Thanks.

pse don't duplicate.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1947186&SiteID=1

Madhu

How to run Access file from SQL job?

I have an Access file that I'm using to move some data around. I want to run it from a SQL job. How do I do that? The Access file runs a command using AutoExec, so it doesn't have any interaction or anything. It just runs and closes itself. Thanks.

use XP_cmdshell

Executes a given command string as an operating-system command shell and returns any output as rows of text. Grants nonadministrative users permissions to execute xp_cmdshell.

check in BOL. IF its sql server 2005 you have to enable it using SQL Server 2005 features.

Madhu

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

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
(
@.MSISDNas varchar(20),
@.SessionIdas 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
<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
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/library/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 Kasm, 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 you
> need to use the TYPE directive.http://technet.microsoft.com/en-us/library/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 Kasm, 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 you
> need to use the TYPE directive.http://technet.microsoft.com/en-us/library/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 Kasm, 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 you
> need to use the TYPE directive.http://technet.microsoft.com/en-us/library/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?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