Protocol Error In TDS Stream

Question

I have a client workstation trying to pull data from a SQL 2012 server.  It is set up with an ODBC connection.  An error is encounter while the report is pulling the data.  Database Connector Error: 'HY000:[Microsoft][SQL Native Client 11.0] Protocol error in TDS stream'  I figured the issue was with the ODBC connection so I dropped and re-created it but the error persisted.  I've removed the firewall as well.  I tried updating the native client driver.  I don't know what else to try.

Answer 1

TDS stream errors indicate either corrupted driver installations or severe physical network error. So when you have already done a clean reinstall of the driver, I would take a look at the network hardware. If only on client has the problem try changing the NIC or wires..

Answer 2

Which drivers - on the Server itself?

And could you explain how to determine which drivers and how to reinstall them.

Thanks.

Answer 3

The SQL Server client drivers. Run odbcad32 on the command line. See the drivers page for installed drivers.

For reinstalling the drivers: Installing SQL Server Native Client and Microsoft SQLNCli team blog.

Answer 4

I have seen this where the server doesn't respond in time, pulling data from SQL via ODBC using Excel. If so the fix is fairly simple:

sp_configure

'remote query timeout (s)',3600

GO

RECONFIGURE

GO

---

JCEH

Answer 5

However, I've also seen it in other places and never worked out why! One instance was pulling from Excel via ODBC to a 64bit instance that linked through to a 32bit instance. Given the mix up in ODBC drivers between 32bit and 64bit it should come as no surprise that it caused a hissy fit.

Why the convoluted route to the data? Security! Most amusing.

J

---

JCEH

Answer 6

A query timeout should never raise a TDS protocol error.

Answer 7

This should also not raise a TDS protocol error. Cause Excel does not get information about the linked server per se.

Answer 8

A query timeout should never raise a TDS protocol error.

I don't know about should, only what is. If you have a large data set it times out. Increasing the timeout period or taking the data direct from a table rather than through a view onto a table solves the issue. Could be a memory constraint for the application (or more likely the Microsoft Query application).

Guessing this isn't so much of an issue in Excel 2007 upwards.

---

JCEH

Answer 9

Incidentally, this also works in some instances.

USE

master

GO

exec

sp_configure'network packet size (B)',16384 -- Was 4096, change to 16384?

GO

RECONFIGURE

GO

---

JCEH

Answer 10

Back to the severe network error: In this case, some of your network components have problems with the frame size.

Answer 11

This worked for me also. wish I knew why!!!

Answer 12

The most common causes were:

1. Incorrect driver version or corrupted driver installation.

2. Frame size, packet fragmentation, TCP chimney.. There some low-level IP things happening under rare circumstances, where TCP/IP itself is working, but TDS as hig level protocol errors out.

If changing the packet size helped, then it is TCP/IP networking issue. Here you need to start your investigation for the real reasons with the local NIC and the drivers used. If it's run in a VM, then you need to check the VM NIC configuration. Often the local NIC is not correctly setup. When you have SNMP capable switches, lock at packet loss/drops from this server instance, then it can be a defect cable.