SQL Server Q&A

Symptoms
Consider the following scenario:On a Microsoft Windows-based computer, you apply Cumulative Update 3 for SQL Server 2008 Service Pack 1 by using a slipstream installation.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/) Cumulative update package 3 for SQL Server 2008 Service Pack 1You tryto uninstall this cumulative update after the slipstream installation.In this scenario, the Full-Text Search component is not completely rolled back to the SQL Server 2008 Service Pack 1 version.
To confirm whether you are encountering this issue, you can check the SQL Server Full-Text Key file (Sql_fulltext_keyfile.dll) in theSQL Server 2008 Installation folder. Typically, this file is located in the following folder:
%ProgramFiles%\Microsoft SQL Server\MSQL10.<Instance Name>\MSSQLIf this file has a file version that is greater than 10.00.2531.00, the Full-Text Search component is not completely rolled back to the SQL Server 2008 Service Pack 1 version.
Resolution
To work around this problem, follow these steps: In Control Panel, open Add or Remove Programs.
Note For Windows Vista or for Windows Server 2008, open Programs and Features.In the Currently installed programs list, click Microsoft SQL Server 2008, and then click Change/Remove.
Note For Windows Vista or Windows Server 2008, right-click Microsoft SQL Server 2008, and then click Change.In the SQL Server 2008 dialog box, click Remove.Select the appropriate instance or instances, and then click Next.In the Select Features window, select Full-Text Search from the Features list.Complete the Remove SQL Server 2008 wizard, as appropriate for your configuration.Click Start, point to All Programs, point to Microsoft SQL Server 2008, point to Configuration Tools, and then click SQL Server Installation Center.Click Installation, and then click New SQL Server stand-alone installation or add features to an existing installation.In the Browse For Folder dialog box, locate the SQL Server 2008 installationfolder that you want, and then click OK.In the SQL Server 2008 Setup window,click Next to move to the Installation Type phase.Click Add Features to an existing instance of SQL Server 2008, select the appropriate instance in the drop-down list, and then click Next to move to the Feature Selection phase.Select Full-Text Search from the Features list.Follow the steps in the Full-Text Search installation wizard.Note There is no workaround for cluster scenarios. To roll back the cumulative update, you must reinstall the clustered instance of SQL Server.

Symptoms
On a server that is running Windows Server 2003, you upgrade from Microsoft SQL Server 2000 to Microsoft SQL Server 2005. However, an access violation occurs during the startup of the SQL Server service. Additionally, the following error message is logged in the SQL Server Errorlog file:

DateTime spid4s * Exception Address = 78144D3A Module(MSVCR80+00014D3A)
DateTime spid4s * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
DateTime spid4s * Access Violation occurred reading address 00000006
Unable to update password policyMessages that resemble the following are logged in the SQLSetup0005_ServerName_SQL.log file:

Service MSSQL$InstanceName with parameters ‘-m SqlSetup -r -T4022 -T4010′ is being started at DateTime
MSI (s) (80:A4) [12:46:36:153]: Running as a service.
Failed to start service MSSQL$InstanceName. Error code 17185
Service failed unexpectedly (17185)
Error Code: 0×80074321 (17185)
Windows Error Text: Source File Name: sqlsetuplib\service.cpp
Compiler Timestamp: DateTime
Function Name: sqls::Service::Start
Source Line Number: 301This problem occurs when SQL Server 2005 is trying to recover the resource database.
This problem occurs if the following conditions are true: The syslogins table in SQL Server 2000 has a SQL Server login.The password of the SQL Server login does not comply with the password policy in Windows Server 2003.
Resolution
To work around this problem, use one of the following methods.
Note We recommend that you use Method 1. Use Method 2 only as a temporary workaround for migration purposes.
Method 1: Update the password of the SQL Server login to comply with the password policy in Windows Server 2003Note You may not know the specific SQL Server login whose password does not comply with the password policy. In this case, you must repeat step 1 through step 4 to examine each SQL Server login until you find the problematic SQL Server login. Start the SQL Server service by using trace flag 4606. Open SQL Server Configuration Manager.Click SQL Server 2005 Services, and then double-click SQL Server (InstanceName).In the SQL Server (InstanceName) Properties dialog box, click the Advanced tab.On the Advanced tab, add the following text at the end of the existing string in the Startup Parameters box:
-T4606Click OK.Right-click SQL Server (InstanceName), and then click Start.Update the password of the SQL Server login.Open SQL Server Management Studio, and then connect to the instance of SQL Server 2005.Click New Query.In the query window, run the following statement:

ALTER LOGIN [LoginName] WITH PASSWORD = ‘NewPWD’NotesLoginName represents the name of the SQL Server login that causes the problem.NewPWD represents the new password that complies with the password policy in Windows Server 2003.Remove trace flag 4606 from the startup parameters for the SQL Server service. Open SQL Server Configuration Manager.Click SQL Server 2005 Services, and then double-click SQL Server (InstanceName).In the SQL Server (InstanceName) Properties dialog box, click the Advanced tab.On the Advanced tab, remove the following text from the existing string in the Startup Parameters box:
-T4606Click OK.Right-click SQL Server (InstanceName), and then click Restart.
Method 2: Disable the password policy enforcement feature for the SQL Server loginNote Use Method 2 only as a temporary workaround for migration purposes until you update the password.
Important These steps may increase your security risk. These steps may also make the computer or the network more vulnerable to attack by malicious users or by malicious software such as viruses. We recommend the process that this article describes to enable programs to operate as they are designed to or to implement specific program capabilities. Before you make these changes, we recommend that you evaluate the risks that are associated with implementing this process in your particular environment. If you decide to implement this process, take any appropriate additional steps to help protect the system. We recommend that you use this process only if you really require this process. Start the SQL Server service by using trace flag 4606. Open SQL Server Configuration Manager.Click SQL Server 2005 Services, and then double-click SQL Server (InstanceName).In the SQL Server (InstanceName) Properties dialog box, click the Advanced tab.On the Advanced tab, add the following text at the end of the existing string in the Startup Parameters box:
-T4606Click OK.Right-click SQL Server (InstanceName), and then click Start.Open SQL Server Management Studio, and then connect to the instance of SQL Server 2005.Click New Query.In the query window, run the following statement:

ALTER LOGIN [LoginName] WITH CHECK_POLICY = OFFRemove trace flag 4606 from the startup parameters for the SQL Server service. Open SQL Server Configuration Manager.Click SQL Server 2005 Services, and then double-click SQL Server (InstanceName).In the SQL Server (InstanceName) Properties dialog box, click the Advanced tab.On the Advanced tab, remove the following text from the existing string in the Startup Parameters box:
-T4606Click OK.Right-click SQL Server (InstanceName), and then click Restart.

Symptoms
This article describes how to create a shared package configuration file so that you can make configuration changes easily in Microsoft SQL Server 2008 Integration Services (SSIS).
Resolution
Description of a shared package configuration fileWhen you deploy sets of related SSIS packages, it is more efficient to reconfigure these packages by including shared settings. For example, a connection string might be shared across multiple packages to point to a frequently used database server. The connection string may have to be changed when the system is deployed from a test environment to a production environment or in other scenarios.
You can apply new settings to a package at runtime by using package configurations. Package configurations are created by using the Package Configurations tool in Business Intelligence Development Studio (BI Development Studio). By using the Package Configuration Organizer dialog box and the Package Configuration Wizard under Package Configurations, you can configure how XML and database configurations are applied to a package before it runs. Additionally, you can edit these configurations when the system is migrated. This mechanism is designed for use with individual packages. However, you can share a single package configuration file across multiple packages. This enables you to change package configurations in one place.
How to create a shared package configuration fileBefore you create a shared package configuration file, you must first uniformly design the set of packages. You must name every object consistently in the set of related packages that have to be configured together. For example, if you want the shared package configuration file to configure the ConnectionString property of a connection manager that is named “ReportsDB” in one package, every other package that shares this configuration file must also contain a ‘ReportsDB’ connection manager. If packages do not contain objects that are specified in the shared package configuration file, the packages cannot use the shared package configuration file. After you have verified that the packages contain consistent object names, you can create the shared package configuration file. To do this, follow these steps:In BI Development Studio, open the first package in Solution Explorer.In SSIS Designer, click the Package Explorer tab, and then on the SSIS menu, click Package Configurations.In the Package Configuration Organizer dialog box, click Enable package configurations, and then click Add.On the Welcome page of the Package Configuration Wizard, click Next.On the Select Configuration Type page, specify the configuration type, and then set the properties that are relevant to the configuration type.On the Select Properties to Export page, select only the properties that you want to configure across multiple packages.
Note If the configuration type supports only one property, the title of this wizard page is Select Target Property.On the Completing the Wizard page, type the name of the shared package configuration file, and then click Finish.Test this first package to make sure that it works correctly.Open each additional package in BI Development Studio, and then in the Package Configuration Organizer dialog box, enable package configurations.Add the file that you created in step 7. When you are prompted to reuse or overwrite the existing file, click Reuse Existing.

Symptoms
In Microsoft SQL Server 2008, when you click the View Single Column Profile By Column buttonin the Data Profile Viewer (DataProfileViewer.exe), you receive the following error message:

Unhandled Exception
Resolution
The fix for this issue was first released in Cumulative Update 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:
956717?(http://support.microsoft.com/kb/956717/) Cumulative update package 1 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

Symptoms
In Microsoft SQL Server 2008, you create a cursor. You try to update the cursor by using a statement. The statement includes an ORDER BY clause. When you update rows by using the cursor, the update may take a long time to finish.
This problem also occurs in Microsoft SQL Server 2005. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
955694?(http://support.microsoft.com/kb/955694/) FIX: When you update rows by using a cursor in SQL Server 2005, the update may take a long time to finish
Resolution
The fix for this issue was first released in Cumulative Update 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:
956717?(http://support.microsoft.com/kb/956717/) Cumulative update package 1 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
Additional informationThe following sections include important information about the fix.Steps to enable the fixIn SQL Server Configuration Manager, add the -T4128 startup parameter to the instance of SQL Server. To do this, follow these steps:In SQL Server Configuration Manager, click SQL Server Services.In the right pane, right-click SQL Server (instance_name), and then click Properties.In the Properties dialog box, click the Advanced tab.On the Advanced tab, locate the Startup Parameters item, and then type -T4128 at the end of the existing value.
Note Separate the parameter from other parameters by using a semicolon (;).Click OK, and then close SQL Server Configuration Manager.Restart the SQL Server 2008 database engine service for the trace flag to take effect.Issues that the fix addressesThe fix addresses this issue when the following conditions are true:The user is updating by using a cursor. This can be identified from the Showplan by the UPDATE CURSOR operator.Before the execution of a Clustered Index Update statement, a Nested Loop statement is executed. This statement includes a cursor work table (CWT) that is used as a object, together with the Inner Join parameter. Then, the CWT object is used in the Clustered Index Statement as part of the execution plan for the node.There is an ORDER BY clause in the DECLARE CURSOR (or equivalent API) statement. You can only determine this with certainty by verifying the DECLARE CURSOR statement text.

Symptoms
Consider the following scenario: You are trying to installa clustered instance of SQL Server 2008.In the Server Configuration page of the installation wizard, you click Browse to specify a domain account for a specific service in the Account Name text box.In the Select Users, Computers, or Groups dialog box, you type a letter, and you find the account by clicking Find Now. You select the account, and then you click OK.You specify a password for the account, and then you click Next.In this scenario,account validation fails even though you have specified the correct domain account and password.
Resolution
This issue occurs because the Account Name text box does not bind the data correctly. When the Setup program validates the domain account, it validates the search string value instead of the result value.