asked on

Invalid Object Name

I have on my SQL box a Stored Procedure in a database called 'Asset'. My problem with this stored procedure is that it wants to reference another table in a separate SQL Server database called 'Discovery' held in the same server group. The table name in 'Discovery' is called 'Audit' and the error message is "[Microsoft][ODBC SQL Server Driver][SQL Server]invalid object name 'Discovery.Audit'" when running the stored procedure. I have tried (with no luck) 'Discovery.dbo.Audit' and am using the generic 'sa' login. The stored procedures permissions are set to 'public' as is the table 'Audit'.

I might resign soon over this, which isn't that bad, but only after I have kicked to pieces the Comms room, which is that bad.

sedmans

Is the Discovery db on a different server to the Asset db?
If so try ServerName.Discovery.dbo.Audit

adathelad

Hi

You need to have the "Discovery" server setup as a linked server on your SQL box.

This can be done through Enterprise Manager. Click on your SQL box go to the Security folder -> Linked Servers.
Add the "Discovery" server in there with the appropriate login details.

The error u are getting is because your SQL box does not know of this other server, you have to explicitly define it.

Cheers

adathelad

ok, I may have gone down the wrong path. I had thought you meant "Discovery" was another server....my fault, I misread ur post.

adathelad

COuld you post your stored procedure code?

ASKER CERTIFIED SOLUTION

silvioq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

Verify that the owner of the Audit table is actually dbo.

Also, please post the exact code that is being run.

Hob_Nob

ASKER

Good work silviog, I will not resign now, and will get my money back on an axe I bought