DBlink Oracle to SQL-Server

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?

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

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.

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)

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

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.

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!

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

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

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.