March 17, 2016 at 2:02 pm
here is my code:
Declare @var varchar(3)
SET @var = '22'
SET @QRY ='exec dbo.sp_policeTest' + @var
Exec msdb.dbo.sp_send_dbmail
@query = @QRY, ---- @var is not getting passed to stored procedure call ------
@reply_to = 'No-Reply@email.com',
@profile_name ='autoemail',
@recipients ='me@domain.com'
@Subject = @Sub,
@attach_query_result_as_file= 1,
@query_attachment_filename = 'textfile.txt',
@query_result_header = 1,
@query_result_separator = ' ',
@body = @GetBody,
@exclude_query_output=1,
@query_result_width = 300
What am I doing wrong?
March 17, 2016 at 2:21 pm
Declare @var varchar(3)
SET @var = '22'
SET @QRY ='exec dbo.sp_policeTest' + @var
Don't you need space after the sp_policeTest"
SET @QRY ='exec dbo.sp_policeTest' + @var
@Qry is "EXEC dbo.sp_policeTest22"
SET @QRY ='exec dbo.sp_policeTest ' + @var
With the space, @Qry is "EXEC dbo.sp_policeTest 22"
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 17, 2016 at 2:26 pm
Tried it with the space Michael. Still no go.
March 17, 2016 at 2:41 pm
Error messages?
March 17, 2016 at 3:54 pm
Hi Lynn,
No error messages. The procedure actually runs, its just that its not returning any data because the variable is not getting passed to the procedure.
March 17, 2016 at 4:45 pm
kstarkes (3/17/2016)
Hi Lynn,No error messages. The procedure actually runs, its just that its not returning any data because the variable is not getting passed to the procedure.
It has been quite awhile since I used database mail, but doesn't it have an error log somewhere or a table where it logs errors?
March 18, 2016 at 2:03 am
I see no variable passed to the query for sp_send_dbmail. You're concatenating the value '22' to the query, which is the only way to execute queries with parameters in dbmail.
sp_send_dbmail cannot accept parameterized queries.
-- Gianluca Sartori
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply