December 12, 2001 at 5:52 am
I would like to be able to filter common error conditions generated within my stored procedures in SQL Server 2000 to help callers by generating more helpful error messages to pass back as OUTPUT parameters.
The kind of things I want to trap are Referential constraint violations and primary key violations and the like.
However, although I can detect the error number (@@ERROR), and cannot seem to find a way of detecting the post-processed message or the variable message information that is passed to the client, so that instead of just generating something like
"You cannot delete this personnel record because it is referenced elsewhere",
I could generate something like
"You cannot delete this personnel record because it is referenced in the ECardOrder table"
Can this information be retrieved or this there a better way?
The intention is to generate a message acceptable for the user to see or at least provide the means to the client to generate it.
December 12, 2001 at 6:26 am
You would probably be better off checking for referential integrity before actioning the command.
To retrieve the error message you can use
spFormatOutputBuffer
(you will need to change some of the offsets for v2000).
But I think this is a lot of effort and still won't be easy to interpret the results.
Do you want to confuse the client with the database schema?
Cursors never.
DTS - only when needed and never to control.
December 12, 2001 at 7:03 am
Thanks for the response.
I would rather not do referential-type checking by hand, otherwise what's the point of declarative referential integrity constraints anyway? 🙂
The intention is not to try to trap everything that could occur, just pre-format the 2 errors commonly generated by users (primary key and foreign key violations) and just optionally present the raw database error for any other unexpected cases. It's rather like coding for expected exceptions and having a catch-all for others.
The stored procedures in question will be called from VB and they typically have done some kind of error message parsing to try and glean some sense from the error rather than present the raw rather terse message received from the OLEDB driver! I would like to take this work from them if possible as it places an unreasonable and unportable load onto the front-end client.
I will try your procedure with the appropriate amendments for 2000. I agree that it is a bit of a dodgy method. As you have indicated, I will still have to parse the error message itself: another portability issue.
If there is really no other method, then I might give up..... 🙁
December 14, 2001 at 6:08 pm
The idea is that the application should only allow valid updates to be made. The integrity constraints are to catch bugs in the application and incompatible changes that have been made by other users in a multi-user system. In both these instances you probably wouldn't want to tell the user about the details but just log them for the support team.
Cursors never.
DTS - only when needed and never to control.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply