'Issues while Using Nested DB Link queries in AWS RDS SQL Server

I have three DB servers and looking to establish a Link Server connection among them to periodical.

  • A - AWS RDS SQL Server database
  • B - SQL Server database on AWS EC2 instance
  • C - Exadata Oracle Server - On-prem

I have got 2 issues as follows:

ISSUE 1

Task is to establish SQL Server Link server connection between DB Server A (SQL Server) and DB Server C (Oracle). All network firewalls and Outbound and rules are defined from Server A to Server C, still I am not able establish a connection between the two servers.

I get the following error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

The test connection to the linked server failed.

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The OLE DB provider "OraOLEDB.Oracle" has not been registered. (Microsoft SQL Server, Error: 7403)

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-7403-database-engine-error

------------------------------
BUTTONS:

OK
------------------------------

ISSUE 2

As a workaround I tried establish a linked server connection from Server A to Server B and then from Server B to Server C. Explained as below:

  • Link Server Connection 1 (ABC) - Server A (RDS SQL Server) to Server B (AWS EC2 SQL Server)
  • Link Server Connection 2 (XYZ)- Server B (AWS EC2 SQL Server) to Server C (Oracle Exadata On-prem). Server B was having an existing connection to Server C.

I tried accessing it using the nested Link Server query. This query is executed on Server A

select * from OPENQUERY (ABC,' SELECT * FROM OPENQUERY (XYZ,''SELECT * FROM EMP'')')

The above query works and produce the required results.

But as I try to use a complex version of the query with couple of joins and where clause it throws error.

Query is as follows:

SELECT * FROM OPENQUERY( ABC,'SELECT * FROM OPENQUERY
   (XYZ,''
           SELECT
            c.emp_id
            ,c.emp_name
            ,e.emp_dept
            FROM
            emp_det c
            JOIN emp e ON c.emp_id = e.emp_id 
            WHERE
            c.emp_id = ''123456''
     '')
   ')

Error Message

OLE DB provider "MSOLEDBSQL" for linked server "ABC" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '123456'.

I have tried searching a solution on the web but did not find anything that can help solve the issue. Would appreciate if someone can help me solve either of these problems.

TIA

sql-serverlinked-server


Solution 1:[1]

Resolved Issue No. 2 by adjusting and balancing the single quotes.

The SQL statement of the nested OPENQUERY will start with a double quotes and value in where clause needs to be enclosed with four pairs of single quotes.

Refer to the working query below:

SELECT * FROM OPENQUERY
( ABC,'
SELECT * FROM OPENQUERY(XYZ,''
        SELECT 
            c.emp_id
            ,c.emp_name 
            ,e.emp_dept
            FROM
                emp_det c 
                JOIN emp e ON c.emp_id  = e.emp_id 
            WHERE
                c.emp_id = ''''123456''''
            '')')

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 A.Bhargava

Related Questions