Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

DBlink Oracle to SQL-Server

Posted on 2009-05-07
16
Medium Priority
?
1,537 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +2
16 Comments
 
LVL 43

Expert Comment

by:Eugene Z
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 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
LVL 35

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 35

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

618 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