SQL Server 2012 – SELECT COUNT(*) Returns No Data

csql serversql-server-2012

I have a query, that as far as I know has failed exactly one time. It's a simple select count(*) from one table, no joins. But at least this once, executing that query resulted in no data read from SqlDataReader. Not even null, just nothing. First call to Read returns false. No exception was raised.

Has any one ever heard of that before? Any scenarios you can think of that would cause it?

I'm not even sure what to ask for to look at beyond SQL server logs. It's not something we can duplicate. I am assuming I'll have to chalk it up to a fluke and move on if/until it becomes a chronic problem. Here's a similar query:

SELECT ISNULL(COUNT(*),0)
FROM dbo.TRANSACTIONS T
WHERE (@TransactionId IS NULL OR (T.TransactionId != @TransactionId))
AND T.ParentId = @ParentId
AND (T.TransactionStatus != 4)

Is there a class of error that will thrown an exception out of the query, but not raise it to the application when executing a command or reading from the resulting SqlDataReader?

UPDATE: Here's the code executing this.


using (SqlConnection conn = /*connection created and opened here*/)
{
    SqlCommand cmd = new SqlCommand("usp_GetChildTransactions");
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@transactionId", SqlDbType.BigInt).Value = transactionId > 0 ? transactionId : (object)DBNull.Value;
    cmd.Parameters.Add("@parentId", SqlDbType.Int).Value = parentId;

    using (SqlDataReader rdr = conn.ExecuteReader(cmd))
    {
        rdr.Read();
        itemCount = rdr.IsDBNull(0) ? 0 : rdr.GetInt32(0); //exception thrown here because there is no data
    }
}

Update 2: I was hoping to not do this, but maybe the exception handling is part of the problem. Here is the create procedure for this, and also another stored procedure being called in the exception handler. Sorry for the length:


CREATE PROCEDURE dbo.usp_GetChildTransactions (
    @transactionId              bigint,
    @parentId                   int,
AS
SET NOCOUNT ON 


BEGIN TRY

    SELECT ISNULL(COUNT(*),0)
    FROM dbo.TRANSACTIONS T
    WHERE (@TransactionId IS NULL OR (T.TransactionId != @TransactionId))
    AND T.ParentId = @ParentId
    AND (T.TransactionStatus != 4)

END TRY
BEGIN CATCH 
    EXEC usp_RethrowException
END CATCH 



CREATE PROCEDURE dbo.usp_RethrowException
AS

SET NOCOUNT ON 

DECLARE @ErrorMessage    NVARCHAR(4000),
       @ErrorNumber     INT,
       @ErrorSeverity   INT,
       @ErrorState      INT,
       @ErrorLine       INT,
       @ErrorProcedure  NVARCHAR(200)

SELECT @ErrorNumber = ERROR_NUMBER(),
       @ErrorSeverity = ERROR_SEVERITY(),
       @ErrorState = ERROR_STATE(),
       @ErrorLine = ERROR_LINE(),
       @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')

if (@ErrorState = 0) set @ErrorState = 1


SELECT @ErrorMessage = ERROR_MESSAGE()

IF (LEFT(@ErrorMessage, 15) = N'( Error Number=')
BEGIN

    RAISERROR 
          (@ErrorMessage,
           @ErrorSeverity,
           @ErrorState)
END
ELSE
BEGIN
    -- First time - Create the message with all error information in a standard string format
    SELECT @ErrorMessage = N'( Error Number=%d, Severity=%d, State=%d, Procedure=%s, Line=%d )' + CHAR(13) + @ErrorMessage
    RAISERROR 
          (@ErrorMessage,
           @ErrorSeverity,
           @ErrorState,
           @ErrorNumber,
           @ErrorSeverity,      
           @ErrorState,
           @ErrorProcedure,
           @ErrorLine)
END

P.S. I did edit this a little, changing names of the procedures, the name of the table only, and removed comments only.

Best Answer

Is there a class of error that will thrown an exception out of the query, but not raise it to the application when executing a command or reading from the resulting SqlDataReader?

If the query errs in the scope of T-SQL structured error handling, the empty or partial result set is returned to the client followed by the error message in the TDS protocol stream. The implications are that client code should not assume SqlDataReader always returns true and that all rows and result sets must be consumed before the exception is raised in the client code. Consider the following example:

USE tempdb;
GO
CREATE PROCEDURE dbo.usp_ExampleError
AS
SET NOCOUNT ON;

BEGIN TRY

    SELECT COUNT(*)
    FROM (VALUES('NotAnInteger')) AS Example(BadData)
    WHERE BadData = 0;

END TRY
BEGIN CATCH 
    THROW;
END CATCH 
GO


using (var connection = new SqlConnection(@"Data Source=.;Initial Catalog=tempdb;Integrated Security=SSPI"))
using (var command = new SqlCommand("dbo.usp_ExampleError", connection))
{
    command.CommandType = CommandType.StoredProcedure;
    connection.Open();
    using (var reader = command.ExecuteReader())
    {
        Console.WriteLine(reader.Read()); //returns false
        Console.WriteLine(reader.NextResult()); //raises the conversion error
    }
}

Note that ExecuteNonQuery implicitly reads and ignores all rows and result sets (which will raise an exception if the batch erred) but ExecuteScaler and ExecuteReader do not. Also, DataAdaper.Fill consumes all results with a DataSet target overload but not with the DataTable overload,

This is one reason I suggest one generally avoid handling errors in T-SQL unless it provides value and specify SET XACT_ABORT ON to avoid continuing after errors and leaving transactions open.

Related Question