Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270

    Unanswered: Linked Server Error Handling

    Hi Guys,

    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.

    Code:
    --Insert 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
    BEGIN
            ROLLBACK TRANSACTION
            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)
            RETURN
    END
    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.


    Thanks,
    Rahul Jha

  2. #2
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    58
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •