Monday, September 13, 2010

Database Mail Formatting, Invalid Parameter Error!

Today I wanted to cover a database mail configuration issue I came across while attempting to send an email with an attachment through a SQL Server Agent job.

I have a t-sql script that calls msdb.dbo.sp_send_dbmail stored procedure in a job step that send the results of query as an attachment. When the job runs it fails with the following error message:

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

When I copied the t-sql script from the job step and ran it in a query window the query ran and the email sent with the attachment without issue. So I went to BOL, SQL Server Newsgroups and Google and through a combination of posts from different sources I found the solution to my problem. The issue was the domain account that the SQL Server Agent service is running under did not belong as a user in the msdb database. For some quirky reason, for sp_send_dbmail the login that executes this must be in the msdb database. If you come across this problem give this solution a try and let us know!...

Crazed DBA

3 comments:

Anonymous December 15, 2010 at 2:25 PM  

I have the same issue and applied your solution by dding the login under which the agent runs as user for msdb BUT still no lock

Any suggestions?

Aldo Gonzalez March 8, 2012 at 10:54 AM  

Thank you

Crazed DBA June 30, 2012 at 7:09 PM  

Did you receive the same error message after adding the login in msdb?

  © Blogger template 'A Click Apart' by Ourblogtemplates.com 2008

Back to TOP