Error 7399: OLE DB provider 'MSDAORA' reported an error. OLE DB error
My link server was working just fine until friday evening.
It stopped worked over the week end.
with and error Error 7399: OLE DB provider 'MSDAORA' reported an error. OLE DB error .
---my oracle 10g client is working just fine
--TNS names looks fine
---i recreated the link but i am still getting the same error.
I need your help because a lot of jobs are using that link on Monday it is going to be crazzzy.
Linked server from SQL Server 2005 to Oracle 11i
When adding a linked server, use a SQL script to gain more control..
BEFORE creating a linked server from SQL to Oracle, you HAVE to install the Oracle client (I installed the 1 GB package, second option when running the Oracle installation).
After installing the Oracle client you HAVE to reboot the server (restarting the SQL server service will not do it).
When this is done, I recommend you create a test connection before creating the linked server. There are several ways of doing this, but here is a simple way that always work for me:
1) Right click on your desktop to create a new .txt file. Rename the file extention from ".txt" to ".udl", and answer "Yes" when asked if you want to rename the file
2) On the "Provider" tab select "Oracle Provider for OLE DB". (in fact.. in most cases the Microsoft OLE DB Provider for Oracle" will work, but this will fail in Linked Server!)
3) Click on the "Next" button
4) Enter this into the "Data source" field: <IP>:<Port>/<SID> Example: 140.10.10.20:1526/mydb
5) Enter a user name and pwd in the boxes further down, and click on "Test connection".
If this works, you know the provider, username, pwd etc. works.
Then.. when you have done the above, add a linked server.
Some forum posts (here and other places), tells you to use the MSDAORA provider. I found it to be the other way around. When I used the MSDAORA provider, it failed. When using the OraOLEDB.Oracle provider it worked!
Script for creating the linked server (you need to enter values between the <..>):
exec sp_addlinkedserver
@server= '<Linked Server Name>',
@provider='OraOLEDB.Oracle',
@srvproduct='Oracle',
@datasrc='<IP>:<Port NB! Rembember Listner on remote Oracle>/<SID>'
Example:
exec sp_addlinkedserver
@server= 'ORALinkedServer',
@provider='OraOLEDB.Oracle',
@srvproduct='Oracle',
@datasrc='140.10.10.20:1526/mydb'
When this is done, you need to configure the permission on the linked server. This can be done by stored procedure, or by selecting properties on the new linked server, security, and select using remote user and enter user name and password.
Good luck!