Unknown Token Received From SQL Server
-
I have an issue when a stored procedure is executed from the SQL Server Agent it fails with the error, "Unknown token received from SQL Server [SQLSTATE HY000] (Error 0)." However, I can execute it locally in Management Studio just fine. I've scoured various resources about the error and behavior between the agent versus management studio but I'm not coming up with anything. I know my post doesn't contain much information but I'm taking a shot in the dark hoping someone has either experienced something similar or can point me in the direction of additional things to help troubleshoot.
-
But other procs execute ok?
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
All other procs are fine. That's what is making this extra difficult. The proc completes locally within a few minutes so it's not an overly long-running proc too.
-
is that proc using a linked server by anychance? that would be a possible reason for that issue (associated with incorrect/invalid credentials for the sql job account)
-
It does use linked servers but I ruled that out since we specify the security context being used. Meaning any reference to the linked server uses the same SQL authenticated ID to link across regardless who executed it or where it is executed from. I also tried breaking out just the part that calls the linked server into its own job and it successfully executed. I will say this one proc actually has about 15-20 nested procs within it. The initial proc will run, call a new proc at the end, and keeps repeating that. It doesn't always fail with this error on the same proc but does tend to be on one that is about halfway through.
-
I've been getting this error in SQL Agent jobs on several SQL 2016 servers intermittently since installing the latest security update (13.0.6455.2, 5046855 Security update for SQL Server 2016 SP3 GDR: November 12, 2024). Just today I may have tied it to the use of sp_MSforeachdb. Do you use that undocumented stored procedure?
P.S. I'm aware I shouldn't be using undocumented SPs and that alternatives exist.
-
While we don't use that, I do find it very interesting that we are also running the same version. Several weeks ago I tried rolling it back but the issue still persisted so I'm not sure if parts don't fully rollback or what. Have you found any workarounds?
-
I have not found a workaround yet. Interesting you uninstalled the update and it persisted. In my research, the error has occurred in the past and was tied to either a Windows update (I cannot make a similar connection) or an ODBC driver update. The ODBC driver was updated with the security update. I wonder if that did not roll back (although, not sure why SQL Agent would have an issue with that driver).
-
I see Microsoft ODBC Driver 18 for SQL Server updated on our end between 2016 SP3 GDR 13.0.6450.1 and 2016 SP3 GDR 13.0.6455.2. These windows updates were installed during the same time period
- 2024-11 Cumulative Update for Windows Server 2016 for x64-based Systems (KB5046612)
- 2024-11 Cumulative Update for .NET Framework 4.8 for Windows Server 2016 for x64 (KB5046266)
It seems like SQL 2016 SP3 GDR 13.0.6455.2 doesn't like one of those but I'm still confused why rolling the SQL update back wouldn't get it working again. Any suggestions?
-
Do your proc(s) use sp_executesql? I just realize both my failing jobs use it. It appears to have some relation to ODBC: https://learn.microsoft.com/en-us/sql/relational-databases/native-client-odbc-queries/executing-statements/direct-execution?view=sql-server-2016
I suspect that proc and the ODBC driver at this point. In my case, the driver is Microsoft ODBC Driver 13 for SQL Server that is updated to 13.3.6455.2.
-
We don't use that but our ODBC Driver 13 version matches what you have. The date which it was installed/updated coincides with the date the latest SQL patch was installed. I wonder if it's possible to only rollback the ODBC driver update and see what happens.
Something else that stood out in that article is this part:
Our linked server uses MSOLEDBSQL so that could make sense. It's still strange why it is intermittent though
-
For us, I'm almost certain it is the ODBC 13 Driver and using sp_executesql. The latest publicly available version of that driver appears to be 13.1 so not sure how to roll it back. Re-apply the previous SQL security update? Since you uninstalled the latest security update are you able to try that?
-
I discovered rolling back SQL updates doesn't rollback updates to Microsoft ODBC Driver 13 for SQL Server. The easy way would be to rollback SQL to 13.0.6450.1, uninstall the ODBC Driver, and re-install ODBC Driver version 13.3.6450.1. However, I wasn't quickly able to find a version of the that driver so here is the roundabout way I went about it.
1. Rolled SQL back to 13.0.6445.1
2. Uninstalled Microsoft ODBC Driver 13 for SQL Server
3. Applied SQL patch 13.0.6450.1 and this added the driver back
Thanks Terry for bringing to my attention the ODBC driver. I wouldn't have thought to make the connection between that and the SQL Agent jobs.
-
Good teamwork Ron! I've been waiting for over a month for someone to post anywhere on the internet that they also have this issue.
Thanks for posting your steps to temporarily addressing it. I hope Microsoft fixes this in the next security update for SQL 2016 since my security department is unlikely to let me uninstall it. Which means I'm still working on a solution to work around calls to sp_executesql.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply