weinrosni
asked on
SQL query to get Process ID
Hi there,
Is it possible to get the client process ID of an application that runs on SQL server?
Is it possible to get the client process ID of an application that runs on SQL server?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The reason I didn't specify SP_WHO2 is because that is a proc that microsoft is moving away from. It is there for backwards compatability and the new DMV referenced is the proper replacement.
I also didn't mention select @@spid because that will give you the current SQL Server process ID/session_id of the SSMS session you are in but that is the internal to SQL Server process information not the process_id within windows on the host that is calling the SQL.
It sounds like that is what the author is asking for.
I also didn't mention select @@spid because that will give you the current SQL Server process ID/session_id of the SSMS session you are in but that is the internal to SQL Server process information not the process_id within windows on the host that is calling the SQL.
It sounds like that is what the author is asking for.
ASKER
Thanks all. All of your comments did help me finding out more information about what i was after. I have distributed the points evenly.
I found this thread by looking for the same information as the asker; assuming that I'm probably not the only one who has, it should be noted that the only correct answer is http:#21830305, however even there the comments seem to be wrong.
Client process id has nothing to do with the processes on the server machine where sql server runs. The term "Client process id" appears only in the documentation on the Profiler, as one of the columns, and is the process id of the client application that has requested the data, on the client machine. If the same client application has opened several connections to sql server, these connections will have different spid, but the same client process id. For example, Microsoft Access ADP is opening several connections to the database.
One practical use of client process id is uniquely identifying the client application working with the data, so even if the user has opened two instances of the same application, they wouldn't use each other's data. This can happen if the application is using a permanent table like a temporary one, for storing intermediate data.
Client process id has nothing to do with the processes on the server machine where sql server runs. The term "Client process id" appears only in the documentation on the Profiler, as one of the columns, and is the process id of the client application that has requested the data, on the client machine. If the same client application has opened several connections to sql server, these connections will have different spid, but the same client process id. For example, Microsoft Access ADP is opening several connections to the database.
One practical use of client process id is uniquely identifying the client application working with the data, so even if the user has opened two instances of the same application, they wouldn't use each other's data. This can happen if the application is using a permanent table like a temporary one, for storing intermediate data.
If you're connecting as a different user, and want to see the sessions that a particular application has open, you could look in sys.dm_exec_sessions - lots of nice information there.
Hopefully the applicationname is being set in the ConnectionString - that'll help you see which sessions are from what.
Rob