/*
Environment:
Local (XP + sql2005 ):
Select @ version
Icrosoft SQL Server 2005-9.00.1399.06 (Intel x86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (build 2600: Service Pack 3)
Data Server (Win2000 + SQL2000 ):
Select @ version
Microsoft SQL Server 2000-8.00.2039 (Intel x86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (build 2195: Service Pack 4)
*/
-- 1 create a linked server
Exec sp_addrole server 'data', '', 'sqloledb', '192. 168.102.208'
Exec sp_add1_srvlogin 'data', 'false', null, 'sa ', 'sa'
Go
-- 2 create a test process
If object_id ('P _ test') is not null drop proc p_test
Go
Create proc p_test @ SQL varchar (8000)
As
Begin
Exec (@ SQL)
End
-- 3 Test
Declare @ SQL varchar (8000)
Set @ SQL ='
Delete from data. master. DBO. TB
Insert data. master. DBO. Tb (item) values (''aaaaaaaaa '')
Select item from data. master. DBO. tb'
Exec p_test @ SQL
/*
(One row is affected)
(One row is affected)
Item
Bytes ----------------------------------------------------------------------------------------------------
Aaaaaaaaaa
(One row is affected)
*/
-- 4 delete a link
Exec sp_dropserver 'data', 'droplogins'