Error 7399: OLE DB provider 'MSDAORA' reported an error. OLE DB error

[indyandumi]

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.

[rmiao]

Reboot sql server if possible or try odbc provider for linked server.

[indyandumi]

rmaio,
is to stop sql server the same as to reboot?
What i did i stopt sql server and restarted, and then i used ODBC Connection but i keep getting the same error.

---I went to ODBC and setup oracle connection .

Any new ideas

[rmiao]

Stopping sql is different from rebooting server since it can't recover os or network issues.

[indyandumi]

Thanks, i am gonna have to wait until tmwrw to restart the server.

[indyandumi]

rmaio,
We restarted SQL server and we still had the same problem,the problem was on Linux server which runs oracle, so we stoped all oracle services and when we restarted the server BINGO, i am back in business.

Thanks man

[skhanal]

Use the Microsoft driver for Oracle, do not use Oracle's driver. This has been a problem with Linked server for a long time.

[ivarandreassen]

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!