Updated: 5/24/2006

The most up-to-date version of this file is available online at the Microsoft Download Center.

The SQL Server documentation team cannot answer technical support questions, but welcomes your suggestions and comments about this readme documentation. You can quickly and directly send e-mail feedback by using the link provided. Please send your feedback in English.

To submit written feedback about this document, click here: Submit Feedback.

Contents

1.0 Introduction

Microsoft® SQL Server™ 2005 Express Edition with Advanced Services (SQL Server Express) is a free and easy-to-use version of SQL Server 2005. Integrated with Microsoft Visual Studio 2005, SQL Server Express makes it easy to develop powerful, secure, data-driven applications and deploy them quickly. SQL Server Express can be redistributed (subject to agreement), and can function as the client database as well as a basic server database. SQL Server Express is an ideal choice for independent software vendors (ISVs), server users, non-professional developers, Web application developers, Web site hosters, and hobbyists building client applications. If you need more advanced database features, SQL Server Express can be seamlessly upgraded to more sophisticated versions of SQL Server.

In addition to the features offered in SQL Server 2005 Express Edition, SQL Server Express with Advanced Services offers additional components that include:

  • SQL Server Management Studio Express (SSMSE), a subset of SQL Server Management Studio.

  • Support for full-text catalogs.

  • Support for viewing reports via report server.

Important:
SQL Server Express with Advanced Services setup installs the server components of Reporting Services. Installing Reporting Services allows you to publish and view reports that run on a Report Server. To create reports, install Business Intelligence Development Studio. For more information, see 4.2 Creating Reports by Using Reporting Services and Business Intelligence Development Studio.

Note:
SQL Server Express with Advanced Services contains all updates that are included in SQL Server 2005 Service Pack 1.

[Top]

2.0 Downloading and Installing SQL Server Express

This section covers information about installing .NET Framework 2.0, SQL Server Express, tool that can be used to connect to SQL Server Express, and hardware and software requirements to install SQL Server Express.

2.1 .NET Framework 2.0

You must install the .NET Framework 2.0 before installing SQL Server Express.

SQL Server Express depends on a specific 2.0 version of the .NET Framework. If you have a different version, your SQL Server Express installation might function unpredictably. You can download the .NET Framework 2.0 from this Microsoft Web site.

Note:
The .NET Framework 2.0 is installed automatically by Microsoft Visual Studio 2005 and all editions of SQL Server 2005 except SQL Server Express.

[Top]

2.2 SQL Server Express

SQL Server Express is available for download at this Microsoft Web site.

Note:
Be sure to follow the instructions provided at the download site for downloading and extracting the product.

After you install SQL Server Express, to connect to SQL Server Express by using the command prompt, use the following command:

sqlcmd -S Server\Instance

Where Server is the name of the computer and Instance is the name of the instance you want to connect to. If you have used the default named instance during setup, specify the instance as "SQLExpress".

[Top]

2.3 Installation Requirements

Hardware and software requirements are summarized in the Installation Requirements for SQL Server Express document.

[Top]

3.0 Setup Issues

This section details Setup issues for SP1. SQL Server 2005 Express Edition with Advanced Services contains all updates that are included in SQL Server 2005 Service Pack 1.

Note:
Unless otherwise noted, these issues apply only when installing a new instance of SQL Server Express.

[Top]

3.1 Existing SQL Native Client Installation May Cause Setup to Fail

Setup might fail and roll back with the following error message: "An installation package for the product Microsoft SQL Native Client cannot be found. Try the installation again using a valid copy of the installation package 'sqlncli.msi'." To work around this problem, uninstall SQL Native Client by using Add or Remove Programs.

[Top]

3.2 System Configuration Checker Fails with "Performance Monitor Counter Check Failed" Message

System Configuration Checker (SCC) verifies the value of the Performance Monitor Counter registry key before SQL Server installation begins. If SCC cannot verify the existing registry key, or if SCC cannot run the Lodctr.exe system program, the SCC check fails, and Setup is blocked. To complete setup, you must manually increment the registry key.

Caution:
Incorrectly editing the registry can cause serious problems that might require you to reinstall your operating system. Microsoft cannot guarantee that problems resulting from editing the registry incorrectly can be resolved. Before editing the registry, back up any valuable data. For information about how to back up, restore, and edit the registry, see Microsoft Knowledge Base article 256986.

To manually increment the counter registry key:

  1. On the taskbar, click Start, click Run, type regedit.exe in the Run text box, and then click OK.

  2. Navigate to the following registry key: [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib]. Look up the following keys:

    • "Last Counter"=dword:00000ed4 (5276)

    • "Last Help"=dword:00000ed5 (5277)

  3. Verify the values. The Last Counter value from the previous step (5276) must be equal to the maximum value of the Counter key from Perflib\009 in the following registry key, and the Last Help value from the previous step (5277) must be equal to the maximum value of the Help key from Perflib\009 in the following registry key: [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\009].

    Note:
    "009" is the key used for the English (United States) language.

  4. If necessary, modify the value for the Last Counter and Last Help values in the \Perflib key. Right-click Last Counter or Last Help in the right pane, select Modify, click Base = Decimal, set the value in the Value data field, and then click OK. Repeat for the other key, if necessary, and then close the registry editor.

  5. Run SQL Server Express Setup again.

[Top]

3.3 If Cryptographic Services Are Disabled on Windows Server 2003, Setup Fails with Windows Logo Requirement Dialog

Windows Cryptographic Service Provider (CSP) is code that performs authentication, encoding, and encryption services that Windows-based applications access through CryptoAPI on Windows Server 2003. If the CSP service is stopped or disabled, SQL Server Setup fails and displays a Windows Logo Requirement message.

Note:
Before running SQL Server Setup on a Windows Server 2003 failover cluster, the CSP service must be started on all cluster nodes.

To enable the Windows CSP service on Windows Server 2003:

  1. In Control Panel, open Administrative Tools, and double-click Services.

  2. In the Name column, right-click Cryptographic Services, and then click Start.

  3. Close Services.

  4. Run Setup.

[Top]

3.4 MSDTC Is Not Fully Enabled on Windows

Because the Microsoft Distributed Transaction Coordinator (MS DTC) is not completely configured in Windows, applications might fail to enlist SQL Server Express resources in a distributed transaction. This problem can affect linked servers, distributed queries, and remote stored procedures that use distributed transactions. To prevent such problems, you must fully enable MS DTC services on the server where SQL Server Express is installed.

To fully enable MS DTC:

  1. In Control Panel, open Administrative Tools, and then double-click Component Services.

  2. In the left pane of Console Root, click Component Services, and then expand Computers.

  3. Right-click My Computer, and then click Properties.

  4. On the MSDTC tab, click Security Configuration.

  5. Under Security Settings, select all of the check boxes.

  6. Verify that the DTC Logon Account name is set to NT AUTHORITY\NetworkService.

[Top]

3.5 "Force Encryption" Configuration Might Cause SQL Server Express Setup to Fail

Setup might fail if an existing SQL Server client installation is configured with the "force encryption" option enabled. To work around this issue, disable the option on any SQL Server clients. For Microsoft Data Access Components (MDAC) clients in SQL Server 2000, use the SQL Server 2000 Client Network Utility. For SQL Native Client, uninstall SQL Native Client by using Add or Remove Programs.

[Top]

3.6 Installing a Default Instance of SQL Server 2000 May Disable SQL Server Express

If your computer has SQL Server 2000 Management Tools and a default instance of SQL Server Express is installed, SQL Server Setup will permit you to install a SQL Server 2000 default instance. However, doing so will disable the installed instance of SQL Server Express. Therefore, do not install a default instance of SQL Server 2000 when SQL Server 2000 Management Tools and a default instance of SQL Server Express already exist on the computer.

[Top]

3.7 Installing SQL Server Express on a Windows Domain Controller

Security Note:
We recommend against running SQL Server Express on a domain controller.

It is possible to install SQL Server Express on a Windows domain controller; however, it cannot run on a Windows Server 2003 domain controller as Local Service or Network Service. SQL Server service accounts should run as Windows domain user accounts. It is also possible to install SQL Server service accounts to run as Local System, but this option is not recommended.

Do not change the role of the server after you install SQL Server Express. For example, if you install SQL Server Express on a member server, do not use the Dcpromo tool to promote the server to a domain controller. Or, if you install SQL Server Express on a domain controller, do not use Dcpromo to demote the server to a member server. Changing the role of a server after you install SQL Server Express can result in loss of functionality and is not supported.

[Top]

3.8 Maintenance Mode Prompts for Path to Setup.exe

If you install a new SQL Server Express component in maintenance mode, you will be prompted for the location of Setup.exe on the SQL Server Express installation media. When specifying the location, make sure that the path includes "Setup.exe." For example, the path "D:\" will fail, but "D:\Setup.exe" will succeed.

[Top]

3.9 Troubleshooting Failure of Setup Command Shell Scripts

Setup command shell scripts can generate Windows script errors when path variables contain parentheses. This occurs because command shell scripts do not support parentheses in path variables, which can occur when installing 32-bit components to the Windows on Windows (WOW64) 32-bit subsystem on a 64-bit computer. For example, the following script, with a path value of "C:\Program Files (x86)\", generates an error because the shell script interpreter misinterprets the parentheses in the expanded PATH variable as part of the IF/ELSE statement:

IF "%SOME_PATH%" == "" ( 
        SET PATH=%PATH%;%PATH_A% 
) ELSE ( 
        SET PATH=%PATH%;%PATH_B% 
)

To work around this issue, change the script to remove the parentheses. For example:

IF "%SOME_PATH%" == "" set PATH=%PATH%;%PATH_A% 
IF NOT "%SOME_PATH%" == "" set PATH=%PATH%;%PATH_B%

Or remove the SQL entry containing parentheses from the path.

[Top]

3.10 Existing Report Server Database Files Might Cause Setup to Fail

When an instance of SQL Server Express that hosts the report server database for an instance of Reporting Services is uninstalled by running Setup from the command prompt with the SAVESYSDB parameter, the report server database files are left behind with the rest of the system database files. By default, these files are named ReportServer$Instancename and ReportServer$InstancenameTempdb. If you then reinstall SQL Server Express with the USESYSDB Setup parameter and specify that the instance host the report server database, Setup will be unable to re-create these database files because they already exist.

To correct this problem, do one of the following:

  • Drop report server databases before uninstalling using SAVESYSDB.

  • Do not install Reporting Services along with SQL Server when using the SAVESYSDB Setup parameter.

  • Use the RSDATABASENAME Setup parameter to specify a new name for the report server database when installing Reporting Services.

[Top]

3.11 Database Compatibility Level Setting When Upgrading From MSDE 2000

This issue only applies when upgrading an instance of MSDE 2000 to SQL Server Express with Advanced Services.

When an instance of MSDE 2000 is upgraded to SQL Server Express with Advanced Services, the database compatibility level remains at 80 to enable backward compatibility with existing MSDE 2000 applications. This backward compatibility level disables certain new functionalities of SQL Server Express with Advanced Services. For more information, see the "sp_dbcmptlevel" reference topic in SQL Server 2005 Books Online.

[Top]

3.12 Setup May Fail If There Is a Single Quotation Mark in the System Database Directory

This issue only applies when upgrading an instance of SQL Server Express to SQL Server Express with Advanced Services.

When you upgrade an instance of SQL Server Express to SQL Server Express with Advanced Services, Setup may fail if the system databases are installed in a directory that has a single quotation mark in the directory name. If failure occurs, you must move the system databases to a directory without a single quotation mark in the directory name. For more information about moving system databases, see "Moving System Databases" in SQL Server 2005 Books Online.

[Top]

3.13 Restrictions on ALTER DATABASE May Cause Upgrade to Fail

This issue only applies when upgrading an instance of SQL Server Express to SQL Server Express with Advanced Services.

When you upgrade an instance of SQL Server Express to SQL Server Express with Advanced Services, Setup upgrades system databases. If you have implemented restrictions on the ALTER DATABASE syntax, upgrade may fail. Restrictions to ALTER DATABASE may include the following:

  • Explicitly denying the ALTER DATABASE statement.

  • A data definition language (DDL) trigger on ALTER DATABASE that rolls back the transaction containing the ALTER DATABASE statement.

If you have restrictions on ALTER DATABASE, and Setup fails to upgrade system databases, you must disable these restrictions and then re-run Setup.

[Top]

3.14 Upgrade to the Final Release Version of SQL Server Express with Advanced Services Is Not Supported

A pre-release version of SQL Server Express with Advanced Services cannot be upgraded to the final release version. You must uninstall the pre-release version of SQL Server Express with Advanced Services before you can install this version.

Caution:
When you uninstall a pre-release version of SQL Server Express with Advanced Services on a computer that also has Visual Studio 2005 installed, you must repair the Windows Registry before you can use the Visual Studio Dataset Designer. For more information, see Knowledge Base article 917635.

[Top]

3.15 SQL Server Express cannot be upgraded when a User Instance is running.

When a user instance is running, a number of database files in the Template Data folder are locked, and you cannot make any upgrades. To upgrade the SQL Server Express installation, you must manually shut down the running user instances.

To manually shut down user instances:

  1. Connect to the parent instance of SQL Server Express, and then disable the user instance by running the following statement:

    EXEC sp_configure ‘user instances enabled’,0
RECONFIGURE

  2. Determine the pipe name for any active user instance by running the following statement:

    SELECT owning_principal_name, instance_pipe_name FROM sys.dm_os_child_instances WHERE heart_beat = ‘alive’

  3. Connect to each user instance by using the instance_pipe_name, and then manually shut down the instance by running the following statement:

    SHUTDOWN WITH NOWAIT

  4. Apply the update.

  5. Re-enable user instances by running the following statement:

    EXEC sp_configure ‘user instances enabled’, 1
RECONFIGURE

[Top]

3.16 Upgrade Might Fail When Using SQL Server Authentication with User Instances

This issue only applies when upgrading an instance of SQL Server Express to SQL Server Express with Advanced Services.

When you install SQL Server Express with Advanced Services to upgrade an instance of SQL Server Express that has user instances enabled, you must use Windows Authentication. If you specify SQL Server Authentication, the upgrade might fail.

[Top]

4.0 Additional Information

4.1 Service Account and Network Protocols

For SQL Server Express, the Local System Account is Network Service Account.

SQL Server Express listens on local named pipes and shared memory. With a default installation, you cannot remotely connect to SQL Server Express. You will need to enable TCP/IP and check if the firewall is enabled.

To enable TCP/IP:

  1. From the Start menu, choose All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.

    Optionally, you can open Computer Manager by right-clicking My Computer and choosing Manage. In Computer Management, expand Services and Applications, expand SQL Server Configuration Manager.

  2. Expand SQL Server 2005 Network Configuration, and then click Protocols for InstanceName.

  3. In the list of protocols, right-click the protocol you want to enable, and then click Enable.

    The icon for the protocol will change to show that the protocol is enabled.

To enable the firewall:

  1. Click Start, click Control Panel, and then click Network Connections.

  2. From the navigation bar on the left, click Change Windows Firewall settings.

  3. On the Exceptions tab, in the Programs and Services box, you will probably see that SQL Server is listed, but not selected as an exception. If you select the check box, Windows will open the 1433 port to let in TCP requests. Alternatively, if you do not see SQL Server listed, do the following:

    1. Click Add Program.

    2. Click Browse.

    3. Navigate to drive:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BINN

    4. Add the file sqlservr.exe to the list of exceptions.

[Top]

4.2 Creating Reports by Using Reporting Services and Business Intelligence Development Studio

Setup installs Reporting Services. After you install Reporting Services, you can view reports against relational data on the local computer.

Note:
Because Reporting Services in SQL Server Express is supported on a local server only, you must install Reporting Services and the SQL Server Express Database Engine on the same computer.

To create reports by using Reporting Services, you must also install Business Intelligence Development Studio.

Business Intelligence Development Studio is the Microsoft Visual Studio 2005 environment with enhancements that are specific to SQL Server 2005 business intelligence solutions.

Business Intelligence Development Studio is available as part of Microsoft SQL Server 2005 Express Edition Toolkit (SQL Server Express Toolkit). SQL Server Express Toolkit installs the design tools that are used to create reports. SQL Server Express Toolkit is available for download at this Microsoft Web site.

[Top]

4.3 SQL Server Management Studio Express

Microsoft SQL Server Management Studio Express (SSMSE), which is installed by running SQL Server Express with Advanced Services, provides a graphical management tool for instances of Microsoft SQL Server 2005 Express Edition and Microsoft SQL Server 2005 Express Edition with Advanced Services. SSMSE can also manage relational engine instances that are created by any edition of SQL Server 2005. SSMSE cannot manage the following:

  • SQL Server Reporting Services

Note:
To manage Reporting Services, you must use Report Manager.

  • SQL Server Analysis Services

  • SQL Server 2005 Integration Services

  • SQL Server 2005 Mobile Edition

  • SQL Server Notification Services

  • SQL Server Agent

SSMSE is also available as a free download at this Microsoft Web site.

How to connect to SQL Server Express by using SSMSE

To connect SSMSE to a SQL Server Express Database Engine instance, follow these steps:

  1. In the Connect to Server dialog box, specify one of the following:

    • .\sqlexpress
    • (local)\sqlexpress
    • server name\sqlexpress
  2. Click Enter.

    Note:
    The default installation of SQL Server Express uses the instance name SQLExpress. This instance name must be provided to connect to SQL Server Express by using SSMSE. If you have an instance name other than SQLExpress, connect by using computer name\instance name.

[Top]

4.4 User Instance Functionality

SQL Server Express lets non-administrator users copy or move databases (by using Xcopy deployment) without requiring DBCreator privileges. For more information, see “User Instances for Non-Administrators” in SQL Server 2005 Books Online at this Microsoft Web site.

[Top]

4.5 Books Online

Starting with the April 2006 update of SQL Server 2005 Books Online, a separate Books Online for SQL Server Express is unavailable. SQL Server 2005 Books Online is the primary documentation for SQL Server Express.

SQL Server 2005 Books Online is available at this Microsoft Web site. After installing Books Online, you can use the SQL Server Express filter in the Contents pane to hide the content that does not apply to SQL Server Express edition. For more information about SQL Server Express documentation, see "Using SQL Server Express Books Online" in SQL Server 2005 Books Online.

The April 2006 update of Books Online reflects all product upgrades that are included in this service pack, and also includes other improvements to the documentation. We strongly recommend that you install this update to keep the information in your local copy of the documentation current with SQL Server Express.

[Top]

4.6 Getting SQL Server Express Assistance

There are three principal sources of information about SQL Server Express:

Do not use other Microsoft newsgroups for posting questions regarding SQL Server Express. For more information, see "Getting SQL Server Express Assistance" in SQL Server 2005 Books Online. The latest information from the SQL Server Express team can be found at the SQL Server Express Weblog.

[Top]

4.7 Providing Feedback on SQL Server Express

To provide suggestions and bug reports about SQL Server Express Toolkit, you can do one or more of the following:

  • Send suggestions and bug reports about the features and user interface of SQL Server Express Toolkit at the MSDN Product Feedback Center.

  • Send suggestions and report inaccuracies about the documentation by using the feedback functionality in SQL Server 2005 Books Online at this Microsoft Web site.

  • Send error reports and feature usage data automatically to Microsoft for analysis. For more information, see "Error and Usage Report Settings (SQL Server Express)" in SQL Server 2005 Books Online.

[Top]

5.0 Database Engine

The notes in this section are issues for the Database Engine and Database Engine-specific command prompt utilities that also apply to SQL Server Express.

[Top]

5.1 DBCC Error Reporting

A mini-dump file (SQLDUMPnnnn.txt) is created in the SQL Server LOG directory whenever a DBCC CHECKDB, DBCC CHECKALLOC, DBCC CHECKFILEGROUP, or DBCC CHECKTABLE command detects a corruption error. When the Feature Usage data collection and Error Reporting features are enabled for the instance of SQL Server, the file is automatically forwarded to Microsoft. The collected data is used to improve SQL Server functionality. For more information, see "Error and Usage Report Settings" in SQL Server 2005 Books Online.

The dump file contains the results of the DBCC command and additional diagnostic output. The file has restricted discretionary access- control lists (DACLs). Access is limited to the SQL Server service account and members of the sysadmin role. By default, the sysadmin role contains all members of the Windows BUILTIN\Administrators group and the local administrator's group. The DBCC command does not fail if the data collection process fails.

[Top]

5.2 Change to the Behavior of Uncommittable Transactions in a Batch

When a batch is completed, the Database Engine automatically rolls back any active uncommittable transactions. Before this release, uncommittable transactions had to be rolled back manually.

[Top]

5.3 Considerations for the Autorecovered Shadow Copy Feature of the Volume Shadow Copy Service

The autorecovered shadow copy feature of the Volume Shadow Copy Service (VSS) has the following limitations.
Multiple Persisted Autorecovered Shadow Copies

On Windows Server 2003 Service Pack 1 (SP1) and later, you can create only a single persisted autorecovered shadow copy. To create an additional shadow copy, you must first apply the update described in Knowledge Base article 891957.

Note:
If you have not applied this update, you can create a new persisted autorecovered shadow copy by deleting the existing one first, and then creating the new one.

Autorecovered Shadow Copies and Full-Text Catalogs

The autorecovered shadow copy feature does not support full-text catalogs. When an autorecovered shadow copy is created, any full-text catalogs in the database on the shadow copy are taken offline. When the database is attached from the shadow copy, the full-text catalog remains offline permanently. All other data remains available in the attached database.

When a database that contains a full-text catalog is attached directly from an autorecovered shadow copy, the attach operation returns the following error message:

Server: Msg 7608, Level 17, State 1, Line 1
An unknown full-text failure (0xc000000d) occurred during "Mounting a full-text catalog".

If you do not need to attach a database directly from the shadow copy, you can avoid this issue by copying the database files and full-text catalogs from the shadow copy to a regular drive-letter based volume, and then attaching the database from that location. As long as the attach command specifies the correct location of the copied full-text files, the full-text catalogs will work.

[Top]

5.4 Full Text Search on User Instances

Full text Search is not supported on a SQL Server Express user instance (RANU).

5.5 Using Full Text Search feature Requires a Machine Restart

If you install SQL Server Express under the Network Service account, you are unable to use Full Text Search capability until you restart the machine.

[Top]

5.6 RC4 Encryption Should Not Be Used

Do not use RC4 encryption to protect your data in SQL Server 2005. Use a block cipher such as AES 256 or Triple DES instead.

[Top]

6.0 Replication

The notes in this section are late-breaking items for replication.

[Top]

6.1 Replication is Disabled by Default

SQL Server Express can serve as a Subscriber for all types of replication, but replication is not installed by default for this edition.

To install replication components:

  1. On the Feature Selection page, expand Database Services.

  2. Click Replication, and then click Entire feature will be installed on local hard drive.
To install connectivity components and Replication Management Objects (RMO):

  1. On the Feature Selection page, click Client Components, and then click Entire feature will be installed on local hard drive.

[Top]

6.2 Publications Are Not Removed When Upgrading to SQL Server Express

If you upgrade to SQL Server Express from an instance of MSDE that is acting as a Publisher, publications are not removed. SQL Server Express cannot act as a Publisher; therefore, the publications that remain cannot be used on SQL Server Express. For information about how to remove publications, see "Removing Replication" in SQL Server 2005 Books Online.

Important:
The above issue applies only to SQL Server Express with Advanced Services and not to SQL Server Express SP1.

[Top]