7of9121098
asked on
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
I get the blocked and program_name fields as the ambiguous field error.
select spid, blocked, hostname, program_name, loginame from master..sysprocesses
I get the statement working ok on both 2000 and 2005. can you post an image of the error you are getting?
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
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
i should say NULL or blank
ASKER
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'.
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'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wierd, I wonder why it didn't work with alias. Thanks.