Prev: Microsoft SQL Server 2005 Express Edition Service Pack 3 (KB955706
Next: Figuring sql server version via code
From: Erland Sommarskog on 21 Mar 2010 18:21
febo(a)delenda.net (febo(a)delenda.net) writes:
>> I'm quite certain that you can control this from ADO level with one of
>> these dynamic properties, although I don't know what you property name
>> you need to specify.
>
> You can (only at runtime I guess), but an existing classic ado
> application will probably break..

As if it wouldn't break with those extra connections!

The extra connections is a trick that does not always work. For instance,
say that you open a connection, and then you create a temp table in SQL
Server. Then you start a loop where you try to insert records in the temp
table. The first INSERT goes alright, but the second fails with an error
saying that your temp table does not exist.

There are plenty of variations on this theme. Some of them can easily be
avoided by issuing SET NOCOUNT ON and AdExecuteNorecords. Then there is
the classic scenario where you get rows from the server on a server-
side cursor (or a firehose cursor), and try to update rows as they
arrive. This is when you need the new connection, because the current
connection is busy. But if you are unlucky, SQL Server is still holding
a lock on the row you wish to update, and you deadlock yourself.

So I would suggest setting DBPROP_MULTIPLECONNECTIONS to False, means
that you trap the situations where you get an extra connections early.

> What about using MARS and the native sql client?

MARS is one more attempt to address the desire to update the rows as
you receive. And it is one more failed attempt. Maybe it works in exactly
that scenario. There are a whole lot of other situations where you could
try MARS, and the result will be utterly confusing. Say that you write
a multi-thread application and want to share a connection between the
thread. MARS looks like the thing for you? Not really. Since requests are
executed in interleaved fashion, you have now created a serialisation
point in SQL Server, rather than protecting the connection with a
semaphore in your application.


--
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

First  |  Prev  | 
Pages: 1 2 3
Prev: Microsoft SQL Server 2005 Express Edition Service Pack 3 (KB955706
Next: Figuring sql server version via code