asked
Error handling EXEC(@CMD) in SP (SS2000)
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 your question, and post either the full T-SQL or a sample of the surroundings where things go wrong.
Here's was it is up until now . . . : 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**.
Thanks Magnus, for your effort and time. There remains the question of how to overcome such errors (if expected) and continue executing a script in SS2000? Or is Mr.Magoo right and can this only be done in a job with all jobsteps going to the next step (fail or success)? Thanks again, Jaap.
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.
Did you try RAISERROR? Rather than execute it, set the iteration to print the TSQL out. Copy that into a new SSMS tab and execute it there. The line that causes the problem should be easily found there.
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?
Found this on the web:
http://afjohansson.spaces.live.com/Blog/cns!3CA68ED86F5A5970!318.entry Tried his solution using sp_executesql, but alas!
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][1] [1]:
http://msdn.microsoft.com/en-us/library/aa259577(SQL.80).aspx
