The provider ran out of memory.

Question

Post reply

The provider ran out of memory.

whitesql

Ten Centuries

Points: 1237
More actions
June 23, 2014 at 1:56 am

#287851

Hi,
We are running openrowset and we're getting this. SQL Server is 2008 R2 SP1
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider ran out of memory.
Any ideas?
Thanks!

Viewing 15 posts - 1 through 15 (of 15 total)

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

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 1

Are you reading Excel files?

The JET OLE DB provider is 32-bit only.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

whitesql Ten Centuries Points: 1237 More actions · Answer 2

Yes..

all are 32 bit. But still getting that error.

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 3

Is your SQL Server also a 32 bit ?If yes then it looks like the linked server has no memory available to pull the data.

How much memory does your server has ?

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

whitesql Ten Centuries Points: 1237 More actions · Answer 4

yes, SQL Server is 32 bit. Server's memory is 8GB.

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 5

whitesql (6/23/2014)
Yes..
all are 32 bit. But still getting that error.

You can perhaps use the ACE OLE DB provider, which is available in 64-bit (and can thus use more memory).

Use the first connection string of this page:

https://www.connectionstrings.com/ace-oledb-12-0/

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

whitesql Ten Centuries Points: 1237 More actions · Answer 6

Tried it (ACE) and now I'm getting this error:

Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 7

Is AWE enabled for your server ?

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

whitesql Ten Centuries Points: 1237 More actions · Answer 8

whitesql

Ten Centuries

Points: 1237

June 23, 2014 at 2:51 am

#1723934

Yes, it is enabled

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 9

What is the value of physical memory allocated to sql instance set to ?I think not enough memory(VAS) is available for the linked server.

How big is the Excel data ?

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

whitesql Ten Centuries Points: 1237 More actions · Answer 10

physical memory allocated is the default in sql server. Excel file size is only 26 KB.

whitesql Ten Centuries Points: 1237 More actions · Answer 11

physical memory is default in sql server.. 2147483647 ... excel file is only 26KB

whitesql Ten Centuries Points: 1237 More actions · Answer 12

how I allocate or adjust memory for linked server?

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 13

Try setting up as "Outprocess"

To connect to Microsoft Access, SQL Server must start the Microsoft Access Database Engine. Unlike most other providers, Microsoft Access is not a light-weight provider, but is the entire Microsoft Access Database Engine. Opening Microsoft Access in the SQL Server process space can cause failures due to lack of disk, processor, or memory resources. Errors include, "Cannot initialize the data source object of OLE DB provider." To avoid out of memory errors, configure the provider to open outside of the SQL Server memory process space.

http://technet.microsoft.com/en-us/library/ms175866%28v=SQL.105%29.aspx

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

whitesql Ten Centuries Points: 1237 More actions · Answer 14

Hi Sachin,

I tried your suggestion but I'm still getting this error:

Msg 7399, Level 16, State 1, Line 2

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider ran out of memory.