asked on

Problems using OpenDataSource in MS SQL to export to Dbase III sizes over 262,144 KB

Hi all,

Have this snippet of code that I use fairly often:

insert into OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=D:\DP1_DBF\;Extended Properties=dBASE III;')...Any_DBF
	(Field1, Field2, Field3)
select Field1, Field2, Field3
from dbo.Any_Name

Open in new window

Usually works great (and it's perfect for a lot of the dynamic stuff we do), but I run into this error on large tables when the .dbf size hits exactly 262,144 KB. (2^18 in KB, so not a random number).

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not update; currently locked.".
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" could not INSERT INTO table "[Microsoft.Jet.OLEDB.4.0]".

Any ideas to get around this limitation or possible workarounds? It would need to work in SQL 2000/2005/2008. Thanks in advance =)

Pavel Celba

Are you able to split the insert into several smaller parts or does it crash always when this limit is reached?

Did you try newer data engine, like microsoft.ace.oledb.12.0 or even microsoft.ace.oledb.14.0 ?
(available here: http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en)

VFP OLE DB provider could also work for you.

dbmgroup

ASKER

Smaller files will work, but I need this to be a dynamic solution and I can't think of a good, reliable way to split these tables. (Not impossible if there's a key I can use, but I won't always have that.)

I'm fiddling with VFP OLE DB, but haven't gotten it to work 100% yet. (It seems it's very picky with permissions, I've add everyone/full control to the .dbf folder but that still hasn't worked.)

Pavel Celba

Smaller files was ment as several consecutive inserts into one big DBF, e.g.

insert into ...
select Field1, Field2, Field3
from dbo.Any_Name
WHERE ID between 1 and 10000

insert into ...
select Field1, Field2, Field3
from dbo.Any_Name
WHERE ID between 10001 and 20000

etc.

VFP OLE DB must be reinstalled to make it operational sometimes... I hope it is working under the SQL Server engine account when handling DBF data. Also, permissions to the Oledb folder must be defined (obviously C:\Program Files\Common Files\System\Ole DB)

Your target file size can be "rounded" due to the allocation block or page size. I am testing similar inserts on my notebook but it is crashing much earlier and the error is:

Msg 701, Level 17, State 123, Line 1
There is insufficient system memory in resource pool 'default' to run this query.

I'll test it once more later.

Pavel Celba

So, the Microsoft.Jet.OLEDB.4.0 behaves exactly same way on my machine and the DBF size limit is 256 MB. The VFPOLEDB is able to insert 500 MB data without problems. The previous error was caused by attempt to insert more than 2 GB data into DBF.

ASKER CERTIFIED SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dbmgroup

ASKER

Hi pcelba,

Sorry, I totally forgot I posted this here until I ran into the situation again today. I will try the extended properties=dBASE IV and see if that helps. Regardless, you've been very helpful so you definitely deserve points for this, so I'll update that.

dbmgroup

ASKER

Turns out Microsoft.Jet.OLEDB.4.0 with extended properties dBASE IV can do up to 2GB. Thanks again pcelba, definitely wouldn't have figured that out without you!

Pavel Celba

Thanks for the info. Some settings are really unbelievable... But they let us remember old times.

I have to mention one more trap: Microsoft.Jet.OLEDB.4.0 does not support long file names.