2

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.

  • Without seeing your code, it's pretty hard to tell if there are any errors that might not be caught. – Max Vernon Feb 10 '17 at 18:42
  • One thing I can tell you for certain, SQL Server will never ever return no result set, if rows exist in the table that match the WHERE clause requirements.. – Max Vernon Feb 10 '17 at 18:43
  • 2
    This is a scalar aggregate - it should always return exactly one row even if the source table was empty. COUNT(*) can never be NULL by the way so the ISNULL is pointless. You say " Here's a similar query" does the actual query have any GROUP BY? – Martin Smith Feb 10 '17 at 18:44
  • Also very unlikely but does your code make use of set parseonly on or noexec or similar? – Martin Smith Feb 10 '17 at 18:50
  • Yeah I didn't add that ISNULL, but it's in the real query so I put it here. i'm not sure why someone would have done that. It made me wonder if this has happened before, and this was someone's misguided attempt at fixing it.. – Dean Feb 10 '17 at 18:55
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?

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.

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.