2

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

  • I would start by adding SET NOCOUNT ON; to the beginning of the proc to suppress the DONE_IN_PROC (row count) messages that can confuse app code that expect them. – Dan Guzman Sep 14 at 10:08
  • Your problem might be how you are calling the queries using EXEC. Have you tried EXEC (@Query) instead of EXEC @Query. sqlfiddle.com/#!18/3282d/2 – Chessbrain Sep 14 at 17:49
  • You might be interested in reading this – mustaccio Sep 14 at 18:54
  • Are your Query_2 and Query_3 procedures dependent on any data returned from the previous procedures? – Adam K. Sep 15 at 2:26
  • 1
    Welcome to DBA.SE and thanks for your participation. You're going to have to be a bit more specific about the "We call a stored procedure on one ..." part of your question. E.g. If We is a SQL Server Agent job that doesn't start, then you'll have to debug the SQL Server Agent. However, if We is a bit of program code that starts the procedure, then start there. Currently your question doesn't contain enough information and might be closed as unclear what you're asking. – John aka hot2use Sep 16 at 5:48
3

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;
  • Thank you @David, I will try that next. – Robbie Sep 16 at 16:38
0

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.