Execute SQL Task gets terminated

Question

Execute SQL Task gets terminated

jaganmbv

SSC Rookie

Points: 44
More actions
July 28, 2003 at 5:45 am

#151887

We are calling T-SQLs from Execute SQL Task. The logic in T-SQLs is as follows:
OPEN CURSOR ..
FETCH FIRST ROW..
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO ABC (...) VALUES (..)
SELECT @L_ERROR_CODE = @@ERROR
IF @L_ERROR_CODE = @C_DUPLICATE_RECORD_ERROR
BEGIN
UPDATE
ABC
SET
ABC_NAME= @abc_name,
DATE_MODIFIED = GETDATE(),
MODIFIED_BY = CURRENT_USER
WHERE
ABC_ID=@abc_id
END
ELSE IF @@ERROR <> 0
BEGIN
SELECT @P_SUCCESS = 1
SELECT @P_MSG_TEXT = 'Error'
END
FETCH NEXT ROW
END
Execute SQL Task gets terminated when first error (unique index error) occurs, though it is handled in T-SQLs.Package fails. Help needed in this context.
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

brendt hess Hall of Fame Points: 3256 More actions · Answer 1

You will be better off by checking for the existence of the key in the destination table. In fact, looking at your logic, you will be better off coding this as an Update and an Insert statement, instead of using a CURSOR, e.g.:

Update ABC

Ser ABC_NAME = x.ABC_NAME,

Date_Modified = getdate(),

Modified_by = Current_User

FROM ABC A

INNER JOIN (<Your cursor select here>) X ON

A.abc_ID = x.abc_id

INSERT INTO ABC (...)

SELECT <your cursor select here>

LEFT JOIN ABC a ON

X.ABC_ID = a.ABC_ID

WHERE a.ABC_ID Is Null

As for the error trap -- Duplicate Key errors automatically kick you out. You cannot trap them in TSQL code.

jaganmbv SSC Rookie Points: 44 More actions · Answer 2

Thanks for ur suggestion. It solved my problem. Following the same approach.

Thanks