Why does sp_send_dbmail fail inside SQL Agent jobs?

Question

I have a simple query that I run to send email notifications to our AD account administrators when accounts have been disabled for more than 30 days. It works perfectly fine when I run it on my own, logged in as SA, but it fails when run inside SQL Server Agent jobs.

Here's the query, with business-specific items and object names replaced:

DECLARE @QueryString varchar(max)
SET @QueryString = 'Select TrackingTable.Username FROM dbName.Schema.TrackingTable inner join dbName.Schema.viewName on DisabledAccounts.username = viewName.username WHERE DATEDIFF(dd,DateDisabled,GETDATE()) > 25 AND viewName.OU = ''InactiveAccounts'''
EXEC msdb.dbo.sp_send_dbmail  @profile_name = 'Profile', @body = 'This is the body text. Nothing interesting here.
', @recipients = '[email protected]', @subject='Account status update', @query = @QueryString, @importance = 'High'

When I run it as SA, the message gets sent. Inside a SQL Server Agent job, I get this error:

Executed as user: DOMAIN\MemberOfDomainAdmins. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050).  The step failed.

The domain user the job executes under is assigned the db_owner role on msdb and the database used for the query attached to the message. Its default schema on both databases is dbo.

It's also assigned the sysadmin role on the server and is a member of the DatabaseMailuserRole on msdb. It also has private and public access to the Database Mail profile used by the query.

I've seen dozens of examples of the same issue online, but I've already taken the steps that remedied this problem in the examples I saw. What else can I try?

Answer 1

I figured it out this morning. The problem was with the DATEADD function. When it's inside an EXECUTE statement (which is how a SQL Server Agent job would run it), the interval has to be a specific datepart (day), not one of the tokens ("dd").

So, this function:DATEDIFF(dd,DateDisabled,GETDATE())

Needed to be more like this: DATEDIFF(day,DateDisabled,GETDATE())

There were a multiple calls to that function there, but you get the idea.

Here's how I figured it out: I had SQL Server Management Studio script the job as a CREATE script in a new query editor document. Once I found the step I was trying to run, I copied it out. The juicy bits look something like this:

@command = N'[my query]'

I copied that group of lines to a new window and added a DECLARE for the command variable.

Finally, I used EXECUTE (@command) AS USER = '[the user the job runs as]' to see what would happen. Running the query this way, I got much more detailed error messages that way than I did from the log for the job.

I've corrected it now, and the job runs perfectly.