System Resource Exceeded - Append query to ODBC link / SQL Server (1 Viewer)

[Isaac]

I'm running an Append query in Access. Source table is a link to an Excel worksheet. Destination table is SQL server - linked table object in Access.
Data is pretty substantial - 76 columns, but only about 4000 rows.

There's no criteria or complexity to it - a simple append from source to destination.

I'm getting "System Resource Exceeded" in MS Access. Just prior to that, I get the message that there is not enough disk space or memory to undo the changes, am I sure...... etc.

A few notes

1. I realize there's the possibility that I might be violating the table structure - (exceeding length, or implicit conversion failing) - but wouldn't that present quite differently - i.e. in SQL Server as truncation warning/error, or inability to convert ___ to ___ - which would then probably present as an Access "ODBC Call - Failed"?
2. This isn't being done in code or recordsets - just double clicking a newly minted Append query in Access
3. Note - I increased my MaxLocksPerFile from where I saw it was at (9,500) to 100,000, but it didn't solve the problem. After making the registry change, I restarted the Access database. Do I need to go higher? Am I just guessing at the effective # here?

I'm using SQL Server Express. Checking the Disk Usage report of the only database I've created thus far shows total space reserved 144 MB.

I've already had to use T-SQL Bulk Insert on another dataset to this database, due to # of columns > 255. However, that causes me extra effort and time, since the source file's headers aren't exact matches to the SQL destination columns, so custom rigging is required and I'd like to avoid that if I can figure out why I'm getting System Resource Exceeded.

 

[The_Doc_Man]

I believe the "Resource Exceeded" message is effect, not cause. You say you get a message about disk space or memory needed to undo the change.

First question: You have 76 columns - but how many bytes per record? If variable, what is the longest record?

I would not increase MaxLocksPerFile again until you can nail down what is happening. My thought would be to have Task Manager running on the "Performance" tab and have it monitor your RAM when you launch this particular action. Based on your comments, at least one memory statistic should drop to zero. If this happens, then the "Resource Exceeded" message is a diversion.

As a second experiment, use Task Manager, launch Access (but don't start the transfer). In Task Manager, find MSACCESS.EXE and highlight it. (Don't do anything else with it, just highlight it.) Click your mouse in the Memory column so that the highest memory user is at the top. Now start your transfer and see what Access does. I would expect Access to grow in memory usage but the real question is, to what level?

Another issue that concerns me is that you are using a Linked Table from Excel to populate the SQL Server table. You suggest you expect to get about 4000 rows. So... when you try this, do you get ANY rows at all? If so, how many?

 

[Isaac]

First question: You have 76 columns - but how many bytes per record? If variable, what is the longest record?

Below is the max length of all 73 fields: (looks like 2000)

8
9
10
82
153​
3
117
1
19
12
4
8
8
8
9
3
3
7
6
6
2
2
2
7
6
6
7
7
2
12
60
6
22
16
28
28
22
10
25
10
9
25
14
9
29
10
7
14
41
34
9
13
3
2000
84
80
84
83
79
79
80
80
80
78
79
80
80
80
62
72
237
165
144

My thought would be to have Task Manager running on the "Performance" tab and have it monitor your RAM when you launch this particular action. Based on your comments, at least one memory statistic should drop to zero

None dropped to around zero, screenshot:

As a second experiment, use Task Manager, launch Access (but don't start the transfer). In Task Manager, find MSACCESS.EXE and highlight it. (Don't do anything else with it, just highlight it.) Click your mouse in the Memory column so that the highest memory user is at the top. Now start your transfer and see what Access does. I would expect Access to grow in memory usage but the real question is, to what level?

It started at 32k. While the query started trying to run, it shot up to around 100k+, then I got the error, and then once I clicked "ok" on "system resource exceeeded" (at which point Access stops trying and it's all over), it drops back down to about 32k. This query attempt caused Access to become the 2nd highest memory (active private working set) item while it was 'trying' (sqlservr.exe was the top item, at 600k)

Another issue that concerns me is that you are using a Linked Table from Excel to populate the SQL Server table. You suggest you expect to get about 4000 rows. So... when you try this, do you get ANY rows at all? If so, how many?

Try this meaning run the query? after running the query (which results in a system resource exceeded error), zero rows are appended. when i simply double-click the excel linked table in access, i see 4006 rows

PS - I should mention that the machine this is running on is Windows Server 2019, virtual private server, which I am RDP'ing into

 

[Galaxiom]

Most times I have seen that message it turned out to be a misconstructed join condition.

 

[Isaac]

Most times I have seen that message it turned out to be a misconstructed join condition.

That is also what I noticed when I was researching this, that it was common, but in this case there are no joins at all. It's just a single source and all fields are being appended to basically all fields in the destination.

I am thinking of just changing gears at this point and doing a tSql bulk insert as I have with a couple other data sets in this project. After all, even if I get the access append query to work it may perform slowly and I may be unsatisfied anyway. I know the bulk insert will execute in about a second since I have another one with 40,000 records and 300 columns that executes in about a second.

Don't even know if I want to continue trying to solve this issue anymore, although it's perplexing to leave unsolved.

 

[Galaxiom]

Could one of the Excel column names or the filename be a reserved word?

 

[Minty]

Is the SQL Server running on the same machine? if it is 5Gb of ram is not probably enough.

SQL will make use of as much memory as it can and Access will need a fair chunk to move 4000 rows of 76 columns, so that will possibly be your problem.