|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
how to fix error "Connection is busy with results for another hstmt"
Hi, I am using OleDB connection in vc++ code to insert data in sql server database. Single connection is being used by multiple threads to insert data. Sometime program is reporting error "Connection is busy with results for another hstmt". Please help me how this issue can be resolved. Thanks, Vijay > Sometime program is reporting error "Connection is busy with results for This error indicates that a prior command on the connection is still > another hstmt". executing or the results have not been processed completely. Ensure that all results are retrieved before attempting another command on the connection. Show quoteHide quote "vijay singh" <vijaysi***@abosoftware.com> wrote in message news:Oh119oL9JHA.200@TK2MSFTNGP05.phx.gbl... Environment: MS SQL Server 2005, VS 2008/VC++, W2K3 R2
Sorry, I should have been clearer. For my simultaneous database inserts (additions/forward only; no edits) from different threads not requiring any result back and not requiring any transitioning, I was hoping that I could use a single database connection without locking for such inserts (no need to sequence inserts one by one on the same connection). I use OLEDB and I am able to do this with Oracle Database and Postgress database. However, when I use MS SQL Server database, I get his error "Connection is busy with results for another hstmt". How do I achieve simultaneous inserts in above scenario with single database connection without have to lock it or issue insert commands one by one? Thanks, Vijay Show quoteHide quote "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message news:%23$M3VfM9JHA.1380@TK2MSFTNGP02.phx.gbl... >> Sometime program is reporting error "Connection is busy with results for >> another hstmt". > > This error indicates that a prior command on the connection is still > executing or the results have not been processed completely. Ensure that > all results are retrieved before attempting another command on the > connection. > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > http://weblogs.sqlteam.com/dang/ > > "vijay singh" <vijaysi***@abosoftware.com> wrote in message > news:Oh119oL9JHA.200@TK2MSFTNGP05.phx.gbl... > How do I achieve simultaneous inserts in above scenario with single The SQLOLEDB driver doesn't allow concurrent batches on a single connection, > database connection without have to lock it or issue insert commands one > by one? I believe the SQL Server Native Client OLEDB driver (SQLNCLI) will allow multiple concurrent batches this with MARS enabled. I have not personally seen MARS used for multi-threaded inserts since the overhead of multiple connections in SQL Server isn't that significant; one usually employs separate connections. Show quoteHide quote "vijay singh" <vijaysi***@abosoftware.com> wrote in message news:%23qJLOnU9JHA.3544@TK2MSFTNGP04.phx.gbl... > Environment: MS SQL Server 2005, VS 2008/VC++, W2K3 R2 > > Sorry, I should have been clearer. > > For my simultaneous database inserts (additions/forward only; no edits) > from different threads not requiring any result back and not requiring any > transitioning, I was hoping that I could use a single database connection > without locking for such inserts (no need to sequence inserts one by one > on the same connection). I use OLEDB and I am able to do this with Oracle > Database and Postgress database. However, when I use MS SQL Server > database, I get his error "Connection is busy with results for another > hstmt". > > How do I achieve simultaneous inserts in above scenario with single > database connection without have to lock it or issue insert commands one > by one? > > Thanks, > > Vijay > "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message > news:%23$M3VfM9JHA.1380@TK2MSFTNGP02.phx.gbl... > >>> Sometime program is reporting error "Connection is busy with results for >>> another hstmt". >> >> This error indicates that a prior command on the connection is still >> executing or the results have not been processed completely. Ensure that >> all results are retrieved before attempting another command on the >> connection. >> >> -- >> Hope this helps. >> >> Dan Guzman >> SQL Server MVP >> http://weblogs.sqlteam.com/dang/ >> >> "vijay singh" <vijaysi***@abosoftware.com> wrote in message >> news:Oh119oL9JHA.200@TK2MSFTNGP05.phx.gbl... > > Dan Guzman (guzmanda@nospam-online.sbcglobal.net) writes:
>> How do I achieve simultaneous inserts in above scenario with single Actually, by default SQLOLEDB does. It will open a second physical >> database connection without have to lock it or issue insert commands one >> by one? > > The SQLOLEDB driver doesn't allow concurrent batches on a single > connection, I believe the SQL Server Native Client OLEDB driver > (SQLNCLI) will allow multiple concurrent batches this with MARS enabled. > I have not personally seen MARS used for multi-threaded inserts since > the overhead of multiple connections in SQL Server isn't that > significant; one usually employs separate connections. connection if the connection is busy. However, since Vijay get an error message talking about hstmt, he is obviously not using SQLOLEDB, but MSDASQL, because hstmt is an ODBC thing. MARS could be an option, but Vijay did not want to lock his connection and get serialisation. Since MARS means execution in an interleaved fashion, that is in fact serialisation. Vijay should have one conenction per thread, or connect and disconnect every time he performs an operation. Connection pooling will make sure that there is no physical disconnection, as long as the connections are reused within 60 seconds. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > Actually, by default SQLOLEDB does. It will open a second physical I've always been under the impression that the second connection was > connection if the connection is busy. something that ADO did; I didn't know that it was SQLOLEDB doing this dirty work. > MARS could be an option, but Vijay did not want to lock his connection You are right - interleaved and concurrent are not the same thing.> and get serialisation. Since MARS means execution in an interleaved > fashion, that is in fact serialisation. > Vijay should have one conenction per thread, or connect and disconnect +1> every time he performs an operation. Connection pooling will make sure > that there is no physical disconnection, as long as the connections are > reused within 60 seconds. Show quoteHide quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns9C361EF9E8DBYazorman@127.0.0.1... > Dan Guzman (guzmanda@nospam-online.sbcglobal.net) writes: >>> How do I achieve simultaneous inserts in above scenario with single >>> database connection without have to lock it or issue insert commands one >>> by one? >> >> The SQLOLEDB driver doesn't allow concurrent batches on a single >> connection, I believe the SQL Server Native Client OLEDB driver >> (SQLNCLI) will allow multiple concurrent batches this with MARS enabled. >> I have not personally seen MARS used for multi-threaded inserts since >> the overhead of multiple connections in SQL Server isn't that >> significant; one usually employs separate connections. > > Actually, by default SQLOLEDB does. It will open a second physical > connection if the connection is busy. > > However, since Vijay get an error message talking about hstmt, he is > obviously not using SQLOLEDB, but MSDASQL, because hstmt is an ODBC > thing. > > MARS could be an option, but Vijay did not want to lock his connection > and get serialisation. Since MARS means execution in an interleaved > fashion, that is in fact serialisation. > > Vijay should have one conenction per thread, or connect and disconnect > every time he performs an operation. Connection pooling will make sure > that there is no physical disconnection, as long as the connections are > reused within 60 seconds. > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >  
Other interesting topics
32 bit SQL Server on 64 bit OS
SQL Timeout Error Multiple databases performance Can I script out SQL Server jobs programmatically? setup a sql login which can only reset password kill the long run job automatically Sql server 2000 client libraries and SQL Server 2008 collation for multiple language in a column understanding metrics/performance SQL Server Import Export Wizard does not create table indexes |
|||||||||||||||||||||||
