Prev: Microsoft SQL Server 2005 Express Edition Service Pack 3 (KB955706
Next: Figuring sql server version via code
From: Erland Sommarskog on 20 Mar 2010 15:23
ralph (nt_consulting64(a)yahoo.net) writes:
> Also as noted above, SQL Server will often create additional 'internal
> connections' for its own use for some queries.

No, this is entirely incorrect. This will not happen.

Any extra connections that does not directly related to Connection.Open,
are due to that ADO/OLE DB opens extra connections behind your back.

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

From: ralph on 21 Mar 2010 02:53
On Sat, 20 Mar 2010 20:23:32 +0100, Erland Sommarskog
<esquel(a)sommarskog.se> wrote:

>ralph (nt_consulting64(a)yahoo.net) writes:
>> Also as noted above, SQL Server will often create additional 'internal
>> connections' for its own use for some queries.
>
>No, this is entirely incorrect. This will not happen.
>
>Any extra connections that does not directly related to Connection.Open,
>are due to that ADO/OLE DB opens extra connections behind your back.

You are confusing ADO with OLE DB, and OLE DB with the data 'engine'.
Actually it is the Provider, but I will not quibble about who is doing
what, or under what conditions, or even what is more germane - what
exactly is a "connection".

The fact remains it is quite common for a developer to meticulously
track "ADO Connections" in a client application to determine their
number, while another developer using database services will come up
with a different number, usually greater with varying degrees of
accuracy. (The latter because of the introduction of Heisenbugs.)

Whether the extra connections are being nefariously created and
"hidden" by ADO or whether the database engine is deviously demanding
and using "extra" connections doesn't really matter - they are there.
From: Erland Sommarskog on 21 Mar 2010 07:26
ralph (nt_consulting64(a)yahoo.net) writes:
> You are confusing ADO with OLE DB, and OLE DB with the data 'engine'.

Not really. I do have quite a good understanding of what is what.

> Whether the extra connections are being nefariously created and
> "hidden" by ADO or whether the database engine is deviously demanding
> and using "extra" connections doesn't really matter - they are there.

To say that this is of no importance, is certainly not correct. If you
look for how to turn how to this behvaiour, you would be out of luck if
you looked in SQL Server. However, if you set the session property
DBPROP_MULTIPLECONNECTIONS to VARIANT_FALSE in OLE DB, this does not
happen any more.

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.

Furthermore, saying that this happens in the engine may lure users of
other client APIs to think that they may also be subject to this "feature",
but this is incorrect.

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

From: febo on 21 Mar 2010 14:10
Il 21/03/2010 12:26, Erland Sommarskog ha scritto:

> To say that this is of no importance, is certainly not correct. If you
> look for how to turn how to this behvaiour, you would be out of luck if
> you looked in SQL Server. However, if you set the session property
> DBPROP_MULTIPLECONNECTIONS to VARIANT_FALSE in OLE DB, this does not
> happen any more.
>
> 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..

What about using MARS and the native sql client?

From: Andrew J. Kelly on 21 Mar 2010 14:22
SQL Server does not create connections but it may spawn multiple threads for
a given connection. These are not extra connections they are extra threads
which are not the same thing as Erland points out.

--

Andrew J. Kelly SQL MVP
Solid Quality Mentors

"ralph" <nt_consulting64(a)yahoo.net> wrote in message
news:62ebq5t44do9dd8q1i9b9soj63c1vguako(a)4ax.com...
> On Sat, 20 Mar 2010 20:23:32 +0100, Erland Sommarskog
> <esquel(a)sommarskog.se> wrote:
>
>>ralph (nt_consulting64(a)yahoo.net) writes:
>>> Also as noted above, SQL Server will often create additional 'internal
>>> connections' for its own use for some queries.
>>
>>No, this is entirely incorrect. This will not happen.
>>
>>Any extra connections that does not directly related to Connection.Open,
>>are due to that ADO/OLE DB opens extra connections behind your back.
>
> You are confusing ADO with OLE DB, and OLE DB with the data 'engine'.
> Actually it is the Provider, but I will not quibble about who is doing
> what, or under what conditions, or even what is more germane - what
> exactly is a "connection".
>
> The fact remains it is quite common for a developer to meticulously
> track "ADO Connections" in a client application to determine their
> number, while another developer using database services will come up
> with a different number, usually greater with varying degrees of
> accuracy. (The latter because of the introduction of Heisenbugs.)
>
> Whether the extra connections are being nefariously created and
> "hidden" by ADO or whether the database engine is deviously demanding
> and using "extra" connections doesn't really matter - they are there.

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