469,077 Members | 1,457 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,077 developers. It's quick & easy.

Error handling with CREATE INDEX

I'm receiving a "severe" error that I can't seem to trap and evaluate in SQL Server 2000 (and no, I can't switch to 2005, this is on our customers' machines). And I've not been able to find info on this specific problem.

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.
Jun 14 '07 #1
3 1668
So should I take the lack of response to mean that there is no way to trap this kind of error?
Jun 18 '07 #2
Motoma
3,237 Expert 2GB
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.
Jun 19 '07 #3
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....
Jun 19 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

12 posts views Thread by Xeon | last post: by
4 posts views Thread by Ian Lazarus | last post: by
3 posts views Thread by WindAndWaves | last post: by
12 posts views Thread by scsharma | last post: by
6 posts views Thread by Cruithne3753 | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.