Tuesday, April 29, 2014

Drop all SqlQueryNotificationService from Database


DECLARE @PROCNAME VARCHAR(500)
DECLARE CUR CURSOR 

FOR SELECT [NAME] FROM SYS.OBJECTS 
    WHERE TYPE IN (N'SQ') AND NAME LIKE 'SQLQUERYNOTIFICATIONSERVICE%'
OPEN CUR
FETCH NEXT FROM CUR INTO @PROCNAME
WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        EXEC ('DROP QUEUE [' + @PROCNAME+']')
        FETCH NEXT FROM CUR INTO @PROCNAME
    END TRY
    BEGIN CATCH
        FETCH NEXT FROM CUR INTO @PROCNAME
    END CATCH
END
CLOSE CUR
DEALLOCATE CUR

No comments:

Post a Comment