Reuse application-level connection object 
Author Message
 Reuse application-level connection object

In my VB6 app, I am using an application level connection
object to connect to SQL Server 7 database.  All data
queries and updates are done through stored procedure
calls using ADO command object.  In reviewing the SQL
Profiler log, it looks like the existing connection object
is being reused successfully whenever a stored procedure
returns a recordset (select stmts only in sp, and
recordset created using open command and passing the
command object as parameter).  On the other hand, all
calls to stored procedures with update and insert SQL
stmts (using execute on command object) are causing SQL
Server to create a new connection each time.  I am
explicitly setting ActiveConnection to application level
connection object (using Set Cm.ActiveConnection = Cn).  

Here is the code:

Set Cm = New ADODB.Command
Set Cm.ActiveConnection = Cn
Cm.CommandTimeout = 45
Cm.CommandType = adCmdStoredProc
Cm.CommandText = "procAddM1Ranking"

Set prm = Cm.CreateParameter("mSSN", adChar, adParamInput,
9, tmpSSN)
Cm.Parameters.Append prm
Cm.Execute recordsAffected, adExecuteNoRecords
Set Cm = Nothing

This code runs fine for about 250 some iterations and then
tends to hang (I am assuming it's because of number of
concurrent connections), and if it recovers before it
reaches the timeout interval, it runs fine again.  

Does anyone know why SQL Server is creating a new
connection each time and not reusing existing connection?



Wed, 01 Dec 2004 02:09:36 GMT  
 Reuse application-level connection object
Hi,

Check next article. I think this is what you need

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q194979

--
Val Mazur
Microsoft MVP


Quote:
> In my VB6 app, I am using an application level connection
> object to connect to SQL Server 7 database.  All data
> queries and updates are done through stored procedure
> calls using ADO command object.  In reviewing the SQL
> Profiler log, it looks like the existing connection object
> is being reused successfully whenever a stored procedure
> returns a recordset (select stmts only in sp, and
> recordset created using open command and passing the
> command object as parameter).  On the other hand, all
> calls to stored procedures with update and insert SQL
> stmts (using execute on command object) are causing SQL
> Server to create a new connection each time.  I am
> explicitly setting ActiveConnection to application level
> connection object (using Set Cm.ActiveConnection = Cn).

> Here is the code:

> Set Cm = New ADODB.Command
> Set Cm.ActiveConnection = Cn
> Cm.CommandTimeout = 45
> Cm.CommandType = adCmdStoredProc
> Cm.CommandText = "procAddM1Ranking"

> Set prm = Cm.CreateParameter("mSSN", adChar, adParamInput,
> 9, tmpSSN)
> Cm.Parameters.Append prm
> Cm.Execute recordsAffected, adExecuteNoRecords
> Set Cm = Nothing

> This code runs fine for about 250 some iterations and then
> tends to hang (I am assuming it's because of number of
> concurrent connections), and if it recovers before it
> reaches the timeout interval, it runs fine again.

> Does anyone know why SQL Server is creating a new
> connection each time and not reusing existing connection?



Wed, 01 Dec 2004 04:15:13 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Reusing ADO connections in HTA applications

2. Command Object vs Connection Object for Row Level Locking

3. Command Object vs Connection Object for Row Level Locking

4. Command Object vs Connection Object for Row Level Locking

5. Reusing Connection objects with MSDAIPP

6. Unclear on Reuse of Connection & Recordset Objects

7. Move ADO Connection object to Applictaion level?

8. Remote Data Objects in combination with level 1 compliance level

9. ADO Connection Reuse

10. Unable to reuse ADO.Recordset connection using VBA script in Excel

11. Question re: reusing the same ADODB.Connection in my VB app

12. Connection reuse

 

 
Powered by phpBB® Forum Software