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

dbmgroup
dbmgroup used Ask the Experts™
on
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 =)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.

Author

Commented:
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.)
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.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

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.
Just FIY, I've tested microsoft.ace.oledb.12.0 provider with the same result.

BUT when the "Extended Properties=dBASE IV" is used in provider string then it supports tables larger than 256 MB. I've tested just 64 bit version of above provider but I don't think it should differ from 32 bit version in this detail.

Author

Commented:
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.

Author

Commented:
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!
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial