Post reply

2 instances

  • February 10, 2011 at 12:30 pm

    #246404

    HI all

    We currently have a linked server 'ABC' set up from sql1(SQL Server) database to Ora1(Oracle) database. We need to create another linked server named 'ABC' on the same machine from sql2 to ora2 database. Both the SQl server databases sql1 & sql2 reside on the same machine.. I believe it is not possible to name the linked server 'ABC" again. Due to the

    coding difficulties, we want to use the same name 'ABC' for the linked servers..

    So we are planning on creating another instance of SQL Server on this machine, and move the database sql2 to this instance. I just wanted to make sure that we will be able to set up linked server 'ABC' on this 2nd SQL Server Instance from sql2 to ora2 database?

    Thanks

  • February 13, 2011 at 4:19 pm

    #1286212

    Yes, the two instances are independent and so can have the same names for the linked servers

  • February 13, 2011 at 6:17 pm

    #1286225

    Do you have multiple SQL Server instances - one for SQL1 and one for SQL2? Or, do you have a single instance of SQL Server with two databases?

    If the latter, then you should look at using synonyms to identify the objects you need to access from each database. This way, you can have separate linked servers as ORA1 and ORA2 - and from each database you would have the same synonym that would access a different linked server.

    For example, in SQL1 you would have this:

    CREATE SCHEMA ora AUTHORIZATION dbo;

    GO

    CREATE SYNONYM ora.TableA FOR ora1.catalog.schema.TableA;

    GO

    And in SQL2 you would have the following:

    CREATE SCHEMA ora AUTHORIZATION dbo;

    GO

    CREATE SYNONYM ora.TableA FOR ora2.catalog.schema.TableA;

    GO

    Now, from either database you can use the same query:

    SELECT ...

    FROM ora.TableA

    WHERE ...

    And if run from the SQL1 database it would access TableA through the ORA1 linked server. If run from SQL2 database it would access TableB through the ORA2 linked server.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • March 7, 2011 at 8:25 am

    #1295362

    Thanks so much.

  • March 28, 2011 at 2:43 pm

    #1304585

    Hi all,

    APplication connects to non-unicode SQL server database which has linked server connected to unicode Oracle database. Unfortunately, the application does not work when selecting unicode Oracle tables via SQL Server database because the application is set to handle non unicode data types in SQL Server database. Application cannot handle both data types at one time.. My question - is it possible to force the SQL server linked server to use non-unicode datatypes when it connects to Oracle even though Oracle database is unicode?

    Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply