42000 Error

[Former Member]

Getting the following error on a report that use to run:

Failed to open a rowset.

Details: 42000:[Microsoft][ODBC SQL Server Driver][SQL Server]

Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query ( 256 ) was exceeded.

[Former Member · Accepted solution]

Hi Steve

From the error message, it is clear that you received this error message because you have exceed the maximum number of tables that can be used in the SQL statement.

The maximum number of tables that can be referred in a SELECT Transact-SQL statement is 256. This limit is documented under the "Maximum Capacity Specifications" section of the SQL Server Books Online.

To overcome this issue, you can break the query so that a lower number of tables or views are referenced in the query.

Hope this helps.

Regards

Poonam Thorat