Updating Applications from SQL Server 2005 Native Client
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
This article discusses the breaking changes in OLE DB Driver for SQL Server since SQL Server Native Client in SQL Server 2005 (9.x).
When you upgrade from Microsoft Data Access Components (MDAC) to OLE DB Driver for SQL Server, you might also see some behavior differences. For more information, see Updating an Application to OLE DB Driver for SQL Server from MDAC.
SQL Server Native Client 9.0 shipped with SQL Server 2005 (9.x). SQL Server Native Client 10.0 shipped with SQL Server 2008 (10.0.x). SQL Server Native Client 10.5 shipped with SQL Server 2008 R2 (10.50.x). SQL Server Native Client 11.0 shipped with SQL Server 2012 (11.x) and SQL Server 2014 (12.x).
| Changed behavior in OLE DB Driver for SQL Server compared to SQL Server 2005 (9.x) Native Client | Description |
|---|---|
| OLE DB pads only to the defined scale. | 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 padded to 9 digits. |
| Validate DBTYPE_DBTIMESTAMP for ICommandWithParameter::SetParameterInfo. | 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. |
| The sp_columns stored procedure now returns "NO" instead of "NO " for the IS_NULLABLE column. | 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. | 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. Specifically, SQL 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 value truncates fractional seconds and not round if rounding will change the day. | Prior to SQL 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. In OLE DB Driver for SQL Server, this scenario causes a truncation of fractional seconds if rounding changes the day. |
| Possible truncation of seconds for datetime value. | An application built with OLE DB Driver for SQL Server that connects to a SQL 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: Input data: 1994-08-21 21:21:36.000 Inserted data: 1994-08-21 21:21:00.000 |
| OLE DB data conversion from DBTYPE_DBTIME to DBTYPE_DATE no longer can cause the day to change. | Prior to SQL 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. In OLE DB Driver for SQL Server, the day will not change (fractional seconds are truncated and not rounded). |
| IBCPSession::BCColFmt conversion changes. | 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. For example, when converting type SQLDATETIME to type SQLNVARCHARMAX, versions prior to SQL Server Native Client 10.0 returned 1989-02-01 00:00:00. OLE DB Driver for SQL Server returns 1989-02-01 00:00:00.0000000. |
| Custom applications that use the BCP API can now see a warning. | 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. |
| Inserting an empty string into a sql_variant bound as a date/time type generates an error. | In SQL 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 correctly generates an error in this situation. |
| SQL Server might return different results when a trigger runs. | Changes introduced in SQL Server 2008 (10.0.x) 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. To resolve this error, set NOCOUNT ON in the trigger. |
See also
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for