Getting Error 7399 - OLE DB provider 'ORAOLEDB.ORACLE' reported an error. The provider did not give any information about the error.

Question

Getting Error 7399 - OLE DB provider 'ORAOLEDB.ORACLE' reported an error. The provider did not give any information about the error.

Marios Philippopoulos

SSC Guru

Points: 57190
More actions
April 17, 2008 at 4:04 pm

#189285

I'm getting the following error while using OPENROWSET:
OLE DB provider 'ORAOLEDB.ORACLE' reported an error. The provider did not give any information about the error. [SQLSTATE 42000] (Error 7399) OLE DB error trace [OLE/DB Provider 'ORAOLEDB.ORACLE' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.]. [SQLSTATE 01000] (Error 7300). The step failed.
The OPENROWSET call is done like this:
SET @sql = 'INSERT INTO #tbl (col1, col2, col3) SELECT [col1], [col2], [col3] FROM OPENROWSET(''ORAOLEDB.ORACLE'','''+@Server+'''; '''+@Login+'''; '''+@Password + ''','''+ @querysql + ''')
EXEC (@sql)
Anyone have any ideas what could be wrong and how to fix it?
Thanks!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

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

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Marios Philippopoulos (4/17/2008)
I'm getting the following error while using OPENROWSET:
OLE DB provider 'ORAOLEDB.ORACLE' reported an error. The provider did not give any information about the error. [SQLSTATE 42000] (Error 7399) OLE DB error trace [OLE/DB Provider 'ORAOLEDB.ORACLE' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.]. [SQLSTATE 01000] (Error 7300). The step failed.
The OPENROWSET call is done like this:
SET @sql = 'INSERT INTO #tbl (col1, col2, col3) SELECT [col1], [col2], [col3] FROM OPENROWSET(''ORAOLEDB.ORACLE'','''+@Server+'''; '''+@Login+'''; '''+@Password + ''','''+ @querysql + ''')
EXEC (@sql)
Anyone have any ideas what could be wrong and how to fix it?
Thanks!

Hello Marios,

Can you replace your query with this one and try

SET @sql = 'INSERT INTO #tbl (col1, col2, col3) SELECT [col1], [col2], [col3] FROM OPENROWSET(''ORAOLEDB.ORACLE'',' + @server + ',' + @login + ',' + @Password + ',' + @querysql + ')'

Thanks

Lucky

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 2

I'm running the following query (on 1 line) and getting same error:

SELECT [col1], [col2], [col3] FROM OPENROWSET('ORAOLEDB.ORACLE','server'; 'user'; 'passwd','SELECT col1, col2, col3 FROM tbl')

Same query works in our test environment.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 3

It looks like we are missing the ORAOLEDB.ORACLE provider. When I check in the registry I do not see it:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\IntanceName\Providers

Any advice on how to install it?

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 4

After checking the ORACLE installer, found that the provider is already installed.

The issue is caused by missing registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\Providers\OraOLEDB.Oracle

I have manually added this key to the registry and added a new DWORD value named "AllowInProcess".

Set AllowInProcess to 1.

My SQL job has now run successfully.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]