Link DB (MS-SQL)

出自 ProgWiki
前往: 導覽搜尋

技術文件

Link DB範例

Link DB到MS-SQL

  • 設定Link DB(例如把「REMOTE_SQL_1」定義為連線到「10.101.107.178」的「MyDB」這個DB)
exec master.dbo.sp_addlinkedserver
        @server= N'REMOTE_SQL_1'
      , @srvproduct= N''
      , @provider=  N'SQLNCLI'
      , @datasrc= N'10.101.107.178'
      , @catalog= N'MyDB';
  • 設定Link DB的連線帳密(例如把「REMOTE_SQL_1」定義為連線帳密設為「sa」與「########」)
exec master.dbo.sp_addlinkedsrvlogin
      @rmtsrvname=N'REMOTE_SQL_1'
      ,@useself=N'False'
      ,@locallogin=NULL
      ,@rmtuser=N'sa'
      ,@rmtpassword='########';
  • 其他相關設定
EXEC master.dbo.sp_serveroption @server=N'REMOTE_SQL_1', @optname=N'collation compatible', @optvalue=N'false';
EXEC master.dbo.sp_serveroption @server=N'REMOTE_SQL_1', @optname=N'data access', @optvalue=N'true';
EXEC master.dbo.sp_serveroption @server=N'REMOTE_SQL_1', @optname=N'dist', @optvalue=N'false';
EXEC master.dbo.sp_serveroption @server=N'REMOTE_SQL_1', @optname=N'pub', @optvalue=N'false';
EXEC master.dbo.sp_serveroption @server=N'REMOTE_SQL_1', @optname=N'rpc', @optvalue=N'false';
EXEC master.dbo.sp_serveroption @server=N'REMOTE_SQL_1', @optname=N'rpc out', @optvalue=N'false';
EXEC master.dbo.sp_serveroption @server=N'REMOTE_SQL_1', @optname=N'sub', @optvalue=N'false';
EXEC master.dbo.sp_serveroption @server=N'REMOTE_SQL_1', @optname=N'connect timeout', @optvalue=N'0';
EXEC master.dbo.sp_serveroption @server=N'REMOTE_SQL_1', @optname=N'collation name', @optvalue=null;
EXEC master.dbo.sp_serveroption @server=N'REMOTE_SQL_1', @optname=N'lazy schema validation', @optvalue=N'false';
EXEC master.dbo.sp_serveroption @server=N'REMOTE_SQL_1', @optname=N'query timeout', @optvalue=N'0';
EXEC master.dbo.sp_serveroption @server=N'REMOTE_SQL_1', @optname=N'use remote collation', @optvalue=N'true';

Link DB到Oracle

  • 先在Oracle的ODBC做好設定(例如為「MYDB」)
    • 需使用【Configuration and Migration Tools】→【Net Manager】,在【區域】→【服務命名】的地方建立網路服務
  • 設定Link DB
EXEC master.dbo.sp_addlinkedserver
      @server = N'REMOTE_ORACLE_1'
      , @srvproduct=N'MYDB'
      , @provider=N'MSDAORA'
      , @datasrc=N'MYDB';
  • 設定Link DB的連線帳密(例如把「REMOTE_ORACLE_1」定義為連線帳密設為「myuser」與「########」)
exec master.dbo.sp_addlinkedsrvlogin
      @rmtsrvname=N'REMOTE_ORACLE_1'
      ,@useself=N'False'
      ,@locallogin=NULL
      ,@rmtuser=N'myuser'
      ,@rmtpassword='########';
  • 其他相關設定
EXEC master.dbo.sp_serveroption @server=N'REMOTE_ORACLE_1', @optname=N'collation compatible', @optvalue=N'true';
EXEC master.dbo.sp_serveroption @server=N'REMOTE_ORACLE_1', @optname=N'data access', @optvalue=N'true';
EXEC master.dbo.sp_serveroption @server=N'REMOTE_ORACLE_1', @optname=N'dist', @optvalue=N'false';
EXEC master.dbo.sp_serveroption @server=N'REMOTE_ORACLE_1', @optname=N'pub', @optvalue=N'false';
EXEC master.dbo.sp_serveroption @server=N'REMOTE_ORACLE_1', @optname=N'rpc', @optvalue=N'false';
EXEC master.dbo.sp_serveroption @server=N'REMOTE_ORACLE_1', @optname=N'rpc out', @optvalue=N'false';
EXEC master.dbo.sp_serveroption @server=N'REMOTE_ORACLE_1', @optname=N'sub', @optvalue=N'false';
EXEC master.dbo.sp_serveroption @server=N'REMOTE_ORACLE_1', @optname=N'connect timeout', @optvalue=N'0';
EXEC master.dbo.sp_serveroption @server=N'REMOTE_ORACLE_1', @optname=N'collation name', @optvalue=null;
EXEC master.dbo.sp_serveroption @server=N'REMOTE_ORACLE_1', @optname=N'lazy schema validation', @optvalue=N'false';
EXEC master.dbo.sp_serveroption @server=N'REMOTE_ORACLE_1', @optname=N'query timeout', @optvalue=N'0';
EXEC master.dbo.sp_serveroption @server=N'REMOTE_ORACLE_1', @optname=N'use remote collation', @optvalue=N'true';