Hi Bonnie,

If you want active connections, use sp_who 'active'.

There is a system proc to do these sorts of operations - which is what UI tools like EM actually use.

< JVP >


>Hmmm ... I tried playing around with that a little bit ... are you sure that it's actually representing open connections or might it have something to do with connection pooling? The reason I wonder about that is because I see the connections disappear on their own, even after the app is closed. Here's what I did:
>
>I ran your script (I'm doing this in Query Analyzer), it showed a few connections from the SqlClient while the app was running. I closed the app, those connections still appeared even after I was no longer running the app. But, after a few minutes, I ran the script again and one of the connections was no longer there. In fact, after about 10 minutes, all the connections had disappeared. Sounds like connection pooling to me!! Whaddaya think?
>
>~~Bonnie
>
>
>
>
>>>Yep, that's the way it's supposed to work.
>>>Hmmm ... how are you determining that the connection is still open?
>>
>>With a stored proc I wrote that implements this query:
>>
>>select master.dbo.sysdatabases.Name,
>>   master.dbo.sysprocesses.login_time,
>>   master.dbo.sysprocesses.nt_username,
>>   master.dbo.sysprocesses.loginame,
>>   master.dbo.sysprocesses.program_name,
>>   master.dbo.sysprocesses.open_tran,
>>   master.dbo.sysprocesses.uid
>> from master.dbo.sysprocesses
>>   join master.dbo.sysdatabases
>>    on master.dbo.sysprocesses.dbid
>>     = master.dbo.sysdatabases.dbid
>> where master.dbo.sysdatabases.name = 'Northwind'
>>
>>This returns a result set with a single record for each active connection. Very helpful when you're trying to ensure that a LAN application is using no more than one connection, or when determining how many users are actively logged into an application (assuming each instance of the App is using one connection).