Session Information 3

[hvisionexpo]

Is there a table procedure that I can query to get all active sessions and current session in a database?

I need to write a trigger that will stamp the username of the person logged in to the table that they are updating.

 

[jbenson001]

sp_who

 

[DotNetGnat]

something like this:

USE master
EXEC sp_who


-DNG

 

[hvisionexpo]

Thanks. I see all the processes.

How do I get the current sessions process ID and information.

For example I have 5 users connected with same login information using same database. I need to get Only MY session information i.e. spid and hostname where loginame = 'xyz'

 

[DotNetGnat]

something like this:

USE master
EXEC sp_who 'yourlogin'

or

USE master
EXEC sp_who 'processid'

-DNG

 

[hvisionexpo]

How do I get my processid in order to run EXEC sp_who 'processid'?

 

[DotNetGnat]

when you run this:

USE master
EXEC sp_who

spid is the system process id

-DNG

 

[hvisionexpo]

Can I do select on it?


I need to get the hostname and spid and put it into table trigger to update the column

e.g.

update mytable
set mylocation = (select hostname from sp_who where spid = mySPid)

 

[DrSql]

Here is script that i created, you cna modify as you desire.


[tt]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmpspwho2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tmpspwho2]
GO

CREATE TABLE [dbo].[tmpspwho2] (
[SPID] [int] NULL ,
[Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Login] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HostName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BlkBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DbName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Command] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CPUTime] [int] NULL ,
[DiskIO] [int] NULL ,
[LastBatch] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProgramName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SPID1] [int] NULL
) ON [PRIMARY]
GO
insert into tmpspwho2 exec('sp_who2')
GO

declare @user varchar(50)
select @user = SUSER_SNAME()
select * from tmpspwho2
where Login=@user
go
drop table tmpspwho2
[/tt]

Dr. Sql
goEdeveloper@yahoo.com
Good Luck.

 

[ClayG]

your own spid is the variable @@SPID