Thursday, June 21, 2007

Replication Procedures Accidently Added to Publication

We have merge replication configured for about 20 field computers, and 4 servers on our local network. While trying to fix a problem, all articles on the distributor were added to a publication. This includes about 700 procedures generated by SQL server. We weren't able to uncheck them once the changes were applied, probably because the snapshot was created and to uncheck them would invalidate the snapshot.

This caused a lot of problems because we couldn't create any more snapshots because we kept getting the error:
Cannot drop replicataed procedure 'sp_sel_BD87AFD5144A4C7FEE4B86FD60AC49F3_pal' because it is being used for replication.

So I had to write this script to remove them from the publication programaticly:

DECLARE @proc_name NVARCHAR(100)
DECLARE @pub_name NVARCHAR(100)

SET @pub_name = 'PublicationName'

DECLARE cGetRepProcs CURSOR FOR
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE'
AND ROUTINE_NAME LIKE '%_%_pal'

OPEN cGetRepProcs
FETCH NEXT FROM cGetRepProcs INTO @proc_name
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE sp_dropmergearticle @pub_name, @proc_name,1,0,1

FETCH NEXT FROM cGetRepProcs INTO @proc_name
END

CLOSE cGetRepProcs
DEALLOCATE cGetRepProcs