DAO > SQL Server Processes

[Chris]

I need to know exactly what VBA lines Create and Destroy SQL Server
Processes - i.e. those visible in Enterprise Manager under Management[color=blue]
> Current Activity > Process Info.[/color]

Why?

I am experiencing strange behaviour with Processes that are created
when I create a DAO Database Object with the following line:

Set m_ResDatabase = DBEngine.Worksp aces(0).OpenDat abase(strDSN, False,
False, strODBC)

This creates the process as expected.

However the following lines don't always close the ensuing Process:

If Not m_ResRecordSet Is Nothing Then
m_ResRecordSet. Close
Set m_ResRecordSet = Nothing
End If
If Not m_ResDatabase Is Nothing Then
m_ResDatabase.C lose
Set m_ResDatabase = Nothing
End If
If Not m_ResWorkspace Is Nothing Then
m_ResWorkspace. Close
Set m_ResWorkspace = Nothing
End If

It seems as if SQL Server keeps hold of the first two Processes and
then will release any subsequent ones.
Can anyone shed any light in this - or any good web pages where I
might find some answers?

Regards Chris

[Erland Sommarskog]

Re: DAO > SQL Server Processes

Chris (chris.laycock@ addept.co.uk) writes:[color=blue]
> I am experiencing strange behaviour with Processes that are created
> when I create a DAO Database Object with the following line:
>
> Set m_ResDatabase = DBEngine.Worksp aces(0).OpenDat abase(strDSN, False,
> False, strODBC)
>
> This creates the process as expected.
>
> However the following lines don't always close the ensuing Process:
>
> If Not m_ResRecordSet Is Nothing Then
> m_ResRecordSet. Close
> Set m_ResRecordSet = Nothing
> End If
> If Not m_ResDatabase Is Nothing Then
> m_ResDatabase.C lose
> Set m_ResDatabase = Nothing
> End If
> If Not m_ResWorkspace Is Nothing Then
> m_ResWorkspace. Close
> Set m_ResWorkspace = Nothing
> End If
>
> It seems as if SQL Server keeps hold of the first two Processes and
> then will release any subsequent ones.
> Can anyone shed any light in this - or any good web pages where I
> might find some answers?[/color]

Do DAO have connection pooling? Modern client libraries have connection
pooling, which means that when you close a connection from the code,
the API lingers on the connection for a minute, in case you would
reconnect directly. In such case, it's perfectly normal to see the
connections around.

Else, the only reason I can think of is that you had a transaction in
progress when you closed the connections, and the rollback takes a
long time. If you vie the processes with sp_who what state and active
command do they have?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Trevor Best]

Re: DAO > SQL Server Processes

Erland Sommarskog wrote:[color=blue]
> Do DAO have connection pooling? Modern client libraries have connection
> pooling, which means that when you close a connection from the code,
> the API lingers on the connection for a minute, in case you would
> reconnect directly. In such case, it's perfectly normal to see the
> connections around.
>
> Else, the only reason I can think of is that you had a transaction in
> progress when you closed the connections, and the rollback takes a
> long time. If you vie the processes with sp_who what state and active
> command do they have?[/color]

I've seen this in Access and it's a PITA as it makes changing users
impossible without restarting it, e.g. log in as "sa" then close
everything and log in as "joe", the front end thinks you are "joe" but
the back end thinks you are "sa", which can cause unpredictable results.

If it's connection pooling in place then I don't think it was
implemented right. I've seen it work the other way as well while logged
in as normal user I then try to log in as "sa" to manage users, etc and
get told I have no permission to do it.