Ndtyjky

I am sending queries from MS Access (office 365) to MS SQL 2016 and I am running into an error where the temp table from the query I am running is causing MS Access to fail once the max size of 2GB is reached.

The odd thing is that this only happens when I query for Is Null.

This query has been working for a long time and only recently started failing after I updated the underlying data table with the last months working information.

If I search for a value instead of Null it works just fine.

Any idea on what is causing this?

I was able to to verify that the information from the most recent month is fine and I can pull all the information I loaded in a separate query without triggering the 2GB max size table issue.

SELECT 

    dbo_FY2018TRD.[ACCOUNT NUMBER], 

    dbo_FY2018TRD.[ACCOUNT SHORT NAME], 

    dbo_FY2018TRD.[ACCOUNT NAME], 

    dbo_FY2018TRD.[ACCOUNT RR], 

    dbo_FY2018TRD.[ACCOUNT RR NAME], 

    dbo_FY2018TRD.[ACCOUNT BKR], 

    dbo_FY2018TRD.[ACCOUNT BKR NAME]

FROM 

    dbo_FY2018TRD LEFT JOIN FullRRTable ON dbo_FY2018TRD.[ACCOUNT RR] = FullRRTable.[RRCode]

WHERE 

    (((FullRRTable.RRCode) Is Null));

Explanation from comments: The temp table being used is the generic one MS Access creates. The large table dbo_FY2018TRD sits in the SQL server but the FullRRTable sits in Access. I split them up like this because the FullRRTable I routinely edit to apply labels but the dbo_FY2018TRD table should never be modified other than to add new records. dbo_FY2018TRD is 27 million rows by 50 columns, whereas FullRRTable is 35,000 rows by 5 columns.

Comments reveal that the smaller "temp" table FullRRTable is in the Access database and the table dbo_FY2018TRD is on the SQL Server. Because the query involves tables in both locations, the full query cannot be sent to the server as a pass-through query.

For a mixed query, Access must essentially request all rows from the Server table dbo_FY2018TRD since it must perform the join locally without the benefit of server indices, etc. There may be no optimization (or perhaps only one-sided using indices for the Access table), so Access will perform a row-by-row comparison for all record combinations. If you have the ability to inspect record requests from the server, you may also observe that Access requests the same row multiple times.

2 GB divided by 27 million records of the dbo_FY2018TRD table is around 74 bytes per row, making the limit definitely reachable. Divide that into 7 fields of the original query and it's about 10 bytes per field--not including packaging bytes--also a reasonable estimate.

That answers the basic question "Any idea on what is causing this?". As for a solution, I would consider downloading only the single key field dbo_FY2018TRD.[ACCOUNT RR] that you're using in the join to a "temporary" table in Access, then run the comparison query against that table. The results of that query could then be used to retrieve only the mismatched rows.

Search for "access linked table index" or "access linked table mixed query" for more varied suggestions, although you have to dig to find bits of useful info.