3 Replies Latest reply: Aug 12, 2013 1:55 AM by Kgronau-Oracle RSS

    Syntax to query a MSSQL 2008R2 linked server from Oracle

    SébastienG

      Hi,

          I would know if it's possible to query a MSSQL 2008R2 linked server from Oracle. Actually I can query local MSSQL databases from Oracle using dg4odbc without problem but how can I query the linked servers configured on the MSSQL side ?

       

      From MSSQL, I can query those linked servers using the [linked server name].[catalog].[owner].[table] syntax but using select * from [linked server name].[catalog].[owner].[table]@oracle_dblink_name syntax from the oracle side throw this error:

       

      ERROR at line 1:

      ORA-00933: SQL command not properly ended

       

      I tried some syntax variations, e.g. putting double quotes around the object name without luck. After some hours to find a solution I'm out of idea...

       

      So please if someone had a clue on this it would be very helpfull.

       

      Some info:

       

      Oracle 10g 10.2.0.5 Enterprise Edition x64

      Oracle Linux 5.9 Enterprise Edition x64

       

      MSSQL 2008R2 Enterprise x64

      Windows Server 2008R2 Enterprise x64

      Oracle 11gR2 Enterprise x64 for dg4odbc on MSSQL side

      SQL Server Natice Client 10.0 used as ODBC driver

       


        • 1. Re: Syntax to query a MSSQL 2008R2 linked server from Oracle
          Kgronau-Oracle

          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

          or

          How to run a SQL Server 2005 Integration Services package as a SQL Server Agent job step

           

          Article ID:912911
          • 2. Re: Syntax to query a MSSQL 2008R2 linked server from Oracle
            SébastienG

            Hi kgronau,
                            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 ?

             

            Thanks

            • 3. Re: Syntax to query a MSSQL 2008R2 linked server from Oracle
              Kgronau-Oracle

              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.