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.
Best Answer
Do the update remotely:
Must enable RPC and RPC Out on the linked server.