從 SQL Server 2005 Native Client 更新應用程式Updating an Application from SQL Server 2005 Native Client

適用於: 是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 2005 (9.x)SQL Server 2005 (9.x) 中的 SQL ServerSQL Server Native Client 起,OLE DB Driver for SQL Server 中的重大變更。This topic discusses the breaking changes in OLE DB Driver for SQL Server since SQL ServerSQL Server Native Client in SQL Server 2005 (9.x)SQL Server 2005 (9.x).

當您從 Microsoft Data Access Components (MDAC) 升級為 OLE DB Driver for SQL Server 時,可能也會看見一些行為差異。When you upgrade from Microsoft Data Access Components (MDAC) to OLE DB Driver for SQL Server, you might also see some behavior differences. 如需詳細資訊,請參閱將應用程式從 MDAC 更新為 OLE DB Driver for SQL ServerFor more information, see Updating an Application to OLE DB Driver for SQL Server from MDAC.

SQL ServerSQL Server Native Client 9.0 隨附於 SQL Server 2005 (9.x)SQL Server 2005 (9.x)Native Client 9.0 shipped with SQL Server 2005 (9.x)SQL Server 2005 (9.x). SQL ServerSQL Server Native Client 10.0 隨附於 SQL Server 2008SQL Server 2008Native Client 10.0 shipped with SQL Server 2008SQL Server 2008. SQL ServerSQL Server Native Client 10.5 隨附於 SQL Server 2008 R2SQL Server 2008 R2Native Client 10.5 shipped with SQL Server 2008 R2SQL Server 2008 R2. SQL ServerSQL Server Native Client 11.0 隨附於 SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2014 (12.x)SQL Server 2014 (12.x)Native Client 11.0 shipped with SQL Server 2012 (11.x)SQL Server 2012 (11.x) and SQL Server 2014 (12.x)SQL Server 2014 (12.x).

SQL Server 2005 (9.x)SQL Server 2005 (9.x) Native Client 相比,OLE DB Driver for SQL Server 中已變更的行為Changed behavior in OLE DB Driver for SQL Server compared to SQL Server 2005 (9.x)SQL Server 2005 (9.x) Native Client 描述Description
OLE DB 只會填補至定義的小數位數。OLE DB pads only to the defined scale. 如果轉換作業會將轉換的資料傳送給伺服器,OLE DB Driver for SQL Server 最多只會在資料中用尾端零填滿至 datetime 值的最大長度。For conversions where converted data is sent to the server, OLE DB Driver for SQL Server pads trailing zeros in data only up to the maximum length of datetime values. SQL Server Native Client 9.0 則會填滿至 9 位數。SQL Server Native Client 9.0 padded to 9 digits.
驗證 ICommandWithParameter::SetParameterInfo 的 DBTYPE_DBTIMESTAMP。Validate DBTYPE_DBTIMESTAMP for ICommandWithParameter::SetParameterInfo. OLE DB Driver for SQL Server 會在 ICommandWithParameter::SetParameterInfo 中實作 bScale 的 OLE DB 需求,以設定為 DBTYPE_DBTIMESTAMP 的小數秒精確度。OLE DB Driver for SQL Server implements the OLE DB requirement for bScale in ICommandWithParameter::SetParameterInfo to be set to the fractional seconds' precision for DBTYPE_DBTIMESTAMP.
sp_columns 預存程序現在會針對 IS_NULLABLE 資料行傳回 "NO" ,而非 "NO "The sp_columns stored procedure now returns "NO" instead of "NO " for the IS_NULLABLE column. 在 OLE DB Driver for SQL Server 中,sp_columns 預存程序現在會針對 IS_NULLABLE 資料行傳回 "NO" ,而非 "NO "In OLE DB Driver for SQL Server, sp_columns stored procedure now returns "NO" instead of "NO " for an IS_NULLABLE column.
當日期超出範圍時傳回不同錯誤。Different error returned when date is out of range. 若是 datetime 類型,OLE DB Driver for SQL Server 將會針對超出範圍的日期傳回與舊版不同的錯誤號碼。For the datetime type, a different error number will be returned by OLE DB Driver for SQL Server for an out-of-range date than was returned in earlier versions.

具體而言,SQL ServerSQL Server Native Client 9.0 會針對轉換為 datetime 之字串內所有超出範圍的年份值傳回 22007,而 OLE DB Driver for SQL Server 則會在日期位於 datetime2 支援的範圍內但是超出 datetimesmalldatetime 支援的範圍時,傳回 22008。Specifically, SQL ServerSQL Server Native Client 9.0 returned 22007 for all out of range year values in string conversions to datetime, and OLE DB Driver for SQL Server returns 22008 when the date is within the range supported by datetime2 but outside the range supported by datetime or smalldatetime.
datetime 值會截斷小數秒且不會四捨五入 (如果四捨五入會改變日期的話)。datetime value truncates fractional seconds and not round if rounding will change the day. SQL ServerSQL Server Native Client 10.0 之前,傳送給伺服器之 datetime 值的用戶端行為是要四捨五入到最接近的 1/300 秒。Prior to SQL ServerSQL Server Native Client 10.0, the client behavior for datetime values sent to the server is to round them to nearest 1/300th of a second. 在 OLE DB Driver for SQL Server 中,如果四捨五入會改變日期,這個情況會造成小數秒遭到截斷。In OLE DB Driver for SQL Server, this scenario causes a truncation of fractional seconds if rounding changes the day.
可能會截斷 datetime 值的秒數。Possible trunction of seconds for datetime value. 如果您繫結至類型識別碼為 DBTYPE_DBTIMESTAMP (OLE DB) 或 SQL_TIMESTAMP (ODBC) 且小數位數為 0 的 datetime 資料行,則使用 OLE DB Driver for SQL Server 建置並且連接至 SQL ServerSQL Server 2005 伺服器的應用程式,將會截斷傳送至伺服器的資料中時間部分的秒和小數秒。An application built with OLE DB Driver for SQL Server that connects to a SQL ServerSQL Server 2005 server will truncate seconds and fractional seconds for time portion of data sent to the server if you bind to a datetime column with a type identifier of DBTYPE_DBTIMESTAMP (OLE DB) or SQL_TIMESTAMP (ODBC) and a scale of 0.

例如:For example:

輸入資料:1994-08-21 21:21:36.000Input data: 1994-08-21 21:21:36.000

插入的資料:1994-08-21 21:21:00.000Inserted data: 1994-08-21 21:21:00.000
從 DBTYPE_DBTIME 轉換成 DBTYPE_DATE 的 OLE DB 資料轉換作業不會再造成日期變更。OLE DB data conversion from DBTYPE_DBTIME to DBTYPE_DATE no longer can cause the day to change. SQL ServerSQL Server Native Client 10.0 之前,如果 DBTYPE_DATE 的時間部分在午夜的半秒鐘之內,OLE DB 轉換程式碼會造成日期變更。Prior to SQL ServerSQL Server Native Client 10.0, if the time part of a DBTYPE_DATE was within a half second of midnight, OLE DB conversion code caused the day to change. 在 OLE DB Driver for SQL Server 中,日期將不會變更 (小數秒會遭到截斷,而且不會四捨五入)。In OLE DB Driver for SQL Server, the day will not change (fractional seconds are truncated and not rounded).
IBCPSession::BCColFmt 轉換變更。IBCPSession::BCColFmt conversion changes. 在 OLE DB Driver for SQL Server 中,當您使用 IBCPSession::BCOColFmt 將 SQLDATETIME 或 SQLDATETIME 轉換成字串類型時,會匯出小數值。In OLE DB Driver for SQL Server, when you use IBCPSession::BCOColFmt to convert SQLDATETIME or SQLDATETIME to a string type, a fractional value is exported. 例如,將 SQLDATETIME 類型轉換成 SQLNVARCHARMAX 類型時,SQL ServerSQL Server Native Client 10.0 之前的版本會傳回For example, when converting type SQLDATETIME to type SQLNVARCHARMAX, versions prior to SQL ServerSQL Server Native Client 10.0 returned
1989-02-01 00:00:00。1989-02-01 00:00:00.
OLE DB Driver for SQL Server 會傳回OLE DB Driver for SQL Server returns
1989-02-01 00:00:00.0000000。1989-02-01 00:00:00.0000000.
使用 BCP API 的自訂應用程式現在可以看到警告。Custom applications that use the BCP API can now see a warning. 如果資料長度大於所有類型之欄位所指定的長度,BCP API 將會產生一則警告訊息。The BCP API will generate a warning message if data length is greater than the specified length for a field for all types. 之前這個警告只針對字元類型來提供,但是將不會針對所有類型發出。Previously, this warning was only given for character types, but will not be issued for all types.
將空字串插入繫結為日期/時間類型的 sql_variant 中會產生錯誤。Inserting an empty string into a sql_variant bound as a date/time type generates an error. SQL ServerSQL Server Native Client 9.0 中,將空字串插入繫結為日期/時間類型的 sql_variant 中並不會產生錯誤。In SQL ServerSQL Server Native Client 9.0, inserting an empty string into a sql_variant bound as a date/time type did not generate an error. OLE DB Driver for SQL Server 在這種情況下會正確地產生錯誤。OLE DB Driver for SQL Server correctly generates an error in this situation.
SQL ServerSQL Server 可能會在觸發程序執行時傳回不同的結果。might return different results when a trigger runs. SQL Server 2008SQL Server 2008 中導入的變更可能會使得應用程式從陳述式中傳回不同的結果,該陳述式會在 NOCOUNT OFF 有效時造成觸發程序的執行。Changes introduced in SQL Server 2008SQL Server 2008 might cause an application to have different results returned from a statement that caused a trigger to run when NOCOUNT OFF was in effect. 在此狀況下,您的應用程式可能會產生錯誤。In this situation, your application might generate an error. 若要解決這個錯誤,請在觸發程序中設定 NOCOUNT ONTo resolve this error, set NOCOUNT ON in the trigger.

另請參閱See Also

OLE DB Driver for SQL ServerOLE DB Driver for SQL Server