May 26, 2009 at 8:11 pm
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
May 26, 2009 at 10:39 pm
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]
May 26, 2009 at 11:14 pm
Works great thank you RBarryYoung!
May 26, 2009 at 11:16 pm
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)
May 26, 2009 at 11:26 pm
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]
October 21, 2017 at 11:53 am
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...?.
October 21, 2017 at 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.
October 21, 2017 at 4:31 pm
Ed Wagner - Saturday, October 21, 2017 2:05 PMAs 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
Change is inevitable... Change for the better is not.
October 29, 2017 at 5:18 am
Thank you i can able to see the result
October 29, 2017 at 5:25 am
harinath6nataraJan - Sunday, October 29, 2017 5:18 AMThank 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