Invalid Object Name

Hob_Nob
Hob_Nob used Ask the Experts™
on
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.      
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Is the Discovery db on a different server to the Asset db?
If so try ServerName.Discovery.dbo.Audit
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
ok, I may have gone down the wrong path. I had thought you meant "Discovery" was another server....my fault, I misread ur post.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

COuld you post your stored procedure code?
Commented:
Use server.database.[ownertable].tablename
Example servername.Discover..audit
oh ... You must have permissions for this and link the servers.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

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

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

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial