Msg 0, Sev 0, State 1: Unknown token received from SQL Server [SQLSTATE HY000] String data, right truncation [SQLSTATE 01004] [SQLSTATE 08S01] error
Question
Wednesday, November 5, 2014 11:10 PM
I'm looking for some general guidance on this complete error:
"
Msg 0, Sev 0, State 1: Unknown token received from SQL Server [SQLSTATE HY000]
String data, right truncation [SQLSTATE 01004]
Msg 16389, Sev 16, State 1: The connection is no longer usable because the server response for a previously executed statement was incorrectly formatted. [SQLSTATE 08S01]
"
This error is caused by a SQL Agent Job, but it does not always occur. I understand this could be an issue with SQL dropping the connection, but what is the best way to troubleshoot this?
Thanks,
Phil
All replies (4)
Friday, November 7, 2014 11:07 AM ✅Answered | 1 vote
Hi philliptackett77,
It would be more helpful for troubleshooting the issue if you post the SQL Server error log and SQL Server version. Additionally, please state what purpose of using the agent job.
Based on my research, [SQLSTATE 08S01] error means communication link failure. If the executed statement returns invalid value in the job, the connection between server and client will fail. This error could be caused by the error of [SQLSTATE HY000] and [SQLSTATE 01004]. I’d like to share my knowledge about these errors as below.
Quote: String data, right truncation [SQLSTATE 01004]
This error could occur when passing data whose length is larger than the received object,and the passed value is truncated, and returns invalid value. So please check if the data type of the objects are identical and the length of passed value is not larger than the received object.
Quote: Msg 0, Sev 0, State 1: Unknown token received from SQL Server [SQLSTATE HY000]
The error could occur in the two cases below.
1. If you use SQL Server version 6.50, and execute the cursor in the job step, set the statement options to use a server-side cursor and prepare a select statement on a SQL Server system table. The first execution of the select creates the cursor successfully. After you close this cursor, if you execute the prepared statement again, the error could occur.
I recommend you to apply the latest service pack firstly. If the error still occurs, you could change your code by using a forward-only cursor instead of a static, keyset, or dynamic cursor on the system tables. If a forward-only cursor is unacceptable, prepare the select statement again on the system table and execute it to create a server-side cursor. For more information about the process, please refer to the article: http://support.microsoft.com/kb/151693/en-us
2. The TDS stream from the server is invalid when transfer data between a database server and a client. This error is typically caused by a problem on the server. For more information about errors above, please refer to the article: http://technet.microsoft.com/en-us/library/aa937531(v=sql.80).aspx
Regards,
Michelle Li
Wednesday, December 17, 2014 10:05 PM ✅Answered | 1 vote
Unfortunately no. We did a workaround though by moving the job up 30 minutes and that calmed down the job failures. As far as figuring out the root cause of the error we never did.
Friday, November 7, 2014 5:28 PM | 1 vote
Thanks Michelle. Your answers provide great insight. The errors are sort of vague that's why I wanted to post. I'm interested in your suggestion about changing the cursor to a 'forward only cursor'. I've seen this answer in other posts elsewhere and I can confirm that the code in question does, in fact, contain cursors. What I'm wanting to know is could the solution be that simple just to change the cursor(s) to forward only? Is that solution a common fix for this type of error I'm seeing? Could you perhaps elaborate on the reasoning why changing cursors to 'forward only' are potential fixes in these cases?
Thanks again,
Phil
Monday, November 17, 2014 2:56 AM | 1 vote
Hi philliptackett,
I have the same issue with you, have you solved it??