How delete data from linkedserver table join with local tabl

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How delete data from linkedserver table join with local tabl

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-05-18 : 08:57:48

sunnyluo writes "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."

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-18 : 10:31:14

if you want to delete a linked server you need to issue a sp_dropserver

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources