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
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
Web applications running on IIS 7.5 and that rely on SQL Server Express user instancing will fail to run using the default IIS 7.5 security configuration on both Windows 7 Client and Windows Server 2008 R2. Developers will encounter problems developing web applications using Visual Studio 2005 + SQL Server Express 2005, Visual Studio 2008 + SQL Server Express 2008, or Visual Studio 2010 + SQL Server Express 2008 on both Windows 7 Client and Windows Server 2008 R2.
Developers will encounter similar problems attempting to develop web application projects (WAP) or websites hosted under IIS6/IIS7/IIS7.5 that rely on SQL Server Express user instances where the WAP project structure or website folder structure exists in a user’s Documents folder.  This issue exists for all versions of Visual Studio regardless of the underlying operating system version.  A web application that attempts to create a database or read/write to a database using SQL Server Express user instance mode can encounter any of the following errors:
An attempt to attach an auto-named database for file c:\Users\[YourUserAccountName]\Documents\Visual Studio 20XX\Projects\[YourSolutionName]\[YourProjectnName]\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
–or–
Failed to generate a user instance of SQL Server due to failure in retrieving the user’s local application data path.  Please make sure the user has a local user profile on the computer. The connection will be closed.
 
NOTE: A web application relies on SQL Server Express’ user instance mode if either of the following is true: The application relies on the default “LocalSQLServer” connection string defined in machine.config The application uses a connection string that contains the following attributes:
            “AttachDBFilename=|DataDirectory|xxxxxx.mdf;User Instance=true”
 
 
Resolution
For Windows Server 2008 R2 and Windows 7
The default security configuration for IIS 7.5 sets application pools to run as the “application pool identity”.  Running an application pool using this special identity was first introduced as an optional setting in Vista SP2 and Windows Server 2008 SP2.  On Windows 7 Client and Windows Server 2008 R2 this special identity is now the default.
 
Web applications built with Visual Studio 2005, Visual Studio 2008, or Visual Studio 2010 and that rely on user instancing with either SQL Server Express 2005 or SQL Server Express 2008 do not work with the new application pool identity.  These products were developed and tested against application pools running with the older NETWORK SERVICE account.
 
For Web Application Projects and Websites Located in a User’s Documents Folder Hosted in IIS
Web application projects (WAP) exist in a folder structure under a user’s “Documents\Visual Studio 20XX\Projects” folder.  Website projects exist in a folder structure under a user’s “Documents\Visual Studio 20XX\Websites” folder.  SQL Server Express user instances require file access rights to the parent folders of the website or WAP project’s directory structure.  Because the IIS service account (NETWORK SERVICE) by default does not have these rights within the Visual Studio project folder structure, WAP projects and websites located in a user’s Documents folder and that are hosted in IIS will not be able to open SQL Server Express user instanced databases for read access.
 
WAPs that were originally created within a user’s Documents folder, but were subsequently changed to use IIS for a web server via the web tab of the project’s properties will encounter this file permissions problem.  Websites hosted in IIS where the website directory structure is located within a user’s Documents folder will also encounter the file permissions problem.  This behavior occurs for WAP projects and websites hosted with any IIS versions that run as NETWORK SERVICE (IIS6, IIS7 and IIS 7.5) where the project structure exists within a user’s Documents folder.
 

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.