SQL Agent job calling stored procedure msdb.dbo.sp_send_dbmail @query does not return any records

ERROR:
SQL Agent job calling stored procedure msdb.dbo.sp_send_dbmail @query does not return any records

SOLUTION
Add the Database Name into the @query

BACKGROUND
This may be because of the default database of the account which executes the SQL Agent job.

EXAMPLE
If @query does not include the database name (three-part naming), it is possible that although the EXISTS may fire, the @query does not.
To solve add the Database Name into the @query.

BEGIN TRY

IF EXISTS(SELECT * FROM Production.vAuditSOCDBTable)
  BEGIN
   DECLARE @MyBody nvarchar(100)
   SET @MyBody = ‘<message>’ + char(13) + char(10)

   EXEC msdb.dbo.sp_send_dbmail
    @profile_name = ‘<>’,
    @recipients = ”,
    @blind_copy_recipients = ”,
    @query = ‘SELECT * FROM <databasename>..’,
    –or possibly @execute_query_database = ‘databasename’,
    @subject = ‘subject’,
    –@attach_query_result_as_file = 1 ,
    @body = @MyBody;

   PRINT ‘message’
  END
 ELSE
  PRINT ‘nothing in audit’

END TRY
BEGIN CATCH
 SELECT ERROR_NUMBER() ErrorNumber, ERROR_MESSAGE() [Message]
 PRINT ‘FAILED’
END CATCH

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: