OPENDATASOURCE (Transact-SQL)OPENDATASOURCE (Transact-SQL)

适用对象:是SQL Server 是Azure SQL 数据库(仅限托管实例)否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

在四部分对象名称中提供连接信息,而不使用链接服务器名称。Provides ad hoc connection information as part of a four-part object name without using a linked server name.

链接图标 Transact-SQL 语法约定link icon Transact-SQL Syntax Conventions

语法Syntax

OPENDATASOURCE ( 'provider_name', 'init_string' )  

参数Arguments

'provider_name''provider_name'
注册为用于访问数据源的 OLE DB 访问接口的 PROGID 的名称。Is the name registered as the PROGID of the OLE DB provider used to access the data source. provider_name 的数据类型为 char,并且没有默认值 。provider_name is a char data type, with no default value.

重要

以前的 Microsoft OLE DB Provider for SQL Server (SQLOLEDB) 和 SQL Server Native Client OLEDB 提供程序 (SQLNCLI) 仍然不推荐使用,不建议在新的开发工作中使用它们。The previous Microsoft OLE DB Provider for SQL Server (SQLOLEDB) and SQL Server Native Client OLE DB provider (SQLNCLI) remain deprecated and it is not recommended to use either for new development work. 相反,请使用新的 Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL),其将使用最新的服务器功能进行更新。Instead, use the new Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) which will be updated with the most recent server features.

'init_string''init_string'
连接字符串,该字符串将要传递给目标提供程序的 IDataInitialize 接口。Is the connection string passed to the IDataInitialize interface of the destination provider. 提供程序字符串语法是以关键字值对为基础的,这些关键字值对由分号隔开,例如:'keyword1=value ; keyword2=value' 。The provider string syntax is based on keyword-value pairs separated by semicolons, such as: 'keyword1=value ; keyword2=value'.

若要了解提供程序上支持的特定关键字值对,请参阅 MicrosoftMicrosoft Data Access SDK。For specific keyword-value pairs supported on the provider, see the MicrosoftMicrosoft Data Access SDK. 该文档定义了基本语法。This documentation defines the basic syntax. 下表列出了 init_string 参数中最常用的关键字 。The following table lists the most frequently used keywords in the init_string argument.

关键字Keyword OLE DB 属性OLE DB property 有效值和说明Valid values and description
数据源Data Source DBPROP_INIT_DATASOURCEDBPROP_INIT_DATASOURCE 要连接的数据源的名称。Name of the data source to connect to. 不同的提供程序用不同的方法对此进行解释。Different providers interpret this in different ways. 对于 SQL ServerSQL Server Native Client OLE DB 访问接口,这指示服务器的名称。For SQL ServerSQL Server Native Client OLE DB provider, this indicates the name of the server. 对于 Jet OLE DB 访问接口来说,这指示 .mdb 文件或 .xls 文件的完整路径。For Jet OLE DB provider, this indicates the full path of the .mdb file or .xls file.
位置Location DBPROP_INIT_LOCATIONDBPROP_INIT_LOCATION 要连接的数据库的位置。Location of the database to connect to.
扩展属性Extended Properties DBPROP_INIT_PROVIDERSTRINGDBPROP_INIT_PROVIDERSTRING 提供程序特定的连接字符串。The provider-specific connect-string.
连接超时Connect timeout DBPROP_INIT_TIMEOUTDBPROP_INIT_TIMEOUT 达到此超时值后,连接尝试就会失败。Time out value after which the connection try fails.
用户 IDUser ID DBPROP_AUTH_USERIDDBPROP_AUTH_USERID 用于该连接的用户 ID。User ID to be used for the connection.
PasswordPassword DBPROP_AUTH_PASSWORDDBPROP_AUTH_PASSWORD 用于该连接的密码。Password to be used for the connection.
目录Catalog DBPROP_INIT_CATALOGDBPROP_INIT_CATALOG 连接到数据源时的初始或默认的目录名称。The name of the initial or default catalog when connecting to the data source.
集成安全性Integrated Security DBPROP_AUTH_INTEGRATEDDBPROP_AUTH_INTEGRATED SSPI,指定 Windows 身份验证SSPI, to specify Windows Authentication

RemarksRemarks

OPENROWSET 始终继承实例排序规则,而不考虑列的排序规则集。OPENROWSET always inherits the instance collation, irrespective of collation set for columns.

仅当 DisallowAdhocAccess 注册表选项针对指定的提供程序显式设置为 0,并且启用 Ad Hoc Distributed Queries 高级配置选项时,OPENDATASOURCE 才可用于访问 OLE DB 数据源中的远程数据。OPENDATASOURCE can be used to access remote data from OLE DB data sources only when the DisallowAdhocAccess registry option is explicitly set to 0 for the specified provider, and the Ad Hoc Distributed Queries advanced configuration option is enabled. 如果未设置这些选项,则默认行为不允许即席访问。When these options are not set, the default behavior does not allow for ad hoc access.

OPENDATASOURCE 函数可以在能够使用链接服务器名称的相同 Transact-SQLTransact-SQL 语法位置中使用。The OPENDATASOURCE function can be used in the same Transact-SQLTransact-SQL syntax locations as a linked-server name. 因此,可以将 OPENDATASOURCE 用作四部分名称的第一部分,该部分名称引用 SELECT、INSERT、UPDATE 或 DELETE 语句中的表或视图的名称;或者引用 EXECUTE 语句中的远程存储过程。Therefore, OPENDATASOURCE can be used as the first part of a four-part name that refers to a table or view name in a SELECT, INSERT, UPDATE, or DELETE statement, or to a remote stored procedure in an EXECUTE statement. 当执行远程存储过程时,OPENDATASOURCE 应该引用 SQL ServerSQL Server 的另一个实例。When executing remote stored procedures, OPENDATASOURCE should refer to another instance of SQL ServerSQL Server. OPENDATASOURCE 不接受参数变量。OPENDATASOURCE does not accept variables for its arguments.

OPENROWSET 函数类似,OPENDATASOURCE 应该只引用那些不经常访问的 OLE DB 数据源。Like the OPENROWSET function, OPENDATASOURCE should only reference OLE DB data sources that are accessed infrequently. 对于访问次数较频繁的任何数据源,请为它们定义链接服务器。Define a linked server for any data sources accessed more than several times. 无论 OPENDATASOURCE,还是 OPENROWSET,都不能提供链接服务器定义的全部功能,如安全管理以及目录信息查询功能。Neither OPENDATASOURCE nor OPENROWSET provides all the functionality of linked-server definitions, such as security management and the ability to query catalog information. 每次调用 OPENDATASOURCE 时,都必须提供所有的连接信息(包括密码)。All connection information, including passwords, must be provided every time that OPENDATASOURCE is called.

重要

Windows 身份验证比 SQL ServerSQL Server 身份验证要安全得多。Windows Authentication is much more secure than SQL ServerSQL Server Authentication. 应尽量使用 Windows 身份验证。You should use Windows Authentication whenever possible. OPENDATASOURCE 不应该用于连接字符串中的显式密码。OPENDATASOURCE should not be used with explicit passwords in the connection string.

每个提供程序的连接要求与创建链接服务器时的参数要求相似。The connection requirements for each provider are similar to the requirements for those parameters when creating linked servers. sp_addlinkedserver (Transact-SQL) 一文中列出了很多常见提供程序的详细信息。The details for many common providers are listed in the article sp_addlinkedserver (Transact-SQL).

FROM 子句中对 OPENDATASOURCEOPENQUERYOPENROWSET 的任何调用与对用作更新目标的这些函数的任何调用都是分开独立计算的,即使为两个调用提供的参数相同也是如此。Any call to OPENDATASOURCE, OPENQUERY, or OPENROWSET in the FROM clause is evaluated separately and independently from any call to these functions used as the target of the update, even if identical arguments are supplied to the two calls. 具体而言,应用到上述任一调用的结果的筛选器或联接条件不会影响其他调用的结果。In particular, filter or join conditions applied on the result of one of those calls has no effect on the results of the other.

权限Permissions

任何用户都可以执行 OPENDATASOURCE。Any user can execute OPENDATASOURCE. 用于连接到远程服务器的权限由连接字符串确定。The permissions that are used to connect to the remote server are determined from the connection string.

示例Examples

A.A. 将 OPENDATASOURCE 与 SELECT 和 SQL Server OLE DB 驱动程序配合使用Using OPENDATASOURCE with SELECT and the SQL Server OLE DB Driver

以下示例使用 Microsoft OLE DB Driver for SQL Server 访问远程服务器 Seattle1AdventureWorks2012AdventureWorks2012 数据库中的 HumanResources.Department 表。The following example uses the Microsoft OLE DB Driver for SQL Server to access the HumanResources.Department table in the AdventureWorks2012AdventureWorks2012 database on the remote server Seattle1. 使用 SELECT 语句定义返回的行集。A SELECT statement is used to define the row set returned. 访问接口字符串包含 ServerTrusted_Connection 关键字。The provider string contains the Server and Trusted_Connection keywords. 这些关键字由 SQL ServerSQL Server OLE DB 驱动程序识别。These keywords are recognized by the SQL ServerSQL Server OLE DB Driver.

SELECT GroupName, Name, DepartmentID  
FROM OPENDATASOURCE('MSOLEDBSQL', 'Server=Seattle1;Database=AdventureWorks2016;TrustServerCertificate=Yes;Trusted_Connection=Yes;').HumanResources.Department  
ORDER BY GroupName, Name;  

B.B. 将 OPENDATASOURCE 与 SELECT 和 SQL Server OLE DB 提供程序配合使用Using OPENDATASOURCE with SELECT and the SQL Server OLE DB Provider

以下示例将创建与服务器 Payroll 上的 SQL ServerSQL Server 实例 London 的即席连接,并查询 AdventureWorks2012.HumanResources.Employee 表。The following example creates an ad hoc connection to the Payroll instance of SQL ServerSQL Server on server London, and queries the AdventureWorks2012.HumanResources.Employee table.

备注

使用 SQLNCLI 会将 SQL ServerSQL Server 重定向至 SQL Server Native Client OLE DB 提供程序的最新版本。Using SQLNCLI will redirect SQL ServerSQL Server to the latest version of SQL Server Native Client OLE DB Provider. OLE DB 提供程序应以指定的 PROGID 在注册表中注册。The OLE DB provider is expected to be registered with the specified PROGID in the registry.

重要

SQL Server Native Client OLE DB 提供程序 (SQLNCLI) 仍然不推荐使用,不建议在新的开发工作中使用它。SQL Server Native Client OLE DB provider (SQLNCLI) remains deprecated and it is not recommended to use it for new development work. 相反,请使用新的 Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL),其将使用最新的服务器功能进行更新。Instead, use the new Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) which will be updated with the most recent server features.

SELECT *  
FROM OPENDATASOURCE('SQLNCLI',  
    'Data Source=London\Payroll;Integrated Security=SSPI')  
    .AdventureWorks2012.HumanResources.Employee;  

C.C. 使用 Microsoft OLE DB Provider for JetUsing the Microsoft OLE DB Provider for Jet

以下示例以 1997 - 2003 格式创建与 Excel 电子表格的即席连接。The following example creates an ad hoc connection to an Excel spreadsheet in the 1997 - 2003 format.

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',  
    'Data Source=C:\DataFolder\Documents\TestExcel.xls;Extended Properties=EXCEL 5.0')...[Sheet1$] ;  

另请参阅See Also

OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
sp_addlinkedserver (Transact-SQL)sp_addlinkedserver (Transact-SQL)