Search code examples
sqlsql-serversql-server-2008sql-server-2005sql-server-2000

Referencing another table in T-SQL not working

I've been running a query on a large number of servers, and on just three of them (SQL2000, SQL2005 AND SQL2008) the query fails. I've boiled it down to referencing a table in another DB.

SELECT * FROM MASTER.dbo.syslogins AS syslogins

Which works on 99% of the servers, but on the three gives:

Invalid object name 'MASTER.dbo.syslogins'

If I switch to the master DB and run

SELECT * FROM dbo.syslogins AS syslogins

Then it works. I've also checked I have permissions by running

SELECT IS_SRVROLEMEMBER('sysadmin');

Which returns a 1. So why does referencing the MASTER.dbo.syslogins table fail?

Solution

  • Maybe the server is set up with a case sensitive collation. Try

    SELECT * FROM master.dbo.syslogins