0

I have created a dblink 'POC_HS' from oracle to sql (implemented heterogeneous services) and I am able to successfully pull out data from the default database that the DSN(for sql server) is connected to.

So this 'select * from Test@POC_HS' is working perfectly fine on the Oracle database as 'Test' table resides in the default database (which the System DSN is connected to).

But when I do 'select * Abc.Test@POC_HS' where Test table resides in 'ABC' database which is not the default database throws an error as follows:

ORA-00942: table or view does not exist [Generic Connectivity Using ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Abc.Test'.[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (SQL State: S0002; SQL Code: 208)

The dblink user and System DSN account has access to the 'Abc' database.

Thoughts?

anivohra
  • 221
  • 2
  • 8

2 Answers2

0

The full path to an object is Database.Schema.Object so I'm guessing you should be writing something like this:

select * 
from Abc.dbo.Test@POC_HS

Replace dbo with whatever the actual schema name is, of course.

Pondlife
  • 15,992
  • 6
  • 37
  • 51
  • I tried doing "select * from Abc.dbo.Test@POC_HS" but oracle throws this exception "ORA-00933: SQL command not properly ended" – anivohra Aug 11 '11 at 05:00
0

Answer here - http://forums.oracle.com/forums/message.jspa?messageID=9794581

ODBC Sql Server driver doesn't allow to query across the database, one has to change the odbc default database each time you want to query a different database.

anivohra
  • 221
  • 2
  • 8