將應用程式從 MDAC 更新為適用於 SQL Server 的 OLE DB 驅動程式Updating an Application to OLE DB Driver for SQL Server from MDAC

適用於: 是SQL Server 是Azure SQL Database 是Azure Synapse Analytics (SQL DW) 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

下載OLE DB 驅動程式下載DownloadDownload OLE DB Driver

適用於 SQL Server 的 OLE DB 驅動程式與 Microsoft Data Access Components (MDAC) 之間有一些差異;從 Windows Vista 開始,資料存取元件現在稱為 Windows Data Access Components (或 Windows DAC)。There are a number of differences between OLE DB Driver for SQL Server and Microsoft Data Access Components (MDAC); starting with Windows Vista, the data access components are now called Windows Data Access Components (or Windows DAC). 雖然兩者都提供 SQL ServerSQL Server 資料庫的原生資料存取,但是適用於 SQL Server 的 OLE DB 驅動程式專為公開 SQL ServerSQL Server 的新功能所設計,同時保留了與舊版之間的回溯相容性。Although both provide native data access to SQL ServerSQL Server databases, OLE DB Driver for SQL Server has been designed to expose the new features of SQL ServerSQL Server, while at the same time maintaining backward compatibility with earlier versions.

此外, 雖然 MDAC 包含使用 OLE DB、ODBC 和 ActiveX Data Objects (ADO) 的元件, 但 SQL Server 的 OLE DB 驅動程式只會執行 OLE DB (雖然 ADO 可以存取 OLE DB Driver for SQL Server 的功能)。In addition, although MDAC contains components for using OLE DB, ODBC, and ActiveX Data Objects (ADO), OLE DB Driver for SQL Server only implements OLE DB (although ADO can access the functionality of OLE DB Driver for SQL Server).

SQL Server 和 MDAC 的 OLE DB 驅動程式在其他下欄區域中有所不同:OLE DB Driver for SQL Server and MDAC differ in the other following areas:

  • 使用 ADO 存取 SQL Server 之 OLE DB 驅動程式的使用者, 可能會發現篩選功能比存取 SQL OLE DB 提供者時更少。Users who use ADO to access the OLE DB Driver for SQL Server may find less filtering functionality than when they accessed the SQL OLE DB provider.

  • 如果 ADO 應用程式使用 OLE DB 驅動程式進行 SQL Server 並嘗試更新計算資料行, 將會報告錯誤。If an ADO application uses OLE DB Driver for SQL Server and attempts to update a computed column, an error will be reported. 使用 MDAC,系統會接受但忽略更新。With MDAC, the update was accepted but ignored.

  • SQL Server 的 OLE DB 驅動程式是單一獨立的動態連結程式庫 (DLL) 檔案。OLE DB Driver for SQL Server is a single self-contained dynamic link library (DLL) file. 公開的介面已保留為最少量,這樣不但可便於散發,同時也可限制安全性風險。The publicly exposed interfaces have been kept to a minimum, both to ease distribution, as well as to limit security exposure.

  • 僅支援 OLE DB 介面。Only OLE DB interfaces are supported.

  • SQL Server 名稱的 OLE DB 驅動程式與搭配 MDAC 使用的名稱不同。The OLE DB Driver for SQL Server names are different from names used with MDAC.

  • 當您使用 SQL Server 的 OLE DB 驅動程式時, 可以使用 MDAC 元件提供的使用者可存取功能。User-accessible functionality supplied by MDAC components is available when using OLE DB Driver for SQL Server. 這包括但不限於以下項目:連接共用、ADO 支援和用戶端資料指標支援。This includes, but is not limited to, the following: connection pooling, ADO support, and client cursor support. 使用其中任何一項功能時, SQL Server 的 OLE DB 驅動程式只會提供資料庫連接。When any of these features are used, OLE DB Driver for SQL Server supplies only database connectivity. MDAC 會提供類似追蹤、管理控制項和效能計數器的功能。MDAC provides functionality such as tracing, management controls, and performance counters.

  • 應用程式可以搭配適用於 SQL Server 的 OLE DB 驅動程式使用 OLE DB 核心服務,但如果使用 OLE DB 資料指標引擎,則應使用資料類型相容性選項來避免可能發生的任何問題,原因是資料指標引擎並不知道新的 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 資料類型。Applications can use OLE DB core services with OLE DB Driver for SQL Server, but if using the OLE DB cursor engine, they should use the data type compatibility option to avoid any potential problems that might arise because the cursor engine has no knowledge of the new SQL Server 2005 (9.x)SQL Server 2005 (9.x) data types.

  • SQL Server 的 OLE DB 驅動程式支援對先前SQL ServerSQL Server資料庫的存取。OLE DB Driver for SQL Server supports access to previous SQL ServerSQL Server databases.

  • SQL Server 的 OLE DB 驅動程式不包含 XML 整合。OLE DB Driver for SQL Server does not contain XML integration. SQL Server 的 OLE DB 驅動程式支援 SELECT .。。FOR XML 查詢, 但不支援任何其他 XML 功能。OLE DB Driver for SQL Server supports SELECT ... FOR XML queries, but does not support any other XML functionality. 不過, SQL Server 的 OLE DB 驅動程式支援在中SQL Server 2005 (9.x)SQL Server 2005 (9.x)引進的 xml 資料類型。However, OLE DB Driver for SQL Server does support the xml data type introduced in SQL Server 2005 (9.x)SQL Server 2005 (9.x).

  • 適用於 SQL Server 的 OLE DB 驅動程式支援只利用連接字串屬性來設定用戶端網路程式庫。OLE DB Driver for SQL Server supports configuring client-side network libraries using only connection string attributes. 如果您需要更完整的網路程式庫組態,您必須使用 SQL ServerSQL Server 組態管理員。If you need more complete network library configuration, you must use SQL ServerSQL Server Configuration Manager.

  • MDAC 連接字串允許Trusted_Connection關鍵字的布林值 (true)。MDAC connection strings allow a Boolean value (true) for the Trusted_Connection keyword. SQL Server 連接字串的 OLE DB 驅動程式必須使用 [是] 或 [ ]。An OLE DB Driver for SQL Server connection string must use yes or no.

  • 警告和錯誤發生了些微的變更。Minor changes have occurred to warnings and errors. 伺服器傳回的警告和錯誤在傳遞給適用於 SQL Server 的 OLE DB 驅動程式時,現在保持相同的嚴重性。Warnings and errors returned by the server now retain the same severity when passed to OLE DB Driver for SQL Server. 如果您依賴特定警告和錯誤的截獲,您應該確定您已經徹底測試過您的應用程式。You should ensure you have thoroughly tested your application if you depend on trapping particular warnings and errors.

  • 適用於 SQL Server 的 OLE DB 驅動程式在錯誤檢查上比 MDAC 嚴格,這表示未嚴謹符合 OLE DB 規格的某些應用程式可能會有不同的行為。OLE DB Driver for SQL Server has stricter error checking than MDAC, which means that some applications that do not conform strictly to the OLE DB specifications may behave differently. 例如,SQLOLEDB 提供者並未強制執行結果參數名稱的開頭必須是 '@' 這項規則,但是適用於 SQL Server 的 OLE DB 驅動程式會強制執行這項規則。For example, the SQLOLEDB provider did not enforce the rule that parameter names must start with '@' for result parameters, but the OLE DB Driver for SQL Server does.

  • SQL Server 的 OLE DB 驅動程式的行為與有關失敗連接的 MDAC 不同。OLE DB Driver for SQL Server behaves differently from MDAC regarding failed connections. 例如,MDAC 會針對失敗的連線傳回快取屬性值,而適用於 SQL Server 的 OLE DB 驅動程式則會報告錯誤給呼叫的應用程式。For example, MDAC returns cached property values for a connection that has failed, whereas OLE DB Driver for SQL Server reports an error to the calling application.

  • 適用於 SQL Server 的 OLE DB 驅動程式不會產生 Visual Studio Analyzer 事件,而是產生 Windows 追蹤事件。OLE DB Driver for SQL Server does not generate Visual Studio Analyzer events, but instead generates Windows tracing events.

  • SQL Server 的 OLE DB 驅動程式不能與 perfmon 搭配使用。OLE DB Driver for SQL Server cannot be used with perfmon. Perfmon 是一種 Windows 工具,它只能搭配使用 Windows 隨附之 MDAC SQLODBC 驅動程式的 DSN 一起使用。Perfmon is a Windows tool that can only be used with DSNs that use the MDAC SQLODBC driver included with Windows.

  • 當 SQL Server 的 OLE DB 驅動程式連接到SQL Server 2005 (9.x)SQL Server 2005 (9.x)和更新版本時, 會以 SQL_ERROR 的形式傳回伺服器錯誤16947。When OLE DB Driver for SQL Server is connected to SQL Server 2005 (9.x)SQL Server 2005 (9.x) and later versions, server error 16947 is returned as a SQL_ERROR. 當定點更新或刪除無法更新或刪除資料列時,就會發生這個錯誤。This error occurs when a positioned update or delete fails to update or delete a row. 使用 MDAC 時,如果連接到任何 SQL ServerSQL Server 版本,伺服器錯誤 16947 會以警告 (SQL_SUCCESS_WITH_INFO) 的形式傳回。With MDAC when connecting to any version of SQL ServerSQL Server, server error 16947 is returned as a warning (SQL_SUCCESS_WITH_INFO).

  • 適用於 SQL Server 的 OLE DB 驅動程式會實作 IDBDataSourceAdmin 介面,這是之前未實作的選擇性 OLE DB 介面,但是只會實作這個選擇性介面的 CreateDataSource 方法。OLE DB Driver for SQL Server implements the IDBDataSourceAdmin interface, which is an optional OLE DB interface that was not previously implemented, but only the CreateDataSource method of this optional interface is implemented. 這項功能處於維護模式,並可能在 Microsoft SQL Server 的未來版本中移除。This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. 請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

  • 適用於 SQL Server 的 OLE DB 驅動程式會在 TABLES 和 TABLE_INFO 結構描述資料列集中傳回同義字,並將 TABLE_TYPE 設定為 SYNONYM。The OLE DB Driver for SQL Server returns synonyms in the TABLES and TABLE_INFO schema rowsets, with TABLE_TYPE set to SYNONYM.

  • 資料類型 varchar(max)nvarchar(max)varbinary(max)xmludt 或其他大型物件類型的傳回值無法傳回給 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 以前的用戶端版本。Return values of data type varchar(max), nvarchar(max), varbinary(max), xml, udt, or other large object types cannot be returned to client versions earlier than SQL Server 2005 (9.x)SQL Server 2005 (9.x). 如果您想要使用這些類型做為傳回值, 則必須使用 SQL Server 的 OLE DB 驅動程式。If you wish to use these types as return values, you must use OLE DB Driver for SQL Server.

  • MDAC 允許在手動和隱含交易的開頭執行下列陳述式,但適用於 SQL Server 的 OLE DB 驅動程式則不允許。MDAC allows the following statements to be executed at the start of manual and implicit transactions, but OLE DB Driver for SQL Server does not. 它們必須在自動認可模式中執行。They must be executed in autocommit mode.

    • 所有全文檢索作業 (索引和目錄 DDL)All full-text operations (index and catalog DDL)

    • 所有資料庫作業 (建立資料庫、改變資料庫、卸除資料庫)All database operations (create database, alter database, drop database)

    • 重新設定Reconfigure

    • ShutdownShutdown

    • 終止Kill

    • BackupBackup

  • 當 MDAC 應用程式連接到 SQL ServerSQL Server 時,SQL Server 2005 (9.x)SQL Server 2005 (9.x) 中導入的資料類型將會以 SQL Server 2000 (8.x)SQL Server 2000 (8.x) 相容的資料類型形式出現,如下表所示。When MDAC applications connect to SQL ServerSQL Server, the data types introduced in SQL Server 2005 (9.x)SQL Server 2005 (9.x) will appear as SQL Server 2000 (8.x)SQL Server 2000 (8.x)-compatible data types as shown in the following table.

    SQL Server 2005 類型SQL Server 2005 type SQL Server 2000 類型SQL Server 2000 type
    varchar(max)varchar(max) texttext
    nvarchar(max)nvarchar(max) ntextntext
    varbinary(max)varbinary(max) imageimage
    udtudt varbinaryvarbinary
    xmlxml ntextntext

    此類型對應會影響資料行中繼資料傳回的值。This type mapping affects the values returned for column metadata. 例如,文字資料行的大小上限為 2147483647, 但 SQL Server 的 OLE DB 驅動程式會根據平臺, 將Varchar (max) 資料行的大小上限報告為2147483647或-1。For example, a text column has a maximum size of 2,147,483,647, but OLE DB Driver for SQL Server reports the maximum size of varchar(max) columns as 2,147,483,647 or -1, depending on platform.

  • 基於回溯相容性的理由,適用於 SQL Server 的 OLE DB 驅動程式允許模稜兩可的連接字串 (例如,可多次指定某些關鍵字,而且可允許衝突的關鍵字,解決方法是以位置或優先順序為根據)。OLE DB Driver for SQL Server allows ambiguity in connection strings (for example, some keywords may be specified more than once, and conflicting keywords may be allowed with resolution based on position or precedence) for reasons of backward compatibility. SQL Server 的 OLE DB 驅動程式的未來版本, 在連接字串中可能不會造成混淆。Future releases of OLE DB Driver for SQL Server might not allow ambiguity in connection strings. 修改應用程式時,適合使用適用於 SQL Server 的 OLE DB 驅動程式來消除任何對於模稜兩可連接字串的相依性。It is good practice when modifying applications to use OLE DB Driver for SQL Server to eliminate any dependency on connection string ambiguity.

  • 如果您使用 OLE DB 呼叫來啟動交易, SQL Server 和 MDAC OLE DB 驅動程式之間會有不同的行為;交易會立即開始使用 SQL Server 的 OLE DB 驅動程式, 但在第一次使用 MDAC 存取資料庫之後, 就會開始交易。If you use an OLE DB call to start transactions, there is a difference in behavior between OLE DB Driver for SQL Server and MDAC; transactions will begin immediately with OLE DB Driver for SQL Server, but transactions will begin after the first database access using MDAC. 這會影響預存程序和批次的行為,原因是 SQL ServerSQL Server 要求 @@TRANCOUNT 在批次或預存程序完成執行的前後必須相同。This can affect the behavior of stored procedures and batches because SQL ServerSQL Server requires @@TRANCOUNT to be the same after a batch or stored procedure finishes execution as it was when the batch or stored procedure started.

  • 使用 SQL Server 的 OLE DB 驅動程式時, ITransactionLocal:: BeginTransaction 會立即啟動交易。With OLE DB Driver for SQL Server, ITransactionLocal::BeginTransaction will cause a transaction to be started immediately. 當使用 MDAC 時,交易會延遲到應用程式執行需要在隱含交易模式中之交易的陳述式之後才開始。With MDAC the transaction start was delayed until the application executed a statement that required a transaction in implicit transaction mode. 如需詳細資訊,請參閱 SET IMPLICIT_TRANSACTIONS (Transact-SQL)For more information, see SET IMPLICIT_TRANSACTIONS (Transact-SQL).

SQL Server 和 MDAC 的 OLE DB 驅動程式都支援使用資料列版本設定的讀取認可交易隔離, 但只有適用于 SQL Server 的 OLE DB 驅動程式才支援快照集交易隔離。Both OLE DB Driver for SQL Server and MDAC support read committed transaction isolation using row versioning, but only OLE DB Driver for SQL Server supports snapshot transaction isolation. (在程式設計的詞彙中,使用資料列版本設定的讀取認可交易隔離與讀取認可的交易相同)。(In programming terms, read committed transaction isolation using row versioning is the same as read-committed transaction.).

另請參閱See Also

利用 OLE DB Driver for SQL Server 建置Building Applications with OLE DB Driver for SQL Server