CODEWITHSUNDEEP
sql-serversql-server-2008
Brad
Brad

Reputation: 55

sp_send_dbmail Error In Microsoft SQL Agent Job

I’m trying to send an email from a scheduled SQL Agent job using sp_send_dbmail and receive the following error:

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 15404, Level 16, State 19, Server MyServer, Line 1 Could not obtain information about Windows NT group/user 'MyDomain\sqlagent', error code 0x5.

Here is the code from the job step:

DECLARE @SQL NVARCHAR(400)
SELECT @SQL = 'SELECT COUNT(staff_id) FROM Staff'

EXEC msdb.dbo.sp_send_dbmail
@recipients = '[email protected]',
@subject = 'Email Alert',
@body = 'Test',
@query = @SQL,
@execute_query_database = 'MyDB'

SQL Agent is running under a domain account [MyDomain\sqlagent]. I granted this user db_owner permission in the MyDB database in addition to adding it as a member of the DatabaseMailUserRole in msdb. Still no luck. If I remove the @query and @execute_query_database parameters it will send a ‘test’ email. However, I need to attach the results from the query.

Any help is appreciated, thanks.

Upvotes: 5

Views: 12759

Answers (2)

vadorian
vadorian

Reputation: 41

This worked for me.

'SELECT columnname from YourDatabase.SchemaName.tablename'

I found this answer here.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7869b033-80f1-4594-a77e-fb6dce582fb4/error-msg-when-sending-email-using-spsenddbmail?forum=transactsql

Upvotes: -1

Nic
Nic

Reputation: 790

I've run into some strange errors with AD in the past. I would recommend checking that the account you are running this under has it's attributes readable within AD. The quickest way to do that would be to run 

exec xp_logininfo 'MyDomain\sqlagent'

and seeing if you get the same error. If you do, check the security properties of the domain account [right click the user in Active Directory > Properties > Security tab] and set Read permissions for Authenticated Users.

Upvotes: 3

Related Questions