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.

Solution

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)

Comments

[Chris Meek]

This does indeed fix the problem in this particular script. However I'd really like to know why this would fail to run on some configurations as it seems to be affecting other scripts too.

[Dave Markle]

It sounds to me like someone with sysadmin privileges somehow accidentally borked the definition of your dbo.sysprocesses view at some point...