Constraint error message handling

  • I have a constraint on a 3rd party app that I can't change the app but can add a constraint at the table level. The constraing works but the error message back is ugly. Is there a way to do some kind of raiserror on a constraint that could send better info back to the user, I have done this in triggers, is the an approach for constraints?

  • I would not advice doing that, but I think it is possible by altering the error message template in sys.messages

    eg:

    select * from bucs.sys.messages where text like '%constraint%'

    Just remember, it will change error message for all constraints.

    You will need to allow Ad Hoc update to system catalogs:

    sp_configure 'allow updates',0

    go

    reconfigure

    go

    Return 1 back after you finished.

    I should not really advise above at all.

    The best way to handle/overwrite the system error, will be on client side (UI or data access layer), or at least in the stored procedure...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • The allow updates option doesn't have any effect any more (from SQL Server 2005). You could use a trigger instead of a constraint in order to return the error message you want, but this is inefficient and I would only use it as a last resort.

    John

  • John Mitchell-245523 (1/20/2015)


    The allow updates option doesn't have any effect any more (from SQL Server 2005). You could use a trigger instead of a constraint in order to return the error message you want, but this is inefficient and I would only use it as a last resort.

    John

    You are right. It is impossible now.

    As most of SQL experts would say:

    Never ever ever, change system tables.

    ...apart of - you can add new user defined messages to sys.messages using sp_addmessage...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

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

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