question

jbregman avatar image
jbregman 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
sql-server-2000stored-procedureserror-handling
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Edit your question, and post either the full T-SQL or a sample of the surroundings where things go wrong.
1 Like 1 ·
@jbregman I copied the source code from your comment and added it to the question so it could be formatted. I hope you don't mind.
1 Like 1 ·
What happened when you tried using IF @@ERROR0?
0 Likes 0 ·
It never reached that point for the first table that caused an error.
0 Likes 0 ·
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
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
**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**.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
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.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
1 Like 1 ·
No problems with schema, names or characters . . . This procedure is an extension to my usp_DisableConstraints and usp_EnableConstraints which work fine but are very unlikely to cause errors on the ALTER TABLE statement CHECK/NOCHECK CONSTRAINT ALL.
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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?
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

That's right, in my case there is just dbo. The message says: Msg 547, Level 16, State 0, Line 1 ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint . . .
0 Likes 0 ·
Have tried some more: I separated the ALTER TABLE command execution into a separate SP with a return value. Outcome doesn't change: SP stops on first table causing trouble.
0 Likes 0 ·
Found this on the web: http://afjohansson.spaces.live.com/Blog/cns!3CA68ED86F5A5970!318.entry Tried his solution using sp_executesql, but alas!
0 Likes 0 ·
Mister Magoo avatar image
Mister Magoo answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.