This is essentially the statement I'd like to catch and gracefully quit if it occurs:
CREATE UNIQUE NONCLUSTERED INDEX UQ_First_Key_SecondField_ThirdField
ON [dbo].[DetailTable] ( Prime_Key, SecondField, ThirdField ) ON [PRIMARY]
SET @.ErrorNumber = @.@.ERROR
Execution never gets to the "SET @.ErrorNumber = @.@.ERROR" statement so I can't act on it. Instead it bombs right away and gives me this error message:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 5. Most significant primary key is '706'.
The statement has been terminated.
When that CREATE statement is executed I'd like to gracefully exit the stored procedure (sproc) and report the error to the operator. It's not that I don't understand the error - I fully expect it with SOME of our customers - the problem is that I want to report the REASON for the error to our customers of various expertise.
I created a series of sprocs to re-create indexes in our customers' databases when we define them. Indexes were not defined before, or were defined with random names, so now I'm trying to manage index names and designs explicitly with a series of sprocs I create in SQL Server 2000 to search for, and/or delete, and/or recreate if necessary after verifying the suitability of the index names and syntax as defined by our developers.
Note that specically I'm using either EXEC( ) or EXEC @.ErrorNumber = sp_executesql @.SQLString and I get the same results as if I just use the CREATE statement described above - it bombs out on me and I can't handle the error gracefully.So should I take the lack of response to mean that there is no way to trap this kind of error?|||You will not be able to trap "fatal errors" in SQL, you should build in error handling at the application level to take care of such situations.|||
Quote:
Originally Posted by Motoma
You will not be able to trap "fatal errors" in SQL, you should build in error handling at the application level to take care of such situations.
Thanks, Motoma, I was afraid that might be the case. I was hoping to avoid having to build a new tool to take care of this "simple" task outside the normal procedures we use to upgrade our customers...
