July 28, 2003 at 5:45 am
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
July 28, 2003 at 5:49 pm
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.
July 30, 2003 at 4:33 am
Thanks for ur suggestion. It solved my problem. Following the same approach.
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply