How to fix native client error 'Connection is busy with results for another command'?

Question

I'm getting a Connection Busy With Results From Another Command error from a SQLServer Native Client driver when a SSIS package is running. Only when talking to SQLServer 2000. A different part that talks to SQLServer 2005 seems to always run fine. Any thoughts?

Answer 1

As I just found out, this can also happen on SQL 2005 if you do not have MARS enabled. I never even knew that it was disabled by default, but it is. And make sure you are using the "NATIVE OLEDB\SQL Native Client" connection type. If you're using the "OLEDB.1" type connection (or whatever...) MARS is not even an option, and you get the SQL 2000 behavior, which is nasty.

You can enable MARS by opening the connection properties, and clicking "All", and scolling down in Management Studio.

I know your question has long since been answered, but I'm just throwing this in for the next sucker like me who gets burned by this.

Answer 2

Had this error today with MS ODBC Driver 11 for SQL Server for Linux to SQL Server connection. Wanted to help next searcher considering this was the first Google search result when I made the search.

You need to set MARS_Connection in /etc/odbc.ini as following:

[ConnName]
Driver=ODBC Driver 11 for SQL Server
Server=192.168.2.218,1433
Database=DBNameHere
MARS_Connection=yes

Speaking of MS ODBC Linux Driver: It is a complete PITA to deal with it but I insisted using native solution. I experienced too many walls especially working with ZF2, however, every trouble has a solution with the driver I can say. Just to encourage people using it instead quickly give up.