sp_addlinkedserver (Transact-SQL)

[アーティクル]
03/07/2014

リンクサーバーを作成します。リンクサーバーを使用すると、OLE DB データソースに対する異種の分散クエリの利用が可能になります。 sp_addlinkedserver を使用してリンクサーバーを作成したら、このサーバーに対して分散クエリを実行できます。リンクサーバーを SQL Server インスタンスとして定義した場合は、リモートストアドプロシージャを実行できます。

適用対象: SQL Server (SQL Server 2008 から現在のバージョンまで)

トピックリンクアイコン Transact-SQL 構文表記規則

構文

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ] 
     [ , [ @provider= ] 'provider_name' ]
     [ , [ @datasrc= ] 'data_source' ] 
     [ , [ @location= ] 'location' ] 
     [ , [ @provstr= ] 'provider_string' ] 
     [ , [ @catalog= ] 'catalog' ]

引数

[ @server= ] 'server'
作成するリンクサーバーの名前を指定します。 server のデータ型は sysname で、既定値はありません。
[ @srvproduct= ] 'product_name'
リンクサーバーとして追加する OLE DB データソースの製品名を指定します。 product_name のデータ型は nvarchar(128) で、既定値は NULL です。 SQL Server の場合、provider_name、data_source、location、provider_string、および catalog は指定する必要はありません。
[ @provider= ] 'provider_name'
このデータソースに対応する OLE DB プロバイダーの一意なプログラム識別子 (PROGID) を指定します。 provider_name は、現在のコンピューターにインストールされている指定の OLE DB プロバイダーに対して一意にする必要があります。 provider_name のデータ型は nvarchar(128) で、既定値は NULL です。ただし、provider_name を省略した場合は、SQLNCLI が使用されます (SQLNCLI を使用すると、SQL Server により最新バージョンの SQL Server Native Client OLE DB プロバイダーにリダイレクトされます)。OLE DB プロバイダーは、指定の PROGID を使用してレジストリに登録されることになっています。
[ @datasrc= ] 'data_source'
OLE DB プロバイダーで認識されるデータソースの名前を指定します。 data_source のデータ型は nvarchar(4000) です。 data_source は DBPROP_INIT_DATASOURCE プロパティとして渡され、OLE DB プロバイダーの初期化に使用されます。
[ @location= ] 'location'
OLE DB プロバイダーで認識されるデータベースの場所を指定します。 location のデータ型は nvarchar(4000) で、既定値は NULL です。 location は DBPROP_INIT_LOCATION プロパティとして渡され、OLE DB プロバイダーの初期化に使用されます。
[ @provstr= ] 'provider_string'
一意なデータソースを識別する、OLE DB プロバイダー固有の接続文字列を指定します。 provider_string のデータ型は nvarchar(4000) で、既定値は NULL です。 provstr は、IDataInitialize に渡されるか、DBPROP_INIT_PROVIDERSTRING プロパティとして設定され、OLE DB プロバイダーの初期化に使用されます。

リンクサーバーを SQL Server Native Client OLE DB プロバイダーに対して作成するときは、SERVER キーワードを使用して、SERVER=servername\instancename の形式で SQL Server インスタンスを指定できます。 servername は SQL Server が実行されているコンピューターの名前、instancename はユーザーが接続する SQL Server インスタンスの名前です。

注意

ミラー化されたデータベースにアクセスするには、接続文字列にデータベース名を含める必要があります。この名前は、データアクセスプロバイダーがフェールオーバーを試行できるようにするために必要です。データベースは、@provstr パラメーターまたは @catalog パラメーターで指定できます。必要に応じて、接続文字列でフェールオーバーパートナー名も指定できます。
[ @catalog= ] 'catalog'
OLE DB プロバイダーに接続するときに使用するカタログを指定します。 catalog のデータ型は sysname で、既定値は NULL です。 catalog は DBPROP_INIT_CATALOG プロパティとして渡され、OLE DB プロバイダーの初期化に使用されます。リンクサーバーを SQL Server インスタンスに対して定義する場合、カタログは、リンクサーバーがマップされる既定のデータベースを参照します。

リターンコード値

0 (成功) または 1 (失敗)

結果セット

なし

説明

次の表は、OLE DB を介してアクセスできるデータソース用にリンクサーバーを設定する方法です。 1 つのデータソースには複数の方法でリンクサーバーを設定できます。したがって、1 つのデータソース型に複数の行が対応している場合もあります。この表には、リンクサーバーの設定に使用する sp_addlinkedserver のパラメーター値も記載されています。

リモート OLE DB データソース	OLE DB プロバイダー	product_name	provider_name	data_source	provider_string	catalog
SQL Server	Microsoft SQL Server Native Client OLE DB プロバイダー	SQL Server1 (既定値)
SQL Server	Microsoft SQL Server Native Client OLE DB プロバイダー		SQLNCLI	SQL Server のネットワーク名 (既定のインスタンスの場合)		データベース名 (省略可能)
SQL Server	Microsoft SQL Server Native Client OLE DB プロバイダー		SQLNCLI	servername\instancename (特定のインスタンスの場合)		データベース名 (省略可能)
Oracle、バージョン 8 以降	Oracle Provider for OLE DB	すべて	OraOLEDB.Oracle	Oracle データベースに対する別名
Access/Jet	Microsoft OLE DB Provider for Jet	任意	Microsoft.Jet.OLEDB.4.0	Jet データベースファイルの完全なパス
ODBC データソース	Microsoft OLE DB Provider for ODBC	任意	MSDASQL	ODBC データソースのシステム DSN
ODBC データソース	Microsoft OLE DB Provider for ODBC	任意	MSDASQL		ODBC 接続文字列
ファイルシステム	Microsoft OLE DB Provider for Indexing Service	任意	MSIDXS	インデックス作成サービスカタログ名
Microsoft Excel ワークシート	Microsoft OLE DB Provider for Jet	任意	Microsoft.Jet.OLEDB.4.0	Excel ファイルのフルパス	Excel 5.0
IBM DB2 データベース	Microsoft OLE DB Provider for DB2	任意	DB2OLEDB		Microsoft OLE DB Provider for DB2 のマニュアルを参照	DB2 データベースのカタログ名

1 この方法でリンクサーバーを設定すると、リンクサーバーの名前は必ず SQL Server のリモートインスタンスのネットワーク名と同じになります。サーバーを指定するには、data_source を使用します。

2 "任意" は任意の製品名を指定できることを示します。

プロバイダー名を指定しない場合、または SQL Server を製品名として指定した場合、SQL Server では Microsoft SQL Server Native Client OLE DB プロバイダーが使用されます。これより以前のプロバイダー名である SQLOLEDB を指定しても、カタログに保存されるときには SQLNCLI に変更されます。

data_source、location、provider_string、および catalog パラメーターによって、リンクサーバーがポイントするデータベースが特定されます。これらのいずれかのパラメーターが NULL に設定されると、対応する OLE DB 初期化プロパティは設定されません。

クラスター化された環境で OLE DB データソースを示すファイル名を指定する場合は、汎用名前付け規則 (UNC) による名前、または共有ドライブを使用して場所を指定します。

ユーザー定義のトランザクション内では、sp_addlinkedserver は実行できません。

セキュリティに関する注意
sp_addlinkedserver を使用してリンクサーバーを作成すると、すべてのローカルログインに対して既定の自己マッピングが追加されます。SQL Server 以外のプロバイダーの場合、SQL Server 認証ログインは、SQL Server サービスアカウントでプロバイダーにアクセスすることもできます。管理者は、sp_droplinkedsrvlogin <linkedserver_name>, NULL を使ってグローバルマッピングを削除することを検討してください。

sp_addlinkedserver を使用してリンクサーバーを作成すると、すべてのローカルログインに対して既定の自己マッピングが追加されます。SQL Server 以外のプロバイダーの場合、SQL Server 認証ログインは、SQL Server サービスアカウントでプロバイダーにアクセスすることもできます。管理者は、sp_droplinkedsrvlogin <linkedserver_name>, NULL を使ってグローバルマッピングを削除することを検討してください。

権限

ALTER ANY LINKED SERVER 権限が必要です。

使用例

A. Microsoft SQL Server Native Client OLE DB プロバイダーを使用する

次の例では、SEATTLESales というリンクサーバーを作成します。製品名は SQL Server で、プロバイダー名は使用されません。

USE master;
GO
EXEC sp_addlinkedserver 
   N'SEATTLESales',
   N'SQL Server';
GO

次の例では、SQL Server Native Client OLE DB プロバイダーを使用して、SQL Server インスタンス上にリンクサーバー S1_instance1 を作成します。

EXEC sp_addlinkedserver   
   @server=N'S1_instance1', 
   @srvproduct=N'',
   @provider=N'SQLNCLI', 
   @datasrc=N'S1\instance1';

B. Microsoft OLE DB Provider for Microsoft Access を使用する

Microsoft.Jet.OLEDB.4.0 プロバイダーは、2002-2003 形式を使用する Microsoft Access データベースに接続します。次の例では、SEATTLE Mktg というリンクサーバーを作成します。

注意

この例では、Microsoft Access と Northwind データベースの両方がインストールされており、Northwind データベースが C:\Msoffice\Access\Samples にあることを前提としています。

EXEC sp_addlinkedserver 
   @server = N'SEATTLE Mktg', 
   @provider = N'Microsoft.Jet.OLEDB.4.0', 
   @srvproduct = N'OLE DB Provider for Jet',
   @datasrc = N'C:\MSOffice\Access\Samples\Northwind.mdb';
GO

Microsoft.ACE.OLEDB.12.0 プロバイダーは、2007 形式を使用する Microsoft Access データベースに接続します。次の例では、SEATTLE Mktg というリンクサーバーを作成します。

注意

EXEC sp_addlinkedserver 
   @server = N'SEATTLE Mktg', 
   @provider = N'Microsoft.ACE.OLEDB.12.0', 
   @srvproduct = N'OLE DB Provider for ACE',
   @datasrc = N'C:\MSOffice\Access\Samples\Northwind.accdb';
GO

C. data_source パラメーターを指定して Microsoft OLE DB Provider for ODBC を使用する

次の例では、Microsoft OLE DB Provider for ODBC (MSDASQL) と data_source パラメーターを使用する、SEATTLE Payroll というリンクサーバーを作成します。

注意

リンクサーバーを使用する前には、指定した ODBC データソース名をサーバーのシステム DSN として定義する必要があります。

EXEC sp_addlinkedserver 
   @server = N'SEATTLE Payroll', 
   @srvproduct = N'',
   @provider = N'MSDASQL', 
   @datasrc = N'LocalServer';
GO

D. Excel スプレッドシート用の Microsoft OLE DB Provider を使用する

Microsoft OLE DB Provider for Jet を使用して 1997-2003 形式の Excel スプレッドシートにアクセスするリンクサーバー定義を作成するには、まず、選択の対象となる Excel ワークシートの列と行を指定して、Excel の名前付き範囲を作成します。こうすると、分散クエリで範囲の名前をテーブル名として参照できるようになります。

EXEC sp_addlinkedserver 'ExcelSource',
   'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'c:\MyData\DistExcl.xls',
   NULL,
   'Excel 5.0';
GO

Excel ワークシートのデータにアクセスするには、セルの範囲を名前と関連付けます。先に設定したリンクサーバーを使って、テーブルとして指定されている名前付き範囲 SalesData にアクセスするときには、次のクエリを使用できます。

SELECT *
   FROM ExcelSource...SalesData;
GO

リモート共有へのアクセスが許可されているドメインアカウントで SQL Server が実行されている場合は、マップされたドライブの代わりに UNC パスを使用できます。

EXEC sp_addlinkedserver 'ExcelShare',
   'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   '\\MyServer\MyShare\Spreadsheets\DistExcl.xls',
   NULL,
   'Excel 5.0';

Excel 2007 形式の Excel スプレッドシートに接続するには、ACE プロバイダーを使用します。

EXEC sp_addlinkedserver @server = N'ExcelDataSource', 
@srvproduct=N'ExcelData', @provider=N'Microsoft.ACE.OLEDB.12.0', 
@datasrc=N'C:\DataFolder\People.xlsx',
@provstr=N'EXCEL 12.0' ;

E. Microsoft OLE DB Provider for Jet を使用してテキストファイルにアクセスする

次の例では、Access .mdb ファイル内のテーブルとしてテキストファイルにリンクするのではなく、直接テキストファイルにアクセスするリンクサーバーを作成します。プロバイダーは Microsoft.Jet.OLEDB.4.0、プロバイダー文字列は Text です。

データソースは、テキストファイルが格納されているディレクトリの完全なパスです。テキストファイルと同じディレクトリ内に、テキストファイルの構造を説明する schema.ini ファイルが存在している必要があります。 Schema.ini ファイルの作成方法の詳細については、Jet Database Engine のマニュアルを参照してください。

--Create a linked server.
EXEC sp_addlinkedserver txtsrv, N'Jet 4.0', 
   N'Microsoft.Jet.OLEDB.4.0',
   N'c:\data\distqry',
   NULL,
   N'Text';
GO

--Set up login mappings.
EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL;
GO

--List the tables in the linked server.
EXEC sp_tables_ex txtsrv;
GO

--Query one of the tables: file1#txt
--using a four-part name. 
SELECT * 
FROM txtsrv...[file1#txt];

F. Microsoft OLE DB Provider for DB2 を使用する

次の例では、Microsoft OLE DB Provider for DB2 を使用する DB2 というリンクサーバーを作成します。

EXEC sp_addlinkedserver
   @server=N'DB2',
   @srvproduct=N'Microsoft OLE DB Provider for DB2',
   @catalog=N'DB2',
   @provider=N'DB2OLEDB',
   @provstr=N'Initial Catalog=PUBS;
       Data Source=DB2;
       HostCCSID=1252;
       Network Address=XYZ;
       Network Port=50000;
       Package Collection=admin;
       Default Schema=admin;';

G. クラウドと内部設置型データベースに対応する分散クエリで使用するリンクサーバーとしての Windows Azure SQL データベースの追加

Windows Azure SQL データベースをリンクサーバーとして追加し、内部設置型データベースとクラウドデータベースにまたがる分散クエリでそのサーバーを使用することができます。これは、内部設置型の企業ネットワークおよび Windows Azure クラウドにまたがるデータベース複合ソリューションに対応するコンポーネントです。

SQL Server ボックス製品には、分散クエリ機能が含まれています。この機能を使用する場合は、クエリを作成し、ローカルデータソースからのデータと、リンクサーバーとして定義されたリモートデータソース (SQL Server 以外のデータソースから取得したデータを含む) からのデータを組み合わせることができます。 (仮想マスターを除く) すべての Windows Azure SQL データベースを個別のリンクサーバーとして追加し、他のデータベースと同様にデータベースアプリケーション内で直接使用することもできます。

Windows Azure SQL データベースを使用する利点として、管理、高可用性、スケーラビリティ、使い慣れた開発モデルとリレーショナルデータモデルを使用して作業できることが挙げられます。データベースアプリケーションの要件によって、クラウド内での Windows Azure SQL データベースの使用方法が決まります。すべてのデータを一度に Windows Azure SQL データベースに移行することも、他のデータを内部設置型に維持したままデータを段階的に移行することもできます。このようなハイブリッドデータベースアプリケーションの場合、Windows Azure SQL データベースをリンクサーバーとして追加し、データベースアプリケーションで分散クエリを実行して Windows Azure SQL データベースからのデータと内部設置型データソースからのデータを結合することもできます。

ここでは、分散クエリを使用して Windows Azure SQL データベースに接続する方法を説明する簡単な例を示します。

------ Configure the linked server
-- Add one Windows Azure SQL DB as Linked Server
EXEC sp_addlinkedserver
@server='myLinkedServer', -- here you can specify the name of the linked server
@srvproduct='',     
@provider='sqlncli', -- using SQL Server Native Client
@datasrc='myServer.database.windows.net',   -- add here your server name
@location='',
@provstr='',
@catalog='myDatabase'  -- add here your database name as initial catalog (you cannot connect to the master database)
-- Add credentials and options to this linked server
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'myLinkedServer',
@useself = 'false',
@rmtuser = 'myLogin',             -- add here your login on Azure DB
@rmtpassword = 'myPassword' -- add here your password on Azure DB
EXEC sp_serveroption 'myLinkedServer', 'rpc out', true;
------ Now you can use the linked server to execute 4-part queries
-- You can create a new table in the Azure DB
exec ('CREATE TABLE t1tutut2(col1 int not null CONSTRAINT PK_col1 PRIMARY KEY CLUSTERED (col1) )') at myLinkedServer
-- Insert data from your local SQL Server
exec ('INSERT INTO t1tutut2 VALUES(1),(2),(3)') at myLinkedServer

-- Query the data using 4-part names
select * from myLinkedServer.myDatabase.dbo.myTable

次の方法で共有

sp_addlinkedserver (Transact-SQL)

構文

引数

リターンコード値

結果セット

説明

権限

使用例

A. Microsoft SQL Server Native Client OLE DB プロバイダーを使用する

B. Microsoft OLE DB Provider for Microsoft Access を使用する

C. data_source パラメーターを指定して Microsoft OLE DB Provider for ODBC を使用する

D. Excel スプレッドシート用の Microsoft OLE DB Provider を使用する

E. Microsoft OLE DB Provider for Jet を使用してテキストファイルにアクセスする

F. Microsoft OLE DB Provider for DB2 を使用する

G. クラウドと内部設置型データベースに対応する分散クエリで使用するリンクサーバーとしての Windows Azure SQL データベースの追加

関連項目

参照

その他のリソース

次の方法で共有

sp_addlinkedserver (Transact-SQL)

構文

引数

リターン コード値

結果セット

説明

権限

使用例

A. Microsoft SQL Server Native Client OLE DB プロバイダーを使用する

B. Microsoft OLE DB Provider for Microsoft Access を使用する

C. data_source パラメーターを指定して Microsoft OLE DB Provider for ODBC を使用する

D. Excel スプレッドシート用の Microsoft OLE DB Provider を使用する

E. Microsoft OLE DB Provider for Jet を使用してテキスト ファイルにアクセスする

F. Microsoft OLE DB Provider for DB2 を使用する

G. クラウドと内部設置型データベースに対応する分散クエリで使用するリンク サーバーとしての Windows Azure SQL データベース の追加

関連項目

参照

その他のリソース

リターンコード値

E. Microsoft OLE DB Provider for Jet を使用してテキストファイルにアクセスする

G. クラウドと内部設置型データベースに対応する分散クエリで使用するリンクサーバーとしての Windows Azure SQL データベースの追加