Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL query to get Process ID

Posted on 2008-06-20
8
Medium Priority
?
15,417 Views
Last Modified: 2011-03-21
Hi there,

Is it possible to get the client process ID of an application that runs on SQL server?
0
Comment
Question by:weinrosni
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 39

Assisted Solution

by:appari
appari earned 200 total points
ID: 21829218

select @@SPID
0
 
LVL 15

Accepted Solution

by:
rob_farley earned 600 total points
ID: 21829537
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 hel

Rob
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 21829542
(sorry - annoying touchpad click)

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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 13

Assisted Solution

by:MikeWalsh
MikeWalsh earned 600 total points
ID: 21830305
You were asking for the client process ID. I presume you mean the actual Windows Server/OS PID that you would see in Task Manager.

You want to look at the Host_Process_ID column in the sys.dm_exec_sessions dmv.

Example: select host_process_id from sys.dm_exec_sessions

Obviously you would want to grab more information but the host_process_id should show you the process_id of any Sessions going on in SQL Server that are being executed from an outside process. Using that with Hostname should help you find what you are looking for.
0
 
LVL 1

Assisted Solution

by:hennessym
hennessym earned 600 total points
ID: 21834838
You could also try sp_who2.  That will give you the SPID, hostname, database, and other interesting information.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 21835410
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.
0
 

Author Comment

by:weinrosni
ID: 22013565
Thanks all. All of your comments did help me finding out more information about what i was after. I have distributed the points evenly.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 35185398
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.
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question