Obscure Problems and Gotchas

Problem

Gotcha

Before I get to the gotcha, I just want to point out that I am a Software Architect with a ton of exposure to tSql, but I am not a DBA so some of these core SQL concentric problems still allude me at times. I always appreciate these moments of absolute clarity due to epiphany, it’s a good feeling. 

DECLARE @batchSize INT = 5000;
DECLARE @rowCount INT = 1;

WHILE @rowCount > 0
BEGIN
 DELETE TOP(@batchSize) t
 FROM dbo.Table1 t

 SET @rowCount = @@ROWCOUNT;
END

The amount being deleted is truly arbitrary, so I just chose 5K for the sake of argument. Let’s say this is a special table that has a lot of things happening to it implicitly and indirectly, much like many things in the magic world of SQL engines. Some of these things are constraints and some of these things are triggers, therefore in order to successfully delete those rows above, these constraints and triggers must be temporarily disabled. This of course leads to a new host of problems such as – should you be running this in single user mode and things of that nature. The answer is yes, now how you convince your co-workers, boss and especially your DBA of this is up to you I cannot help you with this. Especially if you have to tear down replication and rebuild it. Have fun with that.

Toggling Constraints

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- [dbo].[sp_ToggleConstraints] 1
-- [dbo].[sp_ToggleConstraints] 0
CREATE PROCEDURE [dbo].[sp_ToggleConstraints]
(
 @isEnabled BIT
)
AS
BEGIN
 SET NOCOUNT ON;
 
 DECLARE @keywords VARCHAR(50);

 DECLARE @sqlStatements TABLE
 ( 
   EntryId INT IDENTITY(0,1)
  ,SqlStatement VARCHAR(255)
 )

 IF @isEnabled = 1
  SET @keywords = ' WITH NOCHECK CHECK CONSTRAINT '
 ELSE
  SET @keywords = ' NOCHECK CONSTRAINT '

 /* Examples: 
   Disable: ALTER TABLE dbo.[TargetTableName] NOCHECK CONSTRAINT [FK name]
   Enable : ALTER TABLE dbo.[TargetTableName] WITH CHECK CHECK CONSTRAINT [FK name]
 */

 -- Select your dynamically created SQL statements for constraints
 INSERT INTO @sqlStatements
 SELECT 
  sqlConstraint = 'ALTER TABLE dbo.' + x.TableName + @keywords + x.ConstraintName
 FROM
 (
  SELECT
    TableName = t.name
   ,ConstraintName = fk.name
  FROM [dbo].[TableTarget] att -- It's a good idea to know what you are targetting

   INNER JOIN sys.tables t 
    ON t.name = att.TableName
   INNER JOIN sys.schemas s
    ON t.schema_id = s.schema_id
     AND s.name = 'dbo'
   INNER JOIN sys.foreign_keys fk
    ON fk.parent_object_id = t.object_id
     AND fk.delete_referential_action = 1 -- Targetting constraints with cascading deletes only
  WHERE att.ToggleConstraints = 1 -- Only select tables that should have their constraints toggled
  
  -- Sometimes tables are so poorly designed that all constraints have to be toggled
  UNION SELECT
    TableName = 'ScrewedUpTable1'
   ,ConstraintName = 'ALL'
  UNION SELECT
    TableName = 'ScrewedUpTable2'
   ,ConstraintName = 'ALL'
 ) as x
 ORDER BY
  x.TableName

 --SELECT * FROM @sqlStatements
 DECLARE @i INT;
 SET @i = 0;

 DECLARE @count INT;

 SELECT @count = COUNT(1)
 FROM @sqlStatements

 DECLARE @str VARCHAR(255)

 WHILE @i < @count
 BEGIN
  SELECT 
   @str = SqlStatement
  FROM @sqlStatements
  WHERE EntryId = @i;
  
  PRINT @str

  BEGIN TRY
   EXEC(@str);
  END TRY
  BEGIN CATCH
   -- declare the variables
   DECLARE @errorNumber INT
   ,@errorSeverity INT
   ,@errorState INT
   ,@errorProcedure nvarchar(128)
   ,@errorLine INT
   ,@errorMessage nvarchar(4000)

   -- Store the values
   SET @errorNumber = ERROR_NUMBER()
   SET @errorSeverity = ERROR_SEVERITY()
   SET @errorState = ERROR_STATE()
   SET @errorProcedure = ERROR_PROCEDURE()
   SET @errorLine = ERROR_LINE()
   SET @errorMessage = ERROR_MESSAGE()
 
   -- Check the values
   SELECT
    Number = @errorNumber
   ,Severity = @errorSeverity 
   ,[State] = @errorState
   ,[Procedure] = @errorProcedure
   ,Line = @errorLine
   ,[Message] = @errorMessage

   -- Log the error
   EXEC yourLoggingSprocGoesHere_youShouldReallyHaveOnIfYouDont
    @errorNumber = @errorNumber
   ,@errorSeverity = @errorSeverity
   ,@errorState = @errorState
   ,@errorProcedure = @errorProcedure
   ,@errorLine = @errorLine
   ,@errorMessage = @errorMessage
  END CATCH

  SET @i = @i + 1;
 END
END
GO

Toggling Triggers

SET ANSI_NULLS ON

GO
SET QUOTED_IDENTIFIER ON
GO
-- [dbo].[sp_ToggleTriggers] 1
-- [dbo].[sp_ToggleTriggers] 0
CREATE PROCEDURE [dbo].[sp_ToggleTriggers]
(
 @isEnabled BIT
)
AS
BEGIN
 SET NOCOUNT ON;
 
 DECLARE @keyword VARCHAR(7);

 IF @isEnabled = 1
  SET @keyword = 'ENABLE'
 ELSE
  SET @keyword = 'DISABLE'
 
 DECLARE @sqlStatements TABLE
 ( 
   EntryId INT IDENTITY(0,1)
  ,SqlStatement VARCHAR(255)
 )

 /* Examples: 
   ENABLE  TRIGGER dbo.[triggerName] ON dbo.[targetTableName]
   DISABLE TRIGGER dbo.[triggerName] ON dbo.[targetTableName]
 */
 INSERT INTO @sqlStatements
 SELECT
  sqlTrigger = @keyword + ' TRIGGER dbo.' + g.name + ' ON dbo.' + t.name
 FROM [dbo].[TableTarget] att -- It's a good idea to know what you are targetting
  INNER JOIN sys.tables t
   ON t.name = att.TableName
  INNER JOIN sys.schemas s
   ON t.schema_id = s.schema_id
    AND s.name = 'dbo'
  INNER JOIN sys.triggers g
   ON g.parent_id = t.object_id
 WHERE g.name LIKE 'trfs%' -- You may have to filter your triggers if you use replication. Mine happen to start with "trfs"
 ORDER BY g.name

 --SELECT * FROM @sqlStatements
 DECLARE @i INT;
 SET @i = 0;

 DECLARE @count INT;

 SELECT @count = COUNT(1)
 FROM @sqlStatements

 DECLARE @str VARCHAR(255)

 WHILE @i < @count
 BEGIN
  SELECT 
   @str = SqlStatement
  FROM @sqlStatements
  WHERE EntryId = @i;
  
  PRINT @str

  BEGIN TRY
   EXEC(@str);
  END TRY
  BEGIN CATCH
   -- declare the variables
   declare @errorNumber INT
   ,@errorSeverity INT
   ,@errorState INT
   ,@errorProcedure nvarchar(128)
   ,@errorLine INT
   ,@errorMessage nvarchar(4000)

   -- Store the values
   SET @errorNumber = ERROR_NUMBER()
   SET @errorSeverity = ERROR_SEVERITY()
   SET @errorState = ERROR_STATE()
   SET @errorProcedure = ERROR_PROCEDURE()
   SET @errorLine = ERROR_LINE()
   SET @errorMessage = ERROR_MESSAGE()
 
   -- Check the values
   SELECT
    Number = @errorNumber
   ,Severity = @errorSeverity 
   ,[State] = @errorState
   ,[Procedure] = @errorProcedure
   ,Line = @errorLine
   ,[Message] = @errorMessage

   -- Log the error
   EXEC yourLoggingSprocGoesHere_youShouldReallyHaveOnIfYouDont
    @errorNumber = @errorNumber
   ,@errorSeverity = @errorSeverity
   ,@errorState = @errorState
   ,@errorProcedure = @errorProcedure
   ,@errorLine = @errorLine
   ,@errorMessage = @errorMessage
  END CATCH

  SET @i = @i + 1;
 END
END
GO