none
ExecuteNonQuery not executing stored procedure or not giving back a value for an output parameter RRS feed

  • Question

  • I am working on a transaction heavy system that shuffle messages from telematic units to a database and, in the end, to user applications through web services. A number of threads (10) read messages from a MSMQ queue, transforms them and stores them in a SQL Server 2005 (SP3) database. The entire flow is transactional through TransactionScope which is created before a message is read from the MSMQ queue and committed when the transformed message has successfully been stored in the database. One single database is accessed several times in the processing/transaction before a message is finally stored. We are running on .NET 2.0, developing using Visual Studio 2008 SP1 and using Enterprise Library 3.1 in the data access layer.

    During load testing we have discovered a problem that we are unable to resolve. We have spent several days banging our heads against it using Visual Studio debugging and SQL Server profiling but we are at a loss. Not only is the problem wierd but we are unable to reproduce it within deterministic time or in a deterministic manner.

    The problem is that sometimes ExecuteNonQuery in the code above does not give an expected output parameter as a bool but instead the value is null.

                // Access the database, set by configuration
                Database db = DatabaseFactory.CreateDatabase(Scpv2Constants.ConnectionStringNames.CommunicationService);
    
                // Use the stored procedure add the incoming payload to storage.    
                using (DbCommand storedProcedureCommand = db.GetStoredProcCommand(Scpv2Constants.StoredProcedureNames.IsInSessionHistoryCommand))
                {
                    // Add the telephone number in parameter to the stored procedure
                    db.AddInParameter(storedProcedureCommand, "@telephoneNumber", DbType.String, unitKey.TelephoneNumber);
                    // Add the session number in parameter to the stored procedure
                    db.AddInParameter(storedProcedureCommand, "@sessionNumber", DbType.Int16, sessionNumber);
                    // Add the number of already finished sessions out parameter to the stored procedure
                    db.AddOutParameter(storedProcedureCommand, "@isInHistory", DbType.Boolean, 1);
    
                    db.ExecuteNonQuery(storedProcedureCommand);
    
                    bool isInHistory = (bool)db.GetParameterValue(storedProcedureCommand, "@isInHistory");
    
                    return isInHistory;
                }


    The stored procedure that is executed is rather simple and look like this

    ALTER PROCEDURE [Scpv2MODatabaseSessionStorage].[IsInHistory]
        @telephoneNumber varchar(20),
        @sessionNumber smallint,
        @isInHistory bit out
    AS
    BEGIN
        SET NOCOUNT ON;
    
        -- Check if there is any finished session for this sessionNumber.
        SELECT @isInHistory = COUNT(*)
        FROM Scpv2MODatabaseSessionStorage.History
        WHERE TelephoneNumber = @telephoneNumber
        AND SessionNumber = @SessionNumber
           
    END


    The primary key for table Scpv2MODatabaseSessionStorage.History is
    ALTER TABLE [Scpv2MODatabaseSessionStorage].[History] ADD  CONSTRAINT [PK_Scpv2SessionHistory] PRIMARY KEY CLUSTERED
    (
        [TelephoneNumber] ASC,
        [SessionNumber] ASC
    )WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]


    The way the problem is noticed is that the data access method executing the below code gives a NullReferenceException on the line

    bool isInHistory = (bool)db.GetParameterValue(storedProcedureCommand, "@isInHistory");

    The problem is not limited to the code posted above but are present in other methods and stored procedures as well. The common denominator is that all methods that have the problem are used in the first database access after the transaction is started and the message is read from the MSMQ queue. We have tried to use "pure" ADO.NET code also but the problem is the same. The load test statistics says that the problem will occur with 0.007 % of all messages but we are processing a large amount of messaged (up to 70 per second) and cannot afford to loose any through bugs or bad coding.

    We are not experts at SQL Server 2005 profiling but it seems that the stored procedure is not executed when the problem occurs. One would think that ExecuteNonQuery would raise an exception in this case through but it does not.

    Another thing we have noticed through Visual Studio debugging is that when the problem occurs many threads (at least 5 of 10) are excuting ExecuteNonQuery in other methods having the problem or in the actual method raising the exception. There is A LOT of other stuff those threads could be doing. It is either a wierd (but repeatable) concidence or a factor in the problem.

    Any ideas would be greatly appriciated! Please forgive any grammatical or spelling errors as English is not my native tounge.
    • Edited by znipah Wednesday, May 27, 2009 10:57 PM Formatting again
    Wednesday, May 27, 2009 10:20 PM

All replies

  • Checkout the stored procedure returns in sql server directly not in net what it return by putting the input values hard coded.
    I think record is not successfully executed by any reason.
    AND use BEGIN TRY and END TRY with BEGIN CATCH with END CATCH.
    try to check out the display value at sql. I will give any suggestion after that.

    PK
    Thursday, May 28, 2009 7:47 AM
  • When executing the stored procedure directly with the same input parameter values and base data as when an error occurs it gives the expected and correct output parameter.

    We have tried to use exception handling in the stored procedure but the stored procedure is never actually executed when the error occurs.

    During the night we tested to turn off ADO.NET pooling in the connection string and run load tests and have not been able to reproduce the error. There is an unacceptable performance hit however so the problem is not solved. The pooling in Enterprise Library is still active.

    Thursday, May 28, 2009 9:23 AM
  • The one thing I've noticed is that you're using a bit data type as your output data type, although your SELECT statement in the stored procedure could possibly return values other than 0 or 1. Is there any reason why you're not using an int instead?


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, May 28, 2009 2:28 PM
  • It seemed more natural with a bit since we are interested in a boolean value. The primary key only makes 0 or 1 a possibility in that particular query. However, we tried with an int just to make sure without any success.

    We have some deadlock situations in some stored procedures. Explicitly clearing the ADO.NET connection pool when a deadlock occurs solves the problem without any noticable performance loss. While the problem is solved I am still very curious as to why ExecuteNonQuery ignores executing a stored procedure without raising an exception and how it is related to a potentially correupted connection pool in ADO.NET...... 
    Thursday, May 28, 2009 5:52 PM
  • Hi there

    Only had a quick look, but it seems that the parameter direction is not set to Output.
    The default direction for a stored procedure parameter is Input.

    In your case you are problaby missing something like:

    cmd.Parameters.Add("@isInHistory", System.Data.SqlDbType.Boolean).Direction = System.Data.ParameterDirection.Output;

    if this is not set, then when you try to access the parameter.value, you will get a NULL back.

    More information here:

    Configuring Parameters (ADO.NET)
    http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx

    HTH
    //Michael


    This posting is provided "AS IS" with no warranties.
    Friday, May 29, 2009 9:40 AM
  • It is set to Output. Ent Lib takes care of that

    db.AddOutParameter(storedProcedureCommand, "@isInHistory", DbType.Boolean, 1);
    Otherwise the execution would always fail which is not the case at all.

    Friday, May 29, 2009 9:50 AM