Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050)

I just tripped over this problem, and despite a fair amount of Googlage, I didn’t find anything that directly resolved my issue. I was trying to use dbMail in SQL Server 2008 to send an email on a schedule which included the results of a query. Doing this should be fairly straightforward, by executing the sp_send_dbmail stored procedure, which is in MSDB:

This query works fine in SSMS, but when run as a SQL Server Agent Job, it fails, with the error

Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050)

A few things need to happen to make this work:

1) The account that the task is running as (e.g., SQL Server Agent account) needs to be a member of MSDB. I also granted it rights on the Agent roles, and DBMailUser role, as well as Read, Insert, Connect, Execute, etc.
2) It will also need permissions on the database that you’re trying to connect to query.

I saw a lot of other hints on ways around this, like add a “USE DatabaseName” as part of the expression, but none worked. Here’s what got it working for me.

In the Agent Job setup, you have the option to specify which database to use. I instinctively set this to the database I wanted to query. But alas, this is not right. You need to set it to MSDB, and then update your query to include the @execute_query_database parameter:

After that, things should work.

14 Comments

  1. Matt, thinking about the least privileged user, the SQL Server Agent account just need to have a DatabaseMailUserRole permission in the MDSB database.

  2. I had the same error message but it wasn’t a problem of least privilege or something else. In my SQL-Statement, I used a “Select-Case” Statement and I had to add a”blank” character to each “when” row to solve the problem… a little bit strange, but it worked

  3. Thanks for sharing. I had the same issue. Tried everything you suggested but did not seem to work still. Finally gave SQL Agent account sysadmin role membership and it WORKED. Would that be a bit excessive? any one, any thoughts? Thanks again.

  4. Googled ‘error formatting query probably invalid parameters sqlstate 42000 (error 22050)’ and I found tons of discussions on SQL permissions and making changes in SQL query. None of them fixed my issue until yours. I had similar SQL jobs (issuing db mail) created before (even with much longer and complex queries) but never had the error. The fix is logical but not obvious to me. Thanks.

Leave a Reply

Your email address will not be published.


*