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:

    http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/03/28/send-table-or-view-as-embedded-html-lt-table-gt-in-an-email-stored-procedure.aspx

    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


    --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 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply