Getting the SPID for a pooled connection

[Guest]

I am using disconnected datasets and ADO.Net. How would I get the SPID for a
pooled connection?

I would like to log the user name and SPID each time a user performs a
database update transaction, and then correlate this with the the transaction
log (using the Lumigent Log Explorer tool). This way I could see who changed
what and when.

Because of connection pooling, the SPID for a user changes from transaction
to transaction during their session, when there are multiple users.

 

[Guest]

Maybe I just need to query for @@SPID.

 

[Kevin Yu [MSFT]]

Hi Howard,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to get the SPID for a
connected user. If there is any misunderstanding, please feel free to let
me know.

Yes, as you know, we can use SELECT @@SPID directly to get SPID for the
server process identifier (ID) of the current user process.

You can check the following link for more infomation:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
globals_1wh0.asp

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."