SQL Server Performance

Query Oracle Link server connection

Discussion in 'Performance-Related Article Discussions' started by LanLan, Jan 17, 2006.

  1. LanLan New Member

    Our DTS packages pull data from multiple tables form Oracle. The problem we have is that the connection sometimes will drop. I want to write a checking method, first to query the connnection, if there, then pull the data. If not, try, say three times. I write the following script, run on my test pc, create a linkserver, then unplug my network cable. Theoretically, if the connection fails, I should at least have three printed message, yet it is not. I think @@ERROR may not return the connection error or is there a bug in the script?


    DECLARE
    @err int,
    @rowcntint,
    @icountint

    SET @icount = 0

    CONN_LOOP:
    IF @icount < 3
    BEGIN
    SELECT *
    into UserLists
    from LinkServer.DatabaseName.dbo.UserLists
    SELECT @rowcnt=@@rowcount, @err = @@ERROR
    PRINT ( 'loop: ' + cast(@ICOUNT as varchar(5)))
    SET @icount = @icount + 1
    IF (@@ERROR <>0 and @rowcnt <= 0 ) GOTO CONN_LOOP
    END
    ELSE
    BEGIN
    GOTO END_Conn
    END


    GOTO END_Conn

    END_Conn:

    thanks all in advance.
  2. Adriaan New Member

    Depending on the SEVERITY of the error, the whole script may just stop at that point. For instance ...

    You have a script in QA that starts by creating a temporary table, and after that it opens a cursor. At the end of the script, the temporary table is dropped.

    So you run the script, but there's an error in the query definition of the cursor, and the script ends with an eror message.

    You then correct the error, and you run the script again. Now you will get a warning that the temporary table already exists, because the script failed and did NOT run to the end. (The connection was not closed, so the temporary table wasn't dropped automatically.)
  3. LanLan New Member

    Hello Adriaan,

    The temp table can be commented out. It is not important. I just want to check whether the conneciton with Oracle link server is alive. As you say, the whole script seems not go any further. Once the link with Oracle is dropped, it just stopped without any re-try. Instead of rolling back the whole DTS package, I want it to re-try at three times before aborting.

    thanks, Lan
  4. Adriaan New Member

    Sorry about the confusion with the temp table - I was trying to sketch a hypothetical situation, and didn't look close enough at your script to notice you were using a temp table too.

    In SQL 2005 there's a nifty little bit of new syntax called TRY, which (I'm hoping) allows us to catch disruptive errors too.

Share This Page