SQL Server Q&A

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
Consider the following scenario in Microsoft Visual Studio 2008. You create a Windows Forms-based application. Next, you add a data source to this application by connecting to a Microsoft SQL Server Compact 3.5 database file. Then, you set one or more of the following properties to a value other than the default value: Connect TimeoutPacket SizeApplication NameNote The default value of the Connect Timeout property is 30. The default value of the Packet Size property is 4096. The default value of the Application Name property is SQL Server Compact ADO.NET Data Provider.
In this scenario, when you try to drag a table to the Windows form in the Data Sources window, you receive the following error message:

An error occurred while performing the drop:
Exception has been thrown by the target of an invocation.
Resolution
This behavior occurs because SQL Server 3.5 Compact does not support the connection tokens of the following properties in the runtime provider (System.Data.SqlServerCe.dll): Connect TimeoutPacket SizeApplication NameNotesVisual Studio 2008 adds the connection tokens of these properties to the runtime provider only if the values of these properties are not the default values. Therefore, if the values of these properties are the default values, the connection tokens of these properties are still valid.SQL Server Compact 3.5 supports connection tokens in the runtime provider and in the designtime provider. However, SQL Server Compact 3.5 supports different sets of connection tokens in each of these providers. In Visual Studio 2008, Visual Studio designers use the designtime provider. Therefore, the connection tokens of these properties are valid. However, when you drag a table to a Windows form, the code that performs the drag operation uses the runtime provider. Because SQL Server 3.5 Compact does not support the connection tokens for these properties in the runtime provider, the connection tokens are invalid.

Symptoms
When you try to use a Microsoft Visual Studio .NET 2003 client application against a Microsoft SQL Server 2005 native Web service,a System.InvalidOperationException exception may occur.
Resolution
This problem occurs because the System.Data.DataSet object that Visual Studio .NET 2003 uses does not support the XML Schema definition language (XSD) schemas that SQL Server 2005 generates.

Symptoms
The System.Environment class has methods to read the environment variables. However, this class hasno method to set the environment variables for the current process.
Resolution
To work around this problem, use the interop services to set the environment variables. You can set an environment variable by using the Microsoft Platform Software Development Kit (SDK) SetEnvironmentVariable function.
To set an environment variable by calling the Platform SDK SetEnvironmentVariable function, follow these steps: Start Microsoft Visual Studio 2005 or Microsoft Visual Studio .NET. On the File menu, point to New, and then click New Project.In the New Project dialog box, click Visual C# Projects.
Note In Visual Studio 2005, Visual C# Projects is be changed to Visual C#.Under Templates, click Console Application, and then click OK. By default, the Class1.cs file is created.In the code view of the Class1.cs file, specify the using statement to declare the namespaces so that you do not have to qualify the declarations later in the code. Paste the following code in the Class1.cs file:

using System;using System.Runtime.InteropServices;using System.Security;using System.Security.Permissions;Declare the static method and the extern method. Use the DllImport attribute to import the Kernel32.dll file. This declaration indicates that the definition of the function is outside the code. To do this, paste the following code in the Class1.cs file:

// Import the Kernel32 dll file.[DllImport("kernel32.dll",CharSet=CharSet.Auto, SetLastError=true)][return:MarshalAs(UnmanagedType.Bool)]// The declaration is similar to the SDK functionpublic static extern bool SetEnvironmentVariable(string lpName, string lpValue);Paste the following code in the Class1.cs file to add a static method in the class that calls the SetEnvironmentVariable method and that sets the environment variable:

public static bool SetEnvironmentVariableEx(string environmentVariable, string variableValue){ try { // Get the write permission to set the environment variable. EnvironmentPermission environmentPermission = new EnvironmentPermission(EnvironmentPermissionAccess.Write,environmentVariable); environmentPermission.Demand(); return SetEnvironmentVariable(environmentVariable, variableValue); } catch( SecurityException e) { Console.WriteLine(“Exception:” + e.Message); } return false;}Paste the following code in the Main method to set an environment variable:

// Create a sample environment variable and set its value (for the current process).SampleSetEnvironmentVariable.SetEnvironmentVariableEx(“TESTENV”, “TestValue”);Paste the following code to display the environment variable value:

// Verify that environment variable is set correctly.Console.WriteLine(“The value of TESTENV is: ” + Environment.GetEnvironmentVariable(“TESTENV”));Complete Code Sample

using System;using System.Runtime.InteropServices;using System.Security;using System.Security.Permissions;namespace SetEnv{ /// <summary> /// Summary description for Class1. /// </summary> public class SampleSetEnvironmentVariable {// Import the kernel32 dll.[DllImport("kernel32.dll",CharSet=CharSet.Auto, SetLastError=true)][return:MarshalAs(UnmanagedType.Bool)]// The declaration is similar to the SDK functionpublic static extern bool SetEnvironmentVariable(string lpName, string lpValue); public SampleSetEnvironmentVariable() { } public static bool SetEnvironmentVariableEx(string environmentVariable, string variableValue) { try { // Get the write permission to set the environment variable. EnvironmentPermission environmentPermission = new EnvironmentPermission(EnvironmentPermissionAccess.Write,environmentVariable); environmentPermission.Demand(); return SetEnvironmentVariable(environmentVariable, variableValue); } catch( SecurityException e) { Console.WriteLine(“Exception:” + e.Message); } return false; } } class MyClass { /// <summary> /// The main entry point for the application. /// </summary> [STAThread] static void Main(string[] args) { // Create a sample environment variable and set its value (for the current process). SampleSetEnvironmentVariable.SetEnvironmentVariableEx(“TESTENV”, “TestValue”); // Verify that environment variable is set correctly. Console.WriteLine(“The value of TESTENV is: ” + Environment.GetEnvironmentVariable(“TESTENV”)); } }}

Symptoms
When you are developing a Microsoft Visual Basic 2005 project in Microsoft Visual Studio 2005, you may receive the following error messages:

Visual Basic compiler is unable to recover from the following error: System Error &H8013141e& Save your work and restart Visual Studio
Microsoft(R) Visual Basic Compiler has encountered a problem and needs to close. We are sorry for the inconvenience.
The exception unknown software exception (0×800040005) occurred in the application at location xxxxxxxxYou may also receive the following error message in the Visual Studio 2005 IDE output window:

The “Vbc” task failed unexpectedly.
System.Runtime.InteropServices.COMException (0×80004005): Error HRESULT E_FAIL has been returned from a call to a COM component.
at Microsoft.Build.Tasks.Hosting.IVbcHostObject.EndInitialization()
at Microsoft.Build.Tasks.Vbc.InitializeHostCompiler(IVbcHostObject vbcHostObject)
at Microsoft.Build.Tasks.Vbc.InitializeHostObject()at Microsoft.Build.Utilities.ToolTask.Execute()
at Microsoft.Build.BuildEngine.TaskEngine.ExecuteTask(ExecutionMode howToExecuteTask, Hashtable projectItemsAvailableToTask, BuildPropertyGroup projectPropertiesAvailableToTask, Boolean& taskClassWasFound) Additionally, you may be prompted to send error reports to Microsoft before Visual Studio 2005 will close.
Note This problem occurs more frequently when you are developing Microsoft ASP.NET projects or when you are debugging application code by using the Edit and Continue feature in the Visual Studio 2005 IDE.
Resolution
This problem is caused by a bug intheVisual Basic 2005 compiler.

Symptoms
You create or edit a Microsoft SQL Server 2008 Integration Services (SSIS) package in Business Intelligence Development Studio (BIDS). You edit an OLE DB source in a Data Flow task. When you click the Columns tab, you receive the following error message:

TITLE: Microsoft Visual Studio
——————————
Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR.An OLE DB error has occurred. Error code: 0×80040E21.
——————————
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)Note The DTS_E_OLEDBERROR error code has many causes. This article describes only one rare problem that has a specific cause. To make sure that you are encountering this problem, see the “More Information” section for a troubleshooting method.
Note This problem does not occur in SQL Server 2005.
Resolution
In SQL Server 2008 SSIS, a specific check determines whether the identifiers that are used by an OLE DB provider are case-sensitive. This check allows for SSIS to follow the case-sensitivity that is defined by an OLE DB provider configuration when SSIS lists object names, table names, and column names. This check is performed by implementing an OLE DB property that is named DBPROP_IDENTIFIERCASE.However, not all OLE DB providers support and implement this property. If the property is not implemented, an error may occur in the BIDS.