Send results of a query in an email from SQL 2008?

[jbradley]

I have a stored procedure that creates and populates a work table then performs a sequence of operations on the records represented in the work table. At the end of the sp I would like to send the contents of the work table to my users via email. I know that email is set up and working on the server because there is a job that sends out a disk space report every morning. I've looked at it trying to see if I can use it as the basis of sending out this table's contents but no joy so far. Here is my code and the resulting error message.

[b][COLOR=blue]EXEC msdb.dbo.sp_send_dbmail @recipients='sql_alerts@mydomain.com',
    @profile_name = 'DBAdmins',
    @subject = 'Reverted Status Codes',
    @query = 'SELECT * FROM dbo.StatusChangeTemp',
    @body_format = 'HTML' ;
[/color][/b][COLOR=red]
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 208, Level 16, State 1, Server PENNLATSQL, Line 1
Invalid object name 'dbo.StatusChangeTemp'.
[/color]

Any suggestions would be greatly appreciated.

Thanks,
Brad

 

[gmmastros]

Try putting the name of the database in the query, like this:

@query = 'SELECT * FROM [!]YourDatabaseName.[/!]dbo.StatusChangeTemp'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom