Troubleshooting Linked Server Error The OLEDB provider MSDASQL for linked server reported an error. The provider ran out of memory

Dear Friends,

Some months ago, I was working on a SSRS Report which sources data from a third party Oracle database. I had shared my experiences in this post http://daxdilip.blogspot.com.au/2013/03/ssrs-troubleshooting-error-im014.html

I have a Linked Server connection from my SQL box which will talk to the Oracle db and do a nightly refresh of the local data. Now, the source database (oracle db) got upgraded and some of the tables/fields were changed.

Error: I got this error in one of my data transfer jobs.

Solution:

You can refer to the Microsoft KB Article over here http://support.microsoft.com/kb/268520

Re-create the linked server to use the Microsoft OLE DB Provider for ODBC (MSDASQL).

• 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'