title: Update a table on a Linked Server using OPENQUERY
body: <p>I have two servers: <code>SQLSERVER01</code> and <code>SQLSERVER02</code> and I am trying to updated data on <code>SERVER01</code> from <code>SERVER02</code> (<code>SERVER01</code> is a linked server).</p>
  
  <p>My update query is currently dynamic which looks something like this</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>If I now call </p>
  
  <pre><code>EXEC(@SQL) 
  </code></pre>
  
  <p>it will sometimes work, but other times it will just hang there for ages and when I run <code>sp_active</code> I see <code>PREEMPTIVE_OLEDBOPS</code>.</p>
  
  <p>So, I then tried using open query as follows</p>
  
  <pre><code>Select * 
  From OpenQuery(SERVER01,
                 'Update SERVER01.MyDatbase.dbo.MyTable
                  set ModifiedDate = GetDate(), SomeOtherValue = ''xyz''
                  Where Id = 1')
  </code></pre>
  
  <p>but I get this error:</p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p>When I run the following select query I successfully return the row I'm trying to update:</p>
  
  <pre><code>Select * 
  From OpenQuery(SERVER01,
                 'Select *
                  From SERVER01.MyDatbase.dbo.MyTable
                  Where Id = 1')
  </code></pre>
  
  <p>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?</p>
  
  <p>How can I resolve this?</p>
  
  <p>Thanks</p>