SQL Server Q&A

Symptoms
A SELECT statement may generate an exception that causes the SQL Server instance to terminate unexpectedly if all of the following conditions are true: The SELECT statement retrieves a computed column.The computed column references a user defined function (UDF).The UDF accesses table data.An implicit data type conversion is necessary for one of the UDF parameters. No stack dump or other message related to the exception is written to the SQL Server error log.
Resolution
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
290211?(http://support.microsoft.com/kb/290211/EN-US/) INF: How To Obtain the Latest SQL Server 2000 Service PackNOTE: The following hotfix was created before the release of Microsoft SQL Server 2000 Service Pack 3.
The English version of this fix should have the following file attributes or later:
SQL Server 2000 Service Pack 1
This hotfix build is designed to be applied on a server that is running SQL Server 2000 Service Pack 1:

VersionFile nameDescription————————————————8.00.0469Sqlservr.exeSP1-based hotfix SQL Server 2000 Service Pack 2
This hotfix build is designed to be applied on a server running SQL Server 2000 Service Pack 2:

VersionFile nameDescription————————————————8.00.0552Sqlservr.exeSP2-based hotfix NOTE: Because of file dependencies, the most recent hotfix or feature that contains the files may also contain additional files.

Symptoms
Running a complex query that results in a parallel plan may cause an Access Violation (AV) to occur.
Resolution
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
290211?(http://support.microsoft.com/kb/290211/EN-US/) INF: How To Obtain the Latest SQL Server 2000 Service PackNOTE: The following hotfix was created before the release of Microsoft SQL Server 2000 Service Pack 3.
The English version of this fix should have the following file attributes or later:

DateTimeVersionSizeFile name——————————————————-3/7/20025:12 PM8.00.5947269 KBSqlservr.exeNOTE: Because of file dependencies, the most recent hotfix or feature that contains the files may also contain additional files.

Symptoms
When you use Database Engine Tuning Advisor (DTA) to tune a database in Microsoft SQL Server 2005 Service Pack 2 (SP2), you receive the following error message:

An unhandled win32 exception occurred in dtaengine90.exe [4308].Additionally, a minidump file is generated in the SQL Server 2005 log.
Resolution
The fix for this issue was first released in Cumulative Update3. For more information about how to obtain this cumulative update package for SQL Server 2005 Service Pack 2, click the following article number to view the article in the Microsoft Knowledge Base:
939537?(http://support.microsoft.com/kb/939537/LN/) Cumulative update package 3 for SQL Server 2005 Service Pack 2Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2005 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
937137?(http://support.microsoft.com/kb/937137/LN/) The SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 2 was releasedMicrosoft SQL Server 2005 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2005 Service Pack 2 hotfix to an installation of SQL Server 2005 Service Pack 2. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

Symptoms
Consider one of the following scenarios:You restore a database backup from Microsoft SQL Server 2000 on an instance of Microsoft SQL Server 2005 by using the WITH RECOVERY option. You also restore this database backup on another instance of SQL Server 2005 by using the WITH NORECOVERY option.You have two instances of SQL Server 2000 that have a database that is the same on these instances. One instance has the database in the Loading status. The other instance has the database in the regular status. Then, you upgrade these instances to SQL Server 2005.You configure database mirroring on this database in SQL Server 2005. However, when you start database mirroring, you receive the following error message:

The Service Broker ID for the remote copy of database “<DatabaseName>” does not match the ID on the principal server. (Microsoft SQL Server, Error: 1436)Note This problem also occurs in Microsoft SQL Server 2008.
You usually encounter this problem when you upgrade a Microsoft SQL Server 2000 log shipping configuration. Consider the following scenario:You have a SQL Server 2000 log shipping configuration.You upgrade the servers that are involved in the log shipping configuration to SQL Server 2005.After the upgrade, you configure database mirroring by using the database that is used previously in the log shipping configuration.When you start database mirroring, you receive this error message.
Resolution
Databases in SQL Server 2000 do not have a Service Broker ID. If you restore these databases on instances of SQL Server 2005 by using the WITH NORECOVERY option, these databases will not be upgraded. Therefore, theService Broker ID of these databases is NULL after the restore. Additionally, if you upgrade an instance ofSQL Server 2000, existing databases that are in the Loading status will not be upgraded. Therefore, theService Broker ID of these databases is NULL after the upgrade. When you start database mirroring, the Service Broker ID of the principal server is compared to the Service ID of the mirror server. Because the Service Broker IDs do not match, the comparison fails.
In the upgrade scenario of a SQL Server 2000 log shipping configuration, the secondary database is in the Loading status. After you upgrade the server that holds the secondary database, the database does not have a Service Broker ID. After you upgrade the server that holds the primary database, the database has a Service Broker ID. When you start database mirroring, the Service Broker ID of the principal server is sent to the mirror server for comparison. Because the Service Broker ID of the mirror server is NULL, the comparison fails.

Symptoms
The DBCC CHECKDB command may produce an access violation (AV) when run on a database while other users are actively accessing the database.
Resolution
To resolve this problem, obtain the latest service pack for SQL Server 2000. For additional information, click the following article number to view the article in theMicrosoft Knowledge Base:
290211?(http://support.microsoft.com/kb/290211/EN-US/) INF: How to Obtain the Latest SQL Server 2000 Service Pack

Symptoms
If you restore a database with the NORECOVERY option and you then set the database OFFLINE, the DATABASEPROPERTYEX function may report an incorrect status for the database. The DATABASEPROPERTYEX functions reports the database status as ONLINE.
In addition, if you run a RESTORE DATABASE WITH RECOVERY statement, an EXCEPTION_ACCESS_VIOLATION may occur.
Resolution
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in theMicrosoft Knowledge Base:
290211?(http://support.microsoft.com/kb/290211/EN-US/) INF: How to Obtain the Latest SQL Server 2000 Service Pack