远程连接操作

SQL code

  
  

   
   
   
   --
   
   遠程連接操作
   
   
/*
   
   *****************************************************************************************************************************************************
Tab表:
/*
ID 自增列         Name
----------- ---------
1           a
2           b
3           c

   
   */
   
   
存儲過程:

   
   /*
   
   
create proc P
as
select top 10 ID,Name from sysobjects

   
   */
   
   

整理人：中國風(Roy)

日期:
   
   2008.06
   
   .
   
   06
   
   

   
   ******************************************************************************************************************************************************/
   
   


   
   --
   
   遠程鏈接服務器機器名:Roy;實例名:Roy/SQL2005DE;登陸名:sa;密碼:Test2005;
   
   

   
   

   
   --
   
   創建鏈接服務器
   
   

   
   EXEC
   
    master.dbo.sp_addlinkedserver 
   
   @server
   
    
   
   =
   
    N
   
   '
   
   ROY_LNK
   
   '
   
   , 
   
   @srvproduct
   
   =
   
   N
   
   '
   
   ROY_LNK
   
   '
   
   , 
   
   @provider
   
   =
   
   N
   
   '
   
   SQLOLEDB
   
   '
   
   , 
   
   @datasrc
   
   =
   
   N
   
   '
   
   Roy/SQL2005DE
   
   '
   
   --
   
   SQL05可用SQLNCLI/SQLOLEDB
   
   

   
   GO
   
   

   
   EXEC
   
    master.dbo.sp_addlinkedsrvlogin 
   
   @rmtsrvname
   
    
   
   =
   
    N
   
   '
   
   ROY_LNK
   
   '
   
   , 
   
   @locallogin
   
    
   
   =
   
    
   
   NULL
   
    , 
   
   @useself
   
    
   
   =
   
    N
   
   '
   
   False
   
   '
   
   , 
   
   @rmtuser
   
    
   
   =
   
    N
   
   '
   
   sa
   
   '
   
   , 
   
   @rmtpassword
   
    
   
   =
   
    N
   
   '
   
   Test2005
   
   '
   
   

   
   GO
   
   


   
   --
   
   刪除鏈接服務器
   
   
--
   
   exec sp_dropserver 'roy_lnk','droplogins'
   
   

   
   

   
   --
   
   Select
   
   

   
   select
   
    
   
   *
   
    
   
   from
   
    roy_lnk.test.dbo.Tab


   
   --
   
   insert
   
   

   
   insert
   
    roy_lnk.test.dbo.Tab(Name)
   
   --
   
   指定列
   
   

   
   Values
   
   (
   
   '
   
   d
   
   '
   
   )


   
   --
   
   update
   
   

   
   update
   
    roy_lnk.test.dbo.Tab 
   
   set
   
    Name
   
   =
   
   '
   
   DD
   
   '
   
    
   
   where
   
    Name
   
   =
   
   '
   
   d
   
   '
   
   


   
   --
   
   delete 
   
   

   
   delete
   
    roy_lnk.test.dbo.Tab 
   
   where
   
    Name
   
   =
   
   '
   
   DD
   
   '
   
   

用openrowset:


   
   --
   
   支持存儲過程
   
   

   
   select
   
    
   
   *
   
   from
   
    
   
   openrowset
   
   (
   
   '
   
   SQLOLEDB
   
   '
   
   ,
   
   '
   
   Roy/SQL2005DE
   
   '
   
   ;
   
   '
   
   sa
   
   '
   
   ;
   
   '
   
   Test2005
   
   '
   
   ,
   
   '
   
   test.dbo.p
   
   '
   
   )a



   
   --
   
   select 
   
   

   
   select
   
    
   
   *
   
   from
   
    
   
   openrowset
   
   (
   
   '
   
   SQLOLEDB
   
   '
   
   ,
   
   '
   
   Roy/SQL2005DE
   
   '
   
   ;
   
   '
   
   sa
   
   '
   
   ;
   
   '
   
   Test2005
   
   '
   
   ,test.dbo.Tab)a



   
   --
   
   insert
   
   

   
   insert
   
    
   
   openrowset
   
   (
   
   '
   
   SQLOLEDB
   
   '
   
   ,
   
   '
   
   Roy/SQL2005DE
   
   '
   
   ;
   
   '
   
   sa
   
   '
   
   ;
   
   '
   
   Test2005
   
   '
   
   ,test.dbo.Tab)(Name)
   
   values
   
   (
   
   '
   
   EE
   
   '
   
   )


   
   --
   
   update 
   
   

   
   update
   
    
   
   openrowset
   
   (
   
   '
   
   SQLOLEDB
   
   '
   
   ,
   
   '
   
   Roy/SQL2005DE
   
   '
   
   ;
   
   '
   
   sa
   
   '
   
   ;
   
   '
   
   Test2005
   
   '
   
   ,test.dbo.Tab) 
   
   set
   
    Name
   
   =
   
   '
   
   EEE
   
   '
   
    
   
   where
   
    Name
   
   =
   
   N
   
   '
   
   EE
   
   '
   
   


   
   --
   
   delete
   
   

   
   delete
   
    
   
   openrowset
   
   (
   
   '
   
   SQLOLEDB
   
   '
   
   ,
   
   '
   
   Roy/SQL2005DE
   
   '
   
   ;
   
   '
   
   sa
   
   '
   
   ;
   
   '
   
   Test2005
   
   '
   
   ,test.dbo.Tab) 
   
   where
   
    Name
   
   =
   
   N
   
   '
   
   EEE
   
   '
   
   


用openquery:
   
   --
   
   支持存儲過程
   
   

   
   

   
   --
   
   支持存儲過程
   
   

   
   select
   
    
   
   *
   
     
   
   from
   
    
   
   openquery
   
   (roy_lnk, 
   
   '
   
   test.dbo.P
   
   '
   
   )



   
   --
   
   select
   
   

   
   SELECT
   
    
   
   *
   
    
   
   FROM
   
    
   
   openquery
   
   (roy_lnk, 
   
   '
   
   SELECT * FROM test.dbo.tab
   
   '
   
   ) 


   
   --
   
   insert
   
   

   
   insert
   
    
   
   openquery
   
   (roy_lnk, 
   
   '
   
   SELECT * FROM test.dbo.tab
   
   '
   
   ) (Name) 
   
   values
   
   (
   
   '
   
   F
   
   '
   
   )


   
   --
   
   update 
   
   

   
   update
   
    
   
   openquery
   
   (roy_lnk, 
   
   '
   
   SELECT * FROM test.dbo.tab
   
   '
   
   ) 
   
   set
   
    Name
   
   =
   
   '
   
   FF
   
   '
   
    
   
   where
   
    Name
   
   =
   
   '
   
   F
   
   '
   
   


   
   --
   
   delete不支持是根據 OLE DB 提供者的功能而定
   
   
--
   
   delete openquery(roy_lnk, 'SELECT * FROM test.dbo.tab')  where Name='FF' 
   
   

   
   


用OPENDATASOURCE:


   
   --
   
   支持存儲過程
   
   

   
   select
   
    
   
   *
   
     
   
   from
   
    
   
   OPENDATASOURCE
   
   (
   
   '
   
   SQLOLEDB
   
   '
   
   ,
   
   '
   
   Data Source=Roy/SQL2005DE;User ID=sa;Password=Test2005
   
   '
   
   ).
   
   '
   
   test.dbo.p
   
   '
   
    


   
   --
   
   select
   
   

   
   select
   
    
   
   *
   
     
   
   from
   
    
   
   OPENDATASOURCE
   
   (
   
   '
   
   SQLOLEDB
   
   '
   
   ,
   
   '
   
   Data Source=Roy/SQL2005DE;User ID=sa;Password=Test2005
   
   '
   
   ).test.dbo.tab 


   
   --
   
   insert
   
   

   
   insert
   
    
   
   OPENDATASOURCE
   
   (
   
   '
   
   SQLOLEDB
   
   '
   
   ,
   
   '
   
   Data Source=Roy/SQL2005DE;User ID=sa;Password=Test2005
   
   '
   
   ).test.dbo.tab(Name) 
   
   values
   
   (
   
   '
   
   H
   
   '
   
   )


   
   --
   
   update
   
   

   
   update
   
    
   
   OPENDATASOURCE
   
   (
   
   '
   
   SQLOLEDB
   
   '
   
   ,
   
   '
   
   Data Source=Roy/SQL2005DE;User ID=sa;Password=Test2005
   
   '
   
   ).test.dbo.tab 
   
   set
   
    Name
   
   =
   
   '
   
   HH
   
   '
   
    
   
   where
   
    Name
   
   =
   
   '
   
   H
   
   '
   
   


   
   --
   
   delete 
   
   

   
   delete
   
    
   
   OPENDATASOURCE
   
   (
   
   '
   
   SQLOLEDB
   
   '
   
   ,
   
   '
   
   Data Source=Roy/SQL2005DE;User ID=sa;Password=Test2005
   
   '
   
   ).test.dbo.tab 
   
   where
   
    Name
   
   =
   
   '
   
   HH
   
   '
   
   



   
   --
   
   與本機表數據對比時用別名
   
   

   
   select
   
    
   
   *
   
    
   
   from
   
    roy_lnk.test.dbo.Tab a 
   
   join
   
    tab b 
   
   on
   
    a.ID
   
   =
   
   b.ID