I am creating an automated build and test tool for our databases. In general there are two stages: - install databases in version A, install transactional replication - drop replication, update databases to version B, install replication
The problem is that replication is dropped before previous stage finish to create articles (tables) at Subscriber. In this case Subscriber is missing some tables and update script fails.
Can you give me a query or tables to check if all articles are created?
Subscriber is on different instance than Publisher. Articles are created with sp_addarticle and after that we execute this: sp_startpublication_snapshot
UPDATE: I managed to create a workaround but it is not 100% correct. This query is supposed to check if tables created from replication on Subscriber are the same as articles on Publisher. I found out that our actual situation is as follow: - tables on Subscriber exists, because they were created from initial scripts - replication is installed - below query is executed - in the meantime articles on Publisher are created - my query compares them with original tables from initial scripts and finishes with success - Replication is started and Subscriber tables are being dropped and created - update to version B is started - sql agent is stopped, replication is dropped and we have missing tables on Subscriber.
The query is executed on Subsciber with linked connection to Publisher.
WHILE ( SELECT COUNT(1) FROM ( SELECT a.dest_table FROM [10.149.000.000].PubDB.dbo.sysarticles a (NOLOCK), [10.149.000.000].PubDB.dbo.syspublications p (NOLOCK) WHERE a.pubid = p.pubid AND p.NAME = 'my publication name' EXCEPT SELECT t.NAME FROM sys.tables t (NOLOCK) WHERE t.is_ms_shipped = 0 ) AS Z ) > 0 BEGIN PRINT 'Waiting for replication to finish.' WAITFOR DELAY '00:00:02' END