Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sp_send_dbmail @query parameter Expand / Collapse
Author
Message
Posted Wednesday, October 13, 2010 4:30 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 5, 2013 4:06 PM
Points: 225, Visits: 423
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?



Post #1004043
Posted Wednesday, October 13, 2010 4:46 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 12,917, Visits: 32,084
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1004048
Posted Wednesday, October 13, 2010 8:19 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 7:26 AM
Points: 4,390, Visits: 9,536
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
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1004095
Posted Thursday, October 14, 2010 9:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 5, 2013 4:06 PM
Points: 225, Visits: 423
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 #1004531
Posted Tuesday, July 12, 2011 8:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 12, 2011 8:25 AM
Points: 1, Visits: 2
For Dynamic command I used the below and it worked.

set @Command='Execute Database..sp_MySP "' + convert(varchar,@datetime,101) + '"'
Post #1140346
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse