'Not enough memory for file map' when attempting update to linked server using VFP ODBC driver

Question

I have a linked server in SQL Server 2008 that's using the MDASQL provider to access a Visual Fox Pro ODBC DSN.

I'm receiving the below errors when attempting to perform simple queries:

OLE DB provider "MSDASQL" for linked server "odbclinkedserver" returned message "[Microsoft][ODBC Visual FoxPro Driver]Not enough memory for file map.".

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

Msg 7306, Level 16, State 2, Line 1 Cannot open the table "odbctable" from OLE DB provider "MSDASQL" for linked server "odbclinkedserver".

I'm trying to run this update query that is the goal of the activity:

update odbclinkedserver...odbctable
set memofield = m1.blob
from sqlsvrtable m1
where m1.int_1 = odbctable.int_1
and m1.int_2 = odbctable.int_2
and m1.time= odbctable.time
and odbctable.date= '2011-06-28'

I have also attempted some simpler queries and still receive the same errors:

select top 1 * from odbclinkedserver...odbctable

The DSN source is a Free Table directory. The sizes of the files involved are:

• odbctable.dbf = 62MB
• odbctable.cdx = 9.85MB
• odbctable.fpt = 200MB

I have configured the SQL instance to start with the '-g' parameter with a value of 1024 so that a gig of memory is set aside for this operation (this is a test server with no other activities).

Reference used: http://msdn.microsoft.com/en-us/library/ms190737.aspx

Given the size of the files involved, the simplicity of the queries, and that I've set aside a gig of RAM for it to use, I am running out of ideas of how to resolve this problem.

Can anyone suggest a solution to resolve these errors so that I can update the target dbf with the update query above?

Answer 1

Whilst you seem to have enough memory set aside when I had the same issue a stop and start of the SQL Server service followed by running sp_dropserver and sp_addlinkedserver was enough to resolve the issue:

IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'DBF_XXX')EXEC master.dbo.sp_dropserver @server=N'DBF_XXX', @droplogins='droplogins'
GO
EXEC master.dbo.sp_addlinkedserver @server = N'DBF_XXX', @srvproduct=N'Microsoft Jet', @provider=N'VFPOLEDB', @datasrc=N'D:\Data To Import\XXX\data', @provstr=N'dBASE 5.0'

--For security reasons the linked server remote logins password is changed with ########
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DBF_XXX',@useself=N'False',@locallogin=NULL,@rmtuser=N'Admin',@rmtpassword='########'

Answer 2

Make sure you're not violating any of VFP's ODBC restrictions in either the environment or the table structures. Especially note that ODBC hasn't been suported for some time. Check here for a good list of restrictions. Is using the OLEDB driver a possibility for you? If so do a web search for "VFPOLEDB driver download" to get what you need.