question

muk avatar image
muk asked

output stored procedure to xml through dbmail

Hi all, I am trying to take the output of this stored procedure in XML format with tags like etc... and send it to a user through databasemail however in testing, the output comes out correctly as a hyper link in the results window of management studio but the email displays something like this: 0x440372006F007700440A4A00300033005F0045004D0050005F0049004400440D4A00300033005F004A004F0042005F005400490054004C004500440A4A00300033005F004F0046004600490043004500440B4A00300033005F00540045004C005F00450058005400440C4A00300033005F004400490052005F004400490041004C00440A4A00300033005F004F004E004C0049004E004500440D4A00300033005F0044004500500054005F004C004F004E004700440C4A00300033005F004200550049004C00440049004E004700440D4A00300033005F005300490047004E0041005400550052004500010102000978006D006C006E0073003A0078007300690011520068007400740070003A002F002F007700770077002E00770033002E006F00720067002F0032003000300031002F0058004D004C0053006300680065006D0061002D0069006E007300740061006E0063006500410102410310E404000007003030303030303681020103410310E404000016004D6173746572206F662074686520556E69766572736581030104410310E404000004003130323281040105410310E404000016002833313229203432372D323733372045787420383030810501060200077800730069003A006E0069006C000601430107410310E404000001004E81070108410310E40400001E00496E666F726D6174696F6E20546563686E6F6C677920536572766963657381080109410310E40400000800506C796D6F7574688109010A410310E404000001004E810A8101010102000978006D006C006E0073003A0078007300690011520068007400740070003A002F002F007700770077002E00770033002E006F00720067002F0032003000300031002F0058004D004C0053006300680065006D0061002D0069006E007300740061006E0063006500410102410310E404000007003030303030343081020103410310E40400000E004D617374657220416E616C79737481030104410310E4040000030037333381040105410310E404000016002833313229203432372D323733372045787420373931810501060200077800730069003A006E0069006C000601430107410310E404000001004E81070108410310E40400001E00496E666F726D6174696F6E20546563686E6F6C6779205365727669636573810801090200077800730069003A006E0069006C00060143010A410310E4040000010059810A8101 (1 rows affected) Here is the stored procedure: USE [coll18_test02] GO /****** Object: StoredProcedure [dbo].[Populate_Emp_Directory] Script Date: 07/17/2013 09:25:08 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[Populate_Emp_Directory] AS BEGIN declare @query nvarchar(max) set @query = N'SELECT [J03_EMP_ID] as [ID]' + N',[J03_JOB_TITLE] as [JOB_TITLE]' + N',[J03_OFFICE] as [OFFICE]' + N',[J03_TEL_EXT] as [EXT]' + N',[J03_DIR_DIAL] as [DIRECT_DIAL]' + N',[J03_ONLINE] as [DISPLAY_ONLINE_FLAG]' + N',[J03_DEPT_LONG] as [DEPARTMENT]' + N',[J03_BUILDING] as [BUILDING]' + N',[J03_SIGNATURE] as [SIGNATURE]' + N'FROM [coll18_test02].[dbo].[J03_EMP_DIR]' + N'FOR XML PATH, ELEMENTS XSINIL' exec msdb.dbo.sp_send_dbmail @ recipients='muk@jmls.edu', @query=@query, @execute_query_database='coll18_test02', @query_no_truncate=1, @exclude_query_output=1, @query_result_width=32767, @profile_name = 'test' END Anyone see what is wrong here???? Is there another way to do this? I need to send this xml file in this format with as least manual intervention as possible so I thought I could make this stored procedure a weekly job to just create the xml file and send the email. Help!!!!!!
sql-server-2008sql-server-2008-r2stored-proceduresxml
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
That looks like the varbinary representation of the xml. Not sure why you are getting that as when I replicate your query on my environment, I do get XML in the email. You could try explicitly casting the XML to varchar(max) set @query = N' select convert(varchar(max), (select( SELECT [J03_EMP_ID] as [ID] ,[J03_JOB_TITLE] as [JOB_TITLE] ,[J03_OFFICE] as [OFFICE] ,[J03_TEL_EXT] as [EXT] ,[J03_DIR_DIAL] as [DIRECT_DIAL] ,[J03_ONLINE] as [DISPLAY_ONLINE_FLAG] ,[J03_DEPT_LONG] as [DEPARTMENT] ,[J03_BUILDING] as [BUILDING] ,[J03_SIGNATURE] as [SIGNATURE] FROM [coll18_test02].[dbo].[J03_EMP_DIR] FOR XML PATH, ELEMENTS XSINIL ))) as XMLforEmail'
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

That did the trick @Kev Riley thank you so much!!!!!!!!!!!!!!!
0 Likes 0 ·
muk avatar image
muk answered
@Kev Riley or anyone else, does anyone know how I can insert line breaks between the tags on this?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.