July 6, 2010 at 12:05 pm
hi ,
Please suggest , how i can get output of any query (say it "Select @@servername") in HTML format as output or in SQL mail output .
July 6, 2010 at 12:55 pm
the key is two parts: including a @query parameter = 1, and making sure the @attach_query_result_as_file = 0;
the only issue is if you wanted it formatted as each value being in a table cell...it does not do that automatically...it just puts it int he <body> as plain text.
look at this blog for a how to format the results so it looks pretty in HTML:
from BOL:
[ @attach_query_result_as_file= ] attach_query_result_as_file
Specifies whether the result set of the query is returned as an attached file. attach_query_result_as_file is of type bit, with a default of 0.
When the value is 0, the query results are included in the body of the e-mail message, after the contents of the @body parameter. When the value is 1, the results are returned as an attachment. This parameter is only applicable if @query is specified.
here's an example:
declare @body1 varchar(4000),
@bigquery varchar(4000)
set @body1 = 'Sample Email from SQL2008 ' + CONVERT( VARCHAR( 20 ), GETDATE(), 113 ) +
' '
set @bigquery = 'Select @@version,
Serverproperty( ''BuildClrVersion'' ) as BuildClrVersion
, Serverproperty( ''ComputerNamePhysicalNetBIOS'' ) as ComputerNamePhysicalNetBIOS
, Serverproperty( ''Edition'' ) as Edition
, Serverproperty( ''EditionID'' ) as EditionID
, Serverproperty( ''EngineEdition'' ) as EngineEdition
, Serverproperty( ''MachineName'' ) as MachineName
, Serverproperty( ''ProductLevel'' ) as ProductLevel
, Serverproperty( ''ResourceLastUpdateDateTime'' ) as ResourceLastUpdateDateTime
, Serverproperty( ''ResourceVersion'' ) as ResourceVersion
, Serverproperty( ''ServerName'' ) as ServerName
, Serverproperty( ''InstanceName'' ) as InstanceName
'
EXEC msdb.dbo.sp_send_dbmail
@profile_name='Your profile name',
@recipients='youremail@somesite.net',
@subject = 'SQl 2008 email test',
@body = @body1,
@body_format = 'HTML',
@query = @bigquery,
@query_result_header = 0,
@exclude_query_output = 1,
@append_query_error = 1,
@attach_query_result_as_file = 0
Lowell
July 7, 2010 at 1:05 pm
Thanks a lot it worked 🙂
July 7, 2010 at 7:45 pm
It's a real shame that they're doing away with it. Lookup "sp_MakeWebTask" in Books Online. With the advent of VARCHAR(MAX) in 2k5, it's an awesome tool and you don't need to go anywhere near (ugh!) SSRS for something so simple.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply