Don't delete data from linkedserver table .

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_boston',@.srvproduct='', @.provider=N'SQLNCLI', @.datasrc=N'localhost', @.provstr=N'UID=sa;PWD=007;', @.catalog=N'boston'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'collation compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'data access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'dist', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'pub', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'rpc', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'rpc out', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'sub', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'connect timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'collation name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'lazy schema validation', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'query timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'use remote collation', @.optvalue=N'true'

when I run the sql on local server :

delete a from localhost_boston.boston.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_boston.boston.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_boston.boston.dbo.Spot

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

If I run :

delete from localhost_boston.boston.dbo.Spot where spotid=28147

and the row can deleted .why ?

anyone can talk me how to do I can !

Thanks.

Your delete syntax is improper. You should use a subquery to constrain your deleted rows, not a join.

Code Snippet

delete localhost_boston.boston.dbo.Spot where spotid in (select spotid from bica.tmpspot)

Joins create a result set and using a generic join syntax you can get unexpected result sets. Always test your joins in a select statement 1st to make sure you are getting the results you expect.