4

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.

6
  • In this scenario, is RemoteServer a Sql Server, or some other RBDMS such as MySql? Feb 4 '13 at 17:47
  • how about adding AND YourTimeStampColumn=@WhatYouThinkItShouldBe then check for @@ROWCOUNT=0 and issue your own concurrency issue. you'll need to capture the @WhatYouThinkItShouldBe when you load the @FirstName, @LastName, @AccountCode values.
    – KM.
    Feb 4 '13 at 18:14
  • Both servers are running SQL Server. In production one will be SQL Server 2012 and the other SQL Server 2008R2 but I’ve also tested it with both databases on SQL Server 2012 instances.
    – Rich
    Feb 4 '13 at 18:52
  • If I add a restriction by timestamp value to the query, I still get the same error.
    – Rich
    Feb 4 '13 at 19:02
  • I’ve added more detail. For the moment I’m working around the problem by removing the timestamp index but I’d really rather not do that.
    – Rich
    Feb 7 '13 at 14:27
1
+50

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.

0
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.