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
- 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