I am executing a DTS to transfer my SQL SERVER 2000 data to ORACLE 10g.
I have created the destination Oracle 10g as an linked server in SQL SERVER 2000.
In all stored procedures for data movement from SQL SERVER to ORACLE, we have used OPENQUERY.
I am capturing the error through @@ERROR
The issue is, when OPENQUERY thows ececption, the execution of my SP stops there only and it doesn't go to error handling section.
INSERT INTO OPENQUERY(LNKSVRWUPOS, 'SELECT ID FROM TABLENAME')
SELECT (SELECT * FROM OPENQUERY(LNKSVRWUPOS, 'SELECT ID_SEQ.nextval FROM DUAL')) AS ID
--error handling section
SET @ERROR = @@ERROR
IF @ERROR <> 0
INSERT INTO dtslog(FileName, FileTypeID, Message)VALUES(@FILENAME, 1, 'OPERATION failed form SQL SERVER TO ORACLE)
RAISERROR('Error moving data from SQL SERVER TO ORACLE', 17, 1)
If the insert section fails, the control doesn’t go to the error handling section.
We need your guidance on the same at the earliest.
It does not work when you are using sql2000. You are outside of SQL at that point and sql server can't interpret what is going on outside of the environment because it is not an sql error at that point. Using sql2005 is better because you can use the try and catch functions. But because you are using sql2000 you may want to try @@rowcount instead of @@error. Or some other function that you know you should get a result from. Then that becomes your error control.