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

Mon, Dec 12, 2011 2-minute read

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.