@@ROWCOUNT issue

Question

Hi there,

I had the below code and it worked fine in counting the records inserted and logging insert counts.

CREATE PROCEDURE ProcessCustomer

AS

BEGIN

    DECLARE @RowsInserted INT;

    -- Insert into the final table and capture row count

    INSERT INTO dim.customer (col1, col2, col3)

    SELECT col1, col2, col3 FROM [staging].[Customer];

    -- Capture row count directly after the INSERT

    SET @RowsInserted = @@ROWCOUNT;

    -- Log row count

    INSERT INTO logging (RowsInserted) VALUES (@RowsInserted);

END

However lately i had to use a temp table to do a join before INSERT and from then on im getting errors. But target table is getting populated as expected but SP errors out in SSMS and insert counts are not logged. Any suggestions to get the count of inserts?

Msg 4083, Level 16, State 1, Procedure ProcessCustomer, Line 164 [Batch Start Line 0]

The connection was recovered and rowcount in the first query is not available. Please execute another query to get a valid rowcount.

The below is code with temp table created and then the temp table is used in the join.

CREATE PROCEDURE ProcessCustomer

AS

BEGIN

    DECLARE @RowsInserted INT;
	SELECT DISTINCT C1,C2,C3 into #temptbl FROM tableA     

	-- Insert into the final table and capture row count      
	INSERT INTO dim.customer (col1, col2, col3)      
	SELECT col1, col2, col3 FROM [staging].[Customer] c
	JOIN #temptbl t on c.col1=t.c1
	;      
	-- Capture row count directly after the INSERT      
	SET @RowsInserted = @@ROWCOUNT;

    -- Log row count

    INSERT INTO logging (RowsInserted) VALUES (@RowsInserted);

END

Answer 1

This is related to something known as Connection Resiliency. The link is specifically for ODBC, but connection resiliency is implemented in all client APIs from Microsoft. The page includes a link to a technical article about the feature.

In short, what it is all about is that if your connection is cut for whatever reason, the API attempts to reconnect and continue as nothing has happened. This is an important feature in the days of the cloud, since intermittent failures are common. They could be due to glitches in the network connection. But it could also be due to failovers of your Azure SQL Database from one computer to another in the Azure Data Centre because of maintenance.

While connection resiliency saves you from some of the problems this can cause, it does not cover everything. There are situations when reconnecting and pretending as nothing has happened is not the right thing, for instance if the connection was severed in the middle of a transaction.

And the retrieval of @@rowcount also falls into this category. Say that you have a script that goes:

INSERT tbl(...)
   SELECT ....
go
IF @@rowcount > 0

Furthermore say that the network connection drops between the batches. The check on @@rowcount would not be reliable. For some reason, they made the rule so that any retrieval of @@rowcount in the first batch is considered unreliable, although in your case there should not be an issue.

While this may be considered a flaw in SQL Server, there is a simple remedy: Wrap the operations in BEGIN TRANSACTION and COMMIT TRANSACTION, so that either both the rows and the rowcount are inserted or none of them. The error will still be there, but that is just what can happen in the cloud.

For application code, you should have your own retry code that backtracks and reconnects and hides the problem for the user as much as possible.