Attach query results as xml with SP_SEND_DBMAIL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Attach query results as xml with SP_SEND_DBMAIL

Author

Topic

pug2694328
Posting Yak Master

166 Posts

Posted - 2008-06-10 : 10:43:43

I'm trying to get sp_send_dbmail to attach the @query result set as an xml file.

The email is being sent and the attachment is present, but rather than ascii xml, it contains a chunk of nonsense like this
0x440352004F005700440470006B00650079004409660069007200730074006E0061006D00650044086C006100730074006E0061006D0065004410700068

I've tested the query with the first bit of code and sent the email with the second bit. I think I can jimmyjack it to write the xml to a temp file on the server and send that file using the @file_attachments parameter, but I'm hoping someone out there knows how to get this one to work.

Thanks in advance for any help you can give me.

Here's my code:

-- test that xml output is being generated
DECLARE @OUTPUT xml
SET @OUTPUT = (SELECT * FROM database..table FOR XML PATH('ROW'), root('ROOT'))
SELECT @OUTPUT

-- run the email proc
EXEC msdb.dbo.sp_send_dbmail
@QUERY = 'SET NOCOUNT ON SELECT * FROM database..table FOR XML PATH(''ROW''), root(''ROOT'')'
, @RECIPIENTS = 'userid@companyname.COM'
, @subject = 'Subject'
, @attach_query_result_as_file = 1
, @query_attachment_filename = 'ATTACHMENT.xml'
, @query_result_width = 32767
, @query_no_truncate = 1
, @append_query_error = 1
, @query_result_header = 1

pug2694328
Posting Yak Master

166 Posts

Posted - 2008-06-11 : 10:12:36

bonk

Ranja
Starting Member

1 Post

Posted - 2010-08-21 : 06:59:06

@QUERY = 'SET NOCOUNT ON SELECT * FROM database..table FOR XML PATH(''ROW''), root(''ROOT''), TYPE'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources