How delete data from linkedserver table join with local table .

• sunnyluo

  SSC Rookie

  Points: 35

  More actions

  May 16, 2006 at 2:24 am

  #67488

  when our system upgrade to sql server 2005 ,and create  a linked server to localhost database , the script of create linkedserver is :

  /****** Object:  LinkedServer [localhost_boston]    Script Date: 05/07/2006 18:37:15 ******/

  EXEC master.dbo.sp_addlinkedserver @server = N'localhost_test',@srvproduct='',  @provider=N'SQLNCLI', @datasrc=N'localhost', @provstr=N'UID=sa;PWD=007;', @catalog=N'test'

  GO

  EXEC master.dbo.sp_serveroption @server=N'localhost_test', @optname=N'collation compatible', @optvalue=N'false'

  GO

  EXEC master.dbo.sp_serveroption @server=N'localhost_test', @optname=N'data access', @optvalue=N'true'

  GO

  EXEC master.dbo.sp_serveroption @server=N'localhost_test', @optname=N'dist', @optvalue=N'false'

  GO

  EXEC master.dbo.sp_serveroption @server=N'localhost_test', @optname=N'pub', @optvalue=N'false'

  GO

  EXEC master.dbo.sp_serveroption @server=N'localhost_test', @optname=N'rpc', @optvalue=N'true'

  GO

  EXEC master.dbo.sp_serveroption @server=N'localhost_test', @optname=N'rpc out', @optvalue=N'true'

  GO

  EXEC master.dbo.sp_serveroption @server=N'localhost_test', @optname=N'sub', @optvalue=N'false'

  GO

  EXEC master.dbo.sp_serveroption @server=N'localhost_test', @optname=N'connect timeout', @optvalue=N'0'

  GO

  EXEC master.dbo.sp_serveroption @server=N'localhost_test', @optname=N'collation name', @optvalue=null

  GO

  EXEC master.dbo.sp_serveroption @server=N'localhost_test', @optname=N'lazy schema validation', @optvalue=N'false'

  GO

  EXEC master.dbo.sp_serveroption @server=N'localhost_test', @optname=N'query timeout', @optvalue=N'0'

  GO

  EXEC master.dbo.sp_serveroption @server=N'localhost_test', @optname=N'use remote collation', @optvalue=N'true'

   

  when I run the sql on  local server  :

  delete a from localhost_test.test.dbo.Spot as a,

  bica.tmpspot as b where a.spotid=b.spotid

  it display 7 rows have deleted ,but when I  run:

  select * from localhost_test.test.dbo.Spot as a

    join       bica. tmpspot as b on a.spotid=b.spotid

  find the 7 rows have  not  delete,I check the  localhost_test.test.dbo.Spot

  table ,and find 7 rows have deleted that it is  not   a.spotid=b.spotid ,why ?

  If  I run :

   delete   from localhost_test.test.dbo.Spot where spotid=28147

  and the row can deleted .why ?

  anyone can talk me how to do I can !

  Thanks.

• Rob Farley

  SSCrazy

  Points: 2739

  More actions

  May 17, 2006 at 6:42 pm

  #638606

  Seems odd. I would start by using:

  delete a from localhost_test.test.dbo.Spot as a inner join

  bica.tmpspot as b on a.spotid=b.spotid

  Instead of the comma. But I can't guarantee this will offer different results.

  Is your collation the same?

  Rob

  Rob Farley
  LobsterPot Solutions & Adelaide SQL Server User Group
  Company: http://www.lobsterpot.com.au
  Blog: http://blogs.lobsterpot.com.au