Invalid Object Name when using sp_send_dbmail in SQL 2005

If you are trying to send a query result via email in SQL 2005 then you will need to use sp_send_dbmail procedure. As with other Database Stored Mail procedures, the sp_send_dbmail procedure lives in the msdb database. Therefore, you need to ensure that you are a member of the DatabaseMailUserRole in the msdb database.

Now secondly if you are using the @query parameter then you need to make sure that you specify the database where you would like the query to run. You can do this by using the @execute_query_database parameter. If you do not do this then you will receive an invalid object name error.

Example:

EXEC msdb.dbo.sp_send_dbmail
@recipients = ‘youremail@domain.com‘,
@subject = ‘Subject‘,
@profile_name = ‘DBMail Profile Account Name‘,
@query = ‘your query‘
@execute_query_database = ‘database you would like to query‘,
@body = ‘your message here or you can leave out if you don’t want anything other then the query in the body‘,
@attach_query_result_as_file = 0 or 1;

Another thing to remember is if you want the query result to be displayed in the body and not as a file attachement then make sure you use the @attach_query_result_as_file parameter and set the value to 0. If you would like to send it as a file attachment then you will need to set the value as 1.