output of SQL query in mail format
-
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 .
-
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 = 0Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks a lot it worked
-
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
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply