SQL Server Q&A

Symptoms
This article discusses the Microsoft Application Block for .NET topic: “Data Access Application Block Overview.”
The Data Access Application Block is a Microsoft .NET Framework component that contains optimized data access code that can help you do the following:Call stored procedures. Issue SQL text commands against a SQL Server database. The Data Access Application Block returns the SqlDataReader object, the DataSet object, and the XmlReader object. You can use the Data Access Application Block as a building block in your own .NET Framework-based application to reduce the quantity of custom code that you must create, test, and maintain.
Resolution
“Data Access Application Block Overview”contains the following sections: Introduction
The Data Access Application Block encapsulates performance best practices and resource management best practices for how to gain access to Microsoft SQL Server databases. You can use the Data Access Application Block as a building block in your own .NET Framework-based application. The Data Access Application Block reduces the quantity of custom code that you must create, test, and maintain. The Data Access Application Block helps you to do the following: Call stored procedures or SQL text commands.Specify parameter details.Return the SqlDataReader object, the DataSet object, and the XmlReader object.Use strongly typed datasets.What Does the Data Access Application Block Include?
The Data Access Application Block component includes the Microsoft Visual Basic .NET source code or the Microsoft Visual Basic 2005 source code and the Microsoft Visual C# .NET source code or the Microsoft Visual C# 2005 source code for the Data Access Application Block. It also includes a Quick Start Samples client application in each language that you can use to test common scenarios. The sample can help you to better understand how the Data Access Application Block works. You can also customize the source code to fit your requirements.Downloading and Installing the Data Access Application Block
This section discusses how to download and to install the Data Access Application Block. A Microsoft Windows Installer file that contains the signed Data Access Application Block assembly and the comprehensive documentation is available. The install process creates a Microsoft Application Block for .NET menu on your Programs menu. The Data Access menu that appears on the Microsoft Application Block for .NET menu includes options to start the documentation and to start the Data Access Application Block Visual Studio .NET or Visual Studio 2005 solution.Using the Data Access Application Block
This section discusses how to use the Data Access Application Block to run database commands and to manage parameters.Internal Design
This section discusses the internal design of the Data Access Application Block. The Data Access Application Block includes the full source code and a comprehensive guide to its design. This section describes the main implementation details.

Symptoms
An application that uses the Virtual Device Interface feature of Microsoft SQL Server 2000 to back up a SQL Server 2000 database may not back up the database. When the backup operation fails, you experience the followingexception:
HRESULT 0×80070005 Additionally, an error message that is similar to the following error message is logged in the VDI.log file:

Date Time pid(3260) tid(4004)
Error on VIRTUALDEVICE_master
Error at CVDS::Create: Create Memory Status Code: 5, x5
Explanation: Access is denied. You typically experience this issue when multiple instances of the application that uses the Virtual Device Interface feature try to connect at the same time to different instances of SQL Server to initiate backups.
Resolution
This issue occurs because the instances of the application that uses the Virtual Device Interface feature pass the same virtual device name to the IClientVirtualDeviceSet2::CreateEx function at the same time. SQL Server uses that virtual device name as the memory-mapped file name when SQL Server sets up shared memory for backup. This naming conflict causes the backup to fail.

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
In Microsoft SQL Server 2008, new functionalities are added to SQL Server Management Objects (SMO). An application that uses the SQL Server 2005 version of SMO cannot be compiled by using the SQL Server 2008 version of SMO. You must make some changes to the application, and then you must recompile the application. Then, you can use the new functionalities in the SQL Server 2008 version of SMO.
Resolution
This article describes what you must do to use the new functionalities of the SQL Server 2008 version of SMO in an application that uses the SQL Server 2005 version of SMO.

Symptoms
A Visual Basic application that has a CoolBar control crashes while exiting the application. This occurs when there is an End statement in the unload event of the form that hosts a CoolBar control. This behavior occurs only when running the executable and does not happen in the Visual Basic integrated development environment (IDE). The error message is similar to the following:
In the Windows 9x platform:

This program has performed an illegal operation and will be shutdown.Clicking on the Details button may reveal text similar to the following:

PROJECT1 caused an exception 10H in module MSVBVM60.DLL at 015f:66024d53.In Windows NT or Windows 2000 platform, the following error message appears:

Form1:Project1.exe – Application Error
The exception Floating-point inexact result.
(0xc000008f) occurred in the application at location 0×77f1d493.
Resolution
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article. This bug was corrected inService Pack 4 for Visual Studio 6.0.
For additional information about Visual Studio service packs, click the following article numbers to view the articles in the Microsoft Knowledge Base:
194022?(http://support.microsoft.com/kb/194022/EN-US/) INFO: Visual Studio 6.0 Service Packs, What, Where, Why
194295?(http://support.microsoft.com/kb/194295/EN-US/) HOWTO: Tell That a Visual Studio Service Pack Is InstalledTo download the latest Visual Studio service pack, visit the following Microsoft Web site:
http://msdn2.microsoft.com/en-us/vstudio/Aa718353.aspx(http://msdn2.microsoft.com/en-us/vstudio/Aa718353.aspx)

Symptoms
In a Microsoft .NET Framework application, when you perform a late bind to call a Component Object Model (COM) component that returns a structure, you may receive the following exception when you run the application:

An unhandled exception of type ‘System.ArgumentException’ occurred in Mscorlib.dll
Additional information: The method returned a VT_RECORD Variant, which is not supported by Interop.
Resolution
To work around this bug, perform an early bind instead of a late bind while you call the COM component.