I am trying to find a select on sysprocesses that would list all the active
logins. An active login is a login that has a TSQL statment being executed
on the server,

This didnt work to well! Any ideas. Thanks in advance.

select sp.loginame,
-- more columns
from master..sysprocesses sp
where sp.status not in ('sleeping','background' )
order by 1

You can use the system stored procedure sp_who & sp_who2 for these purposes.

--
- Anith
( Please reply to newsgroups only )

kr (zzb26 (at) email.com) writes:
> I am trying to find a select on sysprocesses that would list all the
> active logins. An active login is a login that has a TSQL statment being
> executed on the server,
>
> This didnt work to well! Any ideas. Thanks in advance.
>
> select sp.loginame,
> -- more columns
> from master..sysprocesses sp
> where sp.status not in ('sleeping','background' )
> order by 1

In which way did it not work? In any case, you definitely want to add
"or opentrn > 0". A process which has a open transaction is active,
even if it is sleeping.

I have a lock-monitoring routine, and the condition I use is

upper(p.cmd) <> 'AWAITING COMMAND'

I also check whether sysprocesses.blocked > 0.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp