Total Pageviews

Search This Blog

Monday, November 4, 2013

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'

No comments: