Updating Table on Linked Server

• mohan_rv

  SSC Journeyman

  Points: 88

  More actions

  October 10, 2006 at 10:04 am

  #68964

  Hi

  I am using SQL Server 2005 SP1 std. edition on Windows 2003 SP1 std edition.

  I am updating a table on a Linded server like

    UPDATE t1

    SET t1.col1 = t2.col1

    FROM LinkerServer1.Database1.dbo.Table1 t1 INNER JOIN

         LinkerServer1.Database1.dbo.Table2 t2 ON t1.col2 = t2.col2

    WHERE t1.col2 IS NOT NULL AND t2.col2 IS NOT NULL

      AND t1.col2 = -21470

  t1.col2 is having a unique index on it so it selects only one row at a time.

  I created the linked servers like

  EXEC master.dbo.SP_ADDLINKEDSERVER @server = N'LinkerServer1'

  and i am added local sa as the linked server user with remote sa previleges.

  Now my issue is when i ran the above query it saying that 1 row is updated

  but actually it is not updating. I ran the query using IN instead of INNER JOIN

  still it says that 1 row is updated but row is not getting updated but the

  following querry

    UPDATE t1

    SET t1.col1 = 'Some value'

    FROM LinkerServer1.Database1.dbo.Table1 t1

    WHERE t1.col2 IS NOT NULL AND t1.col2 = -21470

  works fine which actually updates a row.

  Please let me know what is the mistake i am doing here.

  If you you need any further information please let me know.

  thanks in advance

  Mohan

   

• October 13, 2006 at 8:00 am

  This was removed by the editor as SPAM

• jezemine

  SSCrazy Eights

  Points: 8059

  More actions

  October 13, 2006 at 8:23 pm

  #665875

  what do you get if you run this:

  SELECT *

  FROM LinkerServer1.Database1.dbo.Table1 t1 INNER JOIN

  LinkerServer1.Database1.dbo.Table2 t2 ON t1.col2 = t2.col2

  WHERE t1.col2 IS NOT NULL AND t2.col2 IS NOT NULL

  AND t1.col2 = -21470

  ---------------------------------------
  elsasoft.org