January 19, 2015 at 12:01 pm
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?
January 20, 2015 at 3:54 am
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...
January 20, 2015 at 4:40 am
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
January 20, 2015 at 5:20 am
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...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply