Prev: Alternatives to cursors
Next: How to use GO with a sql string
From: John on 2 Jun 2010 17:38
I'm having the same issue. I have a Delphi application that uses an ADO connection and for some reason the ADO layer is creating mutiple SPIDs and I don't know how to fix it? I have one ADO connection object and my app connects once, but multiple SPIDS are used when executing statements through the ADO layer.

From http://www.developmentnow.com/g/113_2004_6_0_0_428184/Multiple-sessions-when-using-ADO.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com/g/
From: Erland Sommarskog on 2 Jun 2010 17:58
John (nospam(a)developmentnow.com) writes:
> I'm having the same issue. I have a Delphi application that uses an ADO
> connection and for some reason the ADO layer is creating mutiple SPIDs
> and I don't know how to fix it? I have one ADO connection object and my
> app connects once, but multiple SPIDS are used when executing statements
> through the ADO layer.

This happens when you issue a command on a connection before you have
consumed all rows in the result set. ADO finds that the connection is
busy and opens a new behind your back. While seemingly helpful, this can
just as well cause trouble.

The most common reason for unprocessed result sets, is the failure
to get the row counts for INSERT, UPDATE and DELETE statements. Tips to
deal with this:

1) Issue SET NOCOUNT ON when you connect.
2) Use adExecuteNoRecords if you don't plan to get any data back.
3) Loop over .NextRecordset until it returns Nothing.


--
Erland Sommarskog, SQL Server MVP, esquel(a)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

 | 
Pages: 1
Prev: Alternatives to cursors
Next: How to use GO with a sql string