parsing variable into @query using sp_send_dbmail

  • Hi,

    I have a stored procedure that users sp_send_dbmail and i'm trying to pass through a variable to @query using sp_send_dbmail. But I get the error message:-

    Msg 22050, Level 16, State 1, Line 0

    Error formatting query, probably invalid parameters

    Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 478

    Query execution failed: Msg 137, Level 15, State 2, Server MSLMKTSQL01, Line 54

    Must declare the scalar variable "@last_id_sent".

    I was just wondering if it's possibe to do this?

    Or if there is any work around?

    Thanks, Warwick

    Stored Procedure:-

    ALTER proc [dbo].[VolunteerEventRegistrationEmail]

    as

    declare @last_id_sent int

    DECLARE @SQL varchar(2000)

    DECLARE @File varchar(1000)

    select @last_id_sent = max(last_id_sent) from WebTeamWebsitesData.dbo.tbl_2009_email

    SET @SQL = 'SET NOCOUNT ON SELECT *

    FROM WebTeamWebsitesData.dbo.tbl_2009

    WHERE id > @last_id_sent'

    SET @File = 'Volunteers'+convert(varchar, convert(datetime, @today), 112)+'.csv'

    EXECUTE msdb.dbo.sp_send_dbmail

    @recipients = 'wf@tyw.com',

    @subject = 'Volunteers',

    @body = 'Attached please find the additional records for Volunteers',

    @query = @SQL,

    @attach_query_result_as_file = 1,

    @query_attachment_filename = @file,

    @query_result_header = 1,

    @query_result_separator = '',

    @query_result_no_padding = 1,

    @query_result_width = 32767

  • Here's your problem:

    ALTER proc [dbo].[VolunteerEventRegistrationEmail]

    as

    declare @last_id_sent int

    DECLARE @SQL varchar(2000)

    DECLARE @File varchar(1000)

    select @last_id_sent = max(last_id_sent) from WebTeamWebsitesData.dbo.tbl_2009_email

    SET @SQL = 'SET NOCOUNT ON SELECT *

    FROM WebTeamWebsitesData.dbo.tbl_2009

    WHERE id > @last_id_sent'

    -- ^^^^^^^^^^

    SET @File = 'Volunteers'+convert(varchar, convert(datetime, @today), 112)+'.csv'

    EXECUTE msdb.dbo.sp_send_dbmail

    @recipients = 'wf@tyw.com',

    @subject = 'Volunteers',

    @body = 'Attached please find the additional records for Volunteers',

    @query = @SQL,

    @attach_query_result_as_file = 1,

    @query_attachment_filename = @file,

    @query_result_header = 1,

    @query_result_separator = '',

    @query_result_no_padding = 1,

    @query_result_width = 32767

    The problem is that you are not passing the @last_id_sent variable to the query, you are merely passing its name. Change it to this:

    SET @SQL = 'SET NOCOUNT ON SELECT *

    FROM WebTeamWebsitesData.dbo.tbl_2009

    WHERE id > ' + @last_id_sent

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Works great thank you RBarryYoung!

  • I am doing exactly what you are doing on SQL Server 2008 SP1

    Not sure what is the issue ..Just try without using @SQL and write the complete query

    Alter proc [dbo].[VolunteerEventRegistrationEmail]

    as

    DECLARE @SQL varchar(2000)

    DECLARE @File varchar(1000)

    declare @today datetime

    declare @last_id_sent int

    select @last_id_sent = max(id) from test

    --SET @SQL = 'SET NOCOUNT ON SELECT * FROM test WHERE id < @last_id_sent'

    set @today= '1/01/93'

    SET @SQL = 'SET NOCOUNT ON SELECT * FROM master.dbo.test'

    SET @File = 'Volunteers'+convert(varchar, convert(datetime, @today), 112)+'.csv'

    EXECUTE msdb.dbo.sp_send_dbmail

    @profile_name = 'test',

    @recipients = '#####.@@@@@@.com',

    @subject = 'Volunteers',

    @body = 'Attached please find the additional records for Volunteers',

    @query = @SQL,

    @attach_query_result_as_file = 1,

    @query_attachment_filename = @file,

    @query_result_header = 1,

    @query_result_separator = ' ',

    @query_result_no_padding = 1,

    @query_result_width = 32767

    Abhay Chaudhary
    Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

  • Warwick Francis (5/26/2009)


    Works great thank you RBarryYoung!

    Glad I could help! 🙂

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • i want to send a mail which contains query results in excel format.I am able to see the contents in .txt format alone ,unable to view in excel.help me out

    DECLARE @value NVARCHAR(MAX)
    DECLARE @file VARCHAR(MAX)
    SET @value ='SET NOCOUNT ON select ipaddress,hostname from mstclientdevices'

      SET @file='data'+'.csv'
    execute as login = 'capuser'

    EXEC msdb.dbo.sp_send_dbmail
         @profile_name ='dataEmailProfile',
         @recipients = 'harinath.n@gmail.com',
         @subject = 'details',
         @body_format = 'html',
         @body = details,
         @importance = 'Normal',
         @sensitivity = 'Normal',
         @file_attachments = null,
         @query = @value,
         @attach_query_result_as_file = 1,
         @query_attachment_filename = @file,
         @query_result_header = 1,
         @query_result_width = 1300,
         @query_result_separator =' ',
         @exclude_query_output = 0,
         @append_query_error = 1,
         @query_result_no_padding =1
         
    why i am not able to see the result in excel format...?.

  • As far as I know, you aren't going to generate a native Excel file from a SQL procedure.  The closest I've been able to come is a .csv file, but that's not native Excel - it's just opened by Excel on most PCs.  The primary purpose of sp_send_dbmail procedure is to send email.  It can also include query results as an attachment, but can also include a file from disk.

    In theory, if you generate your output file on disk, convert it to Excel and then use sp_send_dbmail to send it, it should work.  However, I know of no way to generate an Excel file from within a stored procedure.  I'd think that to do so, you'd need to have Excel installed on the server and then use sp_oacreate to instantiate it, but I've never tried.  Then again, I wouldn't even install Excel on a SQL Server.

  • Ed Wagner - Saturday, October 21, 2017 2:05 PM

    As far as I know, you aren't going to generate a native Excel file from a SQL procedure.  The closest I've been able to come is a .csv file, but that's not native Excel - it's just opened by Excel on most PCs.  The primary purpose of sp_send_dbmail procedure is to send email.  It can also include query results as an attachment, but can also include a file from disk.

    In theory, if you generate your output file on disk, convert it to Excel and then use sp_send_dbmail to send it, it should work.  However, I know of no way to generate an Excel file from within a stored procedure.  I'd think that to do so, you'd need to have Excel installed on the server and then use sp_oacreate to instantiate it, but I've never tried.  Then again, I wouldn't even install Excel on a SQL Server.

    Generate the query with embedded HTML.  Or... create and EXCEL spreadsheet as a "master"... have SQL Server copy it and then use the ACE drivers to populate it with the "query".

    --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)

  • Thank you i can able to see the result

  • harinath6nataraJan - Sunday, October 29, 2017 5:18 AM

    Thank you i can able to see the result

    DECLARE @value NVARCHAR(MAX) 
    DECLARE @file VARCHAR(MAX)
    SET @value ='SET NOCOUNT ON select ipaddress,hostname,descrDECLARE @value NVARCHAR(MAX) 
    DECLARE @file VARCHAR(MAX)
    SET @value ='SET NOCOUNT ON select ipaddress,hostname,descrp from mstclientdevices'

    SET @file='data'+'.csv'
    execute as login = 'capuser'

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name ='dataEmailProfile',
    @recipients = 'harinath.n@gmail.com',
    @subject = 'details',
    @body_format = 'html',
    @body = details,
    @importance = 'Normal',
    @sensitivity = 'Normal',
    @file_attachments = null,
    @query = @value,
    @attach_query_result_as_file = 1, 
    @query_attachment_filename = @file,
    @query_result_header = 1,
    @query_result_width = 1300, 
    @query_result_separator =' ',
    @exclude_query_output = 0,
    @append_query_error = 1,
    @query_result_no_padding =1
    from mstclientdevices'

    SET @file='data'+'.csv'
    execute as login = 'capuser'

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name ='dataEmailProfile',
    @recipients = 'harinath.n@gmail.com',
    @subject = 'details',
    @body_format = 'html',
    @body = details,
    @importance = 'Normal',
    @sensitivity = 'Normal',
    @file_attachments = null,
    @query = @value,
    @attach_query_result_as_file = 1, 
    @query_attachment_filename = @file,
    @query_result_header = 1,
    @query_result_width = 1300, 
    @query_result_separator =' ',
    @exclude_query_output = 0,
    @append_query_error = 1,
    @query_result_no_padding =1

    in my above script ,i can able to get the query result in excel but column (descrp) contains more contents those data are moving to next row.unable to concatenate within the single cell...help me out

Viewing 10 posts - 1 through 9 (of 9 total)

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