January 5, 2006 at 9:53 am
I have a stored procedure that uses FOR XML EXPLICIT to produce data.
What I want to do is use DTS to FTP the results of the stored procedure.
I cannot see any obvious way to do this, can anyone give me a few hints?
January 5, 2006 at 11:57 am
Well, if you've already got the file, you can use a component from http://www.sqldts.com to do FTP uploads. Ideally the filename won't change. If it does change, this will be tricky as they use XML for their parameters (stored in the DTS code). You can also use VBScript, but I'm not sure exactly how - there are examples out there.
-Pete
January 6, 2006 at 1:13 am
If I could get the file then FTPing it is pretty straight forward.
My problem is that my query produces the XML I want but if I try and use a data transformation to write it out I get a file with no XML but a huge string of hex.
I tried using OSQL to write out the results of the query but that just introduced loads of white space and new line characters every 80 characters.
I know I can set the length of a line but the problem is I don't know how long the line will be. I might run the query one day and the resulting XML may be 1000 characters long, I might run it the next day and it will be 100,000 characters long. The result length is unknown.
January 6, 2006 at 2:04 am
OK I have got my file by ditching the FOR XML command and using the sp_makewebtask stored procedure with a template that has my XML structure in it.
If I keep the FOR XML command then provided that the returned XML is short I can have a single command. If the returned XML is long then at some point it throws in a line-feed which means that the XML is not well formed.
This begs the question why what use is the FOR XML command with this limitation?
January 6, 2006 at 2:45 am
Check out this post..it may help.
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=21&messageid=247772#bm248068
Thanks Jeet
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply