SQL Server 6.5 and Year 2000

I did patch my SQL Server 6.5 with the 5a patch and somebody told me that anyway, SQL Server 6.5 is not compatible with year 2000 (there's a link, I didn't find, on the microsoft site that talks about that).

Is that right ? Does anybody wrote such a paper about sql server and year 2000 ?

I SQL Server 6.5 compatible with year 2000.
laurent_diepAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
david_levineConnect With a Mentor Commented:
You can go to the web page at: http://www.microsoft.com/technet/year2k/product/product.asp

and click on SQL Server and then the Perform Search button.

Here's the text from the English SL Server 6.5 page (long):

SQL Server 6.5  (English) - Win NT
Product Summary
Product: SQL Server
Version: 6.5  Category: Compliant*
Operating System: Win NT
Language: English Release Date: 01 Apr 1996
Operational Range: 01 Jan 1753 - 31 Dec 9999
Prerequisites:  Service Pack 5 or higher, Microsoft Data Access Components (MDAC), Microsoft Foundation Classes (MFC)
Product Dependencies: Server and Client platforms documented in SQL Server System requirements.
Clock Dependencies: System Clock
Last Updated: 27 Oct 1999
 
Product Details
 

 


This information applies to Standard, Enterprise, Developer, and Small Business Server editions, as well as to the SQL Server edition provided with the SQL Server Workstation product.

Product Maintenance: While Microsoft continues to recommend that customers install the most current Service Pack/Release for non-Year 2000 reasons, we understand that, for many reasons, this may not be possible. In order to aid our customers’ Year 2000 efforts, Microsoft intends to maintain SQL Server 6.5 Service Pack 5 as compliant through January 1, 2001. Newer Service Packs are also to be maintained as compliant, and may include additional non-Year 2000 updates. This is intended to minimize the Year 2000 as a reason to upgrade.

Recommendations to meet compliance:

It is recommended to update SQL Server to Service Pack 5 or higher to meet Year 2000 compliance requirements. The Service Pack 5 download is available at http://support.microsoft.com/support/downloads/ 

Ensure that all the dependent products listed above are compliant on the computer running SQL Server software. Review the Year 2000 documentation for the dependent products to find out what versions or service packs must be installed to make the dependent products Year 2000 compliant.

SQL Server 6.5 Service Pack 5 provides an optional installation for Microsoft Data Access Components (MDAC) 2.1. MDAC 2.1 now has a software update for a Jet engine issue. While SQL Server does not expose the issue, we recommend that you install MDAC 2.1 Service Pack 1 or later should this be a concern for you. For more information, see the Year 2000 document for the Jet Database Engine.

SQL Server 6.5 Setup installs a Microsoft Foundation Classes (MFC) module Mfc40.dll into the SQL Server \Binn directory. The version of this module installed by SQL Server 6.5 has a Year 2000 issue with its function COleDatetime. MFC is not used by the SQL Server database engine, but the SQL Server Administrative Tools and Microsoft Distributed Transaction Coordinator (MS DTC) both use the MFC API. SQL Server code reviews have not identified any SQL Server product area that makes use of the COleDatetime function. While non-SQL Server applications typically load an instance of MFC from the system directory, there is a chance that an application could load the dll from the SQL Server binn directory if the application is either launched from the Binn directory or no other instances of MFC exist on the computer. Such applications are potentially exposed to the Year 2000 issue. Microsoft recommends searching for all instances of MFC and replacing all of them with a remediated version. Download and installation instructions are provided in Knowledge Base Article Q242378.

How the product handles dates:

1) Server:

SQL Server has two date/time datatypes: datetime and smalldatetime. The datetime datatype is stored in 8 bytes of two 4-byte integers. The first 4 bytes are for the number of days before or after the base date of January 1, 1900, and the other 4 bytes for the number of milliseconds after midnight. The values for datetime range from January 1, 1753, to December 31, 9999, to an accuracy of one three-hundredths of a second (3.33 milliseconds).

The smalldatetime datatype is less precise than datetime, and is stored in 4 bytes consisting of one small integer for the number of days after January 1, 1900, and one small integer for the number of minutes past midnight. The values for smalldatetime range from January 1, 1900, through June 6, 2079, with accuracy to the minute.

Either datatype allows users to specify only the last 2 digits of the year. However the year is still stored the same as if 4 digits were specified. If only 2 digits are specified, values less than 50 are interpreted as 20yy, and values greater than or equal to 50 are interpreted as 19yy. For example, if users specify "03", the date is stored as 2003. If users specify "82", the date is stored as 1982.

2) SQL Administration Tools:

SQL Server Administration Tools include SQL Enterprise Manager, SQL Trace, ISQL/w, Security Manager, Client Configuration Utility, Service Manager, SQL Server Executive, Web Assistant and the SQL Maintenance Wizard. Several of listed tools allow display and/or input of date information:

Date display:

Uses either SQL Server Engine formatting or Windows NT Control Panel Settings formatting.

Date input:

Dates are achieved using either Edit fields or Date controls. Transport means for date information to the Server include Stored Procedures, SQLOLE, DB-Library, SQL Server ODBC driver and Net-Libraries. Plausibility validation of entered dates happens mostly on SQL Engine Level for datetime data types. SQL Server Executive, the Task and Alert scheduling engine of SQL Server, represents an exception: Date information for scheduled tasks is stored in integer datatypes. Plausibility of entered dates in this case is evaluated within the user interface Date controls and by using SQL Servers ISDATE() function within stored procedures prior to storage in the database.

3) Application Programming

General purpose SQL Server applications use the Transact-SQL language to work with the data in SQL Server databases. The Transact-SQL language is processed on the server, and dates in Transact-SQL statements are handled using the rules specified above in 1) Server. SQL Server applications use one of several application programming interfaces (APIs) to send Transact-SQL statements to the server and process the results. Dates can be specified either as part of the Transact-SQL statement sent to SQL Server, or in program variables bound to Transact-SQL parameters or expressions through the API. If dates are specified as a part of a Transact-SQL statement, they are handled on the server using the rules specified in the 1) Server section. Dates specified in variables bound to parameters and expressions through the API are subject to the rules for the API:

OLE DB (using the Microsoft OLE DB Provider for SQL Server):

Dates can be specified using the OLE DB API. For these dates, the Microsoft OLE DB Provider for SQL Server is subject to the same date coercion rules that apply to OLE DB. For more information, see the Year 2000 documentation for the MS Data Access Components (MDAC). The Microsoft OLE DB Provider is also subject to the date conversion rules that apply to the OLE Automation Technologies. For more information, see the Year 2000 document for OLE Automation Technologies.

The OLE DB cutoff date for two digit year conversions defaults to 29, dates that are less than or equal to 29 are interpreted as 20xx and dates that are greater than 29 are interpreted as 19xx. For information on configuring the cutoff date, see the Year 2000 document for OLE Automation Technologies. The cutoff date observed by SQL Server 6.5 is 49, this cannot be configured or altered. The cutoff date used by SQL Server 7.0 can be configured, for more information see the Year 2000 document for SQL Server 7.0.

ODBC (Using the SQL Server ODBC Driver):

The ODBC API does not support specifying two-digit years, it only supports specifying four-digit years. ODBC recommends that applications use ODBC timestamp escape sequences to specify dates in SQL statements, and the timestamp escape sequences only support four-digit years. The SQL Server ODBC Driver, however, does not prevent users from entering Transact-SQL dates. If Transact-SQL dates are used, they are handled on the server using the rules specified in the 1) Server section.

DB-Library:

The DB-Library does not provide any means for specifying dates other than specifying them as part of Transact-SQL statements. These dates are handled on the server using the rules specified in the 1) Server section.

Two-digit shortcut handling:

The SQL Server Engine allows dates to be input using 2-digit years in datetime data types. Regardless of whether the date is input as a 2-digit or 4-digit year, it is stored the same as noted above. When using a 2-digit year, values less than 50 are assumed to be 20XX and values greater than or equal to 50 are parsed 19XX. For example, a 2-digit year of "25" is stored as "2025", while a 2-digit year of "50" is stored as "1950".

List of issues addressed by SQL Server 6.5 Service Pack 5.

The EXPIREDATE clause for DUMP DATABASE does not properly handle dates greater or equal to 2000. EXPIREDATE is used to say when its ok to reuse backup media. Encountering this issue does not impact general operation of SQL Server. The impact is limited to accidental overwrites of backup media and not providing the extra safety check that EXPIREDATE provides. Fixed in SQL Server, Service Pack 5 and higher.
The RETAINDAYS clause for DUMP DATABASE does not work properly when the system time has moved beyond 12/31/99. Impact is that an existing dump cannot be overwritten, workaround is to manually delete the pre-existing dump image before attempting the DUMP. Fixed in SQL Server, Service Pack 5 and higher.
SQL Executive does not recognize year 2000 as a leap year. Fixed in SQL Server 6.5, Service Pack 2 and higher.
Task Manager user interface spin box does not recognize year 2000 as a leap year. The workaround for this is to schedule the task directly via executing the stored procedure, not the user interface. Fix is expected in SQL Server, Service Pack 5 and higher. Web Assistant does not allow scheduling a task in year 2000 and beyond. Workaround is to use the underlying stored procedure SP_MAKEWEBTASK, not the user interface. Fixed in SQL Server, Service Pack 5 and higher.
SP_ADDTASK, SP_PURGEHISTORY and SP_UPDATEALERT stored procedures allow several invalid dates as input parameters. This has no consequences as long as valid dates are used as input parameters. Fixed in SQL Server, Service Pack 5 and higher.
The Microsoft Query client tool, an application shared by several Microsoft products accepts 2-digit year date formatting for the ODBC query, but assumes a twentieth century date. To avoid this, use 4-digit years for queries based upon date data in MS Query or use other client tools provided with SQL Server like ISQL/w or Enterprise Manager. Fixed in SQL Server, Service Pack 5 and higher.
Two APIs in ODBC 2.5 are affected by Year 2000 issues. The SQLInstallDriver and SQLInstallODBC APIs are used to write custom setups for ODBC drivers. If the following conditions are met, a wrong version of a file may be installed:
The Setup is written using the APIs SQLInstallDriver or SQLInstallODBC
An INF file is used to specify the list of files to be installed.
The filelist information in the INF file includes file date but not the file version.
Both the Win32 and the Win16 APIs are affected by this issue. Note, the SQL Server ODBC installs provided with SQL Server 6.5 itself use both, date and version information and are therefore not affected.

The SQLInstallODBC API was discontinued after ODBC 2.5. SQLInstallDriver still exists, but the option to use an INF file (the source of the issue) was removed in ODBC 3.0. Instead a new preferred API, SQLInstallDriverEx, was added.

On Windows NT 4.0, Windows 95, and Windows 98, ODBC versions 3.0 and higher are provided either as part of Operating System Upgrades or Service Packs. For further compliance information, on see ODBC year 2000 compliance. For further information on ODBC and Microsoft Data Access Components, see http://www.microsoft.com/data/.

Microsoft Data Access Components version 2.1 (MDAC 2.1) are provided as part of SQL Server 6.5 Service Pack 5 and are identical to the versions shipping in SQL Server 7.0. The ODBC version provided herein is 3.51. SQL server customers on Windows NT 4, Windows 95 and Windows 98 who wish to upgrade from ODBC 2.5 should use MDAC 2.1.

SQL Server customers on Windows NT 3.51 who wish to upgrade from ODBC 2.5 should use the ODBC 3.0 setup, odbc3brz.exe, which will be available shortly from http://www.microsoft.com/data/. ODBC 3.0 is not available for the Win16 platform.

If the user has optionally installed replication to non-SQL Server datasources using the Microsoft ODBC Desktop Drivers, the Microsoft Jet database engine is being used, which is also used by Microsoft Access 95 (7.0). For Year2000 status consult Microsoft Access 95 (7.0). MDAC 2.1 are provided as part of SQL Server 6.5 Service Pack 5 and are identical to the versions shipping in SQL Server 7.0. The Jet version provided in the MDAC 2.1 release is Jet 4.0. Jet 3.51 for the NT 3.51 platform is available from odbc3brz.exe mentioned above.
Testing guidelines and recommendations:

Examine the database schema to see whether smalldatetime datatypes are used; in some cases, in order to achieve higher precision, it is recommended to convert these to datetime.
Use 4-digit year input to avoid ambiguities or boundary conditions in applications.
Investigate front end application behavior using SQL Server as a backend and interaction with other applications.
 
 
 
 

0
 
laurent_diepAuthor Commented:
Edited text of question.
0
 
laurent_diepAuthor Commented:
I think this is the document I was looking for.
Anyway when patched, SQL Server 6.5 is Y2000 Compatible .

0
All Courses

From novice to tech pro — start learning today.