The OLE DB provider "OraOLDB.Oracle" has not been registered. Linked Server

Question

Post reply

The OLE DB provider "OraOLDB.Oracle" has not been registered. Linked Server

Welsh Corgi

SSC Guru

Points: 116520
More actions
September 5, 2014 at 6:55 am

#289755

I was using the Microsoft Provider for Oracle but I determined that it was not reliable.
I query using an open query and I get one record. I query Oracle and I get 30 records.
The code is listed below.
So I created another Linked Server with the OraOLDB.Oracle Provider. When I select providors it is listed.
But when I attempt to expand the tables not I get the error:
The OLE DB provider "OraOLDB.Oracle" has not been registered.
Any help would be greatly appreciated.:-)
-- Declare Variables
DECLARE @CurrentDate AS datetime2
DECLARE @StrSQL AS NVARCHAR(2000)
SET @CurrentDate = getdate()
--SET @CurrentDate = '2014-09-04 08:23:06.7930000'
-- Print out Date
PRINT @CurrentDate
SET @StrSQL = N'SELECT *
FROM OPENQUERY(LS_RDB_DWH_Oracle_POLICY52,''SELECT count(*) FROM RDB_DWH_POLICY.STG_BATCH_STATUS
WHERE SBS_Batch_Date >= TO_DATE(''''' + CAST(@CurrentDate as nvarchar(10)) + ''''',''''YYYY.MM.DD'''') AND SBS_JOB_NAME = ''''RDB Policy''''''' + ')'
PRINT @StrSQL
EXECUTE sp_executesql @StrSQL;
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 1

Do you have the oracle client installed on the server (especially the oledb driver) and rebooted?

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

Also, are you sure it's "OraOLDB.Oracle" and "OraOLEDB.Oracle". It's been years since I worked with Oracle and I don't have access to one now, but what you have just doesn't feel right.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Ville-Pekka Vahteala SSCrazy Points: 2458 More actions · Answer 3

Your query will return one record.

Are you sure that you used same query for both environments while testing Microsoft provider?

Will cast(getdate() as nvarchar(10)) return date with dots?

In query you have format for TO_DATE YYYY.MM.DD should it be YYYY-MM-DD?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Ville-Pekka Vahteala is quite correct. Your query is returning a count of the number of records.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

I should have been more clear. The following return 1 record, there are 30 records in the Oracle table:

SELECT *

FROM OPENQUERY(LS_RDB_DWH_POLICY52, 'SELECT * FROM RDB_DWH_POLICY.STG_BATCH_STATUS')

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Jo Pattyn (9/5/2014)
Do you have the oracle client installed on the server (especially the oledb driver) and rebooted?

I installed the 64bit and 32 bit client.

The SSIS Packages having been working for years.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/