Accessing Tables across databases

[ncscott]

I have two databases (in the same SQL instance), and I am trying to access a table in database A from a stored procedure which resides on database B. The format of the query is simply:

select field1, field2, field3
from DatabaseB..Tablename

I have this query working find on my local development SQL instance, but when put onto the production server, it returns the error,

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'DatabaseB..Tablename'.

Both the development and production environment are running default installs of SQL Server 2000, and the database and table names are identical across the two environments. Might I have an ODBC issue or perhaps a permissions issue?

 

[nigelrivett]

Probably DatabaseB..Tablename doesn't exist on that server.
It could be that the production server is case sensitive?

Try running the query in query analyser.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[ncscott]

The database and tablename do exist on the production server, and the correct names, including cases, are in the query, which the guy I am working with have run through query analyzer and gotten the error above. He is in front of the production system, I am trying to help him over the phone.

 

[nabiS2K]

You may be having trouble because you do not specify the owner of the database object.

Have you tried DatabaseB.dbo.TableName? Depending on the defaults for the production server, it may not choose the correct owner for the table and return your the same error.

 

[nigelrivett]

How about the table owner then.

try
use DatabaseB
select * from sysobjects where name = 'Tablename'


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.