Problem:

When executing a query in SQL Server against an AcuXDBC Linked server there is an error:

OLE DB provider "MSDASQL" for linked server "<linked server name>" returned message "[TOD][ODBC][GENESIS]Resource error: MERGESIZE overflow

Resolution:

The "MERGESIZE overflow" error can usually be resolved by setting the Merge Buffer Size, on the Advanced tab of the DSN, to the maximum of 65535. That is the number of record identifiers the merge buffer will hold. Should the error still occur then the Merge Buffer Size may be set to 0 which turns off boolean optimization and the merge buffer is not used. The other option is to rework the query so there are fewer duplicates to merge.

Following is the complete information on the function of the Merge Buffer.

Predicate clauses are connected with either AND or OR operators. AND operators are easier to optimize because the values can be pushed to an index. OR operators are much more complicated. For example,

select * from staff where id = 10 or id = 20

select * from staff where id in (10,20)

Both of these mean the same thing. The IN keyword is simply shorthand. In this case, the optimizer cannot simply use staff_ix0 because the predicate is looking for multiple values of the same column. The optimizer can however still use staff_ix0 by breaking up the query into two portions and merging the results:

select * from staff where id = 10

select * from staff where id = 20

AcuXDBC keeps track of the records returned so that the same records are not returned for the case where the same values are in multiple OR clauses. For example, Microsoft Access always generates ten OR clauses when it fetches records based on key values. So even if there are only 5 unique key values, it simply repeats the last one to fill out the ten OR clauses. AcuXDBC keeps these record identifiers in its merge buffer. The default size of the merge buffer is 10000 records. This is modified by setting the Merge Buffer Size parameter on the Advanced tab of the DSN to a value, n, where 0 <= n <= 65535.

If n = 0, then the above optimization is not performed.