Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Feb 5 '13 at 11:47

This question came from our site for professional and enthusiast programmers.

    
In this scenario, is RemoteServer a Sql Server, or some other RBDMS such as MySql? –  Michael Fredrickson 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

2 Answers 2

up vote 1 down vote accepted
+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.

share|improve this answer
    
This works and seems like the least unpleasant of the three options (the other two being to add a stored procedure to the remote server or to remove the index). –  Rich Feb 7 '13 at 15:32
    
Do you know how to stop Visual Studio 2012 complaining about an unresolved reference to the remote stored procedure? I’ve added reference to a DACPAC extracted from the remote server’s database but this doesn’t seem to contain system stored procedures like sp_executesql –  Rich Feb 7 '13 at 15:49
    
I don't know how to suppress the reference, I used to know but forgot... –  Remus Rusanu Feb 7 '13 at 16:25
    
You have to either include master in the DACPAC for the remote server or set the calling object as "not in build". –  spaghettidba Feb 8 '13 at 9:31

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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