September 21, 2011 at 11:13 am
I'm trying to set up a linked server from SQL Server 2008 R2 64 bit to Oracle 7.2.
I have a working connection from SQL Server 2008 R2 64 bti to Oracle 9.
I've altered the TNSNames.ORA to include the connection information for the 7.2 connection, matching the format for the 9 connection. When I try to establish the connection, I get
tns listener was not given the service_name in connect_data
as an error message. I have Oracle 11 installed on the SQL Server machine to make the connection and it works for 9.
Is the format for a connection to 7.2 different?
Does it need to be in its own TNSNames.ORA file and if so, how do I have 2 of them?
Do I need to install an older driver?
Do I need to configure the listener differently?
Is it possible that 7.2 simply doesn't connect to the SQL Server setup I have?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
September 21, 2011 at 1:30 pm
not sure if there were servicenames in 7.2, it is probably a SID.
What are the results of oracle basic connectivity testing?
in command prompt:
tnsping myoldandtrusty
->shows location of the oracle tnsnames.ora location
->tries to connect to the listener of myoldandtrusty
if tnsping reacts to the good server
sqlplus mylogin/mypassword@myoldandtrusty
->tries to connect to the myoldandtrustyinstance
Also check sqlnet.ora in the same location as tnsnames.ora.
Possible lines:
authentication (NFS?=os authentication ora_db group) None= password authentication
default domain (like .world)
Old oracle clients:
Use SID instead of servicename
It is possible you have to add .world in tnsnames.ora as defaultdomain
myoldandtrusty (
config...
)
myoldandtrusty.world (
config ...
)
September 21, 2011 at 1:38 pm
Jo Pattyn (9/21/2011)
not sure if there were servicenames in 7.2, it is probably a SID.What are the results of oracle basic connectivity testing?
in command prompt:
tnsping myoldandtrusty
->shows location of the oracle tnsnames.ora location
->tries to connect to the listener of myoldandtrusty
if tnsping reacts to the good server
sqlplus mylogin/mypassword@myoldandtrusty
->tries to connect to the myoldandtrustyinstance
Also check sqlnet.ora in the same location as tnsnames.ora.
Possible lines:
authentication (NFS?=os authentication ora_db group) None= password authentication
default domain (like .world)
Old oracle clients:
Use SID instead of servicename
It is possible you have to add .world in tnsnames.ora as defaultdomain
myoldandtrusty (
config...
)
myoldandtrusty.world (
config ...
)
Yeah, while the error says Service_Name, I went into the Oracle Net Manager configuration tool and checked the box labeled Use Oracle 8 compatible identification and that greyed out the Service_Name box and activated the SID box. When I tried it, I got an error "No longer supported"
I will try the other things you suggest, thanks!
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
September 21, 2011 at 1:48 pm
September 21, 2011 at 2:00 pm
Jo Pattyn (9/21/2011)
I could be that the oracle 11 client is limited to Oracle 9.*
Have to find my oracle support login as it is on metalink
Yeah, I was worried about that too. Thanks for the link.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
September 22, 2011 at 6:04 am
According to metalink, it's definitely incompatible. Remember Oracle 7 was released in 1992, so is nearly 20 years old now!
I've never dealt with such old versions, but you should be able to install the older drivers to another home and run in parallel to the 11g drivers.
September 22, 2011 at 6:26 am
Actually, this is going to be a tricky one to solve. From what I can see, the latest driver that can connect to Oracle 7 (and it actually mentions a later version than 7.2) is the 9i driver, but this does not have a 64-bit version.
So, you may need a clunky workaround, like install the 32-bit version of the drivers (as it's an old version, you could even use "Microsoft OLEDB provider for Oracle" as this is compatible with 7), setup an SSIS package to extract the data into the 64-bit SQL Server, then call the package in your code with the 32-bit option (either through cmdshell, or by starting an agent job)
September 22, 2011 at 6:30 am
HowardW (9/22/2011)
Actually, this is going to be a tricky one to solve. From what I can see, the latest driver that can connect to Oracle 7 (and it actually mentions a later version than 7.2) is the 9i driver, but this does not have a 64-bit version.So, you may need a clunky workaround, like install the 32-bit version of the drivers (as it's an old version, you could even use "Microsoft OLEDB provider for Oracle" as this is compatible with 7), setup an SSIS package to extract the data into the 64-bit SQL Server, then call the package in your code with the 32-bit option (either through cmdshell, or by starting an agent job)
I'm thinking of using an interim database to get the data out of the old Oracle instance. If I use a 32 bit database on a different server that has the Oracle 7 drivers installed, that should do the trick.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply