I have a stored procedure that drops all constraints and indexes then rebuilds them.
some of if goes something like this...
WHILE @@fetch_status = 0
BEGIN
SET @SQL = 'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + @ObjectName
BEGIN TRY
EXEC (@SQL)
END TRY
BEGIN CATCH
INSERT INTO ##NonDropped VALUES('Constra int ' + @ObjectName + ' on table ' + @TableName, ERROR_MESSAGE() )
END CATCH
all works fine.
What I want to do is improve the error messaging.
If I try to drop a non-existant constraint I would get an error
Msg 3728, Level 16, State 1, Line 1 'constraint_nam e' is not a constraint.
Msg 3727, Level 16, State 0, Line 1 Could not drop constraint. See previous errors.
All error messege does is return the last message, which is a bit meaningingless. I can't seem to find a way of getting the first message which would be more usefull.
Any pointers?
some of if goes something like this...
WHILE @@fetch_status = 0
BEGIN
SET @SQL = 'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + @ObjectName
BEGIN TRY
EXEC (@SQL)
END TRY
BEGIN CATCH
INSERT INTO ##NonDropped VALUES('Constra int ' + @ObjectName + ' on table ' + @TableName, ERROR_MESSAGE() )
END CATCH
all works fine.
What I want to do is improve the error messaging.
If I try to drop a non-existant constraint I would get an error
Msg 3728, Level 16, State 1, Line 1 'constraint_nam e' is not a constraint.
Msg 3727, Level 16, State 0, Line 1 Could not drop constraint. See previous errors.
All error messege does is return the last message, which is a bit meaningingless. I can't seem to find a way of getting the first message which would be more usefull.
Any pointers?