I upgraded a SQL 2000 installation to SQL 2005 this past weekend. Minimal issues overall, but one that did crop up relates to a linked server to an Oracle database. The linked server worked before the upgrade, and stopped working immediately after.
We're running on Windows 2003 x64, latest service pack. The linked server is set up using the MSDAORA provider. The error I'm getting post-upgrade is:
=====================
OLE DB provider "MSDAORA" for linked server "PROD" returned message "ORA-06413: Connection not open.".
Msg 7303, Level 16, State 1, Procedure RDM_GET_REP_LIST_SP, Line 12
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "PROD".
=====================
A tnsping from the server works fine. I've also tried recreating the linked server. Any help would be appreciated. Let me know what other information would be useful.
Thanks,
Adam
Hi Adam,
You mentioned you are running Win2003 x64 edition. SQL 2000 has a 64-bit edition which supports only Itanium architecture, so I am assuming you had been running the 32-bit SQL edition in WOW, is this correct? Did you upgrade to a 32-bit edition of SQL 2005, too? BTW - there's no 64-bit version of MSDAORA, the 64-bit platform comes with the 32-bit version of the provider, so I am assuming that you are using everything 32-bit in WOW, correct? I am wondering if this might be related to the problem.
Regarding the error - apparently the provider initializes and finds the Oracle client. However, the client returns the ORA-06413. This is a typical connectivity error and could be caused by many factors, for instance see the following links:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=288371&SiteID=1
http://www.dbforums.com/archive/index.php/t-1294142.html
In general, SQL 2005 wouldn't change the way MSDAORA is used, except for tightening security and some other similar details. I would check the following:
(a) How is the linked server security configured?
(b) Does it make a difference when you connect to SQL with NT or with SQL authentication?
(c) Does it make a difference when running MSDAORA out-of-process or in-process for the SQL Server?
(d) If you create a short ADO script (vbs or js) and use it to connect to the Oracle server, are you experiencing the same errors?
(e) If you use the Oracle's OLEDB provider, do you get the same error?
In addition, the following KB articles might be of help:
How to set up and troubleshoot a linked server to an Oracle database in SQL Server http://support.microsoft.com/kb/280106
INFO: Limitations of Microsoft Oracle ODBC Driver and OLEDB Provider http://support.microsoft.com/kb/244661/
HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.
No comments:
Post a Comment