none
ERROR [HY000] [Microsoft][ODBC SQL Server Driver]Connection is busy

    Question

  • Hi.
    I'm trying to insert a lot of records with some binary data, one by one.

    Now and then I get this error:
    "ERROR [HY000] [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt"

    I'm running this on a SQL 2008 with the following connectionstring:
    "DRIVER={SQL Server};SERVER=myServer;DATABASE=myDB;MultipleActiveResultSets=true;"

    If I let the thred sleep for some milliseconds and try again everything is normally ok. However, that workaround is not an option for my app. Is there some way to tell the database engine to finish before returning?"

    I have also tried setting MultipleActiveResultSets=false, but with the same result.

    Friday, April 09, 2010 11:21 PM

Answers

  • After some more research I found out that the "SQL Server" driver does not support MARS (MultipleActiveResultSets). When using "SQL Server Native Client 10.0" instead and "MARS_Connection=yes" everything works nice.

    However, this raises a new problem. I have users on both 2005 and 2008 servers. On 2005 servers the only installed driver might be "SQL Server Native Client". Is there anyone that have a nice solution in C# to find out what drivers that are installed?

    • Marked as answer by Ruben Edna Saturday, April 10, 2010 12:12 AM
    Saturday, April 10, 2010 12:12 AM
  • You should be able to use either Driver={SQL Native Client}(SQL Native Client ODBC Driver that comes with SQL Server 2005)or Driver={SQL Server Native Client 10.0}(SQL Native Client 10.0 ODBC Driver that comes with SQL Server 2008)and be able to use MARS. So in your case of SQL Server 2005 and 2008 you should be fine

    This link might provide some pointers on how to list the installed odbc drivers : http://blogs.technet.com/heyscriptingguy/archive/2005/07/07/how-can-i-get-a-list-of-the-odbc-drivers-that-are-installed-on-a-computer.aspx

    Hope this helps


    This posting is provided "AS IS" with no warranties, and confers no rights
    Friday, April 16, 2010 4:00 PM
    Moderator

All replies

  • After some more research I found out that the "SQL Server" driver does not support MARS (MultipleActiveResultSets). When using "SQL Server Native Client 10.0" instead and "MARS_Connection=yes" everything works nice.

    However, this raises a new problem. I have users on both 2005 and 2008 servers. On 2005 servers the only installed driver might be "SQL Server Native Client". Is there anyone that have a nice solution in C# to find out what drivers that are installed?

    • Marked as answer by Ruben Edna Saturday, April 10, 2010 12:12 AM
    Saturday, April 10, 2010 12:12 AM
  • You should be able to use either Driver={SQL Native Client}(SQL Native Client ODBC Driver that comes with SQL Server 2005)or Driver={SQL Server Native Client 10.0}(SQL Native Client 10.0 ODBC Driver that comes with SQL Server 2008)and be able to use MARS. So in your case of SQL Server 2005 and 2008 you should be fine

    This link might provide some pointers on how to list the installed odbc drivers : http://blogs.technet.com/heyscriptingguy/archive/2005/07/07/how-can-i-get-a-list-of-the-odbc-drivers-that-are-installed-on-a-computer.aspx

    Hope this helps


    This posting is provided "AS IS" with no warranties, and confers no rights
    Friday, April 16, 2010 4:00 PM
    Moderator