0

I'm trying to import large amount of data from an Oracle database using a Linked Server on MS SQL 2005. Here's the OPENQUERY command.

SELECT * from OPENQUERY(HRDEV9, 
        'SELECT EMPLOYEE_ID
          ,EMPLOYEE_NAME
          ,TO_CHAR(BIRTHDATE,''yyyy-mm-dd'') AS BIRTHDATE
        FROM SYSADM.PS_EMPLOYEES')

I have not included all the columns for the sake of keeping my example simple, I have around 180 columns and 75000 rows to import.

This is the error I'm getting when I execute the query:

OLE DB provider "OraOLEDB.Oracle" for linked server "HRDEV9" returned message "Not enough storage is available to complete this operation.".

Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "OraOLEDB.Oracle" for linked server "HRDEV9" reported an error. The provider ran out of memory.

Msg 7372, Level 16, State 4, Line 1 Cannot get properties from OLE DB provider "OraOLEDB.Oracle" for linked server "HRDEV9".

I have enabled autogrowth, and set the unrestricted file growth for both Data and Log files.

I have tried importing only 10 rows with the Oracle ROWNUM but still getting the same error.

Thank you for your time.

Shant H.
  • 63
  • 1
  • 1
  • 10
  • 1. Can you load from another OLEDB? (even SQL Server) 2. Can you query ORACLE database through this driver? – vav Nov 17 '14 at 15:53
  • @vav 1. The data I want to import is sitting on an Oracle database, and the only way I can connect to Oracle is through the linked server using the OraOLEDB.Oracle provider. 2. Yes I can – Shant H. Nov 18 '14 at 15:39

2 Answers2

0

Try importing data from Oracle into CSV and then load it into MS SQL. Sorry for pointing you to my blog, but here is how to get data fast from Oracle.

0

I'm posting this to help others with the same issue.

Apparently this is a bug confirmed by Microsoft. The error message occurs if the linked table contains a column of type NUMERIC. The workaround is to re-create the linked server to use the Microsoft OLE DB Provider for ODBC (MSDASQL).

Here are two ways you can work around the behavior:

Use the ODBC driver for ORACLE that is provided by Microsoft:

EXEC sp_addlinkedserver @server = 'ORACLEODBC', @srvproduct = 'MSDASQL', @provider = 'MSDASQL',
 @provstr = 'DRIVER={Microsoft ODBC for Oracle};SERVER=MyOracleServer;UID=USERNAME;PWD=Password;'
go

sp_addlinkedsrvlogin 'ORACLEODBC', false, NULL, 'USERNAME', 'Password'

-OR-

Use the ODBC driver for ORACLE that is provided by ORACLE:

EXEC sp_addlinkedserver @server = 'ORACLEODBC', @srvproduct = 'MSDASQL', @provider = 'MSDASQL',
 @provstr = 'DRIVER={ORACLE ODBC DRIVER};SERVER=MyOracleServer;UID=USERNAME;PWD=Password;DBQ=ORACLE805;' 
go

sp_addlinkedsrvlogin 'ORACLEODBC', false, NULL, 'USERNAME', 'Password'

Hope this helps.

Shant H.
  • 63
  • 1
  • 1
  • 10