Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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 = 'me@mydomain.tld', @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?

share|improve this question
    
Same permissions on the view for SA and your SQL Agent account? –  jl. Sep 28 '10 at 14:53
    
Yes. They're both have the db_owner role on that database, so they have full rights to every object. To be sure, I explicitly added permissions to the account to select from the view and select, insert, update and delete from the table. No change. –  Ben Wyatt Sep 28 '10 at 15:03
add comment

1 Answer

up vote 5 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.