Created
March 19, 2019 01:29
-
-
Save alg0002/d2fc1b4ede87390478ff1f850b0a3ebd to your computer and use it in GitHub Desktop.
SQL ServerからPostgreSQLにリンクサーバー
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE [master] | |
GO | |
EXEC master.dbo.sp_addlinkedserver | |
@server = N'POSTGRES10_3_0_6' /*リンクサーバーの名称(任意の文字列)*/ | |
, @srvproduct=N'PostgreSQL' /*製品名(任意の文字列)*/ | |
, @provider=N'MSDASQL' | |
-- ODBCドライバーの名称 サーバー名 ポート データベース名 ユーザーID パスワード | |
, @provstr=N'Driver={PostgreSQL Unicode(x64)};Server=xx.xx.xx.xx;Port=5432;Database=xxxxxx;UID=postgres;Password=xxxxxxxx;Network=dbmssocn' | |
, @catalog=N'xxxxxx' /*データベース名(接続文字列で指定していれば不要?)*/ | |
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'POSTGRES10_3_0_6',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL | |
GO | |
EXEC master.dbo.sp_serveroption @server=N'POSTGRES10_3_0_6', @optname=N'collation compatible', @optvalue=N'false' | |
GO | |
EXEC master.dbo.sp_serveroption @server=N'POSTGRES10_3_0_6', @optname=N'data access', @optvalue=N'true' | |
GO | |
EXEC master.dbo.sp_serveroption @server=N'POSTGRES10_3_0_6', @optname=N'dist', @optvalue=N'false' | |
GO | |
EXEC master.dbo.sp_serveroption @server=N'POSTGRES10_3_0_6', @optname=N'pub', @optvalue=N'false' | |
GO | |
EXEC master.dbo.sp_serveroption @server=N'POSTGRES10_3_0_6', @optname=N'rpc', @optvalue=N'false' | |
GO | |
EXEC master.dbo.sp_serveroption @server=N'POSTGRES10_3_0_6', @optname=N'rpc out', @optvalue=N'false' | |
GO | |
EXEC master.dbo.sp_serveroption @server=N'POSTGRES10_3_0_6', @optname=N'sub', @optvalue=N'false' | |
GO | |
EXEC master.dbo.sp_serveroption @server=N'POSTGRES10_3_0_6', @optname=N'connect timeout', @optvalue=N'0' | |
GO | |
EXEC master.dbo.sp_serveroption @server=N'POSTGRES10_3_0_6', @optname=N'collation name', @optvalue=null | |
GO | |
EXEC master.dbo.sp_serveroption @server=N'POSTGRES10_3_0_6', @optname=N'lazy schema validation', @optvalue=N'false' | |
GO | |
EXEC master.dbo.sp_serveroption @server=N'POSTGRES10_3_0_6', @optname=N'query timeout', @optvalue=N'0' | |
GO | |
EXEC master.dbo.sp_serveroption @server=N'POSTGRES10_3_0_6', @optname=N'use remote collation', @optvalue=N'true' | |
GO | |
EXEC master.dbo.sp_serveroption @server=N'POSTGRES10_3_0_6', @optname=N'remote proc transaction promotion', @optvalue=N'true' | |
GO |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select | |
* | |
from | |
POSTGRES10_3_0_6.catalogname.[public].tablename | |
--リンクサーバー名.データベース名.変更不可?.テーブル名 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment