技术开发 频道

SQL Server最受欢迎技巧:解读DBA

  2.使用SQL Server的OPENROWSET函数

  你可能常常会需要运行一个ad hoc查询从远程OLE DB数据源提取数据,或者批量向SQL Server表导入数据。在这种情况下,你可以在T-SQL(Transact-SQL,微软对SQL的扩展)中用OPENROWSET函数给数据源传入一个连接串和查询来提取需要的数据。

  你可能常常会需要运行一个ad hoc查询从远程OLE DB数据源提取数据,或者批量向SQL Server表导入数据。在这种情况下,你可以在T-SQL(Transact-SQL,微软对SQL的扩展)中用OPENROWSET函数给数据源传入一个连接串和查询来提取需要的数据。

  你可以使用OPENROWSET函数从任何支持注册OLE DB的数据源获取数据,比如从SQL Server或Access的远程实例中提取数据。如果你用OPENROWSET从SQL Server实例中获取数据,该实例必须配置为允许ad hoc分布式查询。

  要配置远程SQL Server实例支持ad hoc查询,需要使用系统存储过程sp_configure先设置advanced options,再启用Ad Hoc Distributed Queries(ad hoc分布式查询)。请看下面的T-SQL脚本:

  EXEC sp_configure 'show advanced options', 1;

  
GO

  
RECONFIGURE;

  
GO

  
EXEC sp_configure 'Ad Hoc Distributed Queries', 1

  
GO

  
RECONFIGURE;

  
GO

   要注意的是,在运行完存储过程之后,你必须运行“RECONFIGURE”命令。 一旦你配置好了远程SQL Server实例,你就可以对它使用OPENROWSET函数。这个函数可以在SELECT语句的FROM从句里使用。下面的例子显示了该函数的基本语法:

  OPENROWSET('provider', 'connection string', target)

   可以看到,这个函数有三个参数:

  ·Provider —— 某特定数据源支持的OLE DB提供者的人机友好名称(ProgID)。Provider的名字必须用单引号括起来。

  ·Connection string —— 连接串。它是与具体提供者provider相关的字符串,包括连接到给字符串中指定的数据源所需要的细节信息。根据provider的不同,连接串信息需要用一对或多对单引号括起来。

  ·Target —— target参数可以使一个数据库对象或者一个查询。

  ·Object —— 数据库对象的名字,比如表或者视图的名称。对象的完整名字必须提供,它们不需要用单引号括起来。

  ·Query —— query是从远程数据源提取数据的Select语句。Query必须用单引号括起来。

  下面的例子展示了OPENROWSET函数的用法:

  SELECT Employees.*

  
FROM OPENROWSET(

  
'SQLNCLI',

  
'Server=SqlSrv1;Trusted_Connection=yes',

  
'SELECT EmployeeID, FirstName, LastName, JobTitle

  FROM AdventureWorks.HumanResources.vEmployee

  ORDER BY LastName, FirstName
'

  )
AS Employees

   注意该Select语句的FROM从句中使用了OPENROWSET函数和3个参数。第一个参数SQLNCLI是SQL Server OLE DB提供者的名称。

  第二个参数是连接串。对于SQL Server提供者,整个连接串应该被单引号括起来,连接串内的每一组信息用分号分割。在上面的例子中,第一组信息指定了目标服务器SqlSrv1,第二组信息指定了该连接可信任连接。在指定目标Server时,如果实例不是该Server的默认实例,则一定要在连接串中指定实例名。(注意:SQLNCLI提供者还支持其他参数。)

  OPENROWSET函数的最后一个参数是实际执行的Select语句。注意SQL语句中使用了完整对象名来访问视图。

  这样我们就可以使用OPENROWSET函数了。函数返回一个结果集(我把它用AS命名为“Employees”),From使用该结果集的方式与使用其他普通查询的方式一样。 我们在上面提到,你也可以从SQL Server以外的数据源提取数据。例如:下面的Select语句查询微软Access数据库的Employees表。

  SELECT Employees.*

  
FROM OPENROWSET(

  
'Microsoft.Jet.OLEDB.4.0',

  
'C:\Data\Employees.mdb';'admin';' ',

  
'SELECT EmployeeID, FirstName, LastName, JobTitle

  FROM Employees

  ORDER BY LastName, FirstName
'

  )
AS Employees

   你可能注意到了,这次的provider不同于我们在访问SQL Server时使用的Provider。在本例中,Provider是Microsoft.Jet.OLEDB.4.0(注意:对于Access 2007,有新的Provider可用)。

  连接串与前面例子中的写法也不一样。整个连接串从头到尾分成了三部分,每一部分都被单引号单独括起来,各部分之间用分号分割。

  第一部分指定了Access数据库文件的路径和文件名,后面紧跟着是用户账号admin(Access数据库内部的管理员账号)。第三部分是一个空字符串,是Access数据库的密码。因为admin账号没有设定密码,所以使用空字符串。如果该账号设置了密码,应该把密码写在第三部分。

  整个连接串与后面用来从Access数据库查询数据的Select语句用逗号“,”隔开。(我在Access中使用的Employees表是从SQL Server的vEmployee视图导入的) 这就是从Access数据库查询数据要做的全部事情。你的查询会返回一个结果集,该结果集与访问本地SQL Server数据库时得到的结果集类似。

  你也可以使用OPENROWSET函数从多个数据源中查询数据。例如:下面的例子我使用inner join(内连接)从远程SQL Server实例和Access数据库查询数据。

  SELECT e1.EmployeeID, e2.FirstName, e2.LastName, e1.JobTitle

  
FROM OPENROWSET(

  
'SQLNCLI',

  
'Server=SqlSrv1;Trusted_Connection=yes;',

  
'SELECT EmployeeID, FirstName, LastName, JobTitle

  FROM AdventureWorks.HumanResources.vEmployee
'

  )
AS e1

  
INNER JOIN OPENROWSET(

  
'Microsoft.Jet.OLEDB.4.0',

  
'C:\Data\Employees.mdb'; 'admin';' ',

  
'SELECT EmployeeID, FirstName, LastName, JobTitle

  FROM Employees
'

  )
AS e2

  
ON e1.EmployeeID = e2.EmployeeID

  
ORDER BY e2.LastName, e2.FirstName

   注意:外层的Select语句从两个表返回数据——从SQL Server返回员工ID和工作头衔,从Access数据库返回姓和名。由于你可以得到可靠的连接查询,尽管你是从本地SQL Server实例连接表中查询的数据,你可以处理这些数据。

  现在我们来看看OPENROWSET函数的另一个重要功能——批量导入。为了举例需要,我在AdventureWorks数据库中用下面的脚本创建了表Employees并导入数据。

  USE AdventureWorks

  
GO

  
IF OBJECT_ID (N'Employees', N'U') IS NOT NULL

  
DROP TABLE dbo.Employees

  
GO

  
SELECT EmployeeID, FirstName, LastName, JobTitle

  
INTO Employees

  
FROM HumanResources.vEmployee

  
GO

  
ALTER TABLE Employees

  
ADD ResumeFile VARBINARY(MAX) NULL

  
GO

  注意:我没有把ResumeFile列的数据导入,它的数据类型是VARBINARY(MAX)。我会用下面的Update语句把Employee1.docx文件作为二进制数据批量导入到该列。

  USE AdventureWorks

  
GO

  
UPDATE Employees

  
SET ResumeFile = (

  
SELECT *

  
FROM OPENROWSET(BULK 'C:\Data\Employee1.docx', SINGLE_BLOB)

  
AS ResumeContent)

  
WHERE EmployeeID = 1

  可以看到,OPENROWSET函数提供了BULK选项,你可以用它来导入数据。要使用BULK选项,需要指定你想要导入的文件,并指定导入方式。既然我想把文件以二进制形式导入,我在上面的例子中使用了SINGLE_BLOB选项。当然,如果该列支持字符型数据,我也可以用SINGLE_CLOB或者SINGLE_NCLOB选项指定数据存储为字符类型格式。此外,在使用OPENROWSET函数批量导入数据功能时,你也可以使用格式化的文件,不过关于格式化文件的用法超出了本文讨论的范围。

  不管你是否使用OPENROWSET函数批量导入数据或者连接OLE DB数据源,你都会发现用它获取数据非常方便。关于这个函数的更多细节,请在SQL Server联机图书查看标题为“OPENROWSET (Transact-SQL)”的文章。在那里你可以看到关于SQLNCLI提供者(或者SQL Server 2008的SQLNCL10提供者)的更多细节。

0
相关文章