Linking Oracle Servers in SQL Server 2005 Express

Hi Everyone,
I am trying to link an Oracle server to my instance of SQL Server 2005
Express. I have used the following code to add the linked server:
USE MASTER
GO
EXEC sp_addlinkedserver
@.server = 'NameForLinkedServer',
@.srvproduct = 'Oracle',
@.provider = 'OraOLEDB.Oracle',
@.datasrc = 'ActualNameOfOracleServer'
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
GO
Exec sp_addlinkedsrvlogin
@.rmtsrvname='NameForLinkedServer',
@.rmtuser='USERNAME',
@.rmtpassword='PASSWORD'
GO
This all runs very nicely and completes without errors. I then try to
query the linked server I have just added, and the whole thing just
hangs there "executing" the query (Management Studio Express). I have
also tried this with the MSDAORA (Microsoft) provider, and that returns
a "cannot initialise object" error.
I can access the Oracle server with the Oracle tools that I have
installed, and I can access the tables in the Oracle server by linking
them in MS Access (using a User DSN). It just wont work in SQL Server
it seems.
Can anybody help with this one? I am truly stuck and desperately want
to avoid having to use Access for this (we still use Access 97 would
you believe as a corporate standard!).
Cheers, and thanks in Advance
The Frog :)"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164125803.967272.173060@.f16g2000cwb.googlegroups.com...
> Hi Everyone,
> I am trying to link an Oracle server to my instance of SQL Server 2005
> Express. I have used the following code to add the linked server:
> USE MASTER
> GO
> EXEC sp_addlinkedserver
> @.server = 'NameForLinkedServer',
> @.srvproduct = 'Oracle',
> @.provider = 'OraOLEDB.Oracle',
> @.datasrc = 'ActualNameOfOracleServer'
> GO
> Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
> GO
> Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
> GO
> Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
> GO
> Exec sp_addlinkedsrvlogin
> @.rmtsrvname='NameForLinkedServer',
> @.rmtuser='USERNAME',
> @.rmtpassword='PASSWORD'
> GO
> This all runs very nicely and completes without errors. I then try to
> query the linked server I have just added, and the whole thing just
> hangs there "executing" the query (Management Studio Express). I have
> also tried this with the MSDAORA (Microsoft) provider, and that returns
> a "cannot initialise object" error.
> I can access the Oracle server with the Oracle tools that I have
> installed, and I can access the tables in the Oracle server by linking
> them in MS Access (using a User DSN). It just wont work in SQL Server
> it seems.
> Can anybody help with this one? I am truly stuck and desperately want
> to avoid having to use Access for this (we still use Access 97 would
> you believe as a corporate standard!).
>
Remember to set AllowInProcess for OraOLEDB.Oracle.
EG
USE MASTER
GO
EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1
GO
sp_dropserver @.server=N'NameForLinkedServer', @.droplogins='droplogins'
GO
EXEC sp_addlinkedserver
@.server = 'NameForLinkedServer',
@.srvproduct = 'Oracle',
@.provider = 'OraOLEDB.Oracle',
@.datasrc = '//HOSTNAME/SERVICE'
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname = N'NameForLinkedServer',
@.useself = N'False',
@.rmtuser = N'USERNAME',
@.rmtpassword = N'PASSWORD'
GO
GO
SELECT * FROM OPENQUERY(NameForLinkedServer,'SELECT 1 D FROM DUAL')
David|||Thanks for that David,
I have given it a try, and the SQL instructions have run and made the
changes. Unfortunately there is still no action when trying to query
the linked server. I have had a quick look at the network performance
on the pc, and there is not even any network activity.
Do you know if there is a way that I could perhaps query the User DSN
from SQL Server 2005? Or perhaps a file DSN? Maybe the answer is to try
and two-step the solution since it doesnt seem to want to play nicely
with me...
Cheers
The Frog|||"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164185311.741862.54970@.e3g2000cwe.googlegroups.com...
> Thanks for that David,
> I have given it a try, and the SQL instructions have run and made the
> changes. Unfortunately there is still no action when trying to query
> the linked server. I have had a quick look at the network performance
> on the pc, and there is not even any network activity.
> Do you know if there is a way that I could perhaps query the User DSN
> from SQL Server 2005? Or perhaps a file DSN? Maybe the answer is to try
> and two-step the solution since it doesnt seem to want to play nicely
> with me...
>
A couple of things to check out:
Make sure
-you have rebooted since installing the Oracle Client.
-the SQL Service account have permissions for the Oracle client folders.
David|||Thanks again David,
I have checked the permissions and also rebooted the machine a few
times just to be sure. They all seem in order. I did notice something
when playing with the Oracle Client software however that may be
important: The server is an Oracle 9i server, but the client software
is 8.17.
I have downloaded an ODBC driver from Oracle, but have not been able to
try it because I need something called the Oracle Universal
Installer(?). Apparently this installer comes with the client
software, but I am unable to locate it (only the SQL Plus, and another
called Oracle ODBC Test).
Do you think it may be that my driver is simply too old? I would have
thought that MS Access would have also complained (but since it is
Access 97 maybe the 8.17 driver software is actually more sophistocated
than it is?). I was thinking that there may be an incompatability
between the 8.17 driver and SQL 2005 with MDAC 2.8 SP1. Its just a
guess.
Thankyou so much for trying to help with this, it is greatly
appreciated.
Cheers
The Frog
David Browne wrote:
> "The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
> news:1164185311.741862.54970@.e3g2000cwe.googlegroups.com...
> > Thanks for that David,
> >
<SNIP>|||"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164201695.690139.214840@.b28g2000cwb.googlegroups.com...
> Thanks again David,
> I have checked the permissions and also rebooted the machine a few
> times just to be sure. They all seem in order. I did notice something
> when playing with the Oracle Client software however that may be
> important: The server is an Oracle 9i server, but the client software
> is 8.17.
> I have downloaded an ODBC driver from Oracle, but have not been able to
> try it because I need something called the Oracle Universal
> Installer(?). Apparently this installer comes with the client
> software, but I am unable to locate it (only the SQL Plus, and another
> called Oracle ODBC Test).
> Do you think it may be that my driver is simply too old? I would have
> thought that MS Access would have also complained (but since it is
> Access 97 maybe the 8.17 driver software is actually more sophistocated
> than it is?). I was thinking that there may be an incompatability
> between the 8.17 driver and SQL 2005 with MDAC 2.8 SP1. Its just a
> guess.
> Thankyou so much for trying to help with this, it is greatly
> appreciated.
>
That is a very old driver. I would the latest OleDb provider, available at
http://www.oracle.com/technology/tech/windows/ole_db/index.html.
David|||Thankyou once again David,
I am downloading the product from the link now. I will install and
trial it today, and post here again with the results a little later on.
Just thinking about this issue logically, it probably is the driver
thats the cause of the problem. Its a large file, and the company
internet connection is a little slow, so I will have to come back on
this one.
Thanks again for all the help.
Cheers
The Frog|||Hi David,
I have installed the software, and copied the TNSNames.ora file to the
network\admin directory. I seem to be getting network activity now, and
I am getting a response from the Oracle server. The message is an error
of some kind (ORA-12154), which when I look it up states that the issue
is with the TNSNAMES.ora file. I run the network configurator tool for
the product, and am able to test the connection - and it works (once it
has the right username and password).
I have tried to configure the Linked Server several different ways, but
basically the script we discussed earlier is what is being used to
create the server. I have noticed in the servername for the linked
server that it was not exactly the same as the name in the TNSNAMES.ora
file. The ora file has a name like this:
oracledatabase.sub-domain-domain. When I change the script to match
this the response is instant with the same error message above. If the
script drops the domain extensions from the name query takes a few
seconds before returning a 'not going to happen' response.
You have helped me so much with this, I cannot thank you enough for
getting me this far. I hope I am not impinging on your time and
patience too much with this. I am comfortable once I am in SQL Server,
but I am really not an Oracle guy (I have not had enough experience
with it to date).
Thankyou once again
The Frog|||"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164294579.219182.26860@.h54g2000cwb.googlegroups.com...
> Hi David,
> I have installed the software, and copied the TNSNames.ora file to the
> network\admin directory. I seem to be getting network activity now, and
> I am getting a response from the Oracle server. The message is an error
> of some kind (ORA-12154), which when I look it up states that the issue
> is with the TNSNAMES.ora file. I run the network configurator tool for
> the product, and am able to test the connection - and it works (once it
> has the right username and password).
> I have tried to configure the Linked Server several different ways, but
> basically the script we discussed earlier is what is being used to
> create the server. I have noticed in the servername for the linked
> server that it was not exactly the same as the name in the TNSNAMES.ora
> file. The ora file has a name like this:
> oracledatabase.sub-domain-domain. When I change the script to match
> this the response is instant with the same error message above. If the
> script drops the domain extensions from the name query takes a few
> seconds before returning a 'not going to happen' response.
> You have helped me so much with this, I cannot thank you enough for
> getting me this far. I hope I am not impinging on your time and
> patience too much with this. I am comfortable once I am in SQL Server,
> but I am really not an Oracle guy (I have not had enough experience
> with it to date).
>
Now, with the new driver, you can take advantage of one of the best new
features of the Oracle client: bypassing the tnsnames.ora file. With the
10g client you simply don't need a tnsnames.ora file. <<sound of much
rejoicing>>
Instead you specify //hostname/servicename where hostname is the IP address
(or DNS name) of the oracle server, and servicename is the instance.
David|||David you are an absolute LEGEND!
It works like a charm. The script I used in the end is as follows:
USE MASTER
GO
EXEC sp_addlinkedserver
@.server = 'NameForLinkedServer',
@.srvproduct = 'oracle',
@.provider = 'OraOLEDB.Oracle',
@.datasrc = '//full.pc.DomainName/OracleServiceName-or-SID'
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
GO
Exec sp_addlinkedsrvlogin
@.rmtsrvname='NameForLinkedServer',
@.useself= FALSE,
@.rmtuser='remoteusername',
@.rmtpassword='remoteuserpassword'
GO
This was done with the Visual Studio Developer tools installed from the
link you provided above, to make sure all the drivers etc... were up to
date.
Its quick, clean, and scriptable all the way through. I cant thank you
enough. If you are ever in Germany, the beer and schnitzels are on me
:)
Thankyou, thankyou, thankyou
Indebted to your services
The Frog