Link DB (MS-SQL)

出自ProgWiki

目錄 1 技術文件 2 Link DB範例 2.1 Link DB到MS-SQL 2.2 Link DB到Oracle

技術文件

• sp_addlinkedserver
• sp_addlinkedsrvlogin

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';