Strange thing that has been observed using the same query. It works on my local host (SQL 2008 R2) while it does not on dev environment (SQL Server 2005 Express).

I compare the database options between these databases. The 2008 has the option

SET HONOR_BROKER_PRIORITY OFF

while 2005 does not.

Hope someone can help.