Post reply

DB Link from Oracle to SQL Server 2014 Issues

  • February 21, 2018 at 11:43 am

    #1980663

    The fix for this was to modify some values in the gateway init.ora file (an Oracle config file)

    Particularly, give values to HS_DB_NAME and HS_DB_DOMAIN parameters and then use those values as the name for a db link.

    So we put
    HS_DB_NAME myDBName <1-8 characters>
    HS_DB_DOMAIN SQLSERVAD <1-119 characters>
    and created a db link using those values .... meaning that the NAME of the db link is, using this example, "myDBName.SQLSERVAD"...no quotes, of course. Internals of the db link have connection to the actual database and sql server. But what we needed was a name that Oracle could grab onto.

    Now, I can do an insert on a table UserInfo in SQL Server database like
    insert into UserInfo@myDBName.SQLSERVAD (fname, lname, nickname) values ('Mike','Hanson','Mikie')
    and it works.

    The default value of HS_DB_NAME is "HO"
    The default value of HS_DB_DOMAIN is "WORLD"

    So when you get an error saying that the db link connects to HO.WORLD then you know you haven't got these parameters set correctly for your db link.

    Once this is all set up correctly, you can create and use a db link without getting that error and without having to kludge a fix by altering session to set global_names=false.

Viewing post 1 (of 2 total)

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