T-SQL Stored Procedure Returns Before Completing

Question

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

Answer 1

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;

Answer 2

It looks like you're getting an error on Query_B, which is causing it to completely exit the try/catch block.

I'd recommend that you separate each of your execute Query_# into separate try/catch blocks with log table insertion before each execution. I'd also suggest making each one an explicit transaction so that you don't end up with your data in an odd state because something unexpected happened with one of the blocks.

I've encountered this kind of problem before. It was not easy to track down because of the try catch block around more than execution statement.

Answer 3

I figured out the problem!

The Remote query timeout on Server_A was set to 600 seconds (10 minutes). SProc_B takes longer to run, so it timed out and SProc_A continued about its business. That's why SProc_B just appeared to return without completing. On Server_X, Remote query timeout was set to 0, which means no timeout, which is why it didn't have this problem.

I resolved the issue by setting the Remote query timeout on Server_A to 0. Now Query_A runs correctly.

What I don't understand is why the timeout in SProc_B didn't throw an error inside SProc_A.

Thank you all for your help and suggestions.

-Robbie