SQL Server Q&A

Symptoms
This release of Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0 Service Pack 4 (SP4) provides updates for the database components of a SQL Server 7.0 or MSDE 1.0 installation. This article introduces the following information about SP4:
Service Pack versions and the removal of Service Pack 4
How to download andto extract Service Pack 4
How to install Service Pack 4
How to redistribute database components and Service Pack 4 client components
Explains the issues of running Service Pack 4 that affect sites
Resolution
IntroductionService pack versionsRemoving SP4Downloading and extracting SP4Disk space requirements for SP4Extracting Database Components SP4Service pack installationBack up SQL Server databasesMake sure the system databases have sufficient free spacePrepare cluster configurationsStop applications before you run the Service Pack 4 Setup programInstall Database Components SP4Restart servicesRecluster a cluster configurationRestart applicationsInstalling on replicated serversInstalling SP4 on a server that provides a remote distribution database for merge publicationsUpgrading a merge Publisher that receives new subscriptions to SP4 from the original release of SQL Server 7.0Installing SP4 on a merge replication SubscriberRunning SP4 replication with earlier versions of SQL ServerUpdating Access 2000 (Jet 4.0) merge replication SubscribersRedistributing Database Components SP4 client componentsDocumentation notesUpgrading client-only computers after you install SP4Upgrading Japanese database servers to Windows 2000 after you install SP4Applying SP4 to a later versionInstalling SP4 on Windows NT 4.0, Terminal Server EditionNew Troubleshooting GuideReplication enhancements Optimizing replication synchronization Performance enhancements for merge processing of the initial snapshot Merge metadata cleanupChanges in Database Components New database option New sqlservr startup option Change in cursor behavior Changes in SQL Server ProfilerSQL Server clustering white paperData Transformation ServicesExporting to Oracle databasesUsing the DTS Test featureChanges to the Execute Package dialog boxData Transformation Services error messagesUse of the Set keyword in Microsoft Visual Basic scriptsChanges in DTS packages SQL Server Agent proxy account improvementsSQL Server 7.0 and Exchange 5.5The French version of SQL Server Books OnlineRepository changesImporting from DB2 and Informix databasesImproved retrieval of versioned objects

Symptoms
This article describes late-breaking changes in Microsoft SQL Server 2000 Service Pack 4 (SP4) that are not documented in some or all of the available SQL Server readme files. The following is a list of the readme files that are available for SQL Server 2000 SP4:ReadmeSql2k32asp4.htm: “Service Pack 4 for Microsoft SQL Server 2000 – Analysis Services” ReadmeSql2k32sp4.htm: “Service Pack 4 for Microsoft SQL Server 2000 – Database Components” ReadmeSql2k32desksp4.htm: “Service Pack 4 for Microsoft SQL Server 2000 – Desktop Engine (MSDE 2000)” ReadmeSql2k64sp4.htm: “Service Pack 4 for Microsoft SQL Server 2000 (64-bit)”
Resolution
Information that has changedThe following information has changed since publication of the SP4 readme files.Running Address Windowing Extensions (AWE)with SQL Server 2000 SP4Important We have found an issue with the final build of SQL Server 2000 SP4 that affects customers who run SQL Server with AWE support enabled. This issue does not affect the Itanium-based computers or other components of SQL Server beyond the relational databases, such as SQL Server 2000 Analysis Services, SQL Server 2000 Reporting Services, and SQL Server 2000 Notification Services. This issue only affects computers that have more than 2 GB of memory where AWE has been enabled and where more than half of the total system memory is being allocated to a single instance of SQL Server. Customers who have this configuration should apply the hotfix in the following Microsoft Knowledge Base article after they upgrade to SQL Server 2000 SP4:
899761?(http://support.microsoft.com/kb/899761/) FIX: Not all memory is available when AWE is enabled on a computer that is running a 32-bit version of SQL Server 2000 SP4Hotfixes are not included in Service Pack 4Hotfixes 836651 and 826906 were not included in SP4. Customers who apply SP4 in addition to this hotfix will have the hotfix overwritten. A post-SP4 hotfix for these issues will be available shortly.
For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
836651?(http://support.microsoft.com/kb/836651/) FIX: You receive query results that were not expected when you use both ANSI joins and non-ANSI joins
826906?(http://support.microsoft.com/kb/826906/) FIX: A query that uses a view that contains a correlated subquery and an aggregate runs slowlySmssql2ksp4.pdf fileSQL Server 2000 SP4 includes a Smssql2ksp4.pdf file. This file contains a reference to Sms2kdef.bat and Sql2kdef.iss. However, these files no longer exist in the SQL Server 2000 SP4 package.
Note Every time that you run an attended installation of Database Components, the setup options will be written to the Setup.iss file that is located in the \System folder. For more information, see the “unattended installation” section in the SQL Server 2000 SP4 readme file.SQL Query Analyzer SQL Query Analyzer will permit connections to SQL Server 2005. However, some functionality may not be available. This functionality includes the following:Server Trace: The Server Trace window will not display any data. Object Search: The object search will not work. Manage Statistics: Existing statistics will not be displayed in the Manage Statistics dialog box.Registry extended stored procedures Changes were made to extended stored procedures that access the registry. Only members of the system administrator server roles may freely use these extended stored procedures without any difference in behavior from earlier service packs. For example, when you run queries that call registry extended stored procedures, you may receive an “access is denied” error message.
Warning The registry extended stored procedures are undocumented. They are subject to change without notice. And they are not officially supported to be used by applications. Therefore, we do not recommend that you call these procedures in the client code. There is no guarantee that these procedures will be available in later versions of SQL Server.
If all the following conditions are true, see Microsoft Knowledge Base article 887165: You are using registry extended stored procedures.You are not a system administrator.You require that SQL Server behave the way that it did before you upgraded to SP4.
887165?(http://support.microsoft.com/kb/887165/) BUG: You may receive an “Access is denied” error message when a query calls an extended stored procedure after you upgrade from SQL Server 2000 to SQL Server 2000 Service Pack 4 Upgrade SQL Server 2000 Desktop Engine (MSDE) to other versions of SQL Server 2000 If you upgrade SQL Server 2000 Desktop Engine(MSDE) to a different version of SQL Server 2000, such as to Personal Edition or to Developer Edition, you must reinstall SQL Server 2000 SP4 after you upgrade MSDE. Avoid errors when you connect to a third-party database by using Analysis Services If you are running Analysis Services SP4 on an AMD 64 system, and you plan to connect to a third-party database, confirm that the connectivity components for that database are certified for use under WoW by the third-party vendor. If those components are not certified, you may experience errors.Avoid errors when you try to obtain diagnostics for an instance of SP3 If you are installing SP4 on a computer where you will also be running SQL Server 2000 SP3, copy the Dbghelp.dll file from the SP4 BINN folder, and then add the Dbghelp.dll file to the SP3 BINN folder. After you do this, you can successfully capture diagnostics for the instance of SP3 if you have to do this later. Summary dialog box for SQL Server 2000 64-bit SP4 Setup After you have upgraded to SP4 on SQL Server 2000 64-bit, the summary dialog box may show the instance of SQL Server as “success.”However, dependant components or features may show as “not selected.” This is known behavior. For more information, see the SQL Server SP4 Setup log. Add new features to SQL Server 2000 (64-bit) SQL Server 2000 64-bit SP4may not correctly add features or may not correctly upgrade added features. Thisproblem may occur if the correct Windows Installer file name or ProductCode was not in the command line or in the Setup.ini file when the feature was originally added. To resolve this problem, uninstall the newly added feature, and then reinstall it by using the correct Windows Installer file name or ProductCode.
Note Although a feature has been added incorrectly, it may still appear in the Add or Remove Programs tool inControl Panel.Upgrade from SQL Server 2000 64-bit SP4 Beta to the Release to Manufacturing (RTM) versionYou must uninstall SQL Server 2000 64-bit SP4 Beta before you install the RTM version. Uninstall a SQL Server 2000 64-bit SP4clusterTo uninstall a SQL Server 2000 64-bit cluster, you will have to use the Unattended Setup feature and a corresponding Template.ini file. This Template.ini file should contain settings for the SQL Server 2000 64-bit installation. For more information about Template.ini files, see SQL Server 2000 64-bit Books Online.64-bitAnalysis Services installationIf Analysis Services 64-bit is not installed as part of an initial configuration, you cannot add Analysis Services through instance maintenance. If you want to add Analysis Services after the Setup process is completed, you will have to install Analysis Services as a component of a new, additional instance. 64-bit maintenance modeDuring maintenance mode, adding 64-bit Analysis Services features to an existing 64-bit SQL Server feature, or trying to add a 64-bit SQL Server feature to an existing 64-bit Analysis Services feature instance is not supported.
Items that are not documented correctly in the Database Components SP4 readme file3.5 Install database components on replicated servers Section 3.5 of the Database Components SP4 readme file (ReadmeSql2k32sp4.htm) incorrectly refers to “an instance of MSDE 2000.” Instead, it should refer to “an instance of the database engine.”
Section 3.5 of the Database Components SP4 readme file also states the following:
Before you upgrade to SP4, ensure that the Windows account under which the SQL Server service runs is a member of the sysadmin fixed server role. This statement is incorrect.You do not have to change the Windows account under which the SQL Server service runs before you upgrade to SP4. 3.4 Install on a failover clusterSection 3.4 of the 64-bit SP4 readme file (ReadmeSql2k64sp4.htm) currently states the following:
If you need to rebuild a node in the failover cluster, perform the following stepsRebuild the node in the failover cluster. For more information about rebuilding a node, see “How to recover from failover cluster failure in Scenario 1″ in SQL Server 2000 Books Online.Run the original SQL Server 2000 Setup program to add the node back to the failover cluster.Run the SQL Server 2000 SP4 Setup program on the active node that is running virtual server.NotesIf you run the SQL Server 2000 SP4 Setup program from the node that is runningvirtual server, you may have to reinstall SQL Server 2000 SP4 on all nodes. You may also have to run the database upgrade scripts again. This requirement may be a problem if the instance of SQL Server on the existing nodes has a post-SP4 hotfix installed.You can perform a binary-only installation by running the SQL Server 2000 SP4 Setup program on the new node if the node is a passive node. A binary-only installation only updates the binaries on the node on which you runthe SQL Server 2000 SP4 Setup program. A binary-only installation does run the database upgrade scripts. If you want to perform a binary-only installation, we recommend that you perform the installation on a test server before you implement the installation on a production server.
Run SP4 Setup from the node that was not rebuilt and that owns the SQL resources. 1.2.2 Considerations for an instance in a Replication or Log Shipping topologyIn section 1.2.2.of the ReadmeSql2k32sp4.htm file, under “Log Shipping and Database components,” the name of the referenced stored procedure is incorrect. It should be “sp_vupgrade_replication”.

Symptoms
During the setup or patching of a server that is running Microsoft SQL Server 2005 or Microsoft SQL Server 2008, the SQL Server engine (SQLServr.exe) process consumes significant physical random access memory (RAM) of more than 4 gigabytes (GB).
Note This problem is more relevant on Non-Uniform Memory Access (NUMA) systems or on systems that are configured to use large memory pages.
Resolution
Cumulative update informationSQL Server 2005 To resolve this problem, install this cumulative update package for SQL Server 2005 Service Pack 3.
Note Starting with this cumulative update, the SQL Server hotfix.exe instructs the SQL Server engine to start a minimized configuration by using the -f command line parameter.This reduces the required memory footprint.
The fix for this issue was first released in Cumulative Update 3. For more information about how to obtain this cumulative update package for SQL Server 2005 Service Pack 3, click the following article number to view the article in the Microsoft Knowledge Base:
967909?(http://support.microsoft.com/kb/967909/) Cumulative update package 3 for SQL Server 2005 Service Pack 3Note 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 Service Pack 3 fix release. We recommend 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:
960598?(http://support.microsoft.com/kb/960598/) The SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 3 was released Microsoft SQL Server 2005 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2005 Service Pack 3 hotfix to an installation of SQL Server 2005 Service Pack 3. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.The release version of SQL Server 2008Important You must install this cumulative update package if your computer is running the release version of SQL Server 2008.
The fix for this issue was first released in Cumulative Update 5 for SQL Server 2008. For more information about how to obtain this cumulative update package for SQL Server 2008, click the following article number to view the article in the Microsoft Knowledge Base:
969531?(http://support.microsoft.com/kb/969531/) Cumulative update package 5 for SQL Server 2008Note 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 2008 fix release. We recommend 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:
956909?(http://support.microsoft.com/kb/956909/) The SQL Server 2008 builds that were released after SQL Server 2008 was releasedSQL Server 2008 Service Pack 1Important You must install this cumulative update package if your computer is running SQL Server 2008 Service Pack 1.
The fix for this issue was also released in Cumulative Update3 for SQL Server 2008 Service Pack 1. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
971491?(http://support.microsoft.com/kb/971491/LN/) Cumulative update package 3 for SQL Server 2008 Service Pack 1Note 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 2008 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:
970365?(http://support.microsoft.com/kb/970365/LN/) The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 1 was releasedMicrosoft SQL Server 2008hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2008 Service Pack 1 hotfix to an installation of SQL Server 2008 Service Pack 1. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

Symptoms
After you repair an instance of Microsoft SQL Server 2008, the Shared Memory protocol for the instance is always enabled. Additionally, the VIA protocol for the instance is always disabled.
You can use several methods to run the repair operation. For example, you can repair an instance by using theAdd or Remove Programs item in Control Panel.
Resolution
Service pack informationTo resolve this problem, obtain the latest service pack for SQL Server 2008. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
968382?(http://support.microsoft.com/kb/968382/) How to obtain the latest service pack for SQL Server 2008

Symptoms
In Microsoft SQL Server 2005 and in Microsoft SQL Server 2008, the CPU usage and the memory usage increase gradually. Additionally, you find many orphaned session IDs when you run a query against the sys.sysprocesses view. The status of those orphaned session IDs is dormant. The value of the last_batch column for these session IDs is five minutes earlier than the current time.
Note In SQL Server 2005, the maximum retention period of the internal connection pooling is five minutes.
Resolution
Cumulative update information for SQL Server 2005 Service Pack 2 The fix for this issue was first released in Cumulative Update13 for SQL Server 2005 Service Pack 2. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
967908?(http://support.microsoft.com/kb/967908/LN/) Cumulative update package 13 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 2005hotfixes 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.
Cumulative update information for SQL Server 2005 Service Pack 3 The fix for this issue was also released in Cumulative Update3 for SQL Server 2005 Service Pack 3. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
967909?(http://support.microsoft.com/kb/967909/LN/) Cumulative update package 3 for SQL Server 2005 Service Pack 3Note 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:
960598?(http://support.microsoft.com/kb/960598/LN/) The SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 3 was releasedMicrosoft SQL Server 2005hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2005 Service Pack 3 hotfix to an installation of SQL Server 2005 Service Pack 3. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.
Cumulative update information for SQL Server 2008The fix for this issue was first released in Cumulative Update 5 for SQL Server 2008. For more information about how to obtain this cumulative update package for SQL Server 2008, click the following article number to view the article in the Microsoft Knowledge Base:
969531?(http://support.microsoft.com/kb/969531/) Cumulative update package 5 for SQL Server 2008Note 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 2008 fix release. We recommend 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:
956909?(http://support.microsoft.com/kb/956909/) The SQL Server 2008 builds that were released after SQL Server 2008 was released
Cumulative update information for SQL Server 2008 Service Pack 1The fix for this issue was first released in Cumulative Update 2 for SQL Server 2008 Service Pack 1. For more information about how to obtain this cumulative update package for SQL Server 2008, click the following article number to view the article in the Microsoft Knowledge Base:
970315?(http://support.microsoft.com/kb/970315/) Cumulative update package 2 for SQL Server 2008 Service Pack 1Note 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 2008 fix release. We recommend 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:
970365?(http://support.microsoft.com/kb/970365/) The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 1 was released

Symptoms
Consider the following scenario.In Microsoft SQL Server 2008, you create a SQL Server Integration Services (SSIS) package.You use the SQL Server configuration type to create a configuration table for the package. In the configuration table, there is an invalid package path in the PackagePath column. For example, a package path does not exist. The package path can be for a connection manager, for an event handler, or for a log provider.In this scenario, when you run the package in SQL Server Business Intelligence Development Studio (BIDS), you receive the following error message:

Error at Package: The connection “Connection Name” is not found. This error is thrown by Connections collection when the specific connection element is not found.
(Microsoft.DataTransformationServices.VsIntegration)
This issue is likely to occur when you use a shared configuration table in multiple packages. In these packages, the configuration filter values in the configuration table are the same for each package. For example, an object, such as a connection manager, does not exist in all packages. Therefore, you experience this issue when you run packages that do not have this object in SQL Server BIDS.
Note This issue does not occur in SQL Server 2005 Integration Services.
Resolution
SQL Server 2008 Service Pack 1Important You must install this fix if you are running SQL Server 2008 Service Pack 1.
The fix for this issue was first released in Cumulative Update5 for SQL Server 2008 Service Pack 1. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
975977?(http://support.microsoft.com/kb/975977/LN/) Cumulative update package 5 for SQL Server 2008 Service Pack 1Note 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 2008 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:
970365?(http://support.microsoft.com/kb/970365/LN/) The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 1 was releasedMicrosoft SQL Server 2008hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2008 Service Pack 1 hotfix to an installation of SQL Server 2008 Service Pack 1. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.