I'm creating a SP that builds dynamic T-SQL within a loop and executes through EXECUTE. The commands generated are 'ALTER TABLE @tablename WITH CHECK CHECK CONSTRAINT ALL', this because I ran into some old FK's that seem to be not trusted . . . When an error occurs the SP stops without finishing the loop through tables with FK's. I tried using IF @@ERROR<>0 immediately folowing the EXEC and and as part of @CMD. Both don't work. Because the SP must also run on SS2000 I can't use TRY/CATCH blocks. Can anyone help?! **Edit ->** Added a source code of the proc ALTER PROCEDURE usp_EnableConstraintsWithCheck ( @p VARCHAR(256) = 'allTablesWithConstraints' ) AS BEGIN DECLARE @t AS VARCHAR(256); DECLARE @c AS VARCHAR(512); SET NOCOUNT ON; IF @p <> 'allTablesWithConstraints' BEGIN SELECT @t=OBJECT_NAME(OBJECT_ID(@p)); SELECT @c='ALTER TABLE '+@t+' WITH CHECK CHECK CONSTRAINT ALL'; EXEC (@c); PRINT 'Constraints enabled with check for '+@t+'.'; END ELSE BEGIN PRINT 'Generic enabling with checks . . .'; DECLARE t CURSOR FOR SELECT DISTINCT OBJECT_NAME(parent_obj) FROM sysobjects WHERE NULLIF(parent_obj,0) IS NOT NULL AND type IN ('C','F') ORDER BY OBJECT_NAME(parent_obj); OPEN t; WHILE 0=0 BEGIN FETCH NEXT FROM t INTO @t; IF @@FETCH_STATUS<>0 BREAK; SELECT @c='ALTER TABLE '+@t+' WITH CHECK CHECK CONSTRAINT ALL;IF @@ERROR <> 0 PRINT ''Error!'''; EXEC (@c); IF @@ERROR<>0 BEGIN PRINT 'Error enabling constraints with check for '+@t+'.'; END ELSE BEGIN PRINT 'Constraints enabled with check for '+@t+'.'; END END CLOSE t; DEALLOCATE t; END END
**EDIT** I have tried error handling of enabling constraints without luck. Here's a way to check if if there are untrusted constraints before enabling the constraints WITH CHECK -First in the proc - create a temporary table which will hold the rows --that fails integrity check: create table #t ([Table] nvarchar(255), [Constraint] nvarchar(255), [Where] nvarchar(255)) --Do all the magic to get your cursor etc, this is just the inner part of the loop in your proc --Run DBCC CHECKCONSTRAINTS and put the results into temporary table #t insert into #t ([table],[constraint],[where]) EXEC('DBCC CHECKCONSTRAINTS(''' + @tablename + ''')') --IF @@rowcount>0, it means DBCC CHECKCONSTRAINTS have found at least one problem with the table. --Then don't try to enable the constraints --Otherwise, run your ALTER TABLE statement if @@rowcount=0 BEGIN EXEC('ALTER TABLE ' + @tablename + ' WITH CHECK CHECK CONSTRAINT ALL') END --The rest of your proc goes here --Finally, select all the rows from #t to see which tables and rows in these tables fails integrity checks. SELECT * FROM #t drop table #t **END EDIT** I've been thinking about this, and when I saw your error message, it's pretty clear what's happened: Running **ALTER TABLE tablename WITH CHECH CHECH CONSTRAINT ALL** will ***TRY TO*** reenable all constraints. **WITH CHECK** means SQL Server should verify the integrity of the constraints. But as you say in your question - you suspect that there are untrusted FK-constraints. And it seems like you're right. There ARE untrusted FK-constraints, and that's why your ALTER TABLE-command fails. If you want to find out which tables has untrusted constraints defined, run **DBCC CHECKCONSTRAINTS(tablename)** for each table instead. That will tell you if you have untrusted constraints. You need to fix the inconsistency on row-level before you can reenable constraints using **WITH CHECK**.
It may be different schema, table names with illegal characters or bad formatting. Try wrapping the tablename in [ and ] and include the schema in the generation of the command. You might also use `RAISERROR () WITH NOWAIT` to show progress through the iteration.
I've tested your stored procedure on the adventureworks database and there it fails due to schemanames not being part of the code. But when running it on a user database where all tables belong to dbo, there's no problem with the proc. What's the error message you recieve?
One way to do this would be to use your SP to generate steps in a SQL agent job, with each step configured to proceed to the next even on failure. Each step would be an ALTER TABLE command. This would then ensure that every ALTER TABLE would be executed. This link should help with learning how to do this [Microsoft MSDN Library Page] :