April 7, 2010 at 1:40 pm
I have an instance of sql server 2008 express with a linked server to oracle via MS ODBC, this works fine in my application, but...
After the server boots, or the service is stoped and restarted, I first have to open the linked server tree to the tables, before the application can use the linked server?
Any idea how to fix the glitch?
Thanks
Marts
April 7, 2010 at 3:18 pm
Marts (4/7/2010)
I have an instance of sql server 2008 express with a linked server to oracle via MS ODBC, this works fine in my application, but...After the server boots, or the service is stoped and restarted, I first have to open the linked server tree to the tables, before the application can use the linked server?
Any idea how to fix the glitch?
Thanks
Marts
I would not use ODBC with Oracle and since this is an application you may need Oracle client for the version of Oracle you are running installed in the server running SQL Server Express. You should try the SQL Server Native Client provider.
Kind regards,
Gift Peddie
April 8, 2010 at 7:51 am
You should try the SQL Server Native Client provider.
I thought this was only for connections to other SQL Servers?
I tried it and it doest work anyway.
Marts
April 8, 2010 at 8:09 am
Marts (4/8/2010)
You should try the SQL Server Native Client provider.
I thought this was only for connections to other SQL Servers?
I tried it and it doest work anyway.
Marts
No the SQL Native Client can be used to make connections that are not working with ODBC. I checked after I posted so you could try the Microsoft OLEDB provider for Oracle.
When you double click the linked server object in Management Studio you get to the Providers double click on the providers object and the Microsoft OLEDB provider for Oracle is of one of the defined providers.
Kind regards,
Gift Peddie
April 8, 2010 at 8:15 am
Check out this link: http://msdn.microsoft.com/en-us/library/ms189063.aspx
April 8, 2010 at 8:49 am
I see, I took you statement about the native client to mean "SQL Server Native Client 10.0"
The other drives are installed, however I have been unable to get them working.
On the other server I setup, I use "Microsoft OLE DB Provider for Oracle" and it works like a champ, however the difference in the oracle setup on this one is that it is using TNS_ADMIN environment variable with a remote hosts file, whereas the server which works is setup all locally.
Thanks for looking at my post
Marts
April 8, 2010 at 8:56 am
Marts (4/8/2010)
I see, I took you statement about the native client to mean "SQL Server Native Client 10.0"The other drives are installed, however I have been unable to get them working.
On the other server I setup, I use "Microsoft OLE DB Provider for Oracle" and it works like a champ, however the difference in the oracle setup on this one is that it is using TNS_ADMIN environment variable with a remote hosts file, whereas the server which works is setup all locally.
Thanks for looking at my post
Marts
Yes that was what I was talking about installing the Oracle client locally because you have to add the TNSNAME.ORA file locally in the client install. There are many issues with the remote TNS file because if you install the client and the TNSNAMES.ORA file the remote Oracle is local to your application.
Kind regards,
Gift Peddie
April 8, 2010 at 12:47 pm
Once you got everything in place do the 1-2 punch test...
1- ping whatever dns or ip-address you provided for the host in tnsnames entry
2- after #1 works, tnsping the tnsnames alias you provided for the target instance in tnsnames entry
Don't bother to go forward until you get those two answering correctly.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 8, 2010 at 8:05 pm
The remote tns_admin works fine for tnsping, but fails in sql server linked servers, I will try reconfiguring for local tomorow, like my other server that works and hopefully that is the issue 🙂
April 9, 2010 at 6:50 am
Marts (4/8/2010)
The remote tns_admin works fine for tnsping, but fails in sql server linked servers, I will try reconfiguring for local tomorow, like my other server that works and hopefully that is the issue 🙂
This is an omission in Oracle docs but I have always developed with Remote Oracle, sometimes in different states but the client and TNSNAMES.ORA in the development folder and Oracle SQL Developer I can develop without issues.
Kind regards,
Gift Peddie
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply