Microsoft OLE DB Provider for SQL Server
适用于：Access 2013、Office 2013
Microsoft OLE DB Provider for SQL Server (SQLOLEDB) 允许 ADO 访问 Microsoft SQL Server。
连接字符串参数
若要连接到此提供程序，请将 ConnectionString 属性的 Provider 参数设置为：
SQLOLEDB
也可以使用 Provider 属性设置或读取此值。
典型的连接字符串
此提供程序典型的连接字符串为：
"Provider=SQLOLEDB;Data Source=serverName;"
Initial Catalog=databaseName;
User ID=userName;Password=userPassword;"
该字符串由以下关键字组成：
关键字 |
说明 |
|---|---|
Provider |
指定 OLE DB Provider for SQL Server。 |
Data Source 或 Server |
指定服务器的名称。 |
Initial Catalog 或 Database |
指定服务器上的数据库的名称。 |
User ID 或 uid |
指定用户名（用于 SQL Server 身份验证）。 |
Password 或 pwd |
指定用户密码（用于 SQL Server 身份验证）。 |
提供程序特定的连接参数
除了支持 ADO 定义的那些参数以外，该提供程序还支持几个提供程序特定的连接参数。 与 ADO 连接属性一样，可以通过 Connection 的 Properties 集合或将其作为 ConnectionString 的一部分来设置这些提供程序特定的属性。
参数 |
说明 |
|---|---|
Trusted_Connection |
Indicates the user authentication mode. This can be set to Yes or No. The default value is No. If this property is set to Yes, then SQLOLEDB uses Microsoft Windows NT Authentication Mode to authorize user access to the SQL Server database specified by the Location and Datasource property values. If this property is set to No, then SQLOLEDB uses Mixed Mode to authorize user access to the SQL Server database. The SQL Server login and password are specified in the User Id and Password properties. |
Current Language |
指示 SQL Server 语言名称。 标识用于选择和设置系统消息格式的语言。 必须在 SQL Server 上安装该语言，否则打开连接时就会失败。 |
Network Address |
指示通过 Location 属性指定的 SQL Server 的网络地址。 |
Network Library |
指示用于与 SQL Server 进行通信的网络库（动态链接库）的名称。 此名称不应包括路径或 .dll 文件扩展名。 默认值由 SQL Server 客户端配置提供。 |
Use Procedure for Prepare |
确定在准备（通过 Prepared 属性）Command 时，SQL Server 是否创建临时的存储过程。 |
Auto Translate |
Indicates whether OEM/ANSI characters are converted. This property can be set to True or False. The default value is True. If this property is set to True, then SQLOLEDB performs OEM/ANSI character conversion when multi-byte character strings are retrieved from, or sent to, the SQL Server. If this property is set to False, then SQLOLEDB does not perform OEM/ANSI character conversion on multi-byte character string data. |
Packet Size |
指示网络数据包的大小（以字节为单位）。 数据包大小属性值必须介于 512 与 32767 之间。 默认的 SQLOLEDB 网络数据包大小为 4096。 |
Application Name |
指示客户端应用程序名称。 |
Workstation ID |
标识工作站的字符串。 |
Command 对象用法
SQLOLEDB 接受 ODBC、ANSI 以及 SQL Server 特定 Transact-SQL 的混合形式作为有效语法。 例如，以下 SQL 语句使用 ODBC SQL 转义序列指定 LCASE 字符串函数：
SELECT customerid={fn LCASE(CustomerID)} FROM Customers
LCASE 将返回一个字符串，且将所有的大写字符都转换为小写字符等效项。 ANSI SQL 字符串函数 LOWER 执行相同的操作，因此，以下 SQL 语句是与上面显示的 ODBC 语句等效的 ANSI 语句：
SELECT customerid=LOWER(CustomerID) FROM Customers
SQLOLEDB 将成功处理指定为命令文本的这两种语句形式。
存储过程
使用 SQLOLEDB 命令执行 SQL Server 存储过程时，请在该命令文本中使用 ODBC 过程调用转义序列。 随后，SQLOLEDB 将使用 SQL Server DE 的远程过程调用机制来优化命令处理。 例如，以下 ODBC SQL 语句优于 Transact-SQL 形式的命令文本：
ODBC SQL
{call SalesByCategory('Produce', '1995')}
Transact-SQL
EXECUTE SalesByCategory 'Produce', '1995'
Recordset 行为
SQLOLEDB 无法使用 SQL Server 游标来支持通过许多命令生成的多个结果。 如果用户请求需要 SQL Server 游标支持的记录集，则当使用的命令文本生成多个记录集作为结果时，将会发生错误。
SQL Server 游标支持可滚动的 SQLOLEDB 记录集。 SQL Server 将针对对数据库的其他用户所做更改敏感的游标强制施加限制。 具体来说，某些游标中的行不能进行排序，尝试使用包含 SQL ORDER BY 子句的命令创建记录集可能会失败。
动态属性
Microsoft OLE DB Provider for SQL Server 将多个动态属性插入到未打开的 Connection、Recordset 和 Command 对象的 Properties 集合中。
下面的表是每个动态属性的 ADO 和 OLE DB 名称的交叉索引。 OLE DB 程序员参考通过术语“说明”引用 ADO 属性名称。可以在 OLE DB 程序员参考中找到有关这些属性的详细信息。 请在“索引”中搜索 OLE DB 属性名，或者请参阅“附录 C：OLE DB 属性”。
Connection 动态属性
以下属性将被添加到 Connection 对象的 Properties 集合中。
ADO 属性名 |
OLE DB 属性名 |
|---|---|
Active Sessions |
DBPROP_ACTIVESESSIONS |
Asynchable Abort |
DBPROP_ASYNCTXNABORT |
Asynchable Commit |
DBPROP_ASYNCTNXCOMMIT |
Autocommit Isolation Levels |
DBPROP_SESS_AUTOCOMMITISOLEVELS |
Catalog Location |
DBPROP_CATALOGLOCATION |
Catalog Term |
DBPROP_CATALOGTERM |
Column Definition |
DBPROP_COLUMNDEFINITION |
Connect Timeout |
DBPROP_INIT_TIMEOUT |
Current Catalog |
DBPROP_CURRENTCATALOG |
Data Source |
DBPROP_INIT_DATASOURCE |
Data Source Name |
DBPROP_DATASOURCENAME |
Data Source Object Threading Model |
DBPROP_DSOTHREADMODEL |
DBMS Name |
DBPROP_DBMSNAME |
DBMS Version |
DBPROP_DBMSVER |
Extended Properties |
DBPROP_INIT_PROVIDERSTRING |
GROUP BY Support |
DBPROP_GROUPBY |
Heterogeneous Table Support |
DBPROP_HETEROGENEOUSTABLES |
Identifier Case Sensitivity |
DBPROP_IDENTIFIERCASE |
Initial Catalog |
DBPROP_INIT_CATALOG |
Isolation Levels |
DBPROP_SUPPORTEDTXNISOLEVELS |
Isolation Retention |
DBPROP_SUPPORTEDTXNISORETAIN |
Locale Identifier |
DBPROP_INIT_LCID |
Maximum Index Size |
DBPROP_MAXINDEXSIZE |
Maximum Row Size |
DBPROP_MAXROWSIZE |
Maximum Row Size Includes BLOB |
DBPROP_MAXROWSIZEINCLUDESBLOB |
Maximum Tables in SELECT |
DBPROP_MAXTABLESINSELECT |
Multiple Parameter Sets |
DBPROP_MULTIPLEPARAMSETS |
Multiple Results |
DBPROP_MULTIPLERESULTS |
Multiple Storage Objects |
DBPROP_MULTIPLESTORAGEOBJECTS |
Multi-Table Update |
DBPROP_MULTITABLEUPDATE |
NULL Collation Order |
DBPROP_NULLCOLLATION |
NULL Concatenation Behavior |
DBPROP_CONCATNULLBEHAVIOR |
OLE DB Version |
DBPROP_PROVIDEROLEDBVER |
OLE Object Support |
DBPROP_OLEOBJECTS |
Open Rowset Support |
DBPROP_OPENROWSETSUPPORT |
ORDER BY Columns in Select List |
DBPROP_ORDERBYCOLUMNSINSELECT |
Output Parameter Availability |
DBPROP_OUTPUTPARAMETERAVAILABILITY |
Pass By Ref Accessors |
DBPROP_BYREFACCESSORS |
Password |
DBPROP_AUTH_PASSWORD |
Persist Security Info |
DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO |
Persistent ID Type |
DBPROP_PERSISTENTIDTYPE |
Prepare Abort Behavior |
DBPROP_PREPAREABORTBEHAVIOR |
Prepare Commit Behavior |
DBPROP_PREPARECOMMITBEHAVIOR |
Procedure Term |
DBPROP_PROCEDURETERM |
Prompt |
DBPROP_INIT_PROMPT |
Provider Friendly Name |
DBPROP_PROVIDERFRIENDLYNAME |
Provider Name |
DBPROP_PROVIDERFILENAME |
Provider Version |
DBPROP_PROVIDERVER |
Read-Only Data Source |
DBPROP_DATASOURCEREADONLY |
Rowset Conversions on Command |
DBPROP_ROWSETCONVERSIONSONCOMMAND |
Schema Term |
DBPROP_SCHEMATERM |
Schema Usage |
DBPROP_SCHEMAUSAGE |
SQL Support |
DBPROP_SQLSUPPORT |
Structured Storage |
DBPROP_STRUCTUREDSTORAGE |
Subquery Support |
DBPROP_SUBQUERIES |
Table Term |
DBPROP_TABLETERM |
Transaction DDL |
DBPROP_SUPPORTEDTXNDDL |
User ID |
DBPROP_AUTH_USERID |
User Name |
DBPROP_USERNAME |
Window Handle |
DBPROP_INIT_HWND |
Recordset 动态属性
以下属性将被添加到 Recordset 对象的 Properties 集合中。
ADO 属性名 |
OLE DB 属性名 |
|---|---|
Access Order |
DBPROP_ACCESSORDER |
Blocking Storage Objects |
DBPROP_BLOCKINGSTORAGEOBJECTS |
Bookmark Type |
DBPROP_BOOKMARKTYPE |
Bookmarkable |
DBPROP_IROWSETLOCATE |
Change Inserted Rows |
DBPROP_CHANGEINSERTEDROWS |
Column Privileges |
DBPROP_COLUMNRESTRICT |
Column Set Notification |
DBPROP_NOTIFYCOLUMNSET |
Command Time Out |
DBPROP_COMMANDTIMEOUT |
Defer Column |
DBPROP_DEFERRED |
Delay Storage Object Updates |
DBPROP_DELAYSTORAGEOBJECTS |
Fetch Backwards |
DBPROP_CANFETCHBACKWARDS |
Hold Rows |
DBPROP_CANHOLDROWS |
IAccessor |
DBPROP_IAccessor |
IColumnsInfo |
DBPROP_IColumnsInfo |
IColumnsRowset |
DBPROP_IColumnsRowset |
IConnectionPointContainer |
DBPROP_IConnectionPointContainer |
IConvertType |
DBPROP_IConvertType |
Immobile Rows |
DBPROP_IMMOBILEROWS |
IRowset |
DBPROP_IRowset |
IRowsetChange |
DBPROP_IRowsetChange |
IRowsetIdentity |
DBPROP_IRowsetIdentity |
IRowsetInfo |
DBPROP_IRowsetInfo |
IRowsetLocate |
DBPROP_IRowsestLocate |
IRowsetResynch |
|
IRowsetScroll |
DBPROP_IRowsetScroll |
IRowsetUpdate |
DBPROP_IRowsetUpdate |
ISequentialStream |
DBPROP_ISequentialStream |
ISupportErrorInfo |
DBPROP_ISupportErrorInfo |
Literal Bookmarks |
DBPROP_LITERALBOOKMARKS |
Literal Row Identity |
DBPROP_LITERALIDENTITY |
Maximum Open Rows |
DBPROP_MAXOPENROWS |
Maximum Pending Rows |
DBPROP_MAXPENDINGROWS |
Maximum Rows |
DBPROP_MAXROWS |
Notification Granularity |
DBPROP_NOTIFICATIONGRANULARITY |
Notification Phases |
DBPROP_NOTIFICATIONPHASES |
Objects Transacted |
DBPROP_TRANSACTEDOBJECT |
Others' Changes Visible |
DBPROP_OTHERUPDATEDELETE |
Others' Inserts Visible |
DBPROP_OTHERINSERT |
Own Changes Visible |
DBPROP_OWNUPDATEDELETE |
Own Inserts Visible |
DBPROP_OWNINSERT |
Preserve on Abort |
DBPROP_ABORTPRESERVE |
Preserve on Commit |
DBPROP_COMMITPRESERVE |
Quick Restart |
DBPROP_QUICKRESTART |
Reentrant Events |
DBPROP_REENTRANTEVENTS |
Remove Deleted Rows |
DBPROP_REMOVEDELETED |
Report Multiple Changes |
DBPROP_REPORTMULTIPLECHANGES |
Return Pending Inserts |
DBPROP_RETURNPENDINGINSERTS |
Row Delete Notification |
DBPROP_NOTIFYROWDELETE |
Row First Change Notification |
DBPROP_NOTIFYROWFIRSTCHANGE |
Row Insert Notification |
DBPROP_NOTIFYROWINSERT |
Row Privileges |
DBPROP_ROWRESTRICT |
Row Resynchronization Notification |
DBPROP_NOTIFYROWRESYNCH |
Row Threading Model |
DBPROP_ROWTHREADMODEL |
Row Undo Change Notification |
DBPROP_NOTIFYROWUNDOCHANGE |
Row Undo Delete Notification |
DBPROP_NOTIFYROWUNDODELETE |
Row Undo Insert Notification |
DBPROP_NOTIFYROWUNDOINSERT |
Row Update Notification |
DBPROP_NOTIFYROWUPDATE |
Rowset Fetch Position Change Notification |
DBPROP_NOTIFYROWSETFETCHPOSISIONCHANGE |
Rowset Release Notification |
DBPROP_NOTIFYROWSETRELEASE |
Scroll Backwards |
DBPROP_CANSCROLLBACKWARDS |
Server Cursor |
DBPROP_SERVERCURSOR |
Skip Deleted Bookmarks |
DBPROP_BOOKMARKSKIPPED |
Strong Row Identity |
DBPROP_STRONGITDENTITY |
Unique Rows |
DBPROP_UNIQUEROWS |
Updatability |
DBPROP_UPDATABILITY |
Use Bookmarks |
DBPROP_BOOKMARKS |
Command 动态属性
以下属性将被添加到 Command 对象的 Properties 集合中。
ADO 属性名 |
OLE DB 属性名 |
|---|---|
Access Order |
DBPROP_ACCESSORDER |
Base Path |
SSPROP_STREAM_BASEPATH |
Blocking Storage Objects |
DBPROP_BLOCKINGSTORAGEOBJECTS |
Bookmark Type |
DBPROP_BOOKMARKTYPE |
Bookmarkable |
DBPROP_IROWSETLOCATE |
Change Inserted Rows |
DBPROP_CHANGEINSERTEDROWS |
Column Privileges |
DBPROP_COLUMNRESTRICT |
Column Set Notification |
DBPROP_NOTIFYCOLUMNSET |
Content Type |
SSPROP_STREAM_CONTENTTYPE |
Cursor Auto Fetch |
SSPROP_CURSORAUTOFETCH |
Defer Column |
DBPROP_DEFERRED |
Defer Prepare |
SSPROP_DEFERPREPARE |
Delay Storage Object Updates |
DBPROP_DELAYSTORAGEOBJECTS |
Fetch Backwards |
DBPROP_CANFETCHBACKWARDS |
Hold Rows |
DBPROP_CANHOLDROWS |
IAccessor |
DBPROP_IAccessor |
IColumnsInfo |
DBPROP_IColumnsInfo |
IColumnsRowset |
DBPROP_IColumnsRowset |
IConnectionPointContainer |
DBPROP_IConnectionPointContainer |
IConvertType |
DBPROP_IConvertType |
Immobile Rows |
DBPROP_IMMOBILEROWS |
IRowset |
DBPROP_IRowset |
IRowsetChange |
DBPROP_IRowsetChange |
IRowsetIdentity |
DBPROP_IRowsetIdentity |
IRowsetInfo |
DBPROP_IRowsetInfo |
IRowsetLocate |
DBPROP_IRowsetLocate |
IRowsetResynch |
DBPROP_IRowsetResynch |
IRowsetScroll |
DBPROP_IRowsetScroll |
IRowsetUpdate |
DBPROP_IRowsetUpdate |
ISequentialStream |
DBPROP_ISequentialStream |
ISupportErrorInfo |
DBPROP_ISupportErrorInfo |
Literal Bookmarks |
DBPROP_LITERALBOOKMARKS |
Literal Row Identity |
DBPROP_LITERALIDENTITY |
Lock Mode |
DBPROP_LOCKMODE |
Maximum Open Rows |
DBPROP_MAXOPENROWS |
Maximum Pending Rows |
DBPROP_MAXPENDINGROWS |
Maximum Rows |
DBPROP_MAXROWS |
Notification Granularity |
DBPROP_NOTIFICATIONGRANULARITY |
Notification Phases |
DBPROP_NOTIFICATIONPHASES |
Objects Transacted |
DBPROP_TRANSACTEDOBJECT |
Others' Changes Visible |
DBPROP_OTHERUPDATEDELETE |
Others' Inserts Visible |
DBPROP_OTHERINSERT |
Output Encoding Property |
DBPROP_OUTPUTENCODING |
Output Stream Property |
DBPROP_OUTPUTSTREAM |
Own Changes Visible |
DBPROP_OWNUPDATEDELETE |
Own Inserts Visible |
DBPROP_OWNINSERT |
Preserve on Abort |
DBPROP_ABORTPRESERVE |
Preserve on Commit |
DBPROP_COMMITPRESERVE |
Quick Restart |
DBPROP_QUICKRESTART |
Reentrant Events |
DBPROP_REENTRANTEVENTS |
Remove Deleted Rows |
DBPROP_REMOVEDELETED |
Report Multiple Changes |
DBPROP_REPORTMULTIPLECHANGES |
Return Pending Inserts |
DBPROP_RETURNPENDINGINSERTS |
Row Delete Notification |
DBPROP_NOTIFYROWDELETE |
Row First Change Notification |
DBPROP_NOTIFYROWFIRSTCHANGE |
Row Insert Notification |
DBPROP_NOTIFYROWINSERT |
Row Privileges |
DBPROP_ROWRESTRICT |
Row Resynchronization Notification |
DBPROP_NOTIFYROWRESYNCH |
Row Threading Model |
DBPROP_ROWTHREADMODEL |
Row Undo Change Notification |
DBPROP_NOTIFYROWUNDOCHANGE |
Row Undo Delete Notification |
DBPROP_NOTIFYROWUNDODELETE |
Row Undo Insert Notification |
DBPROP_NOTIFYROWUNDOINSERT |
Row Update Notification |
DBPROP_NOTIFYROWUPDATE |
Rowset Fetch Position Change Notification |
DBPROP_NOTIFYROWSETFETCHPOSITIONCHANGE |
Rowset Release Notification |
DBPROP_NOTIFYROWSETRELEASE |
Scroll Backwards |
DBPROP_CANSCROLLBACKWARDS |
Server Cursor |
DBPROP_SERVERCURSOR |
Server Data on Insert |
DBPROP_SERVERDATAONINSERT |
Skip Deleted Bookmarks |
DBPROP_BOOKMARKSKIP |
Strong Row Identity |
DBPROP_STRONGIDENTITY |
Updatability |
DBPROP_UPDATABILITY |
Use Bookmarks |
DBPROP_BOOKMARKS |
XML Root |
SSPROP_STREAM_XMLROOT |
XSL |
SSPROP_STREAM_XSL |
有关具体的实现详细信息以及有关 Microsoft SQL Server OLE DB Provider 的功能信息，请参阅 MDAC SDK 的"OLE DB"一节中的"OLE DB Provider for SQL Server"文档。