Problems with error handling...
-
Hello!
I am new to SQL Server and I am trying to save the error if there is one, and some more information in the table tDTSLogs. I want the program to execute the insert/update EVEN if an error has ocurred!!!
My problem is that when I produec an error it doesn't even print out the error code. Nothing happens! I don't know if it's because I have putted the BEGIN... and so on in places where they are not supposed to be?! If I don't have any errors then it prints out nicely: "@@error is 0." Very happy for answers!!!!
Maria
Code:
DECLARE @intError INT
DECLARE @Col01 varchar(100)
DECLARE @Col02 varchar(100)
DECLARE @Col03 varchar(100)
DECLARE @Col04 varchar(100)
DECLARE @Col05 varchar(100)
DECLARE runCursor CURSOR
FOR
select Col01, Col02, Col03, Col04, Col05
FROM tNavisionBuffer
WHERE Col01 = '01'
OPEN runCursor
FETCH NEXT FROM runCursor
INTO @Col01, @Col02, @Col03, @Col04, @Col05
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRANSACTION
if not exists (select Articulo from tArticulos where Articulo = @Col02)
BEGIN
insert into tArticulos(Articulo, Descripcion, UnidadBase, CosteEstandar)
values(@Col02, @Col03, @Col04, convert(decimal(18,10), @Col05))
PRINT '@@error is ' + ltrim(str(@@error)) + '.'
Select @intError = @@ERROR
if (@intError <> 0) GOTO ErrorCode
END
else
BEGIN
update tArticulos
set Descripcion = @Col03,
UnidadBase = convert(datetime, @Col04),
CosteEstandar = convert(decimal(18,10), @Col05)
where Articulo = @Col02
Select @intError = @@ERROR
if (@intError <> 0) GOTO ErrorCode
END
COMMIT TRANSACTION
FETCH NEXT FROM runCursor
INTO @Col01, @Col02, @Col03, @Col04, @Col05
END
CLOSE runCursor
DEALLOCATE runCursor
ErrorCode:
if (@intError <> 0) BEGIN
PRINT '@intError is ' + ltrim(str(@intError)) + '.'
insert into tDTSLogs(Fecha, Proceso, CodigoSQLServer, Descripcion, IDItem)
values(GETDATE(), 'tArticulos',@intError,'Error tabla tArticulos', @Col02)
END
-
1 - You don't need a cursor for this task.
This is the insert statement :
insert into dbo.tArticulos (Articulo, Descripcion, UnidadBase, CosteEstandar)
select Col01, Col02, Col03, Col04, Col05
FROM dbo.tNavisionBuffer Nav
WHERE Col01 = '01' AND NOT EXISTS (Select * from dbo.tArticulos TA where TA.IDCol = Nav.IDCol)
Update statement :
Update TA SET Articulo = COl1,.... from dbo.tArticulos TA inner join dbo.tNavisionBuffer Nav ON TA.id = Nav.ID
2 - Read these articles for help on error handling :
-
Also - you seem to be doing a lot of data type conversions - from varchar to decimal and datetime....please watch out for inaccurate data that might result from this!!!
Ideally you should change your datatypes to the required ones in your tNavisionBuffer table!
**ASCII stupid question, get a stupid ANSI !!!** -
Meaby I should have explained that what I am doing is DTS, the data in tNavisionBuffer is a mix with different tables with different fields that comes from a script. The first field in the tNavisionBuffer shows what table it is. That is why I can't define the datatypes directly in tNavisionBuffer. What I am trying to do is "organize" the different rows to their table.
I have tried to do without cursor like the suggestion in the first respond, but no luck...
My program works without error (inserts, updates....), but when I want to handle the error, and I provoke one, then it neither adds a row in tArticulos or in tDTSLogs.
-
I have tried to do without cursor like the suggestion in the first respond, but no luck...
Derek - could you please post the code that you used and the error message you got ?!
**ASCII stupid question, get a stupid ANSI !!!** -
Code:
Like in the first messge
Error message:
Server: Msg 241, Level 16, State 1, Line 30
Syntax error converting datetime from character string.
Comments:
I have provoked the error by trying to convert to datetime when I know it's impossible. I just want this error to be saved in my tDTSLogs table and that the register tArticulos is inserted/updated independent if it has error or not!
-
SORRY SUSHILA, WRONG CODE...
DECLARE @intError INT
DECLARE @Col01 varchar(100)
DECLARE @Col02 varchar(100)
DECLARE @Col03 varchar(100)
DECLARE @Col04 varchar(100)
DECLARE @Col05 varchar(100)
insert into tArticulos(Articulo, Descripcion, UnidadBase, CosteEstandar)
select Col02, Col03, convert(datetime, Col04), Col05
FROM dbo.tNavisionBuffer Nav
WHERE Col01 = '01' AND NOT EXISTS (Select * from dbo.tArticulos TA where TA.Articulo = Nav.Col01)
PRINT '@@error is ' + ltrim(str(@@error)) + '.'
Select @intError = @@ERROR
if (@intError <> 0) GOTO ErrorCode
ErrorCode:
if (@intError <> 0) BEGIN
PRINT '@intError is ' + ltrim(str(@intError)) + '.'
insert into tDTSLogs(Fecha, Proceso, CodigoSQLServer, Descripcion, IDItem)
values(GETDATE(), 'tArticulos',@intError,'Error tabla tArticulos', @Col02)
END
But it's the same error:
Server: Msg 241, Level 16, State 1, Line 8
Syntax error converting datetime from character string.
-
Select * from dbo.YourTable where ISDATE(DateCol) = 0
That'll give you a list of invalid dates.
Otherwise, check out the SET DateFormat command in BOLS.
-
Derek - couple of things:
1) Not sure why you're converting @@error to string but it's not necessary.
2) capture the @@error in your variable immediately after your statement..
SET @intError = @@ERROR/PRINT 'Error is ' + @intError
3) If your PRINT statement doesn't work, then it means that the command is getting terminated and your error handling is never reached...do you know what kind of problems you anticipate ?! You say that you threw in the date conversion to deliberately cause an error, but other than that where do you expect it to fail ?!
4) You can also check for @@ROWCOUNT after each command to see how many rows have been affected and code accordingly.
5) Maybe you could post some sample data to help someone better guide you!
**ASCII stupid question, get a stupid ANSI !!!** -
Thanks for all!
I send you a more simplified example of my problem.
--
-- Provocate an error inserting a wrong type value
insert into tPrueba (COL01) values ('A')
-- Catch the error
if @@error > 0 goto ERROR_PROCEDURE
-- On error goto... but this line NEVER is executed, the program stop when an error occurs. Why?
ERROR_PROCEDURE:
-- If error insert a specific value to detect if this line is executed
insert into tPrueba (COL01) values (9)
return
--
Can it be for a configuration on my sql server?? This example works in others machines.
-
If col1 is a datetime column, that'll ALWAYS fail. That's the error you are getting.
-
5000 posts...Derek says that he's deliberately inserting wrong data to see if he can "force an error"...Derek - it would help to post the ddl and sample data...also, I don't think it's possible to bypass the error and continue with insertion/update...but 5000 posts should be able to confirm this..?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
And I've already told him what to do to expose that bad data in his table and what to look for to fix it if it's only a date format.
Am I that bad of a communicator??
-
5000 posts - you are not a "bad communicator" at all...I think though, that Derek's problem is not with a specific datatype but that regardless of the error encountered, he wants it to continue executing...(at least that has been my understanding of the problem)
Derek - here're links to some great articles on this site on error handling & stored procedures..
error description in stored procedure
**ASCII stupid question, get a stupid ANSI !!!** -
I'll take your word for it... I'd still try and validate what I insert in my db and correct the problems, but I don't know why he's doing that so I'll let go.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply