SQL Server Q&A

Symptoms
An ODBC application may fail to connect to SQL Server 6.x with the following error message returned:

[28000][Microsoft][ODBC SQL Server Driver][SQL Server] Login failed (4002)This problem occurs when applications use the Microsoft Data Access Components (MDAC) 2.1 version of the SQL Server driver (3.70.623) to make trusted connections to SQL Server 6.x and an older version (version 6.x or earlier) of the SQL Server client-side Net-Library DLL is the first on the path.
If all client-side Net-Library DLLs are updated to the SQL Server 7.0 version, this problem does not occur. Applications will not experience this problem when connecting to SQL Server 7.0. Finally, if the ODBC application is started from a directory that contains the updated client-side Net-Library DLL, the problem does not occur either.
Resolution
The MDAC 2.1 SQL Server driver is designed to automatically load the correct version of Net-Library from the <Winntroot>\System32 directory if an older version of Net-Library is initially loaded. However, when loading the Net-Library DLL from the System32 directory for the second time, the driver incorrectly assumes it is making a trusted connection to SQL Server 7.0, resulting an invalid login packet sent. This causes the connection to fail and the error to be returned.

Symptoms
When you restore a system database from a backup device file in Microsoft SQL Server, you may receive an error message that is similar to the following:

The backup of the system database on device Full Path of the Backup Device File cannot be restored because it was created by a different version of the server (134218407) than this server (134218488).
RESTORE DATABASE is terminating abnormally.
The error message contains version numbers in parentheses that are not valid (7.00.623 for SQL Server 7.0 or 8.00.760 for SQL Server 2000 Service Pack 3 [SP3], for example).
Resolution
To determine the correct version of a database backup device file in SQL Server, run the RESTORE HEADERONLY Transact-SQL statement using the backup device file name.TheRESTORE HEADERONLY Transact-SQL statement returns the following columns to help you determine the version of the database backup file:SoftwareVersionMajorSoftwareVersionMinorSoftwareVersionBuildFor more information about the RESTORE HEADERONLY Transact-SQL statement, visit the following Microsoft Web site:
http://msdn2.microsoft.com/en-us/library/aa238455(SQL.80).aspx(http://msdn2.microsoft.com/en-us/library/aa238455(SQL.80).aspx)The decimal numberthat indicates the version of SQL Server in the error message is obtained by converting part of the SQL Server version number to a hexadecimal representation.You can also manually determine the actual version number of SQL Server from the decimal number that is mentioned in the error message.To do so, follow these steps: Convert the decimal number that is mentioned in the error message to a hexadecimal representation. Convert only the last four digits of the hexadecimal representation to a decimal representation.Replace the last three digits of the hexadecimal representation with the decimal number from step 2.To determine the actual version number of SQL Server, format the result by using the information in the “Standard format of SQL Server version numbers” section of this article.Note You can use the Calculator program in Microsoft Windows in Scientific view to convert the number from a decimal representation to a hexadecimal representation.
For example, to determine the actual SQL Server version number for the decimal number that is mentioned in the error message (134218407), follow these steps: Convert the decimal number 134218407 to a hexadecimal representation. The hexadecimal representation of 134218407 is 80002A7.Convert the last four digits of the hexadecimal representation to a decimal representation. The decimal representation of 02A7 is 679.Replace the last four digits of the hexadecimal representation with the decimal numbers that you obtained in step 2. The number is 800679.Format the number in step 3 by using the information in the “Standard format of SQL Server version numbers” section of this article. The version of SQL Server is 8.00.679.
Standard format of SQL Server version numbersThe version numbers of SQL Server use the following standard format:
Major version number.Minor version number.Build number
For example, the version number of an instance of SQL Server 2000 SP3 is 8.00.760, where the first number (8) indicates the major version number, the next two numbers (00) indicate the minor version number, and the last three numbers (760) indicate the build number.

Symptoms
In Microsoft SQL Server 2008, new functionalities are added to SQL Server Management Objects (SMO). An application that uses the SQL Server 2005 version of SMO cannot be compiled by using the SQL Server 2008 version of SMO. You must make some changes to the application, and then you must recompile the application. Then, you can use the new functionalities in the SQL Server 2008 version of SMO.
Resolution
This article describes what you must do to use the new functionalities of the SQL Server 2008 version of SMO in an application that uses the SQL Server 2005 version of SMO.

Symptoms
You try to manage certificates in Microsoft SQL Server 2005 and in SQL Server 2000 by using the SQL Server 2008 version of SQL Server Management Objects (SMO). An exception is raised, and SMO does not let you manage the certificates.
Resolution
This problem occurs because SQL Server 2008 has a specific property that SMO looks for when you try to manage certificates. However, SMO does not verify which version of SQL Server owns the certificates, and when it tries to initialize this SQL Server 2008 specific property, SMO raises the exception.

Symptoms
You install Microsoft SQL Server 2008 on a computer that is running Windows XP. Then, you upgrade Windows XP to Windows Vista. When you run a query in SQL Server Management Studio, you receive the following error message:

The type initializer for ‘Microsoft.SqlServer.Management.UI.Grid.ColumnInfo’ threw an exception.However, you can successfully run the query in Windows XP.
Resolution
This problem occurs because the Windows Vista Setup program incorrectly replaces the Mscorlib.dll file of the Microsoft .NET Framework 2.0 Service Pack 1 (SP1). The Mscorlib.dll file of the later version of the file contains the DateTimeOffset data type. This data type does not exist in the earlier version of the Mscorlib.dll file.
When you install SQL Server 2008, SQL Server 2008 installs the .NET Framework 3.5 Service Pack (SP1). The .NET Framework 3.5 SP1 also installs the .NET Framework 2.0 SP1, and this service pack contains the later version of the Mscorlib.dll file. However, the Windows Vista Setup program replaces this version of the Mscorlib.dll file with the earlier version of the Mscorlib.dll file.