topic Re: Data Export failure in Access in SAS Programming

Data Export failure in Access

FredBenta — Fri, 22 Jul 2022 14:41:03 GMT

Hello, we are experiencing a problem exporting data from some of our SAS V9.4 tables (Unix server) to an Access database on a Windows PC via a Proc Export (and PCFile Server). Some tables pass very well and others do not (message: "ERROR: CLI execute error: [Microsoft][Microsoft Access ODBC Driver] System Resources Exceeded.")

What's also weird and frustrating is that we do not encounter the problem every time we run the exports... Some day they will run smoothly and every other day, they will systematically fail.
Have you ever encountered and solved a similar problem?

Thanks for your expertise on the matter.

Re: Data Export failure in Access

ballardw — Fri, 22 Jul 2022 16:31:04 GMT

Are the failures with the exact same source data or different data sets?

Same Access database or different ones?

One of the constraints on the Access side is the limit on variables and size. Access has a 255 variable/column limit per table, so I would not be surprised if you have a data set with many variables that there could be issues.

Also the database may still have the 2GB limit referenced here: https://support.microsoft.com/en-us/office/access-specifications-0cf3c66f-9cf2-4e32-9568-98c1025bb47c which means the total size of ALL tables plus any of the objects such as forms and queries in the database.

Intermittent problems may also just be network traffic volume limiting how much can pass through the available ODBC driver.

Re: Data Export failure in Access

FredBenta — Fri, 22 Jul 2022 16:52:38 GMT

The failures are random, and they usually concern 1 or 2 "big" tables in 1 or 2 databases (we split our different tables into several databases to prevent them from getting to the 2GB limit).

Today, however, we had a bunch of those failures and nothing we tried could prevent them (emptying and compacting the databases, killing all other processes on the Windows machine to free up the memory and the CPU).

That's why I posted my question here.

Our tech support thought it could come from the SAS server (Unix machine) which could be overwhelmed by big working processes from other SAS users at the time.

It might well be but I think it's more a Microsoft problem...

I'll try to send my data into another stronger database system (we have a MySQL base available to us) and create ODBC external links to the MySQL tables in the existing Access Databases in replacement of the local tables that won't update. Hope that this solution will work and be effective enough.

Thanks for the reply.

Re: Data Export failure in Access

ballardw — Fri, 22 Jul 2022 18:48:12 GMT

It might not hurt to show the code for the steps you attempt that sometimes fail.

With ODBC it never hurts to make sure you have the latest driver and check that no one changed settings on a particular link.

Do you have SAS/Access to PC Files available?

Re: Data Export failure in Access

Patrick — Sat, 23 Jul 2022 10:39:51 GMT

@FredBenta If you're already on the path to migrate from MS Access to MySQL then I wouldn't spend much more time for debugging issues with a "Mickey Mouse" database but concentrate on the migration.

If you still have to debug then it would be really good to know if the issues you observe are replicable - like: With the same source data do you always get an error or can you just re-run at a later time and things work?

Re: Data Export failure in Access

Sajid01 — Sat, 23 Jul 2022 15:31:20 GMT

Hello
@FredBenta Your issue and possible solutions are here.

https://serverfault.com/questions/881262/system-resource-exceeded-what-resource-could-that-be

Re: Data Export failure in Access

FredBenta — Mon, 25 Jul 2022 08:37:07 GMT

Thanks Patrick for your comments regarding the migration solution.

As for the recurrence of the issue, all of our Proc Exports to our Access databases ran smoothly during the week-end, just like they normally did unitl last friday...

So I'm thinking the problem might be located on the Sas server's side after all, since I had rebooted the Windows machine on friday, which didn't make any difference, whereas everything went fine on saturday...

Mysteries of the digital world...

Re: Data Export failure in Access

FredBenta — Mon, 25 Jul 2022 08:38:52 GMT

Hi Sajid01, thanks for the tip and the precious link, this one I'll be keeping safely. 🙂

Re: Data Export failure in Access

Sajid01 — Mon, 25 Jul 2022 15:59:28 GMT

Hello
@FredBenta Good idea to check on SAS Server.
Under your situation I would check the option memsize, and if the error can be replicated run htop/top on the UNIX/Linux server and see what's going on.