Connection is busy with results for another hstmt & Win2003 SP1

I wrote a web app using an ASP front end (not .NET) connecting to a SQL
Server 2000 (no SP) back end. Both the web server and the database
server are Windows 2003 boxes. The app was running fine two weeks ago
before I went on vacation. While I was gone my net admin applied
Windows 2003 SP1 to the web server. Upon my return I was informed that
the app is no longer working, getting the following error:

[Microsoft][ODBC SQL Server Driver]Connection is busy with results for
another hstmt

Since that is presumably the only thing that changed in my absence I
believe the SP somehow messed up the way the ODBC was working. I tried
moving the SQL Server database to a different Windows 2003 box which
also received the SP1 update and also includes SP3 for SQL Server, but
get the same error.

I am hitting the database like so:

set GetData = CreateObject("ADODB.Command")
GetData.ActiveConnection = SQLConn
GetData.CommandText = "<stored procedure call>"
Set DataRS= GetData.Execute (,,adCmdText)

I appear to get the error when I execute the second call. I am
learning ASP as I go, so maybe this is a trivial problem.Jegg (jsauri@.gmail.com) writes:
> I wrote a web app using an ASP front end (not .NET) connecting to a SQL
> Server 2000 (no SP) back end. Both the web server and the database
> server are Windows 2003 boxes. The app was running fine two weeks ago
> before I went on vacation. While I was gone my net admin applied
> Windows 2003 SP1 to the web server. Upon my return I was informed that
> the app is no longer working, getting the following error:
> [Microsoft][ODBC SQL Server Driver]Connection is busy with results for
> another hstmt
> Since that is presumably the only thing that changed in my absence I
> believe the SP somehow messed up the way the ODBC was working. I tried
> moving the SQL Server database to a different Windows 2003 box which
> also received the SP1 update and also includes SP3 for SQL Server, but
> get the same error.
> I am hitting the database like so:
> set GetData = CreateObject("ADODB.Command")
> GetData.ActiveConnection = SQLConn
> GetData.CommandText = "<stored procedure call>"
> Set DataRS= GetData.Execute (,,adCmdText)
> I appear to get the error when I execute the second call. I am
> learning ASP as I go, so maybe this is a trivial problem.

The gist of the error message is that you have a command that generated
one or more results, that you have not picked up, and you cannot submit
the next stored procedure for execution.

If that procedure generates result sets, you probably want that data.
Then again, it could be a stray debug result set that should not be
there.

Here are some general rules:
o Unless you want explicit row counts back from INSERT/UPDAET/DELETE
operations, submit a SET NOCOUNT ON when you connect. These rowcounts
are actually kind of result sets, and these need to be consumed.
SET NOCOUNT ON eliminates those.
o If you call a stored procedure that is not supposed to return data,
specify the option adExecuteNoRecords.
o When you run a procedure that can return data, be sure to get all
record sets, by looping over .NextRecordset.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the info. I did have nocount set to on. However, I did not
know about the adExecuteNoRecords option. That actually will come in
very handy.

On a whim I did manage to elimate the error by adding "DataRS = empty"
after the first stored procedure call. I did not have to do this with
any of the others presumably because I loop through the result set
until EOF (there was known to be only one possible record in the first
result set so I did not do this).

Thanks for your speedy reply!

Connection is busy with results for another hstmt

I am working on Sql Server 2000 and Delphi 5. When I try to open a table :

Table1.open;

I get the following error message :

Connection is busy with results for another hstmt.

I am new to Sql Server so please keep the explanation simple !!!

I have already read this got something to do with forward-only, read-only cursor. But I don't know what a forward-only, read-only cursor mean. Please explain !!

Thanks
KarenHowdy

A cursor collects a record set from a database. The cursor has a pointer that can move around in the record set to read a particular row etc. A forward only cursor can only read down ( i.e. forward ) through the record set, so if you are trying to go backwards through the record set it may not let you.

I dont know anything about Delphi, but from SQL Books on Line :

----------

SQLTables
When restricted to the current database, SQLTables executes the Transact-SQL procedure sp_tables to report table catalog data for Microsoft SQL Server.

The following table shows SQLTables parameter mapping for sp_tables stored procedure execution.

SQLTables parameter name sp_tables parameter name
CatalogName table_qualifier
SchemaName table_owner
TableName table_name
TableType table_type

SQLTables can be executed on a static server cursor. An attempt to execute SQLTables on an updatable (dynamic or keyset) cursor will return SQL_SUCCESS_WITH_INFO indicating that the cursor type has been changed.

SQLTables reports tables from all databases when the CatalogName parameter is SQL_ALL_CATALOGS and all other parameters contain default values (NULL pointers). SQLTables does not make use of sp_tables in this special case.

To report available catalogs, schemas, and table types, SQLTables makes special use of empty strings (zero-length byte pointers). Empty strings are not default values (NULL pointers).

The SQL Server ODBC driver supports reporting information for tables on linked servers by accepting a two-part name for the CatalogName parameter: Linked_Server_Name.Catalog_Name.

SQLTables returns information about any tables whose names match TableName and are owned by the current user.

Example
// Get a list of all tables in the current database.
SQLTables(hstmt, NULL, 0, NULL, 0, NULL, 0, NULL,0);
// Get a list of all tables in all databases.
SQLTables(hstmt, (SQLCHAR*) "%", SQL_NTS, NULL, 0, NULL, 0, NULL,0);
// Get a list of databases on the current connection's server.
SQLTables(hstmt, (SQLCHAR*) "%", SQL_NTS, (SQLCHAR*)"", 0, (SQLCHAR*)"",
0, NULL, 0);

Cheers,

SG

Connection is busy with results for another hstmt

HI guys,

I'm using ODBC driver from WinXP SP2 with SQL Server 2005 Express (on my machine for test) and SQL Server 2005 Standard (on the production machine).

With SQL Server 2005 Express I've no problem, but with SQL Server 2005 Standard, in some query, I get "Connection is busy with results for another hstmt".

I'm working with VisualStudio 2003 with standard CDatabase and CRecordSet MFC classes.

I looked for an advice in the Microsoft KB but I found only Service Pack for SQL Server 2000 !

Everything could be useful !

Thanks a lot!

Bye, Domenico

You can avoid this error message by switching to the SQL Server 2005 ODBC driver, 'SQL Native Client' and enabling the MARS (Multiple Active Result Sets feature). There is more information in Books Online and also here http://blogs.msdn.com/dataaccess/archive/2005/08/02/446894.aspx

Connection is busy with results for another hstmt

I have my java based application that works fine with SQL
server 6.5.
I upgraded DB to SQL Server 2000 and now the application
give me "Connection is busy with results for another
hstmt " ODBC error after I do couple of queries or updates
through the application.
Is it anything to do with ODBC drivers or I have to change
setup of SQL Server?
Thank you
Athar
SQL Server only supports a single active statement per connection. Are you
using the MS JDBC driver to access SQL Server?
If you aren't actually trying to use multiple statements per connection,
then you might be able to workaround the error by making sure the statement
is closed before using another one. I'm not familiar with the JDBC
interface, but in ODBC, you'd call SQLMoreResults() until it returned
SQL_NO_DATA to make sure you've processed all of the results on the wire.
In ADO.NET you will get the same error if you haven't called Close() or
Dispose() on the SqlDataReader before trying to Execute another SqlCommand
on the same SqlConnection.
If you are trying to use multiple statements per connection (at the same
time), then you need to use multiple connections instead.
SQL Server 2005 has a new feature called MARS (Multiple Active Result Sets)
which will allow multiple statements per connection.
Brannon
"Athar" <aikram@.celestica.com> wrote in message
news:185a01c47bdf$ee142e80$a601280a@.phx.gbl...
> I have my java based application that works fine with SQL
> server 6.5.
> I upgraded DB to SQL Server 2000 and now the application
> give me "Connection is busy with results for another
> hstmt " ODBC error after I do couple of queries or updates
> through the application.
> Is it anything to do with ODBC drivers or I have to change
> setup of SQL Server?
> Thank you
> Athar

Connection is busy with results for another hstmt

I have my Java based application that works fine with SQL
server 6.5.
I upgraded DB to SQL Server 2000 and now the application
give me "Connection is busy with results for another
hstmt " ODBC error after I do couple of queries or updates
through the application.
Is it anything to do with ODBC drivers or I have to change
setup of SQL Server?
Thank you
AtharSQL Server only supports a single active statement per connection. Are you
using the MS JDBC driver to access SQL Server?
If you aren't actually trying to use multiple statements per connection,
then you might be able to workaround the error by making sure the statement
is closed before using another one. I'm not familiar with the JDBC
interface, but in ODBC, you'd call SQLMoreResults() until it returned
SQL_NO_DATA to make sure you've processed all of the results on the wire.
In ADO.NET you will get the same error if you haven't called Close() or
Dispose() on the SqlDataReader before trying to Execute another SqlCommand
on the same SqlConnection.
If you are trying to use multiple statements per connection (at the same
time), then you need to use multiple connections instead.
SQL Server 2005 has a new feature called MARS (Multiple Active Result Sets)
which will allow multiple statements per connection.
Brannon
"Athar" <aikram@.celestica.com> wrote in message
news:185a01c47bdf$ee142e80$a601280a@.phx.gbl...
> I have my Java based application that works fine with SQL
> server 6.5.
> I upgraded DB to SQL Server 2000 and now the application
> give me "Connection is busy with results for another
> hstmt " ODBC error after I do couple of queries or updates
> through the application.
> Is it anything to do with ODBC drivers or I have to change
> setup of SQL Server?
> Thank you
> Athar

Connection error

S1000 [Microsoft][ODBC SQL Server Driver]Connection is busy with results for
another hstmt
How to resolve this error.Hi
Not much to go on, so look at
http://groups.google.ch/groups?q=Connection%20is%20busy%20with%20results%20for%20&hl=en&lr=&sa=N&tab=wg and see if anything there solves your problem.
When you post, please post more information as errors like this can be
cuased by many problems.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Geeta" wrote:
> S1000 [Microsoft][ODBC SQL Server Driver]Connection is busy with results for
> another hstmt
> How to resolve this error.|||Hi Mike,
The links given by you were of great help. I was using the same
connection object for multiple threads. I used a new connection object and
the issue is now resolved.
Thanks
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Not much to go on, so look at
> http://groups.google.ch/groups?q=Connection%20is%20busy%20with%20results%20for%20&hl=en&lr=&sa=N&tab=wg and see if anything there solves your problem.
> When you post, please post more information as errors like this can be
> cuased by many problems.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Geeta" wrote:
> > S1000 [Microsoft][ODBC SQL Server Driver]Connection is busy with results for
> > another hstmt
> >
> > How to resolve this error.

Connection busy with results for another hstmt...

Hi mates,

I am getting this strange error while working on SQL server through JDBC.
I am using a connection-pooling mechanism. Have written my own connection pool. The pool holds a number of connections and has methods for requesting and returning connections from the clients. The issue is - when I get a connection from the pool and execute a select query, it works fine. Then I return the connection to the pool (at the top). On next request for connection, this same connection object is provided. When another select query is fired on this connection, I get an error - "connection is busy with results for another hstmt"... The error occurs at the time of executing the query and not while obtaining the statement object from the connection object.
If I don't use the connection pool and create new connection every time, everything works fine (except the performance!)...
I'll be grateful if anyone can shed some light on this....
Cheers!That's because each connection can only have 1 active (pending results) statement per connection.

It sounds like you are not closing/dropping the statements, or the driver is not. It might be your connection handling code. You might also try a different jdbc driver. Are you using an sql specific driver? It you use the default jdbc-odbc bridge, you can get bad behavior. That driver doen't work well. There are 3 kinds of replacements:

1) Free Jdbc drivers for SQL Server. MS provides one, and there is an open source one out there.

2) A ready for deployment JDBC-ODBC Bridge from a vendor. There are several.

3) A commercial JDBC Driver.

What you need is dependant on your budget, time and architecture.

Jay Grubb
Technical Consultant
OpenLink Software
Web: http://www.openlinksw.com:
Product Weblogs:
Virtuoso: http://www.openlinksw.com/weblogs/virtuoso
UDA: http://www.openlinksw.com/weblogs/uda
Universal Data Access & Virtual Database Technology Providers