sp_send_dbmail @query parameter

Question

Post reply

sp_send_dbmail @query parameter

ehlinger

Hall of Fame

Points: 3585
More actions
October 13, 2010 at 4:30 pm

#227934

I am trying to use sp_send_dbmail and execute a stored procedure for the @query parameter.
I have the following:
EXEC msdb..sp_send_dbmail
@profile_name = 'reports',
@recipients = 'test@test.com',
@subject = 'Monthly Job Creation',
@query = 'EXEC udsp_rpt_adm_monthly_jobs'
This generates the following error:
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 504
Query execution failed: Msg 2812, Level 16, State 62, Server PGL-WORKGROUPS, Line 1
Could not find stored procedure 'udsp_rpt_adm_monthly_jobs'.
If I put the the actual query in for the parameter rather than the stored procedure it works fine. How can I use a stored procedure so I don't have to put all the code in? Is it even possible?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

the mail runs from the msdb database, so you have to fully qualify everything, like

EXEC msdb..sp_send_dbmail

@profile_name = 'reports',

@recipients = 'test@test.com',

@subject = 'Monthly Job Creation',

@query = 'EXEC Production.dbo.udsp_rpt_adm_monthly_jobs'

i think if you are using a query, there are other parameters required to decide where the results of the query is going to go, like as an attachment or int he body of the email;

this is the example i tend to like a lot:

declare @body1 varchar(4000)

set @body1 = 'Sample Email from SQL2008 ' + CONVERT( VARCHAR( 20 ), GETDATE(), 113 ) +

' '

EXEC msdb.dbo.sp_send_dbmail

@profile_name='gMail Example',

@recipients='lowell@somedomain.net',

@subject = 'SQl 2008 email test',

@body = @body1,

@body_format = 'HTML',

@query = 'SELECT top 3 * from sysobjects where xtype=''U''',

@query_result_header = 0,

@exclude_query_output = 1,

@append_query_error = 1,

@attach_query_result_as_file = 1,

@query_attachment_filename = 'qry.txt',

@query_result_no_padding = 1

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!

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

In additiona to what Lowell has shown, there is a parameter available that defines the database to be used for the query: @query_database (I think - look it up to be sure).

Using that parameter will make sure the query is executed in the context of that database.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

ehlinger Hall of Fame Points: 3585 More actions · Answer 3

Thanks guys! You can do either. You can fully qualify it in the @query parameter or don't do it there and use the @execute_query_database. I don't know how I missed either one of those. Just shows when you are too involved with something it is easy to miss the obvious.

Thanks again.

post.knowledge SSC Journeyman Points: 89 More actions · Answer 4

For Dynamic command I used the below and it worked.

set @Command='Execute Database..sp_MySP "' + convert(varchar,@datetime,101) + '"'