Hi Guys,
I have question about how to create dblink between ORACLE and MSSQL, right now I have created 1 link from Oracle to MSSQL but while I am trying to query I am getting below error:
ORA-00942: table or view does not exist
[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid object name 'WSS_CONTENT.ALLLISTS'. {42S02,NativeErr = 208}[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Statement(s) could not be prepared. {42000,NativeErr = 8180}
ORA-02063: preceding 2 lines from MINH_LINK
- 00942. 00000 - "table or view does not exist"
*Cause:
*Action:
Error at Line: 3 Column: 27
But this query return result successfully:
select * from dual@MINH_LINK;
Here you can find the steps which I have applied for creating dblink between mentioned dbms: http://minhtech.com/oracle/oracle-11g-database-link-to-microsoft-sql-server/
In MSSQL side my tables are under: WSS_CONTENT.DBO.table_names which means WSS_CONTENT is my schema, DBO is my user and table_names are the tables under DBO user.
While query the tables directly from MSSQL, I am using such structure: Select * from [WSS_CONTENT].[dbo].[table_name], so if I am going to use dblink it should be like this from Oracle side: Select * from [WSS_CONTENT].[dbo].[table_name]@MINH_LINK but again return the error "Table or view doesnt exist" which means either link is not used and error directly return from Oracle side or this kind of structure tree is not supported in Oracle and thats why such error returns.
Please share your experience if you have created dblink between Oracle and MSSQL successfully.
Waiting for you responses, cheers