SQL Server Q&A

Symptoms
After you uninstall Microsoft SQL Server 2005 from a computer that has Microsoft SQL Server 2008 installed, the Microsoft ActiveX Script task in a SQL Server Integration Services package does not run.Additionally, when you try to add an ActiveX Script Task to a new package in BIDS, the task will show a validation error pointing ScriptHost can’t be instantiated.
Resolution
This problem occurs because the Axscphst.dllfile is deleted or is unregistered after you uninstall SQL Server 2005. The Axscphst.dllfile is in the following location:
%SystemRoot0%\Program Files\Microsoft SQL Server\100\COMNote SQL Server 2005 and SQL Server 2008 use the Axscphst.dll file.

Symptoms
You install a Microsoft SQL Server 2005 hotfixpackage on a computer that has a 64-bit processor. When youconnect to SQL Server 2005 Integration Services (SSIS) by using SQL Server Management Studio, you receive an error message that resembles the following:

TITLE: Microsoft SQL Server Management Studio
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
ADDITIONAL INFORMATION:
Connect to SSIS Service on machine “ComputerName” failed:
Error loading type library/DLL.Additionally, when you try to create or to edit a maintenance plan, you receive one of the following error messages:
Error message 1

Exception from HRESULT: 0xC0010014 (Microsoft.SqlServer.DTSRuntimeWrap)
Program Location:
at Microsoft.SqlServer.Dts.Runtime.ConnectionManager.get_Properties()
at Microsoft.SqlServer.Management.DatabaseMaintenance.TaskContextUI.InitializeConnectionCombo(String connectionName)
at Microsoft.SqlServer.Management.DatabaseMaintenance.DBMaintTaskBackupForm.SetTask(DbMaintenanceBackupTask task)
at Microsoft.SqlServer.Management.DatabaseMaintenance.DBMaintTaskBackupForm..ctor(TaskHost taskHost, IServiceProvider provider)
at Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintTaskBackupUI.GetView()at Microsoft.DataTransformationServices.Design.DtrTaskDesigner.GetTaskEditor()
Error message 2

Exception from HRESULT: 0xC0010014 (Microsoft.SqlServer.DTSRuntimeWrap)
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Sequence.get_Properties()
at Microsoft.DataTransformationServices.Design.DtsBaseDesigner.PreFilterProperties(IDictionary properties)
at Microsoft.DataTransformationServices.Design.DtsPropExpressionsDesigner.PreFilterProperties(IDictionary properties)
at Microsoft.DataTransformationServices.Design.DbMaintSequenceDesigner.PreFilterProperties(IDictionary properties)
at System.ComponentModel.Design.ComponentDesigner.System.ComponentModel.Design.IDesignerFilter.PreFilterProperties(IDictionary properties)
at System.ComponentModel.Design.TypeDescriptorFilterService.System.ComponentModel.Design.ITypeDescriptorFilterService.FilterProperties(IComponent component, IDictionary properties)
at Microsoft.DataTransformationServices.Design.DtsTypeDescriptorFilterService.System.ComponentModel.Design.ITypeDescriptorFilterService.FilterProperties(IComponent component, IDictionary properties)
at System.ComponentModel.TypeDescriptor.PipelineFilter(Int32 pipelineType, ICollection members, Object instance, IDictionary cache)
at System.ComponentModel.TypeDescriptor.GetPropertiesImpl(Object component, Attribute[] attributes, Boolean noCustomTypeDesc, Boolean noAttributes)
at System.ComponentModel.TypeDescriptor.GetProperties(Object component)
at Microsoft.DataWarehouse.Design.Cud.VsTransaction.ChangeProperty(Object obj, String propertyName)
at Microsoft.DataTransformationServices.Design.DtsBasePackageDesigner.CreateExecutable(String moniker, IDTSSequence container, String name)This problem occurs if one of the following conditions is true:The SQL Server 2005 Integration Services hotfix package is installed. However, the SQL Server 2005 Tools hotfix package is not installed.You install the SQL Server 2005 Tools hotfix package before you install the SQL Server 2005 Integration Services hotfix package.You are running two instances of SQL Server 2005 on the computer. Additionally, the versions of both instances of SQL Server are earlier versions than Microsoft SQL Server 2005 Service Pack 2 (SP2).The computer is running an instance of SQL Server 2005 with SP2. Additionally, you install a post-SP2 hotfix on this instance. Then you install a second instance of SQL Server 2005. On the second instance, you install SQL Server 2005 SP2.
Resolution
This problem occurs because the installer unregisters the 32-bit Dts.dll file when the installer installs the 64-bit Dts.dll file. The 64-bit Dts.dll file is installed together with SQL Server 2005 SP2. Additionally,the 64-bit Dts.dll file may be installed together with any SSIS hotfix package that contains the 64-bit Dts.dll file.

Symptoms
Consider the following scenario:You install a named instance of one or more of the following Microsoft SQL Server 2005 components:SQL Server Database ServicesSQL Server Analysis ServicesOn the “Components to Install” page, you specify different installation locations for one or more of the components as follows:You specify that Database Services is to be installed in a folder that is named “MSSQLServer.”You specify that Analysis Services is to be installed in a folder that is named “MSSQLServerOLAPService.”Note The names of these folders are case sensitive.In this scenario, you may encounter the following symptoms.
Symptom 1Specific .ini files for the components that are mentioned earlier contain information as follows:If you install Database Services, information that resembles the following is contained in the Perf-InstanceNamesqlctr.ini file:
[info]
drivername=MSSQL$InstanceName
trusted=
symbolfile=Drive:\MSSQL$InstanceName\MSSQL.X\MSSQL\Binn\sqlctr.hIf you install Analysis Services, information that resembles the following is contained in the Perf-InstanceNamemsmdctr.ini file:
[info]
drivername=MSOLAP$InstanceName
symbolfile=Drive:\MSOLAP$InstanceName\MSSQL.X\OLAP\bin\msmdctr.h
Symptom 2During the installation of SQL Server 2005, you receive the following error message:

The setup has encountered an unexpected error while installing performance counters. The error is: The specified driver is invalid.
Symptom 3After you click Ignore when you receive the error message that is mentioned in Symptom 2, SQL Server Setup finishes its operation successfully. However, the following error message is logged in the SQLSetup0001_ComputerName_SQL.log file:

Error Code: 2001
MSI (s) (44!C4) [Time]: Product: Microsoft SQL Server 2005 — Error 29528. The setup has encountered an unexpected error while Installing performance counters. The error is: The specified driver is invalid.
Error 29528. The setup has encountered an unexpected error while Installing performance counters. The error is: The specified driver is invalid.
<Failure Type=’Ignored’>
Symptom 4If you install SQL Server 2005 Service Pack 1 (SP1) or SQL Server 2005 Service Pack 2 (SP2) afterward, you experience the following issues.SQL Server 2005 SP1When you install SQL Server 2005 SP1, you receive the following error message:

A recently applied update, KB913090, failed to install.The followingerror message is logged in the SQL9_Hotfix_KB913090.log file:

Date and Time Failed to read registry key: Debug
Date and Time MSP returned 1603: A fatal error occurred during installation.
Date and Time Successfully opened registry key: Software\Policies\Microsoft\Windows\Installer
Date and Time Failed to read registry key: Debug
Date and Time Unable to install MSP file: \\ComputerName\f$\64b4ce0abc66d8453d93b0a1fa\HotFixSQL\Files\sqlrun_sql.msp
Date and Time The following exception occurred: Unable to install Windows Installer MSP file Date and Time File: \depot\sqlvault\setupmain\setup\sqlse\sqlsedll\copyengine.cpp Line: 856The following error message is logged in the SQL9_Hotfix_KB913090_sqlrun_sql.msp.log file:

MSI (s) (C8:A0) [11:15:21:752]: Product: Microsoft SQL Server 2005 – Update ‘Service Pack 1 for SQL Server Database Services 2005 ENU (KB913090)’ could not be installed. Error code 1603. Additional information is available in the log file C:\WINDOWS\Hotfix\SQL9\Logs\SQL9_Hotfix_KB913090_sqlrun_sql.msp.log.
MSI (s) (C8:A0) [Time]: Note: 1: 1729
MSI (s) (C8:A0) [Time]: Transforming table Error.
MSI (s) (C8:A0) [Time]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (C8:A0) [Time]: Transforming table Error.
MSI (s) (C8:A0) [Time]: Transforming table Error.
MSI (s) (C8:A0) [Time]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (C8:A0) [Time]: Transforming table Error.
MSI (s) (C8:A0) [Time]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (C8:A0) [Time]: Transforming table Error.
MSI (s) (C8:A0) [Time]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (C8:A0) [Time]: Transforming table Error.
MSI (s) (C8:A0) [Time]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (C8:A0) [Time]: Transforming table Error.
MSI (s) (C8:A0) [Time]: Transforming table Error.
MSI (s) (C8:A0) [Time]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (C8:A0) [Time]: Transforming table Error.
MSI (s) (C8:A0) [Time]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (C8:A0) [Time]: Transforming table Error.
MSI (s) (C8:A0) [Time]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (C8:A0) [Time]: Product: Microsoft SQL Server 2005 — Configuration failed.
MSI (s) (C8:A0) [Time]: Attempting to delete file C:\WINDOWS\Installer\46000.msp
MSI (s) (C8:A0) [Time]: Unable to delete the file. LastError = 32MSI (s) (C8:A0) [11:15:21:922]: Cleaning up uninstalled install packages, if any exist
MSI (s) (C8:A0) [Time]: MainEngineThread is returning 1603
MSI (s) (C8:F8) [Time]: Destroying RemoteAPI object.
MSI (s) (C8:98) [Time]: Custom Action Manager thread ending.
=== Logging stopped: Date and Time ===
MSI (c) (D8:04) [Time]: Decrementing counter to disable shutdown. If counter >= 0, shutdown will be denied.Counter after decrement: -1
MSI (c) (D8:04) [Time]: MainEngineThread is returning 1603
=== Verbose logging stopped: Date and Time ===SQL Server 2005 SP2When you install SQL Server 2005 SP2, you receive the following error message:

A recently applied update, KB921896, failed to install.Then, you continue the installation. Finally, Database Services and Analysis Services are not installed.The following error message is logged in the Summary.txt file:

Product: Database Services (SQL2005)
Product Version (Previous): 1399
Product Version (Final):
Status: Failure
Log File: C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB921896_sqlrun_sql.msp.log
Error Number: 29528
Error Description: MSP Error: 29528The setup has encountered an unexpected error while Installing performance counters. The error is: The specified driver is invalid.The following information is logged in the Hotfix.log file:

Date and Time Installing file: sqlrun_sql.msp
Date and Time Copy Engine: Creating MSP install log file at: C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB921896_sqlrun_sql.msp.log
Date and Time MSP Error: 29528The setup has encountered an unexpected error while Installing performance counters. The error is: The specified driver is invalid.
Date and Time MSP returned 1603: A fatal error occurred during installation.
Date and Time Copy Engine: Error, unable to install MSP file: e:\ddf143eddce69fe41b19cf97ff46\HotFixSQL\Files\sqlrun_sql.msp
Date and Time The following exception occurred: Unable to install Windows Installer MSP fileDate:
Date and TimeFile: \depot\sqlvault\stable\setupmainl1\setup\sqlse\sqlsedll\copyengine.cppLine: 800The following error message is logged in the SQL9_Hotfix_KB921896_sqlrun_sql.msp file:

<Func Name=’Do_sqlPerfmon2′>
<EndFunc Name=’Do_sqlPerfmon2′ Return=’2001′ GetLastError=’2′>
PerfTime Stop: Do_sqlPerfmon2 : Tue Mar 27 14:49:19 2007
Gathering darwin properties for failure handling.
MSI (s) (70!34) [Time]: Transforming table Error.
MSI (s) (70!34) [Time]: Note: 1: 2262 2: Error 3: -2147287038
Error Code: 2001
MSI (s) (70!34) [Time]: Product: Microsoft SQL Server 2005 — Error 29528. The setup has encountered an unexpected error while Installing performance counters. The error is: The specified driver is invalid.
Error 29528. The setup has encountered an unexpected error while Installing performance counters. The error is: The specified driver is invalid.
<EndFunc Name=’LaunchFunction’ Return=’2001′ GetLastError=’0′>
MSI (s) (70:B8) [Time]: User policy value ‘DisableRollback’ is 0
MSI (s) (70:B8) [Time]: Machine policy value ‘DisableRollback’ is 0
Action ended Time: InstallFinalize. Return value 3
Resolution
The Setup program performs a find-and-replace operation on the following files:Perf-InstanceNamesqlctr.iniPerf-InstanceNamemsmdctr.ini file The find-and-replace operation is case sensitive. If the folder that you specify to install the component has the same name as the service name of the default instance of the component, many strings in the file will be replaced. Therefore, you receive the error message.

Symptoms
Microsoft SQL Server 2005 Express Edition is a service-based product. If you build Microsoft Visual Studio 2005 applications on SQL Server 2005 Express Edition, you can detect the status of the SQL Server Express service when you start the application. You can use the ServiceController class to do the following:Detect the status of the SQL Server Express service.Start the SQL Server Express service if it is not started correctly.Note The default installation of SQL Server 2005 Express Edition uses an instance name of SQLEXPRESS. This instance name maps to the service name of MSSQL$SQLEXPRESS.
Resolution
To use the ServiceController class in a Visual Studio console application to detect and to start the SQL Server Express service, follow these steps: Start Visual Studio 2005.On the File menu, point to New, and then click Project.Click Visual Basic or Visual C# under Project types, and then click Console Application under Visual Studio installed templates.
Note By default, the Module1.vb file is created in the Visual Basic project. By default, the Program.cs file is created in the Visual C# project.Use ConsoleApplication1 as the name in the Name box, and then click OK.Add a reference to the “System.ServiceProcess” namespace. To do this, follow these steps: On the Project menu, click Add Reference.Click the .NET tab, click System.ServiceProcess, and then click OK.Replace the existing code with the following code.
Note Replace the code in the Module1.vb file in the Visual Basic project. Replace the code in the Program.cs file in the Visual C# project.
Visual Basic

Imports SystemImports System.ServiceProcessModule Module1Sub Main()Dim myServiceName As String = “MSSQL$SQLEXPRESS” ’service name of SQL Server ExpressDim status As String’service status (For example, Running or Stopped)Dim mySC As ServiceControllerConsole.WriteLine(“Service: ” & myServiceName)’display service status: For example, Running, Stopped, or PausedmySC = New ServiceController(myServiceName)Trystatus = mySC.Status.ToStringCatch ex As ExceptionConsole.WriteLine(“Service not found. It is probably not installed. [exception=" & ex.Message & "]“)Console.ReadLine()EndEnd TryConsole.WriteLine(“Service status : ” & status)’if service is Stopped or StopPending, you can run it with the following code.If mySC.Status.Equals(ServiceControllerStatus.Stopped) Or mySC.Status.Equals(ServiceControllerStatus.StopPending) ThenTryConsole.WriteLine(“Starting the service…”)mySC.Start()mySC.WaitForStatus(ServiceControllerStatus.Running)Console.WriteLine(“The service is now ” & mySC.Status.ToString)Catch ex As ExceptionConsole.WriteLine(“Error in starting the service: ” & ex.Message)End TryEnd IfConsole.WriteLine(“Press a key to end the application…”)Console.ReadLine()EndEnd SubEnd ModuleVisual C#

using System;using System.Collections.Generic;using System.Text;using System.ServiceProcess;namespace ConsoleApplication1{class Program{static void Main(){string myServiceName = “MSSQL$SQLEXPRESS”; //service name of SQL Server Expressstring status; //service status (For example, Running or Stopped)Console.WriteLine(“Service: ” + myServiceName);//display service status: For example, Running, Stopped, or PausedServiceController mySC = new ServiceController(myServiceName);try{status = mySC.Status.ToString();}catch (Exception ex){Console.WriteLine(“Service not found. It is probably not installed. [exception=" + ex.Message + "]“);Console.ReadLine();return;}//display service status: For example, Running, Stopped, or PausedConsole.WriteLine(“Service status : ” + status);//if service is Stopped or StopPending, you can run it with the following code.if (mySC.Status.Equals(ServiceControllerStatus.Stopped) | mySC.Status.Equals(ServiceControllerStatus.StopPending)){try{Console.WriteLine(“Starting the service…”);mySC.Start();mySC.WaitForStatus(ServiceControllerStatus.Running);Console.WriteLine(“The service is now ” + mySC.Status.ToString());}catch (Exception ex){Console.WriteLine(“Error in starting the service: ” + ex.Message);}}Console.WriteLine(“Press a key to end the application…”);Console.ReadLine();return;}}}Press CTRL+F5 to run the program.

Symptoms
You can create a Microsoft SQL Server 2005 Integration Services (SSIS) packageby using SQL Server Business Intelligence Development Studio. When you create this package, you can create an expression for a property of the SSIS packageto update or to populate the property at runtime. For example, if the SSIS package contains a Send Mail task, you can create an expression for the Subject property and for the MessageSource property. You can use the Subject property expression to dynamically update the subject of an e-mail message. You can use the MessageSource property expression to dynamically update variables in the e-mail message, such as variables that are populated by a Row Count transformation.
This article discusses how to create a dynamic subject or message for the Send Mail task.
Resolution
The following is a sample property expression for the Subject property in a Send Mail task.

“Package>>> ” + @[System::PackageName] +” was executed at>>> ” + (DT_WSTR, 40) @[System::StartTime] + ” by user>>> ” + @[System::UserName] + ” on Machine>>> ” + @[System::MachineName]If you use this sample property expression, the subject of an e-mail message is updated dynamically. The subject will include the following information: Text information
In this example, the e-mail messagesubject includesthe “Package>>> ” text information.System variables
The e-mail message includes the following system variables:PackageName
The package name.StartTime
The time that the package was executed. UserName
The user who executed the package.MachineName
The name of the computer on whichthe package was executed. You can also include more information in the expression, such as a user-defined variable. For example,a Data Flow task can include a Row Count transformation before the Send Mail task. (The Row Count transformation is used to count rows.) The Row Count transformation populates a user-defined variable that is named @myrowcount. This variable stores the count information in the data flow.
To specify that an e-mail message be sent only if the row count is smaller than a certain value, modify the control flow by using precedence constraints. To do this, follow these steps: In SQL Server Business Intelligence Development Studio, right-clickData Flow Task,and then click Add Precedence Constraint.Double-click the precedence constraint that you just created. In the Precedence Constraint Editor dialog box, click Expression and Constraint in Evaluation operation.In the Expression box, type the following expression:
@myrowcount < 2 In the Precedence Constraint Editor dialog box, click OK.If less than two rows are processed in the data flow, an e-mail message is sent.
Additionally, you can use the Send Mail task as part of an error handler. For example, you may want to send an e-mail message to administrators when an SSIS package does not execute. To do this, create an OnError event handler for the package, and then add a Send Mail task to the event handler. Create a subject property expression that captures the time that the package is executed, the start time of the container, or the start time of the event handler from the relevant system variables. For example, create an expression that is similar to the following.

“Error in the task: ” +@[System::SourceName] + “with the ID: ” +@[System::SourceID] + ” has failed at: ” + (DT_WSTR, 20) @[System::ContainerStartTime] + “.”This sample expression uses the following system variables: StartTime
The time when the package was executed.ContainerStartTime
The time that the container started.EventHandlerStartTime
The time that the event handler started.

Symptoms
Consider the following scenario. You develop a SQL Server 2005-based application. The application uses the SQL Server Management Objects (SMO) DLLs that are included in SQL Server 2005 Service Pack 1. The application calls the Server.JobServer.Jobs.Contains method to determine whether a specified job exists in the job collection. When you run the application on a computer that has SQL Server 2005 Service Pack 2 (SP2) installed, you receive a System.InvalidCastException exception.
Resolution
The fix for this issue was first released in Cumulative Update. 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 packagefor 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.