February 27, 2005 at 3:19 pm
Dearest commerads ,
I have been asked to send an XML document via our SQLSERVER server. The idea is to set up a job to run a store Procedure which will email an XML file.
The problem is the query (in the SP) produces an XML document which is truncated and continues on the next line below. So when you try to open the Document it errors?
Am i missing something here?
Any help would be appreciated.
Thanks
Ahsen
February 27, 2005 at 10:41 pm
it has to be in the form of a data stream - bcp or ado
see this for suggestions:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=21&messageid=160112
March 10, 2005 at 9:13 pm
Copy from the query analyzer to notepad, stuff it between <root></root> tags and email it. Make sure that the number of characters per column is not exceeded[ tools->options->Editor tab].
Read teh following articles as they are pertinent to thsi discussion:
http://www.aspfree.com/c/a/MS-SQL-Server/XML-and-the-SQL-2000-Server-Part2/
also read teh part 1, of this article
March 15, 2005 at 12:16 pm
I have been successful creating an xml file using bcp. However, I now need to have a sql query return an xml file in memory, not to an actual file. Is ADO what I should be using?
March 15, 2005 at 7:54 pm
I am pretty sure you could use ado but I dont have a lot of experience with it so I am not able to help much beyond that.
If you want to do it entirely from sql you could also use xp_sendmail to sent the file.
March 16, 2005 at 9:24 am
The problem I am encountering happens when I don't use bcp. The xml file contains carriage returns and line feeds without bcp. I am looking for a way to generate an xml file without carriage returns and line feeds. Is there a setting in Query Analyzer that determines the format of the xml file returned?
March 16, 2005 at 4:53 pm
Yes, thats right. It needs to be a datastream. You can still use bcp but you would need to attach the file to an email to send it - thats what i was getting at before.
From QA you can run Dbcc traceon(257) for each session and turn off column headers. This will format the xml correctly but that is not ideal for exporting or automation.
This book has some really good suggestions and alternatives. It has some ado examples that might help and there is a tool to get xml out in the code (i have not used it)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply