December 1, 2016 at 8:59 am
We've got a SQL Job that runs every week day. It was written by someone, don't know who, years ago. Its been working fine until recently. The job has 8 steps. 2 of them every run. The full error message is:
Executed as user: OURDOMAIN\some.user. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.
I’ve spent time researching what could be causing the problem. What I’ve found is that the problem might be related to using dbo.sp_send_dbmail, which according to the sources I’ve research, must run from MSDB. The SQL that’s used in that job weren’t not specifying the tables referenced in the query fully. So I changed all references to any table to be fully qualified. Unfortunately that didn’t solve the problem. So what else could be causing this problem?
Kindest Regards, Rod Connect with me on LinkedIn.
December 1, 2016 at 11:26 am
Are you are calling the mail stored procedure while connecting to the msdb database like this in your job step?
USE msdb
GO
EXEC sp_send_dbmail
December 2, 2016 at 8:20 am
No, it's not. It connects to the database where the data is that it queries against. However, the next steps, upon failure, is to also run the mail stored proc to send a message to users telling them that there was a problem. That too runs from the database with the data and it runs fine. (It sends an email to users telling them that something went wrong.)
Kindest Regards, Rod Connect with me on LinkedIn.
May 31, 2023 at 6:24 am
Change the database context to msdb, the query will work fine, tested myself.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply