A colleague came to me today with the question why the performance of a simple ad-hoc delete query was slow. I found out that there was a self referring FK that caused this problem. So this is what I did to make de delete work again.

USE <DATABASE>
GO
-- 1.Disable CONSTRAINT 
ALTER TABLE [DBO].[TABLE] NOCHECK CONSTRAINT <FK_Self_RefferingKeyConstraint>;

-- 2. Delete records
DELETE FROM [DBO].[TABLE]  
WHERE ID in 
(
  167364827,
  167364828,
  167364829,
  167364830,
  167364831,
  167364832,
  167364833,
  167364834,
  167364836,
  167364837,
  167364838,
  167364839,
  167364840,
  167364841,
  167364842
) 
-- 3.Enable CONSTRAINT 
ALTER TABLE [DBO].[TABLE] CHECK CONSTRAINT <FK_Self_RefferingKeyConstraint>;
GO

Today I found out that a colleague of mine had forgotten to set SQL Alert Mail notifications

I have written the script below to correct this.

-- set operator
declare @operator_name nvarchar(50) = '<your_operator_name, varchar(50), value>'

-- begin proces
declare @name sysname
declare SetNotificationCursor cursor for
select name from msdb.dbo.sysalerts

open SetNotificationCursor
fetch next from SetNotificationCursor into @name

while @@FETCH_STATUS = 0
begin 
  begin try
    exec (N'EXEC msdb.dbo.sp_add_notification @alert_name=N'''+@name+''', @operator_name=N'''+@operator_name+''', @notification_method = 1')
    end try 
  begin catch
    print  ERROR_MESSAGE()
  end catch
  fetch next from SetNotificationCursor into @name
end

close SetNotificationCursor
deallocate SetNotificationCursor
GO
-- end