November 1, 2002 at 1:28 pm
Is it possible to capture handle errors on the table level?
for example,..
CREATE TABLE table1(table1_field varchar(1));
and I insert a record into that table...
INSERT INTO table1(table1_field) values('zz');
of course I get an error message...
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
How do I log that sql statement into a table (user or system)? Is SQL Profile my only hope?
Thanks in advance,
Billy
November 1, 2002 at 2:02 pm
catch it in the client and insert. Or use a stored procedure to do this.
profiler could, but be ugly.
Steve Jones
November 1, 2002 at 3:14 pm
At the table level no. And there is no real good error trapping in SQL. The big problem is if you wrote to a table the errors via trigger the data is rolled back. As Steve suggests you could right this client side or thru an SP however, with an SP you would still be able to have issues similar to this on the variables themselves and you make too many round trips. The best way is to do client side validation to verify the integrity of the data before submission, and then you really would want to log the error but give messages as to why you will not submit the data to the server. This is the ideal solution, not programming for error trapping. Error trapping is to cover the items you didn't pretrap. Now if you are allowing someone to type directly into the tablesw and still want to catch then Profiler is the only option you have, but it is not very verbose about the exception as I recall.
November 1, 2002 at 3:19 pm
If you are just interested in catching the error itself (no details other than error 8152 has happened), you can setup an alert for error #8152 using SQL Server Agent.
If you are interested in what statement (or what object) caused the error, Steve's right, handling it client-side is the best option if it is available.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
November 1, 2002 at 3:55 pm
Thanks for all your responses!
Because the table I want to audit has many inserts from many locations, I was hoping that there is something simple I can use to audit that table (like put the error trapping in an instead of trigger) but I tried that and it doesn't work because the trigger does not execute.
November 1, 2002 at 4:27 pm
Profiler with SQL stmt begin and, exceptions will be you best approximation.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply