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".

Improve this question
4
  • You'll probably need to prefix the database name as well. TESTCONNECTION.dbo.Test
    – Erik Darling
    Aug 22, 2017 at 13:15
  • @sp_BlitzErik I have already tried SELECT * FROM "TESTCONNECTION.dbo.Test"@SQLSERVER; But got same error message
    – User12345
    Aug 22, 2017 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, 2017 at 13:26
  • @Marco But got same error.
    – User12345
    Aug 22, 2017 at 13:29

1 Answer 1

Reset to default
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

Improve this answer
3
  • Default database is already configured to TESTCONNECTION database
    – User12345
    Aug 22, 2017 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, 2017 at 14:01
  • I can able to access the database after restrating sql server.
    – User12345
    Aug 22, 2017 at 14:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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