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
No comments:
Post a Comment