SQLserver2005 使用openquery访问远程数据_跨服务器查询(SQL SERVER DBLINK)

跨服务器查询(SQL SERVER DBLINK)  

首先要开启一个选项

启用Ad Hoc Distributed Queries：
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
    使用完成后，关闭Ad Hoc Distributed Queries：
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

--跨服务器查询如下：
SELECT a.*,b.stor_Name
from OPENROWSET('MSDASQL',
   'DRIVER={SQL Server};SERVER=tom;UID=sa;PWD=123',
   pubs.dbo.authors) AS a,stores b
ORDER BY a.au_lname, a.au_fname
--其中，tom为远程服务器名，stores 是本机数据库pubs中的表
--需要注意的是若二个表中的记录数目不同会导致某一个表产生完全重复的行，
--得到的记录集的行数为最长的那个表中的行数
如:
insert into dbo.Kqmx_200704
select *
from openrowset('MSDASQL',
   'DRIVER={SQL Server};SERVER=192.168.1.253;UID=sa;PWD=',
   one.dbo.Kqmx_200704)
另:
连接远程服务器进行数据查询时可以这么做：select * from [**.**.**.**].test.dbo.t1
不过，不出意外的话会报错：在 sysservers 中未能找到服务器 '**.*.**.**'。请执行 sp_addlinkedserver 以将服务器添加到 sysservers。
添加sysservers：exec   sp_addlinkedserver     'srv_lnk','','SQLOLEDB','**.*.**.**'
再查询：select * from srv_lnk.test.dbo.t1 （因为定义了“别名”，所以这儿“别名”）
除非两个服务器上的该用户的密码一样，否则会报：用户 '**' 登录失败。
指定登录用户：exec   sp_addlinkedsrvlogin   'srv_lnk','false',null,'rUser','rPwd'  
再查询：select * from srv_lnk.test.dbo.t1，如果无意外的话就应该是成功的
sp_addlinkedserver
创建一个链接的服务器，使其允许对分布式的、针对 OLE DB 数据源的异类查询进行访问。在使用 sp_addlinkedserver 创建链接的服务器之后，此服务器就可以执行分布式查询。如果链接服务器定义为 Microsoft? SQL Server?，则可执行远程存储过程。
Exec sp_droplinkedsrvlogin server,Null
Exec sp_dropserver server
EXEC sp_addlinkedserver
@server= 'server ',--被访问的服务器别名
@srvproduct= ' ',
@provider= 'SQLOLEDB ',
@datasrc= '10.23.11.28,3342 ' --要访问的服务器
EXEC sp_addlinkedsrvlogin
'server ', --被访问的服务器别名
'false ',
NULL,
'la0001 ', --帐号
'aaaaaa ' --密码
 
链接服务器没问题,在查询分析器里执行例如:select * from [192.168.0.119].fash.dbo.vwAllUser没问题.
但如果想建立一个简单的存储过程

SQLserver2005 使用openquery访问远程数据

select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;IMEX=2;DATABASE=D:\testdata\test01.xls',[sheet1$])
初次使用该语句是都会出现这样的错错误

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries'
because this component is turned off as part of the security configuration for this server.
A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure.
For more information about enabling 'Ad Hoc Distributed Queries',
see "Surface Area Configuration" in SQL Server Books Online.

 

 这时只需要将enable openrowset ans spendatasource选项勾上即可

测试数据如图所示

   OpenRowSet

1、向Excel查询

        方法（1）

select * from
       OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=yes;database=D:\testdata\test01.xls;','select * from [Sheet1$] where   sname     like ''%l%''')

方法（2）

select * from
      OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=yes;database=D:\testdata\test01.xls;','select * from [Sheet1$] ')

where sname like '%l%'
1)hdr=yes时可以把xls的第1行作为字段看待，如第1个中hdr=no的话，where时就会报错
2)[]和美圆$必须要，否则M$可不认这个账

2、修改Execl

update
 OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=yes;database=D:\testdata\test01.xls;','select * from [Sheet1$]')
 set ssex='f' where sname like '%l%'

 

3、导入导出

insert into
 OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=yes;database=D:\testdata\test01.xls;','select * from [Sheet1$]')
select s#,sname,ssex from student

/*=================== 导入/导出 excel 的基本方法 ===================*/

从excel文件中,导入数据到sql数据库中,很简单,直接用下面的语句:

/*===================================================================*/
--如果接受数据导入的表已经存在
insert into student(s#,sname,ssex)
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;IMEX=2;DATABASE=D:\testdata\test01.xls',[sheet1$])

--如果导入数据并生成表
select* into aa from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;IMEX=2;DATABASE=D:\testdata\test01.xls',[sheet1$])

/*===================================================================*/
--如果从sql数据库中,导出数据到excel,如果excel文件已经存在,而且已经按照要接收的数据创建好表头

,就可以简单的用:
insert into
 OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=yes;database=D:\testdata\test01.xls;','select * from [Sheet1$]')
select * from 表名
opendatasource

(1)查看远程数据

SELECT * 
FROM opendatasource( 'SQLOLEDB ', 'Data Source=hpsu;User ID=sa;Password=' ).test.dbo.testtable

（2）向远程表中添加数据

insert opendatasource( 'SQLOLEDB ', 'Data Source=hpsu;User ID=sa;Password=' ).test.dbo.testtable 
select * from a

 远程数据的其他访问方法参见SQLserver2005 使用openquery访问远程数据

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

I get this error message from SQL server 2005, How can I change security configuration?
You can use sp_configure to change this.  First you have to "show advanced options", then you have to turn on "ad hoc distributed queries".  Like this:

sp_configure 'show advanced options', 1  

RECONFIGURE  

GO  

sp_configure  'ad hoc distributed queries', 1  

RECONFIGURE  

GO