June 30, 2004 at 3:49 am
Hi
In my SP, when there is an error, I want to apply a special treatment (to report in a log file the wrong record). Here is part of my code :
INSERT INTO SYSADM.ar0 (concur,dat1,libelle1,ref,categorie,nrid,rid,rmod,dmod)
VALUES (0,@CREDAT,@ITMDESI,@ITMREF,@TSICOD0,@NRID,@RID,@RID,getdate())
IF @@Error <> 0
BEGIN
SET @msg = @CREDAT+'#'+@ITMDESI+'#'+@ITMREF+'#'+@TSICOD0
exec sysadm.f_write_file @msg,@file
END
When I execute the SP on my test database, there is an error but no error message. It is what I want : the wrong record is just written in the log file. When I execute the same SP with the same datas on my 'production' database , there is also an error. But my problem is that SQL Server reports the error message :
Server: Msg 242, Level 16, State 3, Procedure sp_importation_temp, Line 345
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statement has been terminated.
I don't want that because the job which executes the SP appears in red...
June 30, 2004 at 7:22 am
There might be a difference in the database options set on the test database and the production database which are suppressing the error messages on the test database...to check if this is so - run DBCC USEROPTIONS on both the databases and check if there is a difference in the Value for 'arithabort' and 'ansi_warnings'...
If you want to allow your SP to continue regardless of arithmetic-overflow/divide-by-zero/truncation errors then set the SET ANSI_WARNINGS and SET ARITHABORT settings to OFF at the beginning of the stored proc...(I think this might also suppress the values of @@Error and you wouldn't be able to log the row with the error...plus this option returns NULL when a divide-by-zero/arithmetic overflow occurs so if the column this value is to be inserted in does not allow NULL then there'll be an error)
HTH
June 30, 2004 at 9:00 am
Thanks for your help
When I set the SET ANSI_WARNINGS and SET ARITHABORT settings to OFF, it happens what you say.
What is strange is that the 2 database have the same values for 'arithabort' and 'ansi_warnings'... ('SET')
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply