Thanks for the info E4D4...

Ok so after what you send I have this ...

Created an SP XMLTest...

USE [Test]

GO

/****** Object: StoredProcedure [dbo].[XML_Test] Script Date: 04/30/2013 10:29:34 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[XML_Test]

-- Report Parameters

AS

SET NOCOUNT ON

SET ANSI_WARNINGS OFF

-- @StartDate set time to 00:00:00

--Set @StartDate = dbo.usi_fnDatePart(@StartDate)

-- @EndDate set time to 00:00:00 and add one day (To this date at midnight)

--Set @EndDate = dbo.usi_fnDatePart(@EndDate)+1

UPDATE dbo.USER_DEFINED_DATA

SET CAD100 = 'X'

WHERE (dbo.USER_DEFINED_DATA.EffDate = CONVERT(DATETIME, '2012-04-03 00:00:00', 102)) and ASSESSMENT_MONIKER = '00437C0817DB41FB9516F449084ACBF5'

SELECT dbo.ASSESSMENT.AbbrName, dbo.USER_DEFINED_DATA.Expdate, dbo.USER_DEFINED_DATA.CAD4, dbo.USER_DEFINED_DATA.CAD16,

dbo.USER_DEFINED_DATA.CAD100, dbo.USER_DEFINED_DATA.ASSESSMENT_MONIKER

FROM dbo.USER_DEFINED_DATA INNER JOIN

dbo.ASSESSMENT ON dbo.USER_DEFINED_DATA.ASSESSMENT_MONIKER = dbo.ASSESSMENT.OID

WHERE (dbo.USER_DEFINED_DATA.EffDate = CONVERT(DATETIME, '2012-04-03 00:00:00', 102))

for xml auto

I then created this ....

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'dbmailprofile',

@recipients = 'jbalbo@myemail.org',

@query = 'EXEC test.dbo.XML_Test' ,

@subject = 'XML Test',

@attach_query_result_as_file = 1 ;

I get an email but its in a text file and looks nothing like the sp when I run it ???

looks like ....

ML_F52E2B61-18A1-11d1-B105-00805F49916B

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--

0x440841006200620072004E0061006D0065004407450078007000640061007400650044044300410044003400440543004100440031003600440643004100440031003000300044124100530053004500530053004D0045004E0054005F004D004F004E0049004B0045005200440E640062006F002E00410053005300450053

00

0x373444373442373539414546344136423141393437363432430108020610E404000020003438343630444537373444373442373539414546344136423141393437363432430108020610E404000020003438343630444537373444373442373539414546344136423141393437363432430108020610E40400002000343834

36

0x39414546344136423141393437363432430108020610E404000020003438343630444537373444373442373539414546344136423141393437363432430108020610E404000020003438343630444537373444373442373539414546344136423141393437363432430108020310E40400000300317663020610E404000020

00

0x444537373444373442373539414546344136423141393437363432430108020310E4040000010034020610E404000020003438343630444537373444373442373539414546344136423141393437363432430108020610E4040000200034383436304445373734443734423735394145463441364231413934373634324301

08

0x65766573020410E4040000FF0057617272656E2069732063757272656E746C79206F6E2061204348494E532C207768696368207761732066696C6564206F6E20627920746865207363686F6F6C20666F7220686973206F6E676F696E6720747275616E63792069737375652E204865206973207363686564756C656420746F

20

Thanks

Joe