Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.

Scheduled job may fail with below error but the sql script may succeed when you run it from SQL Server Management studio.

Error:

Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050).  The step failed.

Troubleshooting:

I had faced similar type of issue a while back and the issue was delete statement was failing while executing through job but that was successful when it is run from SSMS and the resolution is to enable “Set QUOTED_IDENTIFIER on” on job step ..

So with the above background when I came though the same error again I initially thought  that the error is the same so I had gone ahead and added “set QUOTED_IDENTIFIER on” code in the SQL Job step and started the job but unfortunately job failed again ,so I have to pay much attention on the script of the SQL agent job now.

Script is just checking the databases which are not backed up for last 24 hrs and sending the result via email to DBA team by making use of sp_send_db_mail SP.

This SP has an useful parameter @append_query_error which helped me in identyfying  the issue  easily so when I enable the parameter and executed the job ,I  got the mail with actual error attached and the error is below.

Msg 916, Level 14, State 1, Server XXXXXX, Line 1

The server principal "XXXXX" is not able to access the database "Admin" under the current security context.

So this is access related issue which is masked by some formatting error and  I had given enough privileges to the account and that’s it….issue is fixed.

Hope above information will help you in troubleshooting similar type of issues in your environment.