Sql Server sysprocesses query

Question

I have the following query which runs fine on all of my sql server 2005/2008 databases

SELECT sysprocesses.spid
FROM master.dbo.sysprocesses

However for one of my databases it give me a binding error on the spid column (cannot bind multipart identifier).

I've check the compatibility mode of the db and it's set to 2005 so I'm sure this isn't the problem but can't figure out what else to check.

Answer 1

Try using:

select spid from sys.sysprocesses

instead. dbo.sysproceses is deprecated AFAIK.

Answer 2

try this:

SELECT s.spid
FROM master.dbo.sysprocesses s

However, master.dbo.sysprocesses and its compatibility view sys.sysprocesses are deprecated, so use this instead:

select session_id from sys.dm_exec_sessions

see: Mapping System Tables to System Views (Transact-SQL)