sqlserver 多库查询 sp_addlinkedserver使用方法(添加链

sqlserver 多库查询 sp_addlinkedserver使用方法(添加链接服务器)

我们日常使用SQL Server数据库时,经常遇到需要在实例Instance01中跨实例访问Instance02中的数据。例如在做数据迁移时,如下语句:

insert into Instance01.DB01.dbo.Table01

          select * from Instance02.DB01.dbo.Table01

普通情况下,这样做是不允许的,因为SQL Server默认不可以跨实例访问数据。解决方案是使用存储过程sp_addlinkedserver进行实例注册。

sp_addlinkedserver在MSDN中的定义为:

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]     

      [ , [ @provider= ] 'provider_name' ]    

      [ , [ @datasrc= ] 'data_source' ]

      [ , [ @location= ] 'location' ]

      [ , [ @provstr= ] 'provider_string' ]

      [ , [ @catalog= ] 'catalog' ]

例如:在Instance01实例中,执行如下SQL语句EXEC sp_addlinkedserver ‘Instance02’ //只写第一个参数即可,默认情况下,注册的是SQL Server数据库,其他参数用法详见MSDN。

如果你的两个实例在同一个域中,且Instance01与Instance02有共同的域登陆帐号,那么经过上面的注册后,前面的insert语句就可以执行了。否则,还需要对注册的远程实例进行登陆帐号注册,在Instance01实例中,执行如下SQL语句

EXEC sp_addlinkedsrvlogin 'InstanceName','true'  //使用集成认证访问远程实例

或者 EXEC sp_addlinkedsrvlogin 'InstanceName','false','TJVictor,'sa','Password1' //使用Windows认证访问远程实例,当用户以TJVictor用户登陆Instance01实例访问Instance02时,默认把TJVictor映射成sa,且密码为Password1

经过 sp_addlinkedserver实例注册和sp_addlinkedsrvlogin登陆帐户注册后,就可以在Instance01中直接访问Instance02中的数据库数据了。

如果还无法访问,请检查本机DNS是否可以解析远程数据库的实例名。如果无法解析,可以在EXEC sp_addlinkedserver ‘Instance02’中把Instance02换为IP,或者在hosts文件中,自己建立相应DNS映射。

下面列举几个跨实例数据库访问的存储过程和视图。

存储过程名/视图名 作用 举例 
sp_addlinkedserver 注册远程数据库实例 exec sp_addlinkedserver ‘InstanceName’ 
sp_dropserver 删除远程数据库实例 exec sp_dropserver ‘InstanceName’ 
sp_addlinkedsrvlogin 注册远程实例登陆访问帐户 exec sp_addlinkedsrvlogin ‘InstanceName’, null 
sp_droplinkedsrvlogin 删除远程实例登陆访问帐户 EXEC sp_droplinkedsrvlogin 'InstanceName','UserName' 
sp_helpserver 当前实例已注册的可访问的实例(即查看使用sp_addlinkedserver已注册过的实例) sp_helpserver 
sys.sysservers 功能同sp_helpserver select * from sys.sysservers 
sys.linked_logins 查看已注册的登陆访问帐户(即查看使用sp_addlinkedsrvlogin已注册过的帐户) select * from sys.linked_logins 
sys.remote_logins 查看已注册的远端访问帐户 select * from sys.remote_logins

 

Exec sp_droplinkedsrvlogin ZYB,Null --删除映射(录与链接服务器上远程登录之间的映射) 
Exec sp_dropserver ZYB --删除远程服务器链接 

EXEC sp_addlinkedserver 
@server='ZYB',--被访问的服务器别名 
@srvproduct='', 
@provider='SQLOLEDB', 
@datasrc="/Server2" --要访问的服务器 

EXEC sp_addlinkedsrvlogin 
'ZYB', --被访问的服务器别名 
'false', 
NULL, 
'sa', --帐号 
'sa' --密码 

使用实例: 
Select * from ZYB.CDCenter20110822.dbo.cardbase

 

摘自:

Northwind 数据库驻留在 C:。

注意:不能直接用select * from 链接服务器名.数据库名.用户名.表(或视图)

@provider = 'MSDASQL', @datasrc = 'myDSN'
GO
EXEC sp_addlinkedsrvlogin 

配置和测试ODBC完成!

USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
   @server = 'test',
   @provider = 'Microsoft.Jet.OLEDB.4.0',
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = 'C:Northwind.mdb'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver
   'test',
   'OLE DB Provider for Jet',
   'Microsoft.Jet.OLEDB.4.0',
   'C:Northwind.mdb'
GO
使用
select * from test图片 1表名

须与此文本文件存在于相同的目录中。有关创建 schema.ini 文件的更多信息,

',@useself='false',@locallogin='sa',@rmtuser='oracle用户名

3. 选择刚才配置的数据源名称, 再选择 配置, 跳出SYBASETEST MESSAGES:

注意事项:

PASSWORD: 输入SYBASE DATABASE的用户的密码

2、查询数据

方法二
使用ODBC
SQL Server到SYBASE连接服务器的实现
 
作者:  CCBZZP

请参见 Jet 数据库引擎文档。

@rmtsrvname='MySqlTest',@useself='false',@locallogin='sa',@rmtuser='mys

的所有表名,但在这里还并不能查看表的记录,这个需要在sqserver的查询分析

',@useself='false',@locallogin='sa',@rmtuser='sa',@rmtpassword='密码'

Server};SERVER=192.168.0.1;UID=sa;PWD=123;'
--建立链接服务器登录映射
exec sp_addlinkedsrvlogin 

3、执行存储过程

',@provider='sqloledb',@srvproduct='',@datasrc='远程服务器名'
exec sp_addlinkedsrvlogin 

选择OK(确认)即可!

文进行,并录入SYBASE的数据库用户名和密码—》服务器选项标签页可默认—》

SET IDENTITY_INSERT [ database.[ owner.] ] { table } { ON | OFF }
所以不能通过连接服务器设置此属性
into 也存在这样的问题
select  * into   xiaoming.northwind.dbo.tt from

2、使用SQL Server 的 Microsoft OLE DB 提供程序

4.配置sqlserver2000中的连接服务器:
企业管理器—》安全性—》连接服务器—》右键新建连接服务器—》定义连接名

四、链接SQL Server服务器:

器中用具体sql实现!访问表时,使用格式为: [连接服务器名]..[SYBASE用户

二、使用 Microsoft OLE DB Provider For ORACLE 链接ORACLE

].[表名]。

exec sp_addlinkedserver @server='别名

具体实现步骤:
1.要求pc机上安装SYBASE8.0客户端软件和sqlserver2000软件。
2.配置windows的ODBC数据源:
开始菜单—》程式集—》系统管理工具—》资料数据源(ODBC)—》进入配置用

说明  本示例假设已经安装 Microsoft Access 和示例 Northwind 数据库,且

例1、

The data source is not connected.  Connecting to the data source will

USER ID: 输入SYBASE DATABASE的用户

3、查询数据

@rmtsrvname='xiaoming',@useself='false',@locallogin='sa',@rmtuser='sa',

--Query one of the tables: file1#txt
--using a 4-part name.
SELECT *
FROM txtsrv图片 2[file1#txt]

@rmtsrvname='wzb',@useself='false',@locallogin='sa',@rmtuser='sa',@rmtp

接的SYBASE数据库中的用户名和密码 —》 安全性标签页里:设置用此安全上下

按如下格式:UID=username;PWD=userpasswd),这里的用户名和密码对应所要连

provide useful information during configuration.  Would you like to

ql的用户名',@rmtpassword='mysql的密码'

--删除链接服务器登录映射和链接服务器:
exec sp_droplinkedsrvlogin 'xiaoming' ,'sa'
exec sp_dropserver  'xiaoming'

五、设置链接服务器以访问Access数据库

--建立链接服务器
EXEC sp_addlinkedserver 'xiaoming','','MSDASQL',NULL,NULL,'DRIVER={SQL

--Create a linked server.
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
    'Microsoft.Jet.OLEDB.4.0',
    'c:datadistqry',
    NULL,
    'Text'
GO

1、建立链接数据库
sp_addlinkedserver '别名', 'Oracle', 'MSDAORA', '服务名'
GO
EXEC sp_addlinkedsrvlogin  @rmtsrvname='别名

含文本文件的目录的完整路径名称。schema.ini 文件(描述文本文件的结构)必

下面这个不行:
SELECT * FROM OPENQUERY (MySQLTest ,'表' )

SELECT * FROM OPENQUERY (MySQLTest ,'select * from 表' )

SELECT * FROM 别名..用户名.表(视图)

11};Database=hisdb;Srvr=10.211.135.12;UID=sa;PWD=1111;'
使用:
select * from Sybase1.hisdb.dbo.table1

选择YES(OK或确认)即可

使用OPENQUERY:
SELECT *
FROM OPENQUERY(别名, 'exec 用户名.存储过程名')

义数据源名称(随意如: SYBASETEST)—》数据库名称(必选!)—》OK完成。

CONNECTION  MODE: 可以选择默认的SHARE模式

例2、

PROVIDER8.0; 产品名称可不填; 数据源指定刚才ODBC中定义好的数据源名称;

然后你就可以如下:
select * from 别名.库名.dbo.表名
insert 库名.dbo.表名 select * from 别名.库名.dbo.表名
select * into 库名.dbo.新表名 from 别名.库名.dbo.表名
go

EXEC sp_addlinkedserver '别名','','MSDASQL',NULL,NULL,'DRIVER={SQL

@rmtpassword='123'
go
--查询数据
select * from xiaoming.schooladmin.dbo.agent 

确定。
5.准备工作全部完成!在sqlserver企业管理器—》安全性—》连接服务器打开刚

此示例在 SQL Server 的实例上创建一台名为 S1_instance1 的链接服务器,
该服务器使用 SQL Server 的 Microsoft OLE DB 提供程序。

若要直接创建访问文本文件的链接服务器而不将文件链接为 Access .mdb 文件中

EXEC    sp_addlinkedserver    @server='S1_instance1', @srvproduct='',
                                @provider='SQLOLEDB',

用于 Jet 的 Microsoft OLE DB 提供程序可用于访问并查询文本文件。

@datasrc='S1instance1'

建好的连接服务器—》点击表,即可在右边窗口看到该SYBASE数据库用户拥有的

四部分名称查询数据,可能是个Bug.

三、设置链接服务器以访问格式化文本文件

一、使用 Microsoft OLE DB Provider For ODBC 链接MySQL
安装MySQL的ODBC驱动MyODBC
1、为MySQL建立一个ODBC系统数据源,例如:选择数据库为test ,数据源名称为

1、使用 ODBC 的 Microsoft OLE DB 提供程序

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

的表,请执行 sp_addlinkedserver,如下例所示。
提供程序是 Microsoft.Jet.OLEDB.4.0,提供程序字符串为"Text"。数据源是包

六、连接SYBASE
--首先,你要在SQL服务器上装上访问sybase的客户端

2、建立链接数据库
EXEC sp_addlinkedserver  @server = 'MySQLTest', @srvproduct='MySQL',

xiaoming.northwind.dbo.tt

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

称; 选其他数据源; 指定程序名称为:SYBASE ADAPTIVE SERVER ANYWHERE

Server};SERVER=远程名;UID=用户;PWD=密码;'
如果加上参数@catalog,可以指定数据库
exec sp_addlinkedsrvlogin  @rmtsrvname='别名

户DSN或者系统DSN均可以:添加—》选择ADAPTIVE SERVER ANYWHERE8.0—》自定

使用用于 Jet 的 Microsoft OLE DB 提供程序
此示例创建一台名为 test的链接服务器。

myDSN

注意:四部分名称全部用大写

',@rmtpassword='密码'

--创建链接服务器
exec sp_addlinkedserver 'Sybase1', ' ', 'MSDASQL', NULL, NULL
 ,'Driver={Sybase System

提供程序字符串按以下格式填写:User ID=username;Password=userpasswd(或者

 
本文的测试环境为:
操作系统:  WINDOWS2000 SERVER (繁体系统)
安装数据库: SQLSERVER2000(英文版)和SYBASE8.0客户端(英文版)  

assword='密码'

进入CONNECT TO SYBASE  DATABASE画面:

connect to the data source?

本文由开元棋牌发布于数据库,转载请注明出处:sqlserver 多库查询 sp_addlinkedserver使用方法(添加链

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。