Hi all, I've upgraded a SQL 2000 SP3 merge replication publisher to SQL 2005. One publication on this server has over 700 articles and this replication has been running fine on SQL 2000. However, now that the server has been upgraded, the snapshot is failing with the following error: 2008-02-20 13:02:28.13 spid54 Replication-Replication Snapshot Subsystem: agent SRV-GLAS029-ShipSure-ShipSureShipManagement-1 failed. The replication agent had encountered an exception. Source: Replication Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException Exception Message: Too many table names in the query. The maximum allowable is 256 Has the number of tables allowed in a merge replication publication changed? I can't find any documentation to say that it has... Thanks for any help. Lesley
After a little more research, I can answer my own question. Apparently there is a specified limit of 256 articles in a merge replication publication: http://msdn2.microsoft.com/en-us/library/ms143432.aspx This hasn't changed since SQL 2000, but there is a workaround suggested in this forum: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1059104&SiteID=17 I truncated the msmerge_contents table as suggested & managed to successfully run the snapshot. I'm still concerned about this though. We've had this merge replication running on SQL 2000 for years with no problems in this area. We have a publication of 748 articles (18 of which are filtered) running to 12 subscribers. 256 seems like a very small limit compared to the number of articles allowed for transactional replication (32,767) and this workaround seems a bit flaky.
http://support.microsoft.com/kb/296045 asks you to Split the publication into multiple publications so that the combined number of published tables + Join Filters do not exceed 256 in one publication. -or- Disable the dynamic filtering option in the merge publication by setting the @dynamic_filters parameter in the sp_addmergepublication stored procedure to FALSE.
Thanks Dinesh. I experienced performance problems in SQL 2000 when splitting the publication into multiple publications, so I'm not keen to do that again. And we need to use dynamic filters, so the second option is also a no-go. I'm surprised that this is an issue in SQL2005 as it appears that this fix was put into SQL2000 with SP2. I've raised a call with Microsoft to see if there is a 2005 hotfix.
Apparently this is an active bug and will be fixed in SP3. The only workaround suggested was to split up the publication into 3 separate publications. Not particularly happy about this esp as this issue was fixed in SQL2000 SP2. Guess I'll be waiting on the SP3 release before upgrading.
Have you looked at the cumulative update package for SP2 in this case, as referred by Dinesh SP3 is not yet finalized or not planned. http://sqlserver-qa.net/blogs/tools...server-2005-service-pack-2-available-now.aspx fyi on CU.
Thanks for that Satya. It doesn't appear to include the bug that we've come up against, but I'll apply it anyway. I was told that the specific bug limiting the number of merge replication articles to 256 would be fixed in SP3. But of course, they wouldn't commit to any timescale for the release of SP3.
Cool, interesting that you followed up this problem. If it is not fixed then better to raise a connect bug too from http://connect.microsoft.com link.