Updating a table via a linked server

• Ant-Green

  SSC Guru

  Points: 113509

  More actions

  September 25, 2008 at 9:13 am

  #123871

  Hi Guys and Gals

  Could you please advise on how to update records in a remote server.

  eg

  update * from openquery (SVR_LINK, 'update servercollection set username = ' + 'TEST' + ' where hostname = ' + 'TEST' ')

  What we have is a proc which runs a procedure called sp_who2_with_text which outputs to a local table, and we do a select on the remote server to see the records

  select * from openquert (SVR_LINK, 'select * from servercollection')

  what we need to do is update the USERNAME coloumn based on the HOSTNAME where the SPID originated and was wondering if we could do this on the remote server?

  Thanks

  Anthony

• JamieX

  Hall of Fame

  Points: 3763

  More actions

  September 25, 2008 at 10:44 am

  #877174

  you could access it directly instead of using openquery..

  update SERVERLINK.DATABASE.SCHEMA.servercollection

  set username = 'TEST'

  where hostname = 'TEST'

  select *

  from SERVERLINK.DATABASE.SCHEMA.servercollection