master..sysprocesses issue in SQL Server 2005

I can run the following query in Sql Server 2000 with no issues but in SQL server 2005
I get the blocked and program_name fields as the ambiguous field error.

select spid, blocked, hostname, program_name, loginame from master..sysprocesses
7of9121098Asked:
Who is Participating?
 
25112Commented:
try this:

select
spid,
blocked as BlockedProcess ,
hostname,
program_name as ProgramName,
loginame,
 * from master..sysprocesses
where status <> 'background'
order by blocked desc,program_name
0
 
25112Commented:
I get the statement working ok on both 2000 and 2005. can you post an image of the error you are getting?
0
 
25112Commented:
do you mean NULL?
blocked column can be NULL..
and if is a internal system process, the program_name could be NULL, too..
http://msdn.microsoft.com/en-us/library/ms179881%28v=SQL.90%29.aspx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
25112Commented:
i should say NULL or blank
0
 
7of9121098Author Commented:
The problem is with the ORDER BY statement, it works without it. Weird...

select spid, blocked, hostname, program_name, loginame, * from master..sysprocesses
-- to avoid the sql process
where status <> 'background'
order by blocked desc,program_name
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'blocked'.
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'program_name'.
0
 
7of9121098Author Commented:
Wierd, I wonder why it didn't work with alias. Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.