Thursday, August 9, 2012

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

I spent 2 days searching for the above error , the situation is as follows :

1. we have 2 servers, one for dev, one for production using the same domain account both for the SQL Server service and SQLAgent service.
2. the user is not sysadmin on either server.
3. Calling sp_send_dbmail  works fine in SSMS on both servers
4. On my production server , it did not work and had the above error.

On some posts, I read that some people added the SQL Server agent account on the sysadmin group and "it worked" (sounds to me as a quick and dirty solution).

Finally noticed that job owners were different on both servers:
- on the development server , the job owner is a database user (db_owner), with DatabaseMailUserRole.
- on the production server, the job owner is our sysadmin user (not listed in DatabaseMailUserRole).

I just changed the job owner on the production server and it worked !! No sysadmin user to set up, it works with the application user.

No comments:

Post a Comment