链接服务器为SQL Server提供了从远程数据源访问数据的能力，可以执行查询、修改数据、远程过程调用。远程数据源可以是同类的(数据源可以是另一个SQL Server实例)，也可以是不同类的(其他关系型数据库产品和数据源，如：DB2，ORACLE，EXCEL，ACCESS，文本文件)。使用跨平台联接查询可以访问遗留数据库，而且不需要对既有的数据源进行合并、迁移。
分布式查询可以引用一个或多个链接服务器进行查询，在远程表、视图、存储过程上执行读取和修改操作。链接服务器支持的查询类型取决于OLE DB访问接口提供的事务支持级别。可以通过在from子句中使用4部分组成的远程对象名称来引用链接服务器，或者使用T-SQL命令openquery来运行分布式查询。
非 XML 格式化文件的结构
非XML 格式化文件是具有特殊结构的文本文件。非XML 格式化文件包含了有关每个表列的文件存储类型、前缀长度、字段长度和字段终止符的信息。
下图显示了一个示例非XML格式化文件的格式化文件字段。
“版本”和“列数”字段仅出现一次。下表对其意义进行了说明。
| 格式化文件字段 | 说明 | ||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 版本 | 格式化文件的版本号。对于大多数版本的 SQL Server，格式化文件版本与 bcp 实用工具 (Bcp.exe) 相同，如下所示：
1该版本号仅可由 bcp 识别，而无法由 Transact-SQL 识别。
| ||||||||||||||||||||
| 列数 | 数据文件中字段的数目。该数目必须在所有行中都相同。 |
其他格式化文件字段说明需要大容量导入或导出的数据字段。每个数据字段都需在格式化文件中占单独一行。每个格式化文件行均包含下表中说明的格式化文件字段的值。
| 格式化文件字段 | 说明 |
|---|---|
| 宿主文件字段顺序 | 用以表示数据文件中每个字段的位置的数字。行中的第一个字段为 1，依此类推。 |
| 宿主文件数据类型 | 表示存储在数据文件的给定字段中的数据类型。对于 ASCII 数据文件，使用 SQLCHAR；对于本机格式数据文件，使用默认的数据类型。有关详细信息，请参阅使用 bcp 指定文件存储类型。 |
| 前缀长度 | 字段长度前缀字符的数目。有效前缀长度是 0、1、2、4 和 8。若要避免指定长度前缀，将其设置为 0。如果字段包含 NULL 数据值，则必须指定长度前缀。有关详细信息，请参阅指定数据文件中的前缀长度。 |
| 宿主文件数据长度 | 数据文件的特定字段中所存储的数据类型的最大长度（按字节计）。 如果您正在为带分隔符的文本文件创建非 XML 格式化文件，则可以将每个数据字段的宿主文件数据长度指定为 0。当带分隔符的文本文件的前缀长度为 0 并导入终止符时，可忽略字段长度值，因为字段所使用的存储空间等于数据加上终止符的长度。 有关详细信息，请参阅使用 bcp 指定字段长度。 |
| 终止符 | 用来分隔数据文件中各字段的分隔符。常用的终止符为逗号 (,)、制表符 (\t) 和行结束符 (\r\n)。有关详细信息，请参阅指定字段终止符和行终止符。 |
| 服务器列顺序 | 列在 SQL Server 表中显示的顺序。例如，如果数据文件的第四个字段映射到 SQL Server 表中的第六列，则第四个字段的服务器列顺序为 6。 若要阻止表中的某个列接收数据文件中的任何数据，则可以将服务器列顺序值设置为 0。 |
| 服务器列名 | 从 SQL Server 表中复制的列名。无需使用字段的实际名称，但格式化文件中的字段不得为空。 |
| 列排序规则 | 排序规则用于在数据文件中存储字符和 Unicode 数据。 |
| 注意： |
|---|
| 您可以修改格式化文件，以便从字段编号或顺序与表列的编号或顺序不同的数据文件进行大容量导入。有关详细信息，请参阅在大容量导入期间使用格式化文件将字段映射到列。 |
示例
下面的示例显示了一个以前创建的非 XML 格式化文件 (myDepartmentIdentical-f-c.fmt)。该文件描述了AdventureWorks2008R2 示例数据库中的HumanResources.Department 表中每一列的字符数据字段。
生成的格式化文件 myDepartmentIdentical-f-c.fmt 包含以下信息：
10.0 4 1 SQLCHAR 0 7 "\t" 1 DepartmentID "" 2 SQLCHAR 0 100 "\t" 2 Name SQL_Latin1_General_CP1_CI_AS 3 SQLCHAR 0 100 "\t" 3 GroupName SQL_Latin1_General_CP1_CI_AS 4 SQLCHAR 0 24 "\r\n" 4 ModifiedDate "" | |
1、SQL Server
--修改高级参数 sp_configure 'show advanced options',1 go --允许即席分布式查询 sp_configure 'Ad Hoc Distributed Queries',1 go --如果配置的值不在合理范围（在最小值最大值范围内），那么可以强制覆盖 reconfigure with override go /*============================================================= 在分布式即席查询中， select into语句不能用于把本地的表导出到远程服务器 解决办法： 要在远程服务器新建表，必须通过execute at来执行DDL语句， 而要执行execute at，必须先设置链接服务器的rpc、rpc out属性为true， 最后用insert into把本地的数据导入到远程 ===============================================================*/ --1、链接服务器为SQL Server --1.1.1建立链接服务器,通过@datasrc指定数据源，适合链接其他多种数据库 EXEC master.dbo.sp_addlinkedserver @server = N'Link', @srvproduct='ms', @provider=N'SQLNCLI', @datasrc=N'192.168.1.5,1433' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'Link', @useself=N'False', @locallogin=NULL, @rmtuser=N'sa', @rmtpassword='xyz' --1.1.2建立链接服务器,通过@server参数指定服务器，适合直接连接sql server EXEC master.dbo.sp_addlinkedserver @server = N'192.168.1.5,1433', @srvproduct=N'SQL Server' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'192.168.1.5,1433', @useself=N'False', @locallogin=NULL, @rmtuser=N'sa', @rmtpassword='xyz' --1.1.3设置链接服务器的属性，从而远程调用存储过程rpc EXEC master.dbo.sp_serveroption @server=N'192.168.1.5,1433', @optname=N'rpc out', @optvalue=N'true' EXEC master.dbo.sp_serveroption @server=N'192.168.1.5,1433', @optname=N'rpc', @optvalue=N'true' --1.1.4.1通过链接服务器来执行分布式查询 select * from link.wc.dbo.wcc select * from [192.168.1.5,1433].wc.dbo.wcc --1.1.4.2通过openquery来执行分布式查询,需要先建立链接服务器 select * from openquery([link],'select * from wc.dbo.wcc') --1.1.5查看链接服务器 select server_id, name, product, provider, data_source, is_linked, is_remote_login_enabled, is_remote_proc_transaction_promotion_enabled, lazy_schema_validation from sys.servers --查看链接登录名 select s.name, ll.remote_name, sp.name from sys.linked_logins ll inner join sys.servers s on s.server_id = ll.server_id left join sys.server_principals sp on sp.principal_id = ll.local_principal_id --1.1.6删除链接服务器登录名映射、删除链接服务器 exec sp_droplinkedsrvlogin @rmtsrvname = '192.168.1.5,1433', @locallogin = null exec sp_dropserver @server = '192.168.1.5,1433' exec sp_droplinkedsrvlogin @rmtsrvname = 'link', @locallogin = null exec sp_dropserver @server = 'link' --1.2用openrowset函数 --1.2.1第一种写法 SELECT * FROM OPENROWSET('SQLOLEDB', 'server=192.168.1.5,1433;uid=sa;pwd=xyz', --字符串 wc.dbo.wcc) --直接写表的名称 --1.2.2第二种写法 SELECT * FROM OPENROWSET('SQLOLEDB', 'server=192.168.1.5,1433; uid=sa; pwd=xyz', --字符串 'select * from wc.dbo.wcc where wcc_name =''abc''') --查询语句,注意不能用字符串连接符 --1.3用opendatasource函数 select * from opendatasource('SQLOLEDB', 'DATA SOURCE=192.168.1.5,1433; USER ID=sa; password=xyz').wc.dbo.wcc
2、Oracle
--================================================================ --2、链接服务器为Oracle,使用oracle的oledb库MSDAORA --2.1.1为建立链接服务器 EXEC sp_addlinkedserver @server = 'OracleLinkedServer', --要创建的链接服务器名称 @srvproduct = 'Oracle', --产品名称 @provider = 'MSDAORA', --OLE DB 字符 @datasrc = 'orcl' --数据源 --2.1.2为创建连接服务器登陆 EXEC sp_addlinkedsrvlogin @rmtsrvname = 'OracleLinkedServer', --链接服务器名称 @useself = 'false', @locallogin = NULL, @rmtuser = 'scott', --Oracle服务器的登陆用户名 @rmtpassword = 'tiger' --Oracle服务器的登陆密码 --2.2为设置链接服务器属性 exec sp_serveroption @server='OracleLinkedServer', @optname = 'rpc', --从给定的服务器启动rpc @optvalue ='true' --这样可以调用远程的存储过程 exec sp_serveroption @server='OracleLinkedServer', @optname = 'rpc out', --对给定的服务器启动rpc @optvalue ='true' --在远程执行DDL语句 --2.3为在远程服务器上执行DDL语句新建表，用oracle的方式书写语句 exec('create table SCOTT.EMPXX(iddd int)') at OracleLinkedServer --2.4用openrowset函数，把oracle的数据直接新建表的同时插入数据 select * INTO EMP_X from openrowset('MSDAORA','orcl';'scott';'tiger',SCOTT.EMP) select * INTO EMP_X from openrowset('MSDAORA','orcl';'scott';'tiger','select * from SCOTT.EMP') --2.5为opendatasource函数，查询一下oracle中的数据 select * from opendatasource('MSDAORA','Data Source=orcl; User ID=scott; password=tiger')..SCOTT.EMP
3、Excel
/*================================================================== 1.很难从显示的错来知道：openrowset,opendatasource这两个函数哪儿出错了。 2.必须保证函数中的参数正确，否则会有莫名其妙的错误。 3.由于导入的Excel文件格式只能是.xls，所以在导入数据时， 可以先把要导入的扩展名为.xlsx的文件另存为扩展名.xls的文件， 然后把原始.xlsx数据，在格式不变的情况下复制到新的.xls。 4.在导入数据时，必须要按照目标表的字段类型、长度，来转化excel中的数据， 比如，那么在导入时把float转化成numeric，然后再转成varchar， 要是直接从float转成varchar，会把有些数转化成用科学计数法显示的数字， 再进一步转化是会报错。 5.导入到远程的目标表时，目标表必须已经存在 ========================================================================*/ --3.Excel --3.1.1链接服务器excel,不用建立服务器登录名 EXEC sp_addlinkedserver @server = 'EXCEL', @srvproduct = 'Jet 4.0', @provider = 'Microsoft.Jet.OLEDB.4.0', @datasrc = 'c:\t.xls', --@location = NULL, @provstr = 'Excel 5.0;' --不加分号也可以 --3.1.2通过链接服务器查询 SELECT * FROM [EXCEL]...[sheet1$] --3.1.3引用12.0库 EXEC sp_addlinkedserver @server = 'EXCEL', @srvproduct = 'Jet 12.0', @provider = 'microsoft.ace.oledb.12.0', @datasrc = 'c:\t.xls', --@location = NULL, @provstr = 'Excel 12.0;' --不加分号也可以 SELECT * FROM [EXCEL]...[sheet1$] - -查询语句完全一样 --3.2.1用openrowset函数,引用4.0的库 select * from openrowset('microsoft.jet.oledb.4.0', 'Excel 5.0;database=c:\t2.xls', --文件必须存在 sheet1$) --表必须存在 select * from openrowset('microsoft.jet.oledb.4.0', 'Excel 5.0;database=c:\t2.xls', 'select * from [sheet1$]') /*========================================================================= 如果在要导入数据的目标机器上装有office2007及其以上版本， 那么可以用以下的方法导入扩展名为.xlsx,引用了库：microsoft.ACE.oledb.12.0, excel的版本为:12.0 注意：不能在openrowset中写任何的空格等多余的字符， 否则会报错，而且错误没有针对性 ===========================================================================*/ --3.2.2用openrowset函数,引用4.0的库 select * from openrowset('microsoft.ace.oledb.12.0', 'Excel 12.0;database=c:\test.xlsx', 'select * from [sheet1$]') --3.3.1用opendatasrouce,引用4.0的库 select * from opendatasource('microsoft.jet.oledb.4.0', 'data source=c:\t.xls;Extended Properties=Excel 5.0')...[sheet1$] --3.3.2用opendatasrouce,引用12.0的库 select * from opendatasource('microsoft.ace.oledb.12.0', 'data source=c:\t.xls;Extended Properties=Excel 12.0')...[sheet1$]
4、bcp
--4.通过bcp导入导出数据,通过openrowset导入数据 /*========================================================= 以下为bcp命令行常用的参数,注意大小写： -c 以char作为存储类型 -w 和-c类似，只有当使用unicode字符集拷贝数据时使用nchar作为存储类型 -T 用可信连接 -S sql server服务器名称 in 导入数据 out 导出数据 queryout 查询导出数据 -U 用户名 -P 密码 -F 从哪一行开始导入 -L 直到哪一行结束 -f 格式文件 -x 与-f一起使用，用来生产xml格式的格式文件 -t 指定字段分隔符是"\t" -r 指定行分隔符是"\n" =====================================================*/ --导出格式化文件 --4.1.1用可信连接登陆 bcp wc.dbo.calendar format nul -f c:\calendar.fmt -c -T -S pc0227gry\mssqlserver2008 --4.1.2用户名、密码登陆 bcp wc.dbo.calendar format nul -f c:\calendar.fmt -c -Usa -Pxyz -S pc0227gry\mssqlserver2008 --4.2导出数据文件，可以把远处服务器的数据导出保存为本地的文件 bcp wc.dbo.calendar out c:\calendar.txt -f c:\calendar.fmt -c -T -S PC0227GRY\MSSQLSERVER2008 --4.3导入数据 bcp wc.dbo.calendar in c:\calendar.txt -c -f c:\calendar.fmt -Usa -Pxyz -S pc0227gry\mssqlserver2008 /*============================================================= 4.3openrowset函数带bulk操作符的用法 第1种用法： openrowset ( bulk 要加载结果集的文件名, formatfile = 格式化文件名, bulk选项 ) 第2种用法： openrowset ( bulk 文件名, SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB ) ==============================================================*/ --4.4.1第1种用法,把bcp导出的数据，导入数据库中 --具体导入数据时，可以先用bcp把表的格式文件倒出来，然后在把数据导进去，这样按照格式自动转化 select * from openrowset(bulk 'c:\calendar.txt', --要读取的文件路径和名称 formatfile='c:\calendar.fmt', --格式化文件的路径和名称 firstrow = 1, --要载入的第一行 --lastrow = 1000, --要载入的最后一行,此值必须大于firstrow maxerrors = 10, --在加载失败之前加载操作中最大的错误数 errorfile ='c:\calendar_error.txt', --存放错误的文件 rows_per_batch = 100 --每个批处理导入的行数 ) as calendar --4.4.2第2种用法 select 1, BulkColumn --这个是从数据源获取的，要插入的列，默认的列名 from openrowset( BULK 'c:\helloworld.class',--指定文件路劲，这里是本地路径 SINGLE_BLOB --指定二进制数据是SINGLE_BLOB --指定文本是SINGLE_CLOB、SINGLE_NCLOB )X --必须要别名 select 1, binColumn --引用后面定义的列别名 from openrowset( BULK 'c:\Katongxiar01.jpg', --指定文件路劲，这里是本地路径 SINGLE_BLOB --指定二进制数据是SINGLE_BLOB --指定文本是SINGLE_CLOB、SINGLE_NCLOB )X(binColumn) --这里还定义了列的别名 --可以这么更新 update t set pic = ( select BulkColumn from openrowset(bulk 'c:\dwhj.bmp', SINGLE_BLOB) AS X --一定不要忘了定义别名 select 1, binColumn --引用后面定义的列别名 from openrowset( BULK 'c:\calendar.txt', --指定文件路劲，这里是本地路径 SINGLE_CLOB --指定二进制数据是SINGLE_BLOB --指定文本是SINGLE_CLOB、SINGLE_NCLOB )X(binColumn) --这里还定义了列的别名
5、txt
--=========================================================== --5.txt,cvs格式的数据导入 --5.1建立txt的连接服务器 EXEC sp_addlinkedserver @server = 'txtsrv', @srvproduct = 'Jet 4.0', @provider = 'Microsoft.Jet.OLEDB.4.0', @datasrc = 'c:\', --文件所在的目录 --NULL, @provstr = 'Text' --设置登录名 EXEC sp_addlinkedsrvlogin txtsrv, FALSE, NULL, Admin, NULL GO --列出在链接服务器上的表,会列出目录中所有的文件:t#txt , list#csv EXEC sp_tables_ex txtsrv GO --查询 SELECT * FROM txtsrv...[t#txt] --5.2.1通过bulk insert的方式导入数据,必须先建立一个表 create table tt(a1 varchar(10), a2 varchar(10), a3 varchar(10)) --5.2.2大量插入,表的字段必须与要导入的文件中的字段数量一致 BULK INSERT tt FROM 'c:\t.txt' --扩展名为.csv的文件也可以 WITH( FIELDTERMINATOR = ',', --字段分隔符 ROWTERMINATOR = '\n') --行终止符 --5.2.3 SELECT * FROM tt /*==================================================== 5.3用逗号分隔字段，导入txt和csv格式的文件 在32位系统上通过openrowset函数，用Microsoft Text Driver或 OLE DB Provider for Jet库将txt和CSV数据导入SQL Server表， 但是没有大容量导入优化。 ======================================================*/ --5.3.1csv格式，通过Microsoft Text Driver驱动 select * from openrowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=C:\;', --驱动程序,存放txt或cvs的文件路径 'select * from list.csv') --用文件名称做为表名 --5.3.2txt格式，通过Microsoft Text Driver驱动 select * from openrowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=C:\;', --驱动程序,存放txt或cvs的文件路径 'select * from list.txt') /*=================================================== 如果分隔符不是逗号那么需要定义schema.ini文件。 schema.ini要和数据文件放在同一个目录下面. schema.ini的定义方法： [t.txt] COLNAMEHEADER=TRUE FORMAT=csvdelimited col1=name1 short col2=name2 short col3=name3 short col4=name4 short 第一行:如果文本文件的数据有列名，并且作为查询结果的列名的话， 则ColNameHeader应该为true，否则定义为false， 但是如果后面定义了列名的话，则不使用文本文件中数据的列名，而使用后面定义的列名。 但是如果数据有列名的，最好定义为true，否则的话，数据的列名将作为表中的数据。 第二行:format定义数据的分隔符。 一般的分隔符：空格，制表符，逗号。 分别对应：delimited( )(注意括号里应该有一个空格),tabdelimited,csvdelimited。 在选择分隔符时，建议使用tab，因为数据看起来比较整齐，但不能为保证数据的严格整齐， 而在数据当中使用多个tab，这样会导致读取数据时出现错误。其他的逗号和空格同样。 第三行到最后:指定每一列的列名、数据类型、字符集(使用ansi或者oem)、数据类型转换。 jet数据类型：bit,byte,short,long,currency, single,double,datetime,text,momo ODBC数据类型：char,float,int,longchar,date. 后面两个如果没有特殊情况的话可以不写，默认即可。 ==========================================================================*/ --5.3.3txt格式，通过OLE DB Provider for Jet select * from openrowset('microsoft.jet.oledb.4.0', 'text; hdr=yes; database=c:\', --文件的路径 t#txt) --格式为：文件名称#扩展名 --5.3.4csv格式，通过OLE DB Provider for Jet select * from openrowset('microsoft.jet.oledb.4.0', 'text; hdr=yes; database=c:\', --文件的路径 list#csv) --格式为：文件名称#扩展名
6、Access
--6.Access --6.1建立连接服务器 exec sp_addlinkedserver @server ='ACCES***v', @srvproduct ='Access', @provider = 'Microsoft.Jet.OLEDB.4.0', @datasrc = 'c:\t.mdb' /*=========================================== 1.若要访问不安全的 Access 数据库， 尝试访问Access数据库的SQL Server登录名, 应将登录映射定义为用户名Admin，且不带密码: exec sp_addlinkedsrvlogin @rmtsrvname = 'ACCES***v', @userself = false, @locallogin = 'Joe', @rmtuser = 'Admin', @rmtpassword = NULL 2.若要访问安全的Access数据库，请使用注册表编辑器配置注册表， 以使用Access所用的正确的工作组信息文件。 使用注册表编辑器, 把Access使用工作组信息文件的完整路径名添加到下面注册表项： HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\SystemDB 配置完注册表项后，创建本地登录名到Access登录名的登录名映射： EXEC sp_addlinkedsrvlogin 'ACCES***v', false, 'Joe', 'AccessUser', --用户名 '034fhd99dl' --密码 ===================================================================*/ SELECT * FROM [ACCES***v]...wc --6.2使用12.0库导入access数据 SELECT * FROM OPENROWSET('microsoft.ace.oledb.12.0', --库名称 'C:\t.accdb';'admin';'', --'路径和文件名称';'用户名';'密码' e); --表名 --6.3使用4.0库导入access数据 SELECT * FROM OPENROWSET('microsoft.jet.oledb.4.0', --库名称 'C:\t.mdb';'admin';'', --'路径和文件名称';'用户名';'密码' 'select * from wc'); --表名
--可以执行存储过程 --在调用openrowset等函数，注意参数格式，以及参数之间的字符的格式。 SELECT * FROM OPENROWSET('SQLOLEDB', 'Server=PC0627JVC\MSSQLSERVER2008;Trusted_Connection=yes;database=master', 'exec sp_lock') SELECT * FROM OPENROWSET('SQLOLEDB', 'Server=PC0627JVC\MSSQLSERVER2008;uid=sa;pwd=xxx;', 'exec master.dbo.sp_lock')
转载于:https://blog.51cto.com/yupeigu/1367981
894
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
