sqlserver opendatasource 调用函数

SQLServer读取及导⼊Excel数据

⼀、引⾔

使⽤SQL Server的OPENROWSET及OPENDATASOURCE函数，可以像查询数据表⼀样来读取Excel数据。但是，要想让这两个函数能正常运⾏，可不是那么容易，假如没理解或没配置好的话，⼀路的报错会让你怀疑⼈⽣。⼆、配置

2.1、组件安装

要想使⽤OPENROWSET及OPENDATASOURCE函数来读取Excel数据，⾸先要在⽬标的SQL Server主机上安装AccessDatabaseEngine组件。

1）换句话说：假如要操作的数据库是在本地的，那我在本地安装AccessDatabaseEngine即可；假如要操作的数据库安装在远程的服务器上，那么需在远程的服务器上安装

AccessDatabaseEngine。

2）需要说明的是，读取Excel数据，只需安装AccessDatabaseEngine，并不⼀定要安装Office。

3）依⽬标的SQL Server主机的操作系统位数，来对应安装AccessDatabaseEngine版本。本处Excel是2013版本(.xlsx)，需安装Microsoft Access Database Engine 2010

Redistributable。

2.2、服务配置

在⽬标的SQL Server主机上，Win+R调出运⾏，输⼊services.msc调出服务。将SQL Server (MSSQLSERVER)、SQL Full-text Filter Daemon Launcher (MSSQLSERVER)两个服务的登录⾝份，改为本地系统账户。

2.3、参数配置

在⽬标的SQL Server上打开查询分析器，执⾏以下语句：

--1、开启导⼊功能（查看参数：exec sp_configure）

exec sp_configure 'show advanced options',1

reconfigure

exec sp_configure 'Ad Hoc Distributed Queries',1

sql server使用OpenRowSet函数查询数据

来源：原创 作者：小人物 录入时间：2009-10-30

内容导读：sql server使用OpenRowSet函数查询数据:OpenRowSet( )函数中包含访问OLE DB数据源中的远程数据所需的全部连接信息。当访问链接服务器中的表时，这种方法是一种替代方法，并且是一种使用OLE

使用OpenRowSet函数查询数据

OpenRowSet( )函数中包含访问OLE DB数据源中的远程数据所需的全部连接信息。当访问链接服务器中的表时，这种方法是一种替代方法，并且是一种使用OLE DB连接并访问远程数据的特殊方法。该函数可以在查询的FROM子句中，像引用表名那样引用OpenRowSet函数。

语法：

OPENROWSET ( 'provider_name'

, { 'datasource' ; 'user_id' ; 'password' | 'provider_string' }

, { [ catalog.] [ schema.] object | 'query' } )

参数说明：

provider_name：注册为用于访问数据源的OLE DB提供程序的PROGID的名称。

datasource：字符串常量，它对应着某个特定的OLE DB数据源。datasource是将被传递到提供程序IDBProperties接口以初始化提供程序的DBPROP_INIT_DATASOURCE 属性。通常，这个字符串包含数据库文件的名称、数据库服务器的名称，或者提供程序能理解的用于查找数据库的名称。

user_id：字符串常量，它是传递到指定OLE DB提供程序的用户名。

password：字符串常量，它是将被传递到OLE DB提供程序的用户密码。

provider_string：提供程序特定的连接字符串，将它作为DBPROP_INIT_PROVIDERSTRING属性传递进来以初始化 OLE DB 提供程序。

sqlserver存储过程跨库查询

实践环境：SQL SERVER 2008R2

⼀般跨库查询： select * from [远程IP].库名.dbo.表名/存储过程

正常存储过程如果在数据库管理器的链接服务器地址中添加了远程IP，是没问题的，但是如果创建作业定时执⾏任务就报如下错误：

已以⽤户 NT AUTHORITY\NETWORK SERVICE 的⾝份执⾏。 链接服务器 "远程IP" 的 OLE DB 访问接⼝ "SQLNCLI10" 报错。⾝份验证

失败。 [SQLSTATE 42000] (错误 7399) ⽆法初始化链接服务器 "远程IP" 的 OLE DB 访问接⼝ "SQLNCLI10" 的数据源对象。 [SQLSTATE 42000] (错误 7303) 链接服务器"远程IP"的 OLE DB 访问接⼝ "SQLNCLI10" 返回了消息 "⽆效的授权说明"。

[SQLSTATE 01000] (错误 7412). 该步骤失败。

针对此报错可以使⽤下⾯跨库查询⽅法：

select * from OPENDATASOURCE('SQLOLEDB','Data Source=远程IP;User ID=sa;Password=sasa').库名.dbo.表名/存储过程

注意：

有的服务器因为安全问题可能会关闭⼀些组件，会报如下错误：

已以⽤户 NT AUTHORITY\NETWORK SERVICE 的⾝份执⾏。 SQL Server 阻⽌了对组

件 'Ad Hoc Distributed Queries' 的 STATEMENT 'OpenRowset/OpenDatasource' 的访问，因为此组件已作为此服务器安全配置的⼀部分⽽

被关闭。系统管理员可以通过使⽤ sp_configure 启⽤ 'Ad Hoc Distributed Queries'。有关启⽤ 'Ad Hoc Distributed Queries' 的详细信息，请

SQLserver⾃定义函数FUNCTION的使⽤

⼀.标量值函数、内联表值函数、多语句表值函数举例说明

1、标量值函数（返回⼀个标量值）

CREATE FUNCTION dbo.func_date_get_name(@date_into varchar(8))

--CREATE FUNCTION 函数名称（@参数名 参数的数据类型）

RETURNS varchar(20) --返回返回值的数据类型

--[WITH ENCRYPTION] --如果指定了 encryption 则函数被加密

as

BEGIN

declare @result_name varchar(20)

select @result_name = Value_name from test_ceshi where statdate = @date_into

RETURN @result_name

END

--select dbo.func_date_get_name('20180808') name;

--select * from test_ceshi;

2、内联表格值函数定义格式：

特点：内联表格值函数⽀持在WHERE⼦句中使⽤参数

CREATE FUNCTION dbo.func_date_get_table(@date_into varchar(8))

RETURNS table

--[WITH ENCRYPTION] --如果指定了 encryption 则函数被加密

as

RETURN select statdate,Value_name from test_ceshi where statdate = @date_into

--select * from dbo.func_date_get_table('20180808') ;

3、多语句表值函数定义格式：

多语句表值函数跟内联表值函数都是表值函数，它们返回的结果都是Table类型。多语句表值函数通过多条语句来创建Table类型的数据。这