SQL Server UPDATE Command Fails without error

Using SSMS running the below UPDATE command I get returned message says 1 row affected. but if I then follow it up with a Select and it shows that the row never updated.

This issue happens in SSMS and a custom application I built in C# using Entity Framework. I have since stopped using Entity Framework generated code and moved straight to a stored proc. All of 3 different ways of performing the same task I randomly get this issue. Sometimes it can be 100’s or 1000’s of updates before the issue happens again or it could be a few updates later it happens again. I have run UPDATE STATISTICS, I have Rebuilt Indexes and Reorganized Indexes.

As a side note, I NEVER have an issue with INSERTs only UPDATEs we never DELETE any records. and total record count on the table is 201,741

UPDATE CreativeWorkFlowQueue     SET CurrentIndicator = 'N' WHERE CreativeWorkFlowQueueId = 198231  SELECT * FROM CreativeWorkFlowQueue WHERE CreativeWorkFlowQueueId = 198231 

enter image description here

here is the table

SET ANSI_NULLS ON GO  SET QUOTED_IDENTIFIER ON GO  CREATE TABLE [dbo].[CreativeWorkFlowQueue](     [CreativeWorkFlowQueueId] [int] IDENTITY(1,1) NOT NULL,     [DigitalCampaignCreativeId] [int] NOT NULL,     [WorkFlowQueueId] [int] NOT NULL,     [CurrentIndicator] [char](1) NOT NULL,     [CreateDate] [datetime] NOT NULL,     [LastUpdatedDate] [datetime] NOT NULL,     [IsDeleted] [bit] NOT NULL, PRIMARY KEY CLUSTERED  (     [CreativeWorkFlowQueueId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO  ALTER TABLE [dbo].[CreativeWorkFlowQueue] ADD  CONSTRAINT [CreateDate defaults to utcDate on CreativeWorkFlowQueue]  DEFAULT (getutcdate()) FOR [CreateDate] GO  ALTER TABLE [dbo].[CreativeWorkFlowQueue] ADD  CONSTRAINT [LastUpdatedDate defaults to utcDate on CreativeWorkFlowQueue]  DEFAULT (getutcdate()) FOR [LastUpdatedDate] GO  ALTER TABLE [dbo].[CreativeWorkFlowQueue] ADD  CONSTRAINT [DF_CreativeWorkFlowQueue_IsDeleted]  DEFAULT ((0)) FOR [IsDeleted] GO  ALTER TABLE [dbo].[CreativeWorkFlowQueue]  WITH CHECK ADD  CONSTRAINT [CreativeWorkFlowQueue DigitalCampaignCreativeId fk] FOREIGN KEY([DigitalCampaignCreativeId]) REFERENCES [dbo].[DigitalCampaignCreative] ([DigitalCampaignCreativeId]) GO  ALTER TABLE [dbo].[CreativeWorkFlowQueue] CHECK CONSTRAINT [CreativeWorkFlowQueue DigitalCampaignCreativeId fk] GO  ALTER TABLE [dbo].[CreativeWorkFlowQueue]  WITH CHECK ADD  CONSTRAINT [CreativeWorkFlowQueue WorkFlowQueueId fk] FOREIGN KEY([WorkFlowQueueId]) REFERENCES [dbo].[WorkFlowQueue] ([WorkFlowQueueId]) GO  ALTER TABLE [dbo].[CreativeWorkFlowQueue] CHECK CONSTRAINT [CreativeWorkFlowQueue WorkFlowQueueId fk] GO 

EDIT Adding the Stored Proc that also has the same issue.

I did not include this stored proc simply due to the fact that it is complex and if a simple update doesn’t work I figured start there first.

CREATE PROCEDURE [dbo].[sp_WtvToRtl]      -- Add the parameters for the stored procedure here     @tbl DCCIdList READONLY,         @WorkFlowQueueId int AS BEGIN     -- SET NOCOUNT ON added to prevent extra result sets from     -- interfering with SELECT statements.     SET NOCOUNT ON;           DECLARE @dccList TABLE (         CreativeWorkflowQueueId int null,         DigitalCampaignCreativeId int null,         WorkFlowQueueId int null,         CurrentIndicator varchar(1) collate SQL_Latin1_General_CP1_CI_AS null,         LastUpdatedDate datetime null     )      DECLARE @DCIDList TABLE(         DigitalCampaignCreativeId int null     )              DECLARE @res TABLE (         MergeAction VARCHAR(50) collate SQL_Latin1_General_CP1_CI_AS ,         CreativeWorkFlowQueueId INT,          _Position INT         )      BEGIN TRANSACTION t1      BEGIN TRY         INSERT INTO @dccList (CreativeWorkflowQueueId, DigitalCampaignCreativeId,LastUpdatedDate,WorkFlowQueueId,CurrentIndicator)         select CreativeWorkflowQueueId, DigitalCampaignCreativeId,LastUpdatedDate,WorkFlowQueueId, 'N' as CurrentIndicator from          dbo.CreativeWorkFlowQueue Where CurrentIndicator = 'Y'          and IsDeleted = 0         and DigitalCampaignCreativeId In          (SELECT DigitalCampaignCreativeId FROM VRF.DigitalCampaignCreative          WHERE IsDeleted = 0 and SfClosed = 0          and DccId in (Select DccId COLLATE SQL_Latin1_General_CP1_CI_AS from @tbl)         )                    INSERT INTO @DCIDList           SELECT DigitalCampaignCreativeId FROM @dccList          GROUP BY DigitalCampaignCreativeId          INSERT INTO @dccList (DigitalCampaignCreativeId,WorkFlowQueueId,CurrentIndicator)         select DigitalCampaignCreativeId, @WorkFlowQueueId as WorkFlowQueueId, 'Y' as CurrentIndicator from          @DCIDList           MERGE vrf.CreativeWorkFlowQueue a         USING              (             SELECT                  x.CreativeWorkflowQueueId,                 x.DigitalCampaignCreativeId,                 x.LastUpdatedDate,                 x.WorkFlowQueueId,                 x.CurrentIndicator              FROM @dccList x             ) stg                ON stg.CreativeWorkflowQueueId = a.CreativeWorkflowQueueId          WHEN MATCHED THEN             UPDATE SET                  a.CurrentIndicator  = stg.CurrentIndicator,                 a.LastUpdatedDate = getutcdate()         WHEN NOT MATCHED THEN             INSERT                  (                     CurrentIndicator,                     DigitalCampaignCreativeId,                     WorkFlowQueueId                 )             VALUES                  (                     stg.CurrentIndicator,                      stg.DigitalCampaignCreativeId,                      stg.WorkFlowQueueId                 )               -- ********************************************************************             -- Remove this if you don't need to return the results below.             OUTPUT                  INSERTED.CreativeWorkFlowQueueId,                 $  action              INTO @res (CreativeWorkFlowQueueId, MergeAction);             -- ********************************************************************           COMMIT TRANSACTION t1      END TRY     BEGIN CATCH          DECLARE @ErrorMessage NVARCHAR(MAX), @ErrorSeverity INT, @ErrorState INT;         SELECT @ErrorMessage = ERROR_MESSAGE() + ' Line ' + CAST(ERROR_LINE() AS NVARCHAR(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();         ROLLBACK TRANSACTION t1         --RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);         SELECT 'FAILED' MergeAction,                 s.CreativeWorkflowQueueId,                 s.DigitalCampaignCreativeId,                 '' DccId,                 s.WorkFlowQueueId,                 s.CurrentIndicator,                 null CreateDate,                 null LastUpdatedDate,                 null IsDeleted,                 @ErrorMessage ErrorMessage         FROM @dccList s     END CATCH       -- Normally, I would not have this.  But this will simulate     -- what EF does if you need it.     SELECT          r.MergeAction,         a.CreativeWorkFlowQueueId,         a.DigitalCampaignCreativeId,         d.DccId,         a.WorkFlowQueueId,         a.CurrentIndicator,         a.CreateDate,          a.LastUpdatedDate,         a.IsDeleted,         '' ErrorMessage     FROM vrf.CreativeWorkFlowQueue a      JOIN @res r ON a.CreativeWorkFlowQueueId = r.CreativeWorkFlowQueueId     JOIN vrf.DigitalCampaignCreative d ON a.DigitalCampaignCreativeId = d.DigitalCampaignCreativeId     ORDER BY          r.MergeAction,         r._Position   END 

The User-Defined Table Type is this.

CREATE TYPE [dbo].[DCCIdList] AS TABLE(     [DccId] [nvarchar](18) NULL )