sp_addlinkedserverおよびsp_addlinkedsrvloginを使用してsqlserverデータベースにリモートアクセスします
Remotely Access Sqlserver Database Using Sp_addlinkedserver
1)sp_addlinkedserver
説明:リンクサーバーを作成します。リンクサーバーを介してリモートデータベースにクエリを実行できます
文法:
Sp_addlinkedserver @ server = 'サーバーエイリアス'-カスタム
@ srvproduct = ''-一般的なデフォルトは空欄に記入します:SQLサーバーなど
@ provider = 'provider_name
'-このデータソースに対応するOLEDBプロバイダーの一意のプログラミング識別子:例:SQLNCLI
@ datasrc = 'data_source
'-データソースアクセスパス:127.0.0.1、1433など
@ location = ''-通常、空欄に記入します。
@ provstr = ''-一意のデータソースのOLEDBプロバイダー固有の接続文字列を識別します。通常は空白を埋めます
@ catalog = ''-データディレクトリ。通常、アクセスするデータベースの名前を書き込みます。
サンプルコード:
USE master GO --Define a linked server EXEC sp_addlinkedserver @server=N'MyLinkServer',--Connection server alias customization, representing the defined connection server @srvproduct=N'', --- can be empty, the default is SQLserver root@xxxxx=N'SQLNCLI',--register oledb access interface, SQLNCLI uses sqlserver2005 or above @datasrc=N'58.148.63.172,1433',--Data source path: This format accesses the default instance @provider=N'SQLOLEDB', --- registered oledb access interface, SQLOLEDB for sqlserver2000 root@xxxxx=N'127.0.0.1' test OK @catalog=N'SMSDB'--Database to access (optional) GO --View all available server system information exec sp_helpserver --View linked server configuration information exec sp_linkedservers
2)sp_addlinkedsrvlogin
説明:SQL Serverのローカルインスタンスのログイン名とリモートサーバーのセキュリティアカウントの間のマッピングを作成または更新し、sp_addlinkedsrvloginを使用して、ローカルサーバーがリンクサーバーへのログインに使用するログイン資格情報を指定します。
文法:
Sp_addlinkedsrvlogin @ rmtsrvname = 'rmtname'-リンクされたサーバーエイリアス
@ useself = true--trueはWindows資格情報のログインを意味し、falseはSQLアカウントのパスワードログインを意味し、nullはアカウントログインがないことを意味します
@ locallogin = 'locallogin'-ローカルサーバーにログインします。デフォルトは空です。
@ rmtuser = ''-sqlユーザー名
@ rmtpassword = ''-SQLパスワード
サンプルコード:
--Define a remote map login name EXEC sp_addlinkedsrvlogin @rmtsrvname=N'MyLinkServer', @useself=N'false', @locallogin=null, @rmtuser='sa', @rmtpassword='123456'
包括的:最初にsp_addlinkedserverを使用してリンクサーバーを作成し、次にsp_addlinkedsrvloginを使用してユーザーログインリンクを増やします。完全なリモートアクセスリモートデータコードは次のとおりです。
USE master GO --Define a linked server EXEC sp_addlinkedserver @server=N'MyLinkServer',--Connection server alias customization, representing the defined connection server @srvproduct=N'', --- can be empty, the default is SQLserver root@xxxxx=N'SQLNCLI',--register oledb access interface, SQLNCLI uses sqlserver2005 or above @datasrc=N'58.148.63.172,1433',--Data source path: This format accesses the default instance @provider=N'SQLOLEDB', --- registered oledb access interface, SQLOLEDB for sqlserver2000 root@xxxxx=N'127.0.0.1' test OK @catalog=N'SMSDB'--Database to access (optional) GO --Define a remote map login name EXEC sp_addlinkedsrvlogin @rmtsrvname=N'MyLinkServer', @useself=N'false', @locallogin=null, @rmtuser='sa',--remote sql login name @rmtpassword='123456' remote sql login password GO --Query remote data through a linked server SELECT TOP 10 * FROM MyLinkServer.SMSDB.dbo.USERACCOUNT ORDER BY Col001
検索結果:
-利用可能なサーバー情報を照会する
EXEC sp_helpserver
結果:
-リンクされたサーバー構成情報を表示する
exec sp_linkedservers
-リンクサーバーのログイン名を削除します
--sp_droplinkedsrvlogin
説明:SQLServerを実行しているローカルサーバーのログインとリンクサーバーのログインの間の既存のマッピングを削除します
構文:spdroplinkedsrvlogin @ rmtsrvname = 'servername'-リモートリンクサーバーエイリアス
@ locallogin = 'locallogin'-リンクサーバーにマップされたローカルサーバーのログイン名 rmtsrvname
例:
Execute sp_droplinkedsrvlogin @rmtsrvname='MyLinkServer',--Link server name MyLinkServer @locallogin=null
-サーバーの削除
sp_dropserver
説明:ローカルSQLServerインスタンスの既知のリモートサーバーとリンクサーバーのリストからサーバーを削除します
文法:
Sp_dropserver [ @server = ] 'server' -- server name [ , [ @droplogins = ] 'droplogins' ]--Default is NULL
例:リンクサーバーMyLinkServerの削除
execute sp_dropserver @server='MyLinkServer',@droplogins='droplogins'