Transact SQL – sp_MSforeachtable – Error Trapping

Background

As part of a data loading script, I disabled the foreign keys and now need to re-enable them.

 

Code

Simple Code

One simple straight forward way is to  use sp_MSforeachtable.

Here is the sample script.


declare @sql varchar(4000)

declare @command varchar(255)

set @command = ' PRINT ''?''; ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL; '

EXEC sp_MSforeachtable
@command1 = @command

 

Error Image:
error

Error Text:


[dbo].[tblToken]
[dbo].[tblBookingItem]
Msg 547, Level 16, State 0, Line 5
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_tblBookingItem_tblBooking". The conflict occurred in database "DBSchedule", table "dbo.tblBooking", column 'pkBookingID'.

The simple code has a problem in that it stops abruptly whenever data is missing from the referenced table.

Add Error Handling

To properly handle our error, let us add exception handle via BEGIN TRY / BEGIN CATCH.


declare @sql varchar(4000)
declare @sqlErrorTrapped varchar(4000)

declare @command varchar(255)

set @command = ' PRINT ''?''; ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL; '

/*
EXEC sp_MSforeachtable
        @command1 = @command

*/
set @sql = @command

set @sqlErrorTrapped
             = 'BEGIN TRY'
            + @command
            + ' END TRY '
            + ' BEGIN CATCH '
            +  'print char(9) + ''======================================================================================''; '
            +  'print char(9) + ''Failing Step: - Failed on ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL '' '
            +  'print char(9) + ''Error Message: - '' + ERROR_MESSAGE()  '
            +  'print char(9) + ''======================================================================================''; '
            + ' END CATCH '

EXEC sp_MSforeachtable
        @command1 = @sqlErrorTrapped

 

Output:

Image:

errorGraceful

 

Textual:


[dbo].[tblBookingItem]
	======================================================================================
	Failing Step: - Failed on ALTER TABLE [dbo].[tblBookingItem] WITH CHECK CHECK CONSTRAINT ALL 
	Error Message: - The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_tblBookingItem_tblBooking". The conflict occurred in database "CondecoR", table "dbo.tblBooking", column 'pkBookingID'.
	======================================================================================
[dbo].[tblOutlookPostDataBookingLink]
[dbo].[tblCateringMap]
[Event].[tblContact]

 

Now, even though we failed on one of the tables, the logic moves on to the next table.

 

 

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s