Sql-server – T-SQL Stored Procedure Returns Before Completing

sql serverstored-procedurest-sql

We are running into a very curious error. We have two production SQL Servers. We call a stored procedure on one (call it Server_A) that calls a stored procedure on the other (Server_B).

The stored procedures on Server_B execute a number of queries, but then return without completing all steps. We have try/catch blocks around the queries, but they don’t catch any errors. We are writing to log tables between steps, as well as inside and after the catch block. At some point in the execution, it just returns. There are no later writes to the log table, no writes from the catch block and no writes after the catch bloc. It’s as if the procedure just decided to return. It doesn’t just die without logging its death, either. The calling procedure on Server_A continues processing after the call to the procedure on Server_B.

SProc_A looks like:

Create Procedure Server_A.DB_A.dbo.SProc_A  
    INSERT INTO LOG_TABLE_A …
    EXEC ServerB.DB_B.dbo.SProc_B
    INSERT INTO LOG_TABLE_A …
    RETURN

SProc_B looks like:

Create Procedure Server_B.DB_B.dbo.SProc_B
    BEGIN TRY
        INSERT INTO LOG_TABLE_B…
        EXEC Query_1
        INSERT INTO LOG_TABLE_B …
        EXEC Query_2
        INSERT INTO LOG_TABLE_B …
        EXEC Query_3
        INSERT INTO LOG_TABLE_B …
    END TRY
    BEGIN CATCH     
        INSERT INTO LOG_TABLE_B …
    END CATCH
    INSERT INTO LOG_TABLE…
RETURN

In this example, SProc_B executes through Query_2 and the following Insert, and then returns. It doesn’t execute Query_3, it doesn’t execute any of the following Inserts, including the one in the catch block.

We have two development servers that execute the same stored procedures. And they don’t have this problem.

Any idea what might be causing this?

Update: 9/15/2019 A

SProc_A can be called in two ways, either from a SQL Agent Job, or from a SQL Server Management Studio window. Either way the results are the same.

Here is some more information based on testing this past weekend. It appears that the server on which SProc_A is executed makes a difference. When it is executed from our production server, Server_A, it fails as described. When it is executed from our development server (I'll call it Server_X) it runs correctly. It doesn't appear to matter what the target server is.

Here's an attempt to be clear without being too verbose:

Server_A: Source Production Server
Server_B: Target Production Server
Server_X: Source Development Server
Server_Y: Target Development Server

Results:

Server_A.Sproc_A: Executes Server_B.SProc_B: Fails as described.
Server_A.Sproc_A: Executes Server_Y.Sproc_B: Fails as described.
Server_X.Sproc_A: Executes Server_B.Sproc_B: Runs correctly.
Server_X.Sproc_A: Executes Server_Y.Sproc_B: Runs correctly.

So it appears that there is some problem with the source server, Server_A. A configuration issue? A communication issue? Something else? This is where I'm stumped.

I did try dropping the the linked server on Server_A that points to Server_B and recreating it to see if that was the problem. That didn't correct the issue.

I may be mistaken, but I don't think it's a security issue, at least not directly. I am a sysadmin on all servers, and the service account that the SQL Agent jobs run under is also in the sysadmin role.

Update: 9/15/2019 B

I updated the target procedure SProc_B, to include Throw as the first line in the catch, and ran the whole thing again. Apparently, it didn't get to the Throw because there was no indication of an error that it generated that went up through the stack.

I did see two messages in the SSMS Messages window that I hadn't seen before. (They might have been there previously, but I've just not noticed them.)

OLE DB provider "SQLNCLI11" for linked server "Server_B" returned
message "The connection is no longer usable because the server
response for a previously executed statement was incorrectly
formatted.".
The 'SProc_B' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

I was unable to find information about these messages on line. They are further details pointing to what might be happening.

Thanks Again,
Robbie

Best Answer

It doesn’t execute Query_3, it doesn’t execute any of the following Inserts, including the one in the catch block.

A common problem with using a "log table" is that in error conditions your log entries aren't available, as they can't be written or they get rolled back.

You can return diagnostic messages to the client, and write them to the SQL log instead.

eg:

raiserror ('completed step 1',10,1) with log;

Related Solutions

Sql-server – Handling exceptions in stored procedures called using insert-exec blocks

The error in the EXEC part of the INSERT-EXEC statement is leaving your transaction in a doomed state.

If you PRINT out XACT_STATE() in the CATCH block it is set to -1.

Not all errors will set the state to this. The following check constraint error goes through to the catch block and the INSERT succeeds.

ALTER PROCEDURE test -- or create
AS
  BEGIN try
      DECLARE @retval INT;

      DECLARE @t TABLE(x INT CHECK (x = 0))

      INSERT INTO @t
      VALUES      (1)

      SET @retval = 0;

      SELECT @retval;

      RETURN( @retval );
  END try

  BEGIN catch
      PRINT XACT_STATE()

      PRINT ERROR_MESSAGE();

      SET @retval = -1;

      SELECT @retval;

      RETURN( @retval );
  END catch;

Adding this to the CATCH block

 IF (XACT_STATE()) = -1
BEGIN
    ROLLBACK TRANSACTION;
END;

Doesn't help. It gives the error

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

I don't think there is any way of recovering from such an error once it has happened. For your specific use case you don't need INSERT ... EXEC anyway though. You can assign the return value to a scalar variable then insert that in a separate statement.

DECLARE @RC INT;

EXEC sp_executesql
  N'EXEC @RC = test',
  N'@RC INT OUTPUT',
  @RC = @RC OUTPUT;

INSERT INTO @t
VALUES      (@RC)

Or of course you could restructure the called stored procedure so that it doesn't raise that error at all.

DECLARE @RetVal INT = -1

IF OBJECT_ID('PrintMax', 'P') IS NULL
  BEGIN
      EXEC('create procedure PrintMax as begin print ''hello world'' end;')

      SET @RetVal = 0
  END

SELECT @RetVal;

RETURN( @RetVal );

Sql-server – How to get the current line number from an executing stored procedure

This question actually came up 2.5 years ago on StackOverflow, and I posted an Answer there:

SQL print line number in comment of dynamically created stored procedure?

The relevant portion of that Answer is copied below:

You can use TRY / CATCH with a forced error as the CATCH block can return the line number that the error occurred on via the ERROR_LINE() function. The full construct, formatted for readability, is:

DECLARE @Line INT = -1; -- default to an invalid line #

-- some code

BEGIN TRY
    ;THROW 50000, 'Line#', 1; -- all 3 values are arbitrary, but required
END TRY
BEGIN CATCH
    SET @LineNumber = ERROR_LINE();
END CATCH

-- some code

Now, to get the @LineNumber variable to populate with the line number that it is being set on, you can reduce that construct to a single line as follows:

BEGIN TRY;THROW 50000,'',1;END TRY BEGIN CATCH;SET @Line=ERROR_LINE();END CATCH

Please note that the THROW command started in SQL Server 2012. If you are using SQL Server 2005, 2008, or 2008 R2, then you need to use RAISERROR() function instead of THROW.

Best Answer

Related Solutions

Sql-server – Handling exceptions in stored procedures called using insert-exec blocks

Sql-server – How to get the current line number from an executing stored procedure

Related Question