Sproc is returning output parameter inconsistently

• MV40304

  SSC Enthusiast

  Points: 143

  More actions

  March 6, 2015 at 6:25 am

  #398136

  Hi

  Firstly may I say that the sproc I am having problems with and the service that calls it is inherited technical debt from an unsupervised contractor. We are not able to go through a rewriting process at the moment so need to live with this if possible.

  Background

  We have a service written in c# that is processing packages of xml that contain up to 100 elements of goods consignment data.

  In amongst that element is an identifier for each consignment. This is nvarchar(22) in our table.

  I have not observed any IDs that are different in length in the XML element.

  The service picks up these packages from MSMQ, extracts the data using XPATH and passes the ID into the SPROC in question. This searches for the ID in one of our tables and returns a bool to the service indicating whether it was found or not. If found then we add a new row to another table. If not found then it ignores and continues processing.

  Observations

  The service seems to be dealing with a top end of around 10 messages a minute... so a max of about 1000 calls to the SPROC per minute. Multi-threading has been used to process these packages but as I am assured, sprocs are threadsafe.

  It is completing the calls without issue but intermittently it will return FALSE. For these IDs I am observing that they exist on the table mostly (there are the odd exceptions where they are legitimately missing).

  e.g Yesterday I was watching the logs and on seeing a message saying that an ID had not been found I checked the database and could see that the ID had been entered a day earlier according to an Entered Timestamp.

  So the Sproc...

  USE [xxxxxxxxxx]

  GO

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE PROCEDURE [dbo].[xxxxxxxxxxx]

  @pIdentifier nvarchar(25),

  @PackageMessageID nvarchar(25) = 0,

  @bFound bit = 0 output

  AS

  DECLARE @errMessage nvarchar(255)

  SET @errMessage = 'Invalid Identifier on PackageMessageID ' + @PackageMessageID

  Set nocount on ;

  SET @bFound = 0;

  Select Top(1) @bFound = 1

  from

  xxxxxxxxxxx.dbo.xxxxxxxxxxxxxx

  where Identifier = @pIdentifier

  If @bFound = 0

  INSERT INTO [xxxxxxxxxxxxxx].[dbo].MigrationExceptions

  (batchNo, identifier, errorTime, stage, errorMessage)

  VALUES

  (NEWID(), @pIdentifier, GETDATE(), 'IMPORT DATA xxxxxxxxx', @errMessage)

  RETURN @bFound

  GO

  And is being called from the following C# code.....

  cmd.CommandText = storedProcedureName;

  cmd.CommandType = CommandType.StoredProcedure;

  cmd.Connection = sqlConnection;

  cmd.Parameters.AddWithValue("@pIdentifier", consignment.Identifier);

  cmd.Parameters.AddWithValue("@PackageMessageID", consignment.ID);

  var result = cmd.Parameters.AddWithValue("@bFound", consignmentExist);

  result.Direction = ParameterDirection.InputOutput;

  cmd.ExecuteNonQuery();

  So on occasions (about 0.33% of the time) it is failing to get a bit 1 setting in @bFound after the SELECT TOP(1).

  The only suggestions I can make have been...

• change @pIdentifier nvarchar(25) to nvarchar(22)
• Trim any potential blanks from either side of both parts of the identifier comparison
• Change the SELECT TOP(1) to an EXISTS

The only other thought is the two way parameter direction in the C# for the result OUTPUT.

Not sure why he did it that way or what the purpose is.

I have been unable to replicate this using a test app and our test databases.

Has anyone observed selects failing to find even though the data is there, like this before?

Any thoughts would be most appreciated as everyone here is mystified and the 'tractor can't be asked as he has had it away on his toes!!