SQL Server output

Larry Brister
Larry Brister used Ask the Experts™
on
How do I take the SQl Statement like below
Set @bodyMail = @header + @textMessage + @tableHeader + @content + @signature + @footer

And email it as an attachment instead of as the body in the attached snippet?
EXEC msdb.dbo.sp_send_dbmail 
         @profile_name='Sql Server', 
         @recipients='lbrister@mycompany.com',
         @subject='Funder OC Review',
         @body=@bodyMail,
         @body_format ='HTML'

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior .Net Developer
Commented:
http://msdn.microsoft.com/en-us/library/ms190307.aspx

    [ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]
    [ , [ @query_attachment_filename = ] query_attachment_filename ]

Default max size is 1MB.  See here if you need more:
http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/c289dd4d-9ffa-4afe-905e-b51a83811ff3
lcohanDatabase Analyst
Commented:
Or like this:


declare @report_file_name varchar(30),
            @sql varchar(max),
            @emailbody varchar(4000)
      
      set @sql = N'SET NOCOUNT ON
                        SELECT whatever FROM whatever_table WHERE whatever'

      SET @report_file_name = 'file_list_at_' + convert(varchar(10),getdate(), 112) + '.txt'
      SET @emailbody = 'Here is the list you wanted as today(yyyy.mm.dd):  '+ convert(varchar(10),getdate(), 102)
      EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'yourSQLMAil smtp', -- mail profile here
            @recipients='mail@mailserver.com',
            @subject = 'File Content',
            @body = @emailbody,
            @body_format = 'TEXT',
            @query = @sql,
            @attach_query_result_as_file = 1,
            @execute_query_database = 'YourDB', -- your database name here
            @query_attachment_filename = @report_file_name,
            @query_result_header = 1,
            @query_result_separator = '      ',
            @query_result_width = 1000;


Larry Bristersr. Developer

Author

Commented:
Hey guys
Both are great answers...any problem splitting points with a bit more to ged325 for being firts?
Kyle AbrahamsSenior .Net Developer

Commented:
As you like.
Larry Bristersr. Developer

Author

Commented:
Excellent.  Thanks folks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial