0

I have created a Heterogeneous connection between oracle and sql server. and tested with below query,

SELECT * FROM sys.tables@SQLSERVER; 

Then I have created a new Database in SQL server called "TESTCONNECTION" and created a table called "dbo.TEST". After that I changed the default database of DSN to "TESTCONNECTION". But below query fail with following error message.

SELECT * FROM dbo.TEST@SQLSERVER;

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'DBO.TEST'. {42S02,NativeErr = 208}[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. {42000,NativeErr = 8180

}

How to overcome this error and access tables created under "TESTCONNECTION".

  • You'll probably need to prefix the database name as well. TESTCONNECTION.dbo.Test – Erik Darling Aug 22 '17 at 13:15
  • @sp_BlitzErik I have already tried SELECT * FROM "TESTCONNECTION.dbo.Test"@SQLSERVER; But got same error message – User12345 Aug 22 '17 at 13:20
  • Did you put the table name between quotes (SELECT * FROM "dbo.TEST"@SQLSERVER;)? Oracle SQL is not case sensitive by default. – Marco Aug 22 '17 at 13:26
  • @Marco But got same error. – User12345 Aug 22 '17 at 13:29
0

One potential cause could be that the default database listed for the user account being used to connect to SQL Server is pointed to the wrong database. Adjust this on the SQL Server side and see if that resolves your issue.

enter image description here

  • Default database is already configured to TESTCONNECTION database – User12345 Aug 22 '17 at 13:53
  • @User12345 I just have a nagging feeling you're not connecting to the target database you think you are. I guess you can confirm by rerunning the statement that worked against sys.tables and see if the new table appears in the list or not. – John Eisbrener Aug 22 '17 at 14:01
  • I can able to access the database after restrating sql server. – User12345 Aug 22 '17 at 14:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.