Send query result as HTML Mail

  • EYER Daniel

    Valued Member

    Points: 65

    Comments posted to this topic are about the item Send query result as HTML Mail

  • jcrawf02

    SSC-Insane

    Points: 24198

    Article says "SQLServer 2000 and Windows 2000 ONLY" - will this not work in XP?

    ---------------------------------------------------------
    How best to post your question[/url]
    How to post performance problems[/url]
    Tally Table:What it is and how it replaces a loop[/url]

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

  • EYER Daniel

    Valued Member

    Points: 65

    This script is working on SQL 2000 / Windows 2003 server.

    I do not test it on other configuration.

    But I suppose that that should work.

    Daniel Eyer

  • Toreador13

    SSC Rookie

    Points: 44

    This still has the 8000 character limitation does it not?

    Has anyone found a way around that? Is it possible to bypass this with 2005?

  • pp_gagan

    Grasshopper

    Points: 17

    can any body tell how to email from sql

  • timtactoe

    Grasshopper

    Points: 19

    I am setting up trigger to send out query results and this is exactly what I needed.

    It works perfectly. Whoever you are, thanks a bunch!!!

    This is awesome.:-D

  • gscicluna

    Valued Member

    Points: 53

    yes this does work well but the only issue l am having is the header row on the query is 2 rows high for some reason. Anyone esle having the same issue

  • gscicluna

    Valued Member

    Points: 53

    Can anyone help me with this sp to include the ability to add a header before the results anda footer after the results. I have teried but the results keep generating erratically.

  • gscicluna

    Valued Member

    Points: 53

    Can anyone help me with this sp to include the ability to add a header before the results anda footer after the results. I have teried but the results keep generating erratically.

  • sharad-1144828

    SSC Enthusiast

    Points: 175

    Hi All,

    I have created the same Stored proc & execute the SP as

    exec sp_send_MailHtml

    'smtp.entech.us', --SMTP Server

    'webmail.'

    'sharad@entech.us', --From

    'jatin@entech.us', --To

    'sharad@entech.us', --CC

    '', --CCi

    'Test Page', --Subject

    'select top 2 * from superaccesslevels..totaldatabase'

    But i have not received any mail.

    Can someone plz let me now what is the issue in this & how to resolve it ?

    Sharad

  • EYER Daniel

    Valued Member

    Points: 65

    Hi,

    1-Remove 'webmail.'

    2-The data result of the query need to be strings. For date or numeric field, use convert() function

    Daniel

    exec sp_send_MailHtml

    'smtp.entech.us', --SMTP Server

    'sharad@entech.us', --From

    'jatin@entech.us', --To

    'sharad@entech.us', --CC

    '', --CCi

    'Test Page', --Subject

    'select top 2 Field1,Field2

    from superaccesslevels..totaldatabase'

  • sharad-1144828

    SSC Enthusiast

    Points: 175

    Hi Daniel,

    Thanks 4 reply.

    Still i am not receiving any mail.i have made the changes as per ur suggestion.

    exec sp_send_MailHtml

    'smtp.entech.us', --SMTP Server

    'sharad@entech.us', --From

    'jatin@entech.us', --To

    'sharad@entech.us', --CC

    '', --CCi

    'Test Page', --Subject

    'select top 2 * from superaccesslevels..totaldatabase'

    The store proc sp_send_MailHtml is as follows :

    Text

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

    create PROCEDURE sp_send_MailHtml

    @SmtpServer varchar(128),

    @From varchar(128),

    @To varchar(128),

    @cc varchar(128),

    @BCc varchar(128),

    @Subject varchar(124)=" ",

    @Query varchar(4000) = " "

    /***

    * Date: March 2008

    * Author: daniel.eyer@free.fr

    * Project: Just for fun!

    * Location: Any database

    * Permissions: PUBLIC EXECUTE

    *

    * Description: Send query result as HTML Mail

    *

    *

    ***/

    AS

    --Mail declaration

    Declare @iMsg int

    Declare @hr int

    Declare @source varchar(255)

    Declare @description varchar(500)

    Declare @output varchar(1000)

    --HTML declaration

    declare @Columns varchar(8000)

    declare @ColHeader varchar(8000)

    Declare @SqlCmd varchar(8000)

    Declare @HTMLBody varchar(8000)

    --************* Create the CDO.Message Object ************************

    EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

    --***************Configuring the Message Object ******************

    -- This is to configure a remote SMTP server.

    -- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp

    EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

    -- This is to configure the Server Name or IP address.

    -- Replace MailServerName by the name or IP of your SMTP Server.

    EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @SmtpServer

    -- Save the configurations to the message object.

    EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

    -- Set the e-mail parameters.

    EXEC @hr = sp_OASetProperty @iMsg, 'To', @To

    EXEC @hr = sp_OASetProperty @iMsg, 'From', @From

    If(@Cc Is Not Null)

    Exec @hr = sp_OASetProperty @iMsg, 'Cc', @cc

    If(@BCc Is Not Null)

    Exec @hr = sp_OASetProperty @iMsg, 'BCc', @BCc

    EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

    -- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.

    -- EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body

    /*************************************************************************/

    -- drop temporary tables used.

    IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##TEMPhtml1')

    DROP TABLE ##TEMPhtml1

    IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##TEMPhtml2')

    DROP TABLE ##TEMPhtml2

    -- prepare query

    set @SqlCmd = 'select * into ##tempHTML1 from (' + @Query + ') as t1'

    execute (@SqlCmd)

    --Prepare columns details

    SELECT @columns =

    COALESCE(@columns + ' + ''</td><td>'' + ', '') +

    'RTrim(convert(varchar(100),isnull(' + column_name +','' '')))'

    FROM tempdb.information_schema.columns

    where table_name='##tempHTML1'

    --Prepare column Header

    set @colHeader = '<tr bgcolor=#EDFEDF align=Left>'

    SELECT @colHeader = @colHeader + '<td><b>' + column_name + '</b></td>'

    FROM tempdb.information_schema.columns where table_name='##tempHTML1'

    set @colHeader=@colHeader + '</tr>'

    --prepare final output

    set @SqlCmd =

    'Select ''<tr><td>'' + ' +

    @columns +

    ' ''</td></tr> '' into ##tempHTML2 from ##tempHTML1 '

    execute( @SqlCmd)

    --set @finalhtmlout=

    set @HtmlBody =

    ' <html> <body><style type="text/css" media="all"> ' +

    'table { margin-bottom: 2em; border-collapse: collapse } ' +

    'td,th {border= 1 solid #999; padding: 0.2em 0.2em; font-size: 12;} ' +

    '</style> <table width="100%"> ' +

    @colHeader

    select @HtmlBody = @HtmlBody + [</td></tr>]

    from ##tempHTML2

    set @HtmlBody = @HtmlBody + ' </table></body></htmL>'

    EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @HtmlBody

    EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

    -- drop temporary tables used.

    IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##TEMPhtml1')

    DROP TABLE ##TEMPhtml1

    IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##TEMPhtml2')

    DROP TABLE ##TEMPhtml2

    /*************************************************************************/

    -- Sample error handling.

    /* IF @hr <>0

    select @hr

    BEGIN

    EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT

    IF @hr = 0

    BEGIN

    SELECT @output = ' Source: ' + @source

    PRINT @output

    SELECT @output = ' Description: ' + @description

    PRINT @output

    END

    ELSE

    BEGIN

    PRINT ' sp_OAGetErrorInfo failed.'

    RETURN

    END

    END

    */

    -- Do some error handling after each step if you need to.

    -- Clean up the objects created.

    EXEC @hr = sp_OADestroy @iMsg

  • nathan.syverson

    Old Hand

    Points: 388

    I modified the HTML generating portion of the code to handle column names with spaces and numbers, and also column values with numeric values that might also have null values. The code also uses varchar(max) instead of varchar(8000). This portion is shown here encapsulated in its own stored proc. It writes the results to a temp table.

    CREATE PROC [QueryToHTML](@Query Varchar(MAX))

    AS BEGIN

    DECLARE @Columns VARCHAR(MAX)

    DECLARE @ColHeader VARCHAR(MAX)

    DECLARE @SqlCmd VARCHAR(MAX)

    DECLARE @HTMLBody VARCHAR(MAX)

    -- drop temporary tables used.

    IF EXISTS ( SELECT *

    FROM tempdb.dbo.sysobjects

    WHERE name = '##TEMPhtml1' )

    DROP TABLE ##TEMPhtml1

    IF EXISTS ( SELECT *

    FROM tempdb.dbo.sysobjects

    WHERE name = '##TEMPhtml2' )

    DROP TABLE ##TEMPhtml2

    -- prepare query

    SET @SqlCmd = 'select * into ##tempHTML1 from (' + @Query + ') as t1'

    EXECUTE (@SqlCmd)

    --Prepare columns details

    SELECT @columns = COALESCE(@columns + ' + ''</td><td>'' + ' , '')

    + 'RTrim(isnull(convert(varchar(100),' + '[' + CONVERT(VARCHAR(100),column_name) + ']'

    + '),'' ''))'

    FROM tempdb.information_schema.columns

    WHERE table_name = '##tempHTML1'

    --Prepare column Header

    SET @colHeader = '<tr bgcolor=#EDFEDF align=Left>'

    SELECT @colHeader = @colHeader + '<td><b>' + column_name

    + '</b></td>'

    FROM tempdb.information_schema.columns

    WHERE table_name = '##tempHTML1'

    SET @colHeader = @colHeader + '</tr>'

    --prepare final output

    SET @SqlCmd = 'Select ''<tr><td>'' + ' + @columns

    + ' + ''</td></tr> '' as Cmd into ##tempHTML2 from ##tempHTML1 '

    EXECUTE( @SqlCmd);

    --set @finalhtmlout=

    SET @HtmlBody = ' <html> <body><style type="text/css" media="all"> '

    + 'table { margin: 2em 2em 2em 3em; border-collapse: collapse; width:75%;} '

    + 'td,th {min-width: 55px; border-width: 1px; border-style: none none solid none; border-color: #DCDCDC; padding: 0.2em 0.2em; font-size: 12;} '

    + '</style> <table> ' + @colHeader

    SELECT @HtmlBody = @HtmlBody + Cmd + '</td></tr>'

    FROM ##tempHTML2

    IF EXISTS ( SELECT *

    FROM tempdb.dbo.sysobjects

    WHERE name = '##HTMLFinal' )

    BEGIN

    DROP TABLE ##HTMLFinal

    END

    SELECT @HtmlBody + ' </table></body></htmL>' AS html

    INTO ##HTMLFinal

    END

  • figaro

    SSC Enthusiast

    Points: 195

    Hi,

    What permission needs regular users to execute the SP?

    I'm receiving error: "Invalid object name '##tmpHtml2'." with regular users and with users within the db_owner group.

    The only way I can execute the query is when sa user.

    Thanks.

  • nathan.syverson

    Old Hand

    Points: 388

    Rookie>>

    I am not sure about this. My idea would be that you need to have read/write/create/drop permissions to the tempdb. I would start by googling that?

Viewing 15 posts - 1 through 15 (of 28 total)

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