CODEWITHSUNDEEP
sql-serversql-updateopenquery
Palps
Palps

Reputation: 568

Update a table on a Linked Server using OPENQUERY

I have two servers: SQLSERVER01 and SQLSERVER02 and I am trying to updated data on SERVER01 from SERVER02 (SERVER01 is a linked server).

My update query is currently dynamic which looks something like this

DECLARE @SQL NVARCHAR(MAX)
DECLARE @ID INT

SET @ID = 1

Set @SQL = 'Update SERVER01.MyDatbase.dbo.MyTable
            set ModifiedDate = GetDate(), SomeOtherValue = ''xyz''
            Where Id = ' Convert(varchar(10), @ID)

If I now call 

EXEC(@SQL)

it will sometimes work, but other times it will just hang there for ages and when I run sp_active I see PREEMPTIVE_OLEDBOPS.

So, I then tried using open query as follows

Select * 
From OpenQuery(SERVER01,
               'Update SERVER01.MyDatbase.dbo.MyTable
                set ModifiedDate = GetDate(), SomeOtherValue = ''xyz''
                Where Id = 1')

but I get this error:

The OLE DB provider "SQLNCLI11" for linked server "SERVER01" indicates that either the object has no columns or the current user does not have permissions on that object.

When I run the following select query I successfully return the row I'm trying to update:

Select * 
From OpenQuery(SERVER01,
               'Select *
                From SERVER01.MyDatbase.dbo.MyTable
                Where Id = 1')

I know that the user that is running the query has permissions, but I really don't know where to go from here. I read somewhere this error has something to do with the update query not returning a row. Is there any truth to this?

How can I resolve this?

Thanks

Upvotes: 3

Views: 4763

Answers (1)

Palps
Palps

Reputation: 568

Okay, I found the answer. This works successfully:

execute (@SQL) at SERVER01

Upvotes: 5

Related Questions