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 ) 

Changes to server configuration option remote access are not supported in SQL Database Managed Instances

Having just set up our new SQL Server Managed Instance, restored a sample database for testing, and run Azure’s vulnerability assessment, it produces this high risk finding:

VA2120 – Features that may affect security should be disabled

The more SQL Server features and services you enable, the larger its surface attack area becomes, making your system more vulnerable to potential attacks. These fetures should be disabled unless it is absolutely needed in this environment.

Remediation Script:

EXECUTE sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE; EXECUTE sp_configure 'remote access', 0; RECONFIGURE; EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE; 

Turning to Google before doing anything, I found this Microsoft Docs article stating that (emphasis mine):

This topic is about the "Remote Access" feature. This configuration option is an obscure SQL Server to SQL Server communication feature that is deprecated, and you probably shouldn’t be using it.

Can anyone therefore please provide some clarity on the following?

  1. Why is it enabled given Microsoft’s description?
  2. Does it need to be enabled in Azure SQLMI? Because…
  3. When I run the remediation script I get this error:

Changes to server configuration option remote access are not supported in SQL Database Managed Instances.

How to migrate a SQL Server Erwin Mart to database to Aurora (Amazon RDS)

I want to migrate a SQL Server Erwin data Mart database to Aurora and trying to figure out what the easiest/quickest way to do that is.

Options to me seem to be:

  1. Saving models to the file system, repoint the application to the new mart database, then loading from the file system to the new database. https://support.erwin.com/hc/en-us/articles/360003443452-Java-scripts-that-automatically-save-a-mart-s-models-offline-to-a-drive [support.erwin.com] https://support.erwin.com/hc/en-us/articles/115002674131-ERWIN-DATA-MODELER-MART-API-RESOURCE-PAGE [support.erwin.com]

Has anyone got any experience using these apis?

  1. Export/Import. Mysql migration tool. https://www.mysql.com/products/workbench/migrate/
    Amazon migration tool Does anyone know if the schema is the same, can I simply export/import the data?

How do I delete a user from the master database when the user has been deleted from the Server Logins using the SSMS Object Explorer

I am using an Azure SQL Database. I deleted some users from a Azure SQL database after I saw them mentioned in a Vulnerability Assessment report. Here’s how I deleted them:

  1. Log in to SSMS
  2. Expand the Object Explorer tree
  3. Expand the ‘Security’ folder
  4. Expand the ‘Logins’ folder
  5. Highlight the user
  6. Right-click and select ‘Delete’

I went back to the Vulnerability Assessment blade and ran a new ‘Scan’ but the users I deleted still show up in the list. The list included SQL code to show that my users still exist. I ran that code back in SSMS in the master database and confirmed my users still exist. Here’s the relevant code:

SELECT * FROM sys.database_principals

When I run the following code, I get an error saying the user does not exist (‘or you do not have permission’ – but I am a server admin so I ruled that out):

DROP LOGIN <username>

How do I get rid of these logins?

50% OFF, $6/year, cPanel Shared Hosting, Fast Server, 24/7 Support, 99.9% Uptime

We are excited to offer the following affordable shared hosting plan from Hostpoco.com. Sign-up for any of our cPanel web hosting plans and get 50% off for the life of your account with a Free Domain name. If you worry about anything when trying our service, we offer full refund within 30 days of service for customers.

50% OFF all shared hosting plans for the life of your hosting account when you choose the annually billing term.
Use Discount Code: HP50is good for new clients, however, limit 1 use per customer, and promo can not be used to replace existing

Our Shared Hosting Plans features:
– Unlimited Bandwidth
– 100% CPU
– Unlimited Email Accounts
– Unlimited Sub Domains
– Unlimited Parked Domains
– Unlimited FTP Accounts
– Unlimited MySql Databases
– Unlimited Mailing List
– FREE CloudFlare
– FREE SiteApps
– FREE Online Sitebuilder
– FREE Data Migration
– FREE Attracta
– FREE Setup
– FREE Cpanel Control Panel
– FREE Auto Responders
– FREE Backup Restore
– FREE PhpMyAdmin
– FREE Virus Scanner
– DDOS Protection
– 99.99% uptime
– Softacolous Supported
– Shell Access Available

Detailed Plan Info: https://www.hostpoco.com/one-dollar-cheap-ssd-hosting.php

For more web hosting plans, please visit our website: https://www.hostpoco.com/
Should you have any questions, email us: sales@hostpoco.com

SQL Server Always On AG – Is a Passive Secondary Required for Automatic Failover?

It is my understanding that for automatic failover to occur in an Always On AG environment, the replicas just need to be configured with "automatic failover," "synchronous commit" and the environment must have proper quorum (i.e. odd number of voting members).

However, according to the "SQL Server 2019 Always On" textbook by Peter A. Carter, for automatic failover to work, the secondary cannot be active and must be passive. Passive meaning the Readable Secondary setting is set to ‘No’ so it is unavailable for direct connections or read access, and obviously no backups can be done on them either.

Here is the exact statement from the textbook:

"Although we can change the value of Readable Secondary through the GUI while at the same time configuring a replica for automatic failover without error, this is simply a quirk of the wizard. In fact, the replica is not accessible since active secondaries are not supported when configured for automatic failover."

Here’s a screenshot of the "quirk"

enter image description here

Note: According to the textbox, the Readonly Secondary should be set to ‘No’ if you want automatic failover to work. Thinking back, I don’t recall a successful automatic failover occurring with this set up and I believe when an issue occurred, the AG was in a RESOLVING state. Note, we have third server for proper quorum.

I’ve searched through Microsoft’s documentation to confirm whether or not the author’s statement is true but have yet to find anything that explicitly states that the secondary replica must be passive for automatic failover to work. This Microsoft article (Failover and Failover Modes (Always On Availability Groups)) states the conditions required for automatic failover but does not mention needing a passive secondary replica.

So is the author correct and Microsoft has failed to advertise this OR is the author incorrect and automatic failover does work when the secondary replica is active/readable? My objective is to have a highly available environment with automatic failover but do we need to sacrifice offloading read operations in order to accomplish this? Adding another server is not an option.

Using Dynamic Data Masking in SQL Server Custom String

I need to mask the data in the following table. enter image description here

For example, I need to mask the first_name of the user Roberto to something like NAME<>, So the end result should look like NAME1

Is this possible in SQL Server?

My SQL Server details below.

enter image description here

I have tried these methods from the documentation but still no luck. enter link description here