Sean said:
> This is a known issue with SQL and ODBC. The combination only allows one
> active (updatable?) query per connection. A query is active until all
rows
> have been retrieved from the server. This is why each TTable establishes
a
> new connection.
Just wanted to highlight that it's a problem peculiar to SQL-Server. A *big*
problem, IMHO. Sure, SQL-Server looks pretty, but that's not what being a
database is all about :-)
> Solutions/workarounds
>
> 1 When opening queries, use a FetchAll (or Last) if possible to retrieve
all
> rows.
I know you have no choice (if you're stuck with SQL-Server), but that kind
of code is going to make performance suck, and your network groan.
> 2 Open a new connection (using TSession or TDatabase, can't remmeber
which)
> for persistant offender queries.
If SQL-Server uses connection-based licensing, you're going to run out of
connections pretty quickly!
> I was going to write a query descendant which would open a new connection
> for any live query. However use of 1 and 3 has quelled the problem.
Of course the other solution is to use a real database :-)
[ Ducks ]
[ Runs ]
Cheers,
Kerry S
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
> > Behalf Of [EMAIL PROTECTED]
> > Sent: Tuesday, 15 June 1999 15:08
> > To: Multiple recipients of list delphi
> > Subject: [DUG]: "Connection is busy with results for another hstmt"
> >
> >
> > Hi all,
> >
> > I am currently struggling to get past the following error message
> > when firing off several large queries to SQL Server 7.0:
> >
> > "Connection is busy with results for another hstmt"
> > I am connecting using ODBC, Delphi 4 C/S on WinNT 4.0. A quick
> > search on Developers.href reveals many others who have
> > encountered the same problem, but no practical solutions. One
> > approach is to use server side cursors, but we are executing
> > complex queries which do not meet the criteria for a server
> > cursor (ie multiple SELECTs in a statement).
> > Any suggestions most gratefully accepted.
> > On a related note, we have found that connecting to SQL Server 7
> > via the BDE with the native driver supplied with D4 C/S is
> > considerably (as much as 250%) slower than accessing via the ODBC
> > driver. Anybody noticing similar results, or have we missed
> > something major??
> >
> > Cheers,
> > James.
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
