Microsoft KB Archive/910229

Knowledge Base

SQL Server 2005 Express Edition Readme

Article ID: 910229

Article Last Modified on 11/22/2007

---

APPLIES TO

• Microsoft SQL Server 2005 Express Edition

---

INTRODUCTION

Microsoft SQL Server 2005 Express Edition (SQL Server Express) is a free and easy-to-use version of SQL Server 2005 that replaces the Microsoft Desktop Engine (MSDE). 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.

Any information relevant to SQL Server Express that was not available in time to be included in this Readme file will be published in the following Microsoft Knowledge Base article.

907284 Changes to the readme file for SQL Server 2005

For information about providing feedback using the SQL Server Express newsgroup, see 3.6 Newsgroup Support.

MORE INFORMATION

1.0 Downloading and Installing SQL Server Express

This section covers information about installing .NET Framework 2.0, SQL Server Express, and tools that can be used to connect to SQL Server Express.

1.1 Installation Requirements for SQL Server Express

1.1.1 System requirements

The following table lists the prerequisite software and minimum hardware and software requirements for running Microsoft SQL Server 2005 Express Edition (SQL Server Express). To determine if the computer meets the system requirements, from the Start menu, right-click My Computer, and then click Properties. The General tab displays the CPU type and speed, and the amount of installed memory (RAM).

Prerequisite software	Microsoft .NET Framework Microsoft Internet Explorer 6.0 SP1 or later (prerequisite for .NET Framework)
RAM	Minimum: 192 MB Recommended: 512 MB or higher
Hard Disk space	600 MB free space
Processor	Pentium III Compatible or higher Minimum: 500 MHz Recommended: 1 GHz or higher
Operating System	Windows Server 2003 SP1 Windows Server 2003 Enterprise Edition SP1 Windows Server 2003 Datacenter Edition SP1 Windows Small Business Server 2003 Standard Edition SP1 Windows Small Business Server 2003 Premium Edition SP1 Windows XP Professional SP2 Windows XP Home Edition SP2 Windows XP Tablet Edition SP2 Windows XP Media Edition SP2 Windows 2000 Professional Edition SP4 Windows 2000 Server Edition SP4 Windows 2000 Advanced Edition SP4 Windows 2000 Datacenter Server Edition SP4 Note There is no support in this release for Windows XP Embedded Edition.

1.1.2 Before You Install SQL Server Express

Before you install Microsoft SQL Server 2005 Express Edition (SQL Server Express), make sure that you install the correct version of .NET Framework associated with SQL Server Express.

SQL Server Express Version	.NET Framework version
SQL Server 2005 Express Edition	If you are installing SQL Server 2005 Express Edition, perform the following steps in order: 1. Uninstall .NET Framework 1.2 and later versions from your computer (versions 1.0 and 1.1 do not have to be uninstalled). Then, install .NET Framework 2.0. from .NET Framework 2.0 Download Center. 2. Install SQL Server 2005 Express Edition from the SQL Server Express Web site.

1.1.3 How to Identify the .NET Framework Version on your Computer

You can identify which .NET Framework version is on your computer in one of two ways:

• If the version is 2.0, to identify the exact version, go to %WINDIR%\Microsoft.NET\Framework\version, right-click mscorlib.dll, click Properties, and then click Version.
• Alternatively, on the Start menu, click Control Panel, open Administrative Tools, and then open .NET Framework 2.0 Configuration. At the top of the right pane, the .NET Framework version displays.

1.1.4 Using Command Prompt Options to Install SQL Server Express

SQL Server 2005 Express Edition (SQL Server Express) Setup provides a command prompt interface in addition to the graphical user interface. See "Running Setup from the Command Prompt" in SQL Server 2005 Books Online to customize how Setup installs SQL Server Express.

Note SQL Server Express is a limited edition of SQL Server 2005. The setup experience using the command prompt for SQL Server Express is similar to that of SQL Server 2005.

1.1.5 Modifying SQL Server Express Installed Components

If you install SQL Server Express from the download center on the SQL Server Express Web site, you will use Sqlexpr.exe to run Setup. If Sqlexpr.exe is run directly without saving to a folder, the temporary directory where the binary files are extracted is deleted after the installation is complete. If you try to modify the SQL Server Express components by using Add or Remove Programs in Control Panel, the Change button will look for a setup directory but will not find it.

To modify the installed SQL Server Express components, you will have to download Sqlexpr.exe again from the Web site, and save the .exe to a folder. At the command prompt, change the path to the folder where the .exe is stored, and run Setup by using the Sqlexpr.exe –x parameter. You will be prompted to select an extraction/installation directory.

When you have extracted the Setup files, you can use the Change button to make modifications to your SQL Server Express installation by specifying the extraction directory when prompted.

Note This scenario is not true when SQL Server Express is installed as part of Visual Studio 2005 Express. For more information about Visual Studio Express, see Visual Studio 2005 Express Products.

1.1.6 Upgrading MSDE to SQL Server Express

If MDSE was installed with an MSI setup, the existing MSDE instance is upgraded when you install Microsoft SQL Server 2005 Express Edition (SQL Server Express) with the same instance name. Use the Default instance option when you run SQL Server Express Setup. The exception to this is if the version of SQL Server Express you are installing is not English. Then, the MSDE installation you are upgrading must either be the same language as the SQL Server Express version, or it must be English.

If MSDE was installed as part of another application that used its own install program, the SQL Server Express installation program will not know about it. In this scenario, the only way to upgrade SQL Server Express is to install SQL Server Express under another instance name. Use the Named Instance option to specify a new instance name when you run SQL Server Express Setup, and then detach the databases from MSDE and attach them to SQL Server Express.

To know if MSDE was installed as part of another application, go to Add or Remove Programs in Control Panel. If MSDE does not appear in Add or Remove Programs, it was installed as part of another application. In this scenario, the only way to remove MSDE is to uninstall the application that installed MSDE. In most situations, the application users will let the application vendor deal with the upgrade.

1.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, use the following command to connect to SQL Server Express by using the command prompt:

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".

1.3 Tools to Manage SQL Server Express

For more information about connecting to and managing a SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:

907716 How to connect to SQL Server Express Edition

1.4 Accessing Setup Documentation

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

1.5 SQL Server 2005 Readme

SQL Server 2005 readme file is available online at this Microsoft Web site.
For more information about SQL Server 2005 and SQL Server Express that was not available in time to be included in the Readme file, click the following article number to view the article in the Microsoft Knowledge Base:

907284 Changes to the readme file for SQL Server 2005

2.0 Setup Issues

This section details Setup issues in this release.

2.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. On a cluster, uninstall SQL Native Client from all nodes. Then, run SQL Server Setup again.

2.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.

Note 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 more information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows registry

To manually increment the counter registry key, follow these steps:

1. On the taskbar, click Start, click Run, type regedit.exe in the Open 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.

1. 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.
2. Run SQL Server 2005 Express Edition Setup again.

2.3 SQL Server Express Books Online Has Incorrect Information on Operating System Support

The "Hardware and Software Requirements (SQL Server Express)" topic in SQL Server Express Books Online does not have an accurate list of operating systems. The following operating systems are supported by SQL Server Express.

• Windows Server 2003 SP1
• Windows Server 2003 Enterprise Edition SP
• Windows Server 2003 Datacenter Edition SP1
• Windows Small Business Server 2003 Standard Edition SP1
• Windows Small Business Server 2003 Premium Edition SP1
• Windows XP Professional SP2
• Windows XP Home Edition SP2
• Windows XP Tablet Edition SP2
• Windows XP Media Edition SP2
• Windows 2000 Professional Edition SP4
• Windows 2000 Server Edition SP4
• Windows 2000 Advanced Edition SP4
• Windows 2000 Datacenter Server Edition SP4

2.4 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, follow these steps:

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.

2.5 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, follow these steps:

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.

2.6 Sample Databases Are Not Installed by Default

The sample databases are not installed by default in SQL Server Express. The Northwind and pubs sample databases can be downloaded from this Microsoft Web site. The Adventureworks sample database can be installed from this Microsoft Web site.

2.7 "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. On a cluster, uninstall SQL Native Client from all nodes. Then run SQL Server 2005 Setup again.

2.8 Settings For sp_configure Might Cause Setup to Fail When Upgrading System Databases

Setup from the command prompt might fail when you uninstall an earlier Community Technology Preview (CTP) release of SQL Server Express using SAVESYSDB and then install this release using USESYSDB, if the sp_configure options SMO and DMO XPs are disabled on the earlier instance. To resolve this issue, ensure that these options are enabled before using Setup to upgrade system databases. For more information, see "Setting Server Configuration Options" in SQL Server Books Online at this Microsoft Web site.

2.9 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.

2.10 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.

2.11 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. On a cluster, uninstall SQL Native Client from all nodes. Then, run SQL Server Setup again.

2.12 Uninstalling Pre-release Versions SQL Server Express and Visual Studio 2005

You must remove all previous builds of SQL Server Express, Visual Studio 2005, and the .NET Framework 2.0 before installation. Because both products depend on the same version of the .NET Framework, they must be uninstalled in the following order:

• SQL Server Express
• Visual Studio 2005
• .NET Framework 2.0

2.13 Maintenance Mode Prompts for Path to Setup.exe

If you install a new SQL Server 2005 component in maintenance mode, you will be prompted for the location of Setup.exe on the SQL Server 2005 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.

2.14 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.

3.0 Additional Information

3.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.

3.1.1 To enable TCP/IP, follow these steps:

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.
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.

3.1.2 To enable the firewall, follow these steps:

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.

3.2 User Instance Functionality

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

3.3 WMI provider and User Instance functionality

WMI Provider for Server Events will not be supported on the dynamically spawned user instances. This should still work on the parent SQL Server Express instance.

3.4 Books Online

SQL Server Express Books Online is available for download from this Microsoft Web site.

Note Because SQL Server Express is a limited version of SQL Server 2005, the documentation in SQL Server Express Books Online is heavily dependent on the content present in SQL Server 2005 Books Online.

SQL Server 2005 Books Online is available for download from this Microsoft Web site.

Microsoft periodically publishes downloadable updates to SQL Server Express Books Online and SQL Server 2005 Books Online. We recommend installing these updates to keep the information current in your local copy of the documentation.

3.5 Getting SQL Server Express Assistance

There are four principal sources of information from Microsoft about SQL Server Express:

• SQL Server Express documentation and samples
• SQL Server 2005 documentation
• SQL Server sites on Microsoft Developer Network (MSDN) and TechNet
• SQL Server information on additional Microsoft Web sites

You can also get help from others either through the SQL Server community or directly from Microsoft support. For more information, see "Getting SQL Server Express Assistance" in SQL Server Express Books Online.

3.6 Newsgroup Support

For newsgroup support, visit the SQL Server Express newsgroup at this Microsoft Web site. Do not use other Microsoft newsgroups for posting questions regarding SQL Server Express.
The latest information from the SQL Server Express team can be found at the SQL Server Express Weblog.

Note Newsgroups are supported in English only.

3.7 Providing Feedback on SQL Server Express

To provide suggestions and bug reports on SQL Server Express:

• Send suggestions and bug reports about the features and user interface of SQL Server Express at this Microsoft Web site.
• Send suggestions and report inaccuracies about the documentation using the feedback functionality in SQL Server Express Books Online.
• Choose to send error reports and feature usage data automatically to Microsoft for analysis.

For more information, see "Providing Feedback on SQL Server 2005" in SQL Server Books Online.

4.0 Database Engine

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

4.1 Connections May Be Forcibly Closed When Running on Windows Server 2003 SP1

If TCP/IP networking is turned on, client connections to an instance of the SQL Server Express Database Engine running on Windows Server 2003 Service Pack 1 might fail with the following error:

ProviderNum: 7, Error: 10054, ErrorMessage: "TCP Provider: An existing connection was forcibly closed by the remote host".

This might occur when you are testing scalability with a large number of client connection attempts. To resolve this issue, use the regedit.exe utility to add a new DWORD value named SynAttackProtect to the registry key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\ with value data of 00000000.

Security Note Setting this registry key might expose the server to a SYN flood denial-of-service attack. Remove this registry value when testing is complete.

Note 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 more information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows registry

4.2 Secure Service Broker Dialogs Require a Database Master Key

SQL Server 2005 Books Online incorrectly states that when a conversation using dialog security spans databases, SQL Server creates a session key encrypted with the master key for the database. Actually, the session key is encrypted with the master key for the database for all conversations that use dialog security. If a database master key is not available, messages for the conversation remain in the transmission_queue with an error until a database master key is created or the conversation times out. Either use the ENCRYPTION = OFF parameter to create an unencrypted dialog, or use the following command to create a database master key:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>'

4.3 Common Language Runtime User-Defined Types Should Have Only One Serialization

Each instance of a byte-ordered user-defined type (UDT) object can have only one serialized representation. If the serialize or de-serialize routines recognize more than one representation of a particular object, you may see errors in the following cases:

• During validation of a byte-ordered UDT passed through RPC, UDT validation performs de-serialization/re-serialization of the UDT and requires that the resulting bytes be exactly the same as the original. If the validation fails, you will see the error:

  System.Data.SqlClient.SqlException, Incoming TDS RPC protocol stream is incorrect. Parameter 1 ("<ParameterName>"): The supplied value is not a valid instance of data type <TypeName>. Check the source data for invalid values.

• During DBCC, the bytes stored in a byte-ordered UDT column must be equal to the computed serialization of the UDT value. If this is not true, the DBCC CHECKTABLE routine will report a consistency error.

4.4 Network Connectivity Not Enabled by Setup

To enhance security, SQL Server Express disables network connectivity for some new installations. Network connectivity using TCP/IP is not disabled if you are using SQL Server Enterprise, Standard, or Workgroup Edition, or if a previous installation of SQL Server is present. Named Pipes connectivity is available only for local connections unless a previous installation of SQL Server is present. For all installations, the shared memory protocol is enabled to allow local connections to the server. The SQL Browser service might be stopped, depending on installation conditions and installation options.

4.5 Considerations for Assemblies That Contain User-Defined Types

The following limitations apply to Common Language Runtime (CLR) assemblies that contain user-defined types.

4.5.1 Common Language Runtime User-Defined Types Should Have Only One Serialization

Each instance of a byte-ordered user-defined type object can have only one serialized representation. If the serialize or de-serialize routines recognize more than one representation of a particular object, you may see errors in the following cases:

• During validation of a byte-ordered user-defined type passed through RPC, user-defined type validation performs de-serialization/re-serialization of the user-defined type and requires that the resulting bytes be exactly the same as the original. If the validation fails, you will see the error:

  
  System.Data.SqlClient.SqlException, Incoming TDS RPC protocol stream is incorrect. Parameter 1 ("<ParameterName>"): The supplied value is not a valid instance of data type <TypeName>. Check the source data for invalid values.

• During DBCC CHECKTABLE, the bytes stored in a byte-ordered user-defined type column must be equal to the computed serialization of the UDT value. If this is not true, the DBCC CHECKTABLE routine will report a consistency error.

4.5.2 Updated Restrictions on Updating Assemblies That Hold User-Defined Type Classes

ALTER ASSEMBLY can be used to update CLR user-defined types in the following ways:

• To modify public methods of the user-defined type class, as long as signatures or attributes are not changed.
• To add new public methods.
• To modify private methods in any way.

Fields that are contained within a native-serialized user-defined type, including data members or base classes, cannot be changed by using ALTER ASSEMBLY. All other changes are unsupported.

4.6 SQL Server does not Guarantee Data Consistency when Updating Assemblies

If WITH UNCHECKED DATA is not specified, SQL Server attempts to prevent ALTER ASSEMBLY from executing if the new assembly version affects existing data in tables, indexes, or other persistent sites. SQL Server does not guarantee, however, that computed columns, indexes, indexed views or expressions will be consistent with the underlying routines and types when the Common Language Runtime (CLR) assembly is updated. Use caution when executing ALTER ASSEMBLY to ensure that there is not a mismatch between the result of an expression and a value based on that expression stored in the assembly.

4.7 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.

4.7.1 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.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:

891957 Update is available that fixes various Volume Shadow Copy Service issues in Windows Server 2003

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.

4.7.2 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.

4.8 Restrictions for Registering Common Language Runtime Assemblies

SQL Server does not allow registering different versions of an assembly with the same name, culture, and public key. If you plan to retain databases from a version of SQL Server Express earlier than the September CTP release, you must drop all but one instance of an assembly that has multiple registrations before you install this release.

4.9 Creating EXTERNAL_ACCESS and UNSAFE Assemblies

To create an EXTERNAL_ACCESS or UNSAFE assembly in SQL Server, or to load an assembly, one of the following two conditions must be met:

• The assembly is strong name signed or authenticode signed with a certificate. This strong name (or certificate) is created inside SQL Server as an asymmetric key (or certificate) and has a corresponding logon with EXTERNAL ACCESS ASSEMBLY permission (for external access assemblies) or UNSAFE ASSEMBLY permission (for unsafe assemblies).
• The database owner (DBO) has EXTERNAL ACCESS ASSEMBLY (for EXTERNAL ACCESS assemblies) or UNSAFE ASSEMBLY (for UNSAFE assemblies) permission, and the database has the TRUSTWORTHY database property set to ON.

We recommend that the TRUSTWORTHY property on a database not be set to ON only to run common language runtime (CLR) code in the server process. Instead, we recommend that an asymmetric key be created from the assembly file in the master database. A logon mapped to this asymmetric key must then be created, and the logon must be granted EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permissions.
The following Transact-SQL statements perform the steps that are required to create an asymmetric key, map a logon to this key, and then grant EXTERNAL_ACCESS ASSEMBLY permission to the logon. You must execute the following Transact-SQL statements before executing the CREATE ASSEMBLY statement.

USE master
GO  

CREATE ASYMMETRIC KEY HelloWorldKey FROM EXECUTABLE FILE = 'C:\HelloWorld.dll'  
CREATE LOGIN HelloWorldLogin FROM ASYMMETRIC KEY HelloWorldKey  
GRANT EXTERNAL ACCESS ASSEMBLY TO HelloWorldLogin
GO

4.10 Application Role Compatibility with Metadata Visibility Restrictions and Dynamic Management Views

The behavior of programs that run under an application role might change because, by default, SQL Server Express limits the visibility of master database metadata to application roles. As a temporary workaround, you can enable trace flag #4616.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:

906549 You may receive a "Permission denied" error message when an application role-based application tries to select records from any one of the system tables in a SQL Server 2005 master database

4.11 SUPPLEMENTAL_LOGGING Database Option Is Not Implemented

The SUPPLEMENTAL_LOGGING database option is not implemented in this release of SQL Server. This option can be set but has no effect.

4.12 sys.dm_clr_loaded_assemblies Shows Assemblies That Failed to Load

Assemblies that fail to load into the server address space for any reason will still appear in the sys.dm_clr_loaded_assemblies dynamic management view.

4.13 XQuery Changes

The following aspects of the SQL Server Express XQuery implementation are not documented in Books Online:

• Characters that are generated by CDATA sections are not considered to be white-space characters. For example, select cast(as xml).query('<a> <![CDATA[ ]]> {"abc"}</a>') returns <a> abc</a>.
• Construction of empty elements and attributes is supported.
• The namespace prefix xmlns identifies a namespace declaration attribute and cannot be redeclared in an XQuery expression. This behavior is required by the XQuery specification.
• Using 'for' with source expression () yields a static error.
• In an XML document that is constructed by using the query() method on the xml data type, a carriage return within a CDATA section becomes a line feed. The line feed is used instead of the earlier carriage-return entity reference ( ) for uniformity with text XML parsing.
• Transact-SQL user-defined functions that contain local-name() and namespace-uri() are deterministic.

4.14 Conversion From xsd:dateTime is Less Restrictive Than Documented

A string representation of an xs:dateTime value that does not contain a date and time separator T or a time zone can be converted to an SQL datetime type in a value() method, as in the following example:

declare @aaa xml
set @aaa = '<AAA MyDate="2005/1/1 00:00:00"/>'
select @aaa.value('(//AAA)[1]/@MyDate', 'datetime')

4.15 XML Schema Support

The following aspects of the SQL Server 2005 support for XML Schema are not documented in SQL Server 2005 Books Online:

• The XML schema for Reporting Services (RDL) can be loaded into an XML schema collection.
• Values of type xs:dateTime and xs:time that contain second values that have more than three fractional digits do not return an error. Instead they are rounded off.
• An XML schema is rejected if maxInclusive is redefined in a derived type when base type has fixed="true".
• Trailing spaces in minInclusive, minExclusive, maxInclusive, and maxExclusive facets are ignored in derived types that restrict the xs:dateTime, xs:data, and xs:time data types.

4.16 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.

5.0 Replication

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

Replication is Disabled by Default

Microsoft SQL Server 2005 Express Edition can serve as a Subscriber for all types of replication, but replication is not installed by default for this edition.
To install replication components, follow these steps:

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), follow these steps:

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

Keywords: kbinfo kbsql2005setup KB910229

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.