Posted by: virusno1 September 29, 2010
how to find SPID of client program in SQL-Server
Login in to Rate this Post:     0       ?        
AlwaysHappy,
Your query is not correct. @@SPID gives you the current SPID of the user. So You will give only one spid always.
Here is the complete query. 

IF EXISTS (SELECT * 
           FROM   tempdb.sys.objects 
           WHERE  object_id = Object_id('Tempdb.dbo.#cLIENTsPID')) 
  DROP TABLE #clientspid 

CREATE TABLE #clientspid ( 
  spid        VARCHAR(10), 
  [status]    VARCHAR(20), 
  [login]     VARCHAR(100), 
  [hostname]  VARCHAR(100), 
  blkby       VARCHAR(10), 
  dbname      VARCHAR(100), 
  command     VARCHAR(100), 
  cputime     INT, 
  diskio      INT, 
  lastbatch   VARCHAR(100), 
  programname VARCHAR(100), 
  spid1       INT, 
  requestid   INT) 

INSERT INTO #clientspid 
EXEC Sp_who2 

SELECT spid, 
       hostname, 
       ProgramName
FROM   #clientspid 
WHERE  hostname NOT IN ('  .',@@SERVERNAME)
Last edited: 29-Sep-10 05:26 PM
Read Full Discussion Thread for this article