Solved

DBlink Oracle to SQL-Server

Posted on 2009-05-07
16
1,460 Views
Last Modified: 2012-05-06
Hi.

It is possible to create a dblink from Oracle TO Sqlserver? there is any special attention required in this case? It's just putting a command in sqlplus and this is all? How many informations need I to do this?
Could someone give me an example and some tips for this?
0
Comment
Question by:Thiago_corporative
  • 5
  • 2
  • 2
  • +2
16 Comments
 
LVL 42

Expert Comment

by:EugeneZ
ID: 24326822
yes, it is possible
see:

                 Database link from Oracle to SQL Server
http://www.dba-oracle.com/t_database_link_sql_server_oracle.htm
0
 
LVL 7

Accepted Solution

by:
BobMc earned 500 total points
ID: 24335122
You don't mention your OS, but here are some details from Windows. The method is the same as posted by EugeneZ, but with a few more diagrams

http://www.databasejournal.com/features/oracle/article.php/10893_3442661_1/Making-a-Connection-from-Oracle-to-SQL-Server.htm
0
 

Author Comment

by:Thiago_corporative
ID: 24339842
Even after all the steps in that tutorial, I'm finding an error when try TNSPING string_name...

I followed all the tutorial steps. All that seems works fine. See:

On server:
The odbc source on the server that has sqlserver connects with this one successfully.
The ODBC initfile was configurated like told on tutorial.
Listener too.... and the listener goes up successfully.

On oracle client that will try to see sqlsever db:
TNSNAMES configurated like tutorial steps

Now, the problem:

when I put tnsping string_name on client, I cannot see the server, even with the listener configurated.

so, I went to server, to make a test.... then I've configurated tnsnames with an entry to it own odbc source. So, I put, again, tnsping string_name.

In both, message error:

TNS-12537: TNS: connection closed.

Please, any idea?
0
 
LVL 20

Expert Comment

by:gatorvip
ID: 24355544
Are both servers running on the same network? Is there a firewall between the two?

>>Listener too.... and the listener goes up successfully.

How do you know this is successful?

>>In both, message error:TNS-12537: TNS: connection closed.

Does any other remark follow the TNS-12537 error?
0
 

Author Comment

by:Thiago_corporative
ID: 24355917
Yes, it are on the same network without a firewall.
I've try to start listener by command line and works fine.
This is the only message that appears
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 24394788
In the hs\admin folder of your Oracle_home, did you copy the default "inithsodbc.ora" file to: "init[DNS_NAME].ora'?  And did you change these two lines in it from:
HS_FDS_CONNECT_INFO = <odbc data_source_name>
HS_FDS_TRACE_LEVEL = <trace_level>
to:
HS_FDS_CONNECT_INFO = [DNS_NAME]
HS_FDS_TRACE_LEVEL = 0


0
 

Author Comment

by:Thiago_corporative
ID: 24395830
Yes, I did... but the HS_FDS_TRACE_LEVEL = 0 is set to OFF, not 0... I've readed tutorials on internet and all of them set this parm to OFF... is that wrong?
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 24399027
I don't know.  We had it set to 0 (zero) in our system , and that worked for us.  Our link was not to SQL Server, it was actually to a non-SQL-based (VAX-VMS) system, but it did use an ODBC connection to a third-party gateway product (called CONNX) that supported links between different systems. We were able to create a database link in our Windows-based, Oracle9 database that worked wonderfully for us.
0
 
LVL 7

Expert Comment

by:BobMc
ID: 24414290
Trace level 0 or OFF should both work - they both equate to 0.

OK, so the ODBC test from the server works. Thats a good start..
Without seeing your listener.ora, this is guesswork, but here are a couple of things I would look for:

Check your listener.log for any additional information. There may also be a sqlnet.log generated, but its location may vary.

Run the following from your server - if your listener is called LISTENER, you can omit the listenername
lsnrctl status <listenername>
- this should show a service referenced in the connect_data portion of the listener.ora

lsnrctl services <listenername>
- this should show some more details - check the "Connecting to (ADDRESS..." portion is what you have in your tnsnames.ora

Change the listener.ora to reference localhost or 127.0.0.1 rather than the servername. You could also do this in the tnsnames.ora on the server itself for testing purposes (but not obviously on your remote client)

You also also try changing the port number in your listener.ora for the hs service (and also in your tnsnames.ora)

If none of this throws up any new information that will assist in tracking down the problem, I would suggest a server side sqlnet trace by setting TRACE_LEVEL_CLIENT=ADMIN in your sqlnet.ora on the server. Be warned though, this could generate some pretty large files very quickly, and will slow all your connections down, as it has to write the details out to disk. Turn it off again as soon as you have ran the test, otherwise you'll never find the details in the log it generates!

0
 

Author Comment

by:Thiago_corporative
ID: 24541005
Ok,

Let's suppose that my odbc connection name is SQLSERVER

this is how my tnsnames is configurated for the odbc:

SQLSERVER =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = LOCALHOST)(PORT = 1433))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = SQLSERVER)
    )
    (HS=OK)
  )

And here is my listener, is that right???

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = SQLSERVER)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (PROGRAM = hsodbc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = LOCALHOST)(PORT = 1521))
    )
  )
0
 

Author Comment

by:Thiago_corporative
ID: 24575193
This is how my connection is set on tnsnames

SQLSERVER =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = LOCALHOST)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = SQLSERVER)
    )
    (HS=OK)
  )

so, if I use port 1521, I can see the response on tnsping. But, if I change the port to 1433, and change on odbc too, the tnsping don't give me any response for this connection. The problem is that 1521 is the same port for oracle. So, when I set to this port, stop and restart listener, a problem occurs, of course, then I back to the old, 1433, but never work...
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now