Here in this forum we only deal with links from your Oracle database to MS SQL Server or other foreign databases. When you want to use the MS SQL Server linked database mechanism you might better ask the question in Oracle's ODBC or OLEDB forum (depending on the method you use to link the tables) or you post that in a Microsoft SQL Server forum.
In addition feel free to check out Microsoft's knowledge base and check out these articles:
280106 - HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in SQL Server
How to run a SQL Server 2005 Integration Services package as a SQL Server Agent job step
Article ID : 912911
Maybe I'm not clear in my explanations, sorry.
I want, from Oracle, to query a MSSQL linked server.
Basically I want to do that, if possible:
Oracle --> MSSQL --> linked server --> MSSQL
and maybe have a clue about the syntax to be used.
Knowing that querying the linked server directly form MSSQL is working and querying from Oracle to local MSSQL db is working too.
Using the right syntax should lead me to succes, am I right ? Or maybe its something that cannot be done using oracle dblinks ?
1 person found this helpful
That won't work as in Oracle you can only use a schema and a table/view name separated by a dot. So when you want to access linked server objects you have to define in your MS SQL Server a view that references the linked server object and then access it the gateway and the newly created view rather then using full qualified names.
In addition please make sure MTS is started on the SQL Server to track transactions.