Optimistic concurrency check failure when updating linked server table with timestamp column

Question

I’m trying to update a table in a database on a linked server using code like this:

declare @FirstName nvarchar(30);
declare @LastName nvarchar(50);
declare @AccountNumber nvarchar(30);
set @FirstName = N'John';
set @LastName = N'Smith';
set @AccountNumber = N'ABC001';
update RemoteServer.MyDB.dbo.Customer
set FirstName = @FirstName,
 LastName = @LastName
where AccountNumber = @AccountNumber;

The remote server is also a SQL Server instance and is defined thus:

EXEC master.dbo.sp_addlinkedserver @server = N'RemoteServer'
 , @srvproduct=N'SQL Server'
 /* For security reasons the linked server remote logins password is
 changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'RemoteServer'
 ,@useself=N'False'
 ,@locallogin=NULL
 ,@rmtuser=N'sa'
 ,@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
 , @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
 , @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
 , @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
 , @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
 , @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
 , @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
 , @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
 , @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
 , @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
 , @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
 , @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
 , @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
 , @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

The Customer table is defined as

CREATE TABLE [dbo].[Customer](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [AccountNumber] [nvarchar](20) NOT NULL,
 [FirstName] [nvarchar](30) NOT NULL,
 [LastName] [nvarchar](50) NOT NULL,
 [DBTimeStamp] [timestamp] NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY NONCLUSTERED 
(
 [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
 , IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON
 , ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

and has an index on the timestamp column

CREATE UNIQUE CLUSTERED INDEX [IX_Customer_TimeStamp] ON [dbo].[Customer]
(
 [DBTimeStamp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
 , SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF
 , ONLINE = OFF, ALLOW_ROW_LOCKS = ON
 , ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

When I run the query I get this error message:

The OLE DB provider "SQLNCLI11" for linked server "RemoteServer" could not UPDATE
 table "[RemoteServer].[MyDB].[dbo].[Customer]".
The rowset was using optimistic concurrency and the value of a column has been
 changed after the containing row was last fetched or resynchronized.

If I remove the index on the timestamp column, the query works as I’d expect.

How can I force the linked server query to use pessimistic rather than optimistic concurrency? I’ve already tried adding the tablockx query hint, and also loading the row in a select before running the update and using tablockx and holdlock. Wrapping the whole thing in a regular or distributed transaction also makes no difference.

Answer 1

Do the update remotely:

exec RemoteServer.MyDB.dbo.sp_executesql N'UPDATE Customer 
 set FirstName = @FirstName,
 LastName = @LastName
 where AccountNumber = @AccountNumber', 
 N'@FirstName varchar(...), @LastName varchar(...), @AccountNumber <type>',
 @FirstName, @LastName, @AccountNumber;

Must enable RPC and RPC Out on the linked server.

Answer 2

This whole issue goes away with a setting on the MySQL ODBC connector.

On the SQL Server that is linking to MySQL, go into the configure screen on the MySQL ODBC connection that is used by the linked server. Open "Details". On the "cursor/results" tab. Place a check in the "Return matched rows instead of affected rows".

Upon making that configuration change, updates that set a MySQL field value to the value it already has will not return an error.

Before I discovered this on another forum, I had put a LOT of code in to filter out the offending values on a field by field basis. This is easy.