SQL Server 2005, Email XML File
I have created an xml document using the For Explicit. I would now like to email it using the sp_send_dbmail comment and attach it as an xml file.
How do I do this?
How do I do this?
ASKER
The problem is, how to i get the results of the for xml to be an actual xml file.
I can run the query and generate the xml data fine, but i need to automatically save the results to a file so i can automatically email it.
Hope this makes sense.
I can run the query and generate the xml data fine, but i need to automatically save the results to a file so i can automatically email it.
Hope this makes sense.
This example sends an e-mail message Database Mail attaches the result as a text file
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Mail Profile',
@recipients = 'johan.rosengren@AAA.se',
@query = 'SELECT * FROM [DATABASE].dbo.[TABLE] FOR XML AUTO' ,
@subject = 'XML File',
@attach_query_result_as_fi
@query_attachment_filename
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Mail Profile',
@recipients = 'johan.rosengren@AAA.se',
@query = 'SELECT * FROM [DATABASE].dbo.[TABLE] FOR XML AUTO' ,
@subject = 'XML File',
@attach_query_result_as_fi
@query_attachment_filename
ASKER
My query is very cumbersome. There is nothing that you can doing after the select to tell it to save to a file?
ASKER
Don't think this works. This is what I get in Test.xml:
XML_F52E2B61-18A1-11d1-B10
--------------------------
--------------------------
--
0x44094D006100730074006500
0072006D005F00490044004413
00
0x440053005400020242260000
00500043010902011108004500
02
(2 rows affected)
XML_F52E2B61-18A1-11d1-B10
--------------------------
--------------------------
--
0x44094D006100730074006500
0072006D005F00490044004413
00
0x440053005400020242260000
00500043010902011108004500
02
(2 rows affected)
Since For Xml does not return a resultset, but rather a stream, the only way you could do this in the past (with SQL Server 2000) was using some application to retrieve the stream. SQL Server 2005 has added some enhancements and now supports a new keyword (TYPE) in the FOR XML clause. Perhaps you can use that to return the Xml as an Xml data type and so send it in an email. But I have never done it that way (and probably would not consider doing it that way).
Incidentally, the reason you are getting it returned in segments of 256 bytes has to do with the setting for the @query_no_truncate flag.
It looks like your question was answered in BOL. See here:
SQL 2005 sp_send_dbmail with XML in body or as attachment outputs HEX not XML
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=633800&SiteId=1
SQL 2005 sp_send_dbmail with XML in body or as attachment outputs HEX not XML
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=633800&SiteId=1
ASKER
This is the output i'm getting now. i need this to make an actual xml file. why does it have the rowcount at the end?
<scales_fpnb.dbo.Lookup_Pr
(1 rows affected)
This is the call I'm using:
EXEC msdb.dbo.sp_send_dbmail
@Profile_name = 'DBMailProfile',
@recipients = 'myEmail@xxxx.com',
@query = 'SELECT * FROM [scales_fpnb].dbo.[Lookup_
@subject = 'XML File',
@attach_query_result_as_fi
@query_result_header = 0,
@query_attachment_filename
<scales_fpnb.dbo.Lookup_Pr
(1 rows affected)
This is the call I'm using:
EXEC msdb.dbo.sp_send_dbmail
@Profile_name = 'DBMailProfile',
@recipients = 'myEmail@xxxx.com',
@query = 'SELECT * FROM [scales_fpnb].dbo.[Lookup_
@subject = 'XML File',
@attach_query_result_as_fi
@query_result_header = 0,
@query_attachment_filename
ASKER
ok i added set nocount on, set nocount off to my query, and now i don't see the number of rows affected at the end.
here is the last part of my problem....
how do i make it line break at the end of a row??
right now it is just making one very big line, so it won't let me open it as an xml file.
Here is a copy of my file generated....
<root><scales_fpnb.dbo.loo
p_id>58</product_group_id>
8 Ft. (2.50M) Roundwood</product_group_D
lookup_product_group><scal
><product_group_id>7</prod
>Pulp Small Chips</product_group_Descr
roduct_group></root>
here is the last part of my problem....
how do i make it line break at the end of a row??
right now it is just making one very big line, so it won't let me open it as an xml file.
Here is a copy of my file generated....
<root><scales_fpnb.dbo.loo
p_id>58</product_group_id>
8 Ft. (2.50M) Roundwood</product_group_D
lookup_product_group><scal
><product_group_id>7</prod
>Pulp Small Chips</product_group_Descr
roduct_group></root>
>>how do i make it line break at the end of a row??<<
Xml files do not have rows and therefore no line breaks.
>>right now it is just making one very big line, so it won't let me open it as an xml file. <<
Try opening it with IE or some other Xml aware editor.
Xml files do not have rows and therefore no line breaks.
>>right now it is just making one very big line, so it won't let me open it as an xml file. <<
Try opening it with IE or some other Xml aware editor.
I just did and this is what it looks like:
<root>
<scales_fpnb.dbo.lookup_pr
<product_group_id>44</prod
<product_group_Description
</scales_fpnb.dbo.lookup_p
<scales_fpnb.dbo.lookup_pr
<product_group_id>58</prod
<product_group_Description
</scales_fpnb.dbo.lookup_p
<scales_fpnb.dbo.lookup_pr
<product_group_id>5</produ
<product_group_Description
</scales_fpnb.dbo.lookup_p
<scales_fpnb.dbo.lookup_pr
<product_group_id>35</prod
<product_group_Description
</scales_fpnb.dbo.lookup_p
<scales_fpnb.dbo.lookup_pr
<product_group_id>43</prod
<product_group_Description
</scales_fpnb.dbo.lookup_p
<scales_fpnb.dbo.lookup_pr
<product_group_id>7</produ
<product_group_Description
</scales_fpnb.dbo.lookup_p
<scales_fpnb.dbo.lookup_pr
<product_group_id>28</prod
<product_group_Description
</scales_fpnb.dbo.lookup_p
<scales_fpnb.dbo.lookup_pr
<product_group_id>8</produ
<product_group_Description
</scales_fpnb.dbo.lookup_p
</root>
<root>
<scales_fpnb.dbo.lookup_pr
<product_group_id>44</prod
<product_group_Description
</scales_fpnb.dbo.lookup_p
<scales_fpnb.dbo.lookup_pr
<product_group_id>58</prod
<product_group_Description
</scales_fpnb.dbo.lookup_p
<scales_fpnb.dbo.lookup_pr
<product_group_id>5</produ
<product_group_Description
</scales_fpnb.dbo.lookup_p
<scales_fpnb.dbo.lookup_pr
<product_group_id>35</prod
<product_group_Description
</scales_fpnb.dbo.lookup_p
<scales_fpnb.dbo.lookup_pr
<product_group_id>43</prod
<product_group_Description
</scales_fpnb.dbo.lookup_p
<scales_fpnb.dbo.lookup_pr
<product_group_id>7</produ
<product_group_Description
</scales_fpnb.dbo.lookup_p
<scales_fpnb.dbo.lookup_pr
<product_group_id>28</prod
<product_group_Description
</scales_fpnb.dbo.lookup_p
<scales_fpnb.dbo.lookup_pr
<product_group_id>8</produ
<product_group_Description
</scales_fpnb.dbo.lookup_p
</root>
ASKER
when i double click on the xml file, it gives an ie error message.
if i go in and actually break it up the way you are displaying it, then it will open in ie for me.
so why does it not work when it is all pushed together?
if i go in and actually break it up the way you are displaying it, then it will open in ie for me.
so why does it not work when it is all pushed together?
ASKER
This is the error I get...
The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.
--------------------------
A name was started with an invalid character. Error processing resource
The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.
--------------------------
A name was started with an invalid character. Error processing resource
This has to be after you email it, right? If so make sure to use @body_format = HTML
Never mind I see that you are sending the Xml as an attachment so that parameter si probably not going to help. But in any case, please confirm the format you are using and if the problem only occurs when you receive it via email. What happens when you try the query from SMSS?
ASKER
When I run the query, it says mail queued. This is the query i'm running...
EXEC msdb.dbo.sp_send_dbmail
@Profile_name = 'DBMailProfile',
@recipients = @emailaddy
@query = 'set nocount on; SELECT product_group_id, product_group_Description from scales_fpnb.dbo.[lookup_pr
@subject = 'XML File',
@attach_query_result_as_fi
@query_result_header = 0,
@query_no_truncate = 1,
@query_attachment_filename
The problem occurs when I try to open the xml file from the email. So, if I edit the file, I see that the file does not have a new line for each tag, sometimes the line breaks in the middle of a tag. If I edit the file and put the line breaks after each tag, manually, then it opens as a normal xml file.
EXEC msdb.dbo.sp_send_dbmail
@Profile_name = 'DBMailProfile',
@recipients = @emailaddy
@query = 'set nocount on; SELECT product_group_id, product_group_Description from scales_fpnb.dbo.[lookup_pr
@subject = 'XML File',
@attach_query_result_as_fi
@query_result_header = 0,
@query_no_truncate = 1,
@query_attachment_filename
The problem occurs when I try to open the xml file from the email. So, if I edit the file, I see that the file does not have a new line for each tag, sometimes the line breaks in the middle of a tag. If I edit the file and put the line breaks after each tag, manually, then it opens as a normal xml file.
ASKER
I've read some different posts about ansi/unicode. Not sure if that is part of my problem, since I don't really understand what the difference is, or how it applies to what i'm doing. But there seems to be others that are having problems that the email cannot be sent as ansi.
>>When I run the query, it says mail queued. <<
I meant what happens when you run the quey on its own as in:
SELECT product_group_id, product_group_Description from scales_fpnb.dbo.[lookup_pr
I meant what happens when you run the quey on its own as in:
SELECT product_group_id, product_group_Description from scales_fpnb.dbo.[lookup_pr
ASKER
I get a normal xml file. Well actually i first get a clickable resultset, and when i click on it, it opens an xml file. formatted properly.
<root>
<scales_fpnb.dbo.lookup_pr
<product_group_id>44</prod
<product_group_Description
</scales_fpnb.dbo.lookup_p
<scales_fpnb.dbo.lookup_pr
<product_group_id>58</prod
<product_group_Description
</scales_fpnb.dbo.lookup_p
<scales_fpnb.dbo.lookup_pr
<product_group_id>5</produ
<product_group_Description
</scales_fpnb.dbo.lookup_p
<scales_fpnb.dbo.lookup_pr
<product_group_id>35</prod
<product_group_Description
</scales_fpnb.dbo.lookup_p
<scales_fpnb.dbo.lookup_pr
<product_group_id>43</prod
<product_group_Description
</scales_fpnb.dbo.lookup_p
<scales_fpnb.dbo.lookup_pr
<product_group_id>7</produ
<product_group_Description
</scales_fpnb.dbo.lookup_p
<scales_fpnb.dbo.lookup_pr
<product_group_id>28</prod
<product_group_Description
</scales_fpnb.dbo.lookup_p
<scales_fpnb.dbo.lookup_pr
<product_group_id>8</produ
<product_group_Description
</scales_fpnb.dbo.lookup_p
</root>
<root>
<scales_fpnb.dbo.lookup_pr
<product_group_id>44</prod
<product_group_Description
</scales_fpnb.dbo.lookup_p
<scales_fpnb.dbo.lookup_pr
<product_group_id>58</prod
<product_group_Description
</scales_fpnb.dbo.lookup_p
<scales_fpnb.dbo.lookup_pr
<product_group_id>5</produ
<product_group_Description
</scales_fpnb.dbo.lookup_p
<scales_fpnb.dbo.lookup_pr
<product_group_id>35</prod
<product_group_Description
</scales_fpnb.dbo.lookup_p
<scales_fpnb.dbo.lookup_pr
<product_group_id>43</prod
<product_group_Description
</scales_fpnb.dbo.lookup_p
<scales_fpnb.dbo.lookup_pr
<product_group_id>7</produ
<product_group_Description
</scales_fpnb.dbo.lookup_p
<scales_fpnb.dbo.lookup_pr
<product_group_id>28</prod
<product_group_Description
</scales_fpnb.dbo.lookup_p
<scales_fpnb.dbo.lookup_pr
<product_group_id>8</produ
<product_group_Description
</scales_fpnb.dbo.lookup_p
</root>
Ok. That means the problem occurs only when you email it using sp_send_dbmail. Perhaps you need to set @query_result_width to some value bigger than the default 256.
But as I have never used sp_send_dbmail to send mail and/or attachments, I really don't know.
I would also try something like this:
Declare @SQL varchar(MAX)
Set @SQL = 'SELECT CAST((SELECT product_group_id, product_group_Description from scales_fpnb.dbo.[lookup_pr
EXEC msdb.dbo.sp_send_dbmail
@Profile_name = 'DBMailProfile',
@recipients = @emailaddy
@query = @SQL,
@subject = 'XML File',
@attach_query_result_as_fi
@query_result_header = 0,
@query_no_truncate = 1,
@query_attachment_filename
But as I have never used sp_send_dbmail to send mail and/or attachments, I really don't know.
I would also try something like this:
Declare @SQL varchar(MAX)
Set @SQL = 'SELECT CAST((SELECT product_group_id, product_group_Description from scales_fpnb.dbo.[lookup_pr
EXEC msdb.dbo.sp_send_dbmail
@Profile_name = 'DBMailProfile',
@recipients = @emailaddy
@query = @SQL,
@subject = 'XML File',
@attach_query_result_as_fi
@query_result_header = 0,
@query_no_truncate = 1,
@query_attachment_filename
ASKER
Here is my call.....
Declare @SQL varchar(MAX)
Set @SQL = 'set nocount on; SELECT CAST((SELECT product_group_id, product_group_Description from scales_fpnb.dbo.[lookup_pr
EXEC msdb.dbo.sp_send_dbmail
@Profile_name = 'DBMailProfile',
@recipients = @emailaddy,
@query = @sql,
@query_result_width = 20000,
@subject = 'XML File',
@attach_query_result_as_fi
@query_result_header = 0,
@query_no_truncate = 1,
@query_attachment_filename
@body_format = HTML;
Here is my xml file when I edit it after receiving it in notepad...
<root><scales_fpnb.dbo.loo
still not formatted properly.
this is the error:
The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.
--------------------------
A name was started with an invalid character. Error processing resource
Declare @SQL varchar(MAX)
Set @SQL = 'set nocount on; SELECT CAST((SELECT product_group_id, product_group_Description from scales_fpnb.dbo.[lookup_pr
EXEC msdb.dbo.sp_send_dbmail
@Profile_name = 'DBMailProfile',
@recipients = @emailaddy,
@query = @sql,
@query_result_width = 20000,
@subject = 'XML File',
@attach_query_result_as_fi
@query_result_header = 0,
@query_no_truncate = 1,
@query_attachment_filename
@body_format = HTML;
Here is my xml file when I edit it after receiving it in notepad...
<root><scales_fpnb.dbo.loo
still not formatted properly.
this is the error:
The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.
--------------------------
A name was started with an invalid character. Error processing resource
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
this is the work around i am using. i am creating my xml file using bcp. i am calling my select from a sp, so the bcp command is actually just calling a sp. i am calling the bcp by using the xp_cmdshell. i am then emailing the file created. i have posted other questions concerning how to get bcp working properly, if anyone else has the same problem.
i hope this helps someone else since i spent quite a bit of time getting this working, and to me, this should have been something that worked easily.
i hope this helps someone else since i spent quite a bit of time getting this working, and to me, this should have been something that worked easily.
@profile_name = 'Mail Profile',
@recipients = 'johan.rosengren@AAA.se',
@query = 'SELECT COUNT(*) FROM [DATABASE].dbo.[TABLE]' ,
@subject = 'Work Order Count',
@attach_query_result_as_fi
@query_attachment_filename