Customize SQL server 2008 system error

Question

Customize SQL server 2008 system error

assefam

Say Hey Kid

Points: 707
More actions
September 8, 2010 at 7:33 am

#225933

I am developing a certain system and I want to track the system error and customize it for clients.
let me explain more on this. I have a table with primary key constraint, foreign key constraints,unique constraints and other. When I insert value into the table there may be different violation of constraints and the system will generate the corresponding errors which is not understandable for clients.So , i need to track and customize the error with appropriate message for the client.
Is there any mechanism in SQL server 2008 to handle this issue?
For information, I used stored procedure to insert data to the table
one solution may be parse the error message like a word begin with 'Violation of primary key' and substitute with a message like ' the Id is already exist, please use other id'....But I don't know how to parse a text in SQL server. could you help me in this regard

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Use a TRY-CATCH block, use the Error_Number() function to check what the error code is, then use RAISERROR to raise a custom error back to the app.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

assefam Say Hey Kid Points: 707 More actions · Answer 2

Thank you for your fast reply.

My doubt is about the uniqueness of the error number. for example, primary key violation, foreign key violation, unique constraint error may have the same error number. Could you have any idea on this issue?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Well, you could write some code to throw those errors and see what error numbers they have.

The SQL errors are visible in the sys.messages view. Query that, see what errors you want to handle and neaten up and note the error numbers.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

assefam Say Hey Kid Points: 707 More actions · Answer 4

thank you very much for your advise. I have solved the problem according to your suggestion.

error code for:

unique constraint is 2627

Foreign key constraint is 547

thank you