Optimistic concurrency check failure when updating linked server table with timestamp column 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.
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 &lt;type&gt;',
     @FirstName, @LastName, @AccountNumber;


Must enable RPC and RPC Out on the linked server.