The following stored procedure is called from a .NET Forms application and generally works well but we've had a few occasions where the output parameter returns a value but later when we try and update the row, the row doesn't exist.
It's almost like the transaction is being rolled back but we still receive the id. Any suggestions ? This happens randomly and up until now we've been unable to identify why it happens.
ALTER PROCEDURE [dbo].[spAdm_QueueSummaryAdd]
@fknProductId INT,
@fknProductRunId INT,
@nMailDelay INT,
@sProductRunMailServer VARCHAR(20),
@sFileName VARCHAR(100),
@sStatusCode VARCHAR(10),
@nQueueSummaryId int OUTPUT,
@sStatus NVARCHAR(MAX) OUTPUT
AS
BEGIN TRANSACTION
--check if exists
IF EXISTS
(SELECT
pknQueueSummaryId
FROM
QueueSummary
WHERE
fknProductId = @fknProductId AND
fknProductRunId = @fknProductRunId)
--already exists
BEGIN
SET @nQueueSummaryId = 0
SET @sStatus = 'pknQueueSummaryId already exists'
END
ELSE
BEGIN
--Add
INSERT INTO [QueueSummary]
(
fknProductId,
fknProductRunId,
dtDateCreated,
nMailDelay,
sProductRunMailServer,
sFileName,
nTransactions,
sStatusCode,
dtDateModified,
sModUser
)
VALUES
(
@fknProductId,
@fknProductRunId,
GETDATE(),
@nMailDelay,
@sProductRunMailServer,
@sFilename,
0,
@sStatusCode,
GETDATE(),
'Superuser'
)
SET @nQueueSummaryId = SCOPE_IDENTITY()
SET @sStatus = 'Success'
END
--error control
IF @@ERROR <> 0
BEGIN
SET @nQueueSummaryId = 0
SET @sStatus = ERROR_MESSAGE()
ROLLBACK TRANSACTION
RETURN
END
--commit transaction
COMMIT TRANSACTION
For clarity here is the table definition
CREATE TABLE [dbo].[QueueSummary](
[pknQueueSummaryId] [int] IDENTITY(1,1) NOT NULL,
[fknProductRunId] [int] NOT NULL,
[fknProductId] [int] NOT NULL,
[nMailDelay] [int] NOT NULL,
[sProductRunMailServer] [varchar](20) NOT NULL,
[sFileName] [varchar](255) NOT NULL,
[nTransactions] [int] NOT NULL,
[sStatusCode] [varchar](10) NOT NULL,
[dtDateCreated] [datetime] NOT NULL,
[dtDateModified] [datetime] NOT NULL,
[sModUser] [nchar](10) NOT NULL,
CONSTRAINT [PK_QueueSummary] PRIMARY KEY CLUSTERED
(
[pknQueueSummaryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
And here is C# code that calls the stored proc
public int Add(int fknProductId,
int fknProductRunId,
int nMailDelay,
string sProductRunMailServer,
string sFileName,
string sStatusCode)
{
//declare
SqlCommand Command = null;
SqlConnection Connection = null;
try
{
//init
Connection = new SqlConnection(_store.ConnectionString);
Command = new SqlCommand("spAdm_QueueSummaryAdd", Connection);
Command.CommandType = CommandType.StoredProcedure;
Command.CommandTimeout = _store.nCommandTimeout;
//add parameters
SqlParameter par_fknProductId = new SqlParameter("@fknProductId", SqlDbType.Int);
par_fknProductId.Direction = ParameterDirection.Input;
par_fknProductId.Value = fknProductId;
Command.Parameters.Add(par_fknProductId);
//add parameters
SqlParameter par_fknProductRunId = new SqlParameter("@fknProductRunId", SqlDbType.Int);
par_fknProductRunId.Direction = ParameterDirection.Input;
par_fknProductRunId.Value = fknProductRunId;
Command.Parameters.Add(par_fknProductRunId);
//add parameters
SqlParameter par_nMailDelay = new SqlParameter("@nMailDelay", SqlDbType.Int);
par_nMailDelay.Direction = ParameterDirection.Input;
par_nMailDelay.Value = nMailDelay;
Command.Parameters.Add(par_nMailDelay);
//add parameters
SqlParameter par_sProductRunMailServer = new SqlParameter("@sProductRunMailServer", SqlDbType.VarChar,20);
par_sProductRunMailServer.Direction = ParameterDirection.Input;
par_sProductRunMailServer.Value = sProductRunMailServer;
Command.Parameters.Add(par_sProductRunMailServer);
//add parameters
SqlParameter par_sFileName = new SqlParameter("@sFileName", SqlDbType.VarChar, 100);
par_sFileName.Direction = ParameterDirection.Input;
par_sFileName.Value = sFileName;
Command.Parameters.Add(par_sFileName);
//add parameters
SqlParameter par_sStatusCode = new SqlParameter("@sStatusCode", SqlDbType.VarChar, 10);
par_sStatusCode.Direction = ParameterDirection.Input;
par_sStatusCode.Value = sStatusCode;
Command.Parameters.Add(par_sStatusCode);
//add output parameter
SqlParameter par_nQueueSummaryId = new SqlParameter("@nQueueSummaryId", SqlDbType.Int);
par_nQueueSummaryId.Direction = ParameterDirection.Output;
Command.Parameters.Add(par_nQueueSummaryId);
SqlParameter par_sStatus = new SqlParameter("@sStatus", SqlDbType.NVarChar, -1);
par_sStatus.Direction = ParameterDirection.Output;
Command.Parameters.Add(par_sStatus);
//execute
Connection.Open();
Command.ExecuteNonQuery();
if(Command.Parameters["@sStatus"].Value.ToString() != "Success")
{
_errorHandler.LogError("edd.Admin.QueueSummary Add() Status=" + Command.Parameters["@sStatus"].Value.ToString() + " |fknProductId=" + fknProductId + " |fknProductRunId=" + fknProductRunId + " |nMailDelay=" + nMailDelay + " |sProductRunMailServer=" + sProductRunMailServer + " |sFileName=" + sFileName +" |@nQueueSummaryId=" + Command.Parameters["@nQueueSummaryId"].Value + " |sStatusCode=" + sStatusCode);
}
return Convert.ToInt32(Command.Parameters["@nQueueSummaryId"].Value);
}
catch (Exception errExp)
{
_errorHandler.LogError(errExp);
return 0;
}
finally
{
//dispose objects
if (Connection != null)
{
if (Connection.State != ConnectionState.Closed)
{
Connection.Close();
}
Connection.Dispose();
}
if (Command != null)
{
Command.Dispose();
}
}
}
