FIX: Merge dynamic Snapshot Agent may fail while it generates an initial snapshot for a publication with a large number of tables (308744)

SYMPTOMS

When you use a dynamic snapshot for merge publications that contain a large number of articles, the dynamic Snapshot Agent may fail with various error messages such as:

146 Could not allocate ancillary table for a subquery. Maximum number of tables in a query (%d) exceeded.

251 Could not allocate ancillary table for query optimization. Maximum number of tables in a query (%d) exceeded.

4408 The query and the views or functions in it exceed the limit of %d tables.

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

8620 Internal Query Processor Error: The query processor encountered an internal limit overflow.

CAUSE

A SQL Server query can reference a maximum of 256 objects (tables and/or views). During the creation of a dynamic snapshot, the dynamic Snapshot Agent tries to create views based on merge system tables and other views defined on the articles contained in the publication. During this process, the dynamic Snapshot Agent builds a view definition that references more than the maximum limit of 256 objects in the FROM clause of the Transact-SQL query, which results in any of the error message shown in the "Symptoms" section.

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 How to obtain the latest SQL Server 2000 service pack

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.