Additions to the Readme file for SQL Server 2005 CTP 15

INTRODUCTION

This article describes late-breaking changes in Microsoft SQL Server 2005 Community Technical Preview (CTP) 15. These changes are not documented in the Readme file.

Note Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

More Information

Information that has changed

The following information has changed or has become available since the SQL Server 2005 CTP 15 Readme file was published.

Note Unless otherwise stated, these items will be resolved in the final release of SQL Server 2005.

Local connections to the server may fail when the Shared Memory protocol is enabled

After you remove SQL Server 2000 and you install SQL Server 2005 CTP 15, local connections to the server may fail. This problem may occur although the Shared Memory protocol is enabled on the server.

To work around this problem, use the SQL Configuration Manager to determine whether the client has enabled the Shared Memory protocol. If the Shared Memory protocol is disabled, enable the Shared Memory protocol. Then, try again to connect to the server.

Query Notifications are fired under memory pressure

Although the expected behavior is that some Query Notifications will start when the server is under memory pressure, changes have been made to this behavior in SQL Server 2005 CTP 15. These changes affect how the server reacts under resource pressure. In SQL Server 2005 CTP 15, Query Notifications are more aggressively evicted than in earlier CTP releases. The notification information indicates “resource” when memory pressure causes Query Notifications to be fired. This behavior will be changed in later releases. In later releases, resource eviction will be less aggressive.

An error occurs that involves the Sqldmo90.dll file

You may receive an error message after you upgrade SQL Server 2000 to a CTP release of SQL Server 2005. To work around this issue, register the SQLDMO90.dll file after the upgrade Setup process has completed. To register the SQLDMO90.dll file, run the following command at a command prompt:
regsvr32 sqldmo90.dll

MDAC 2.8 clients that use OLEDB may not be able to connect to SQL Server 2005 CTP 15 for local connections that use ".", “(local)”, or (<blank>) when TCP is disabled

To enhance security, SQL Server 2005 CTP 15 disables TCP/IP connectivity to SQL Server 2005 CTP 15 for new installations of the Developer Edition, of the Evaluation Edition, and of the SQL Express Edition. OLEDB applications that connect by using Microsoft Data Access Components (MDAC) 2.8 cannot connect to the default instance on the local server by using ".", (local), or (<blank>) as the server name.

To resolve this problem, provide the server name or enable TCP/IP on the server. This problem does not affect connections to local named instances or connections that use SQL Native Client. Also, this problem may not affect installations where a previous installation of SQL Server is present.

User names that are longer than 64 characters may not work

If you use a user name that is longer than 64 characters in one of the following statements, the statement may not work:
  • SETUSER
  • EXECUTE AS USER
  • the EXECUTE AS clause for modules

The SQL Server service and the SQL Server Agent service are removed after you upgrade a second instance of SQL Server 2000 to SQL Server 2005 CTP 15 64-bit

You experience this problem if you have two or more instances of a SQL Server 2000 64-bit edition installed on an IA-64-based computer.


This problem occurs because the Setup process for SQL Server 2005 CTP 15 removes these services when the Setup process removes the previous installation of SQL Server 2000. SQL Server 2005 CPT 15 will not work until you repair the instances of SQL Server 2005 CPT 15.

To repair an instance of SQL Server 2005 CPT 15, follow these steps:
  1. Find the instance name and the corresponding instance ID. This information is located in the following registry key:
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL
  2. Use the Instance ID to find the product code. This information is located in the following registry key:
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Id \Setup
  3. Run the following command at a command prompt to repair the instance:
    msiexec /I {ProductCode} REINSTALL=SQL_Engine SQLACCOUNT="Account that was used for the first SQL Server service installation" SQLPASSWORD="Password" AGTACCOUNT="Account that was used for the first SQL Server Agent service installation" AGTACCOUNT="Password" /qb
    Note Replace ProductCode with the product code that you found in step 2. Replace the parameters in quotation marks with the login account information.

The Setup process may fail when you upgrade from an earlier CTP release of SQL Server 2005

The Setup process may fail when you upgrade from an earlier CTP release of SQL Server 2005 if you use the SAVESYSDB option and the USESYSDB option. This problem occurs if you run the Setup process by using a different service account than the account that was used to run the Setup process for the earlier release.

The SAVESYSDB option and the USESYSDB option are available during the Setup process. These options reuse the master database, the msdb database, and the tempdb database from the earlier release of CTP version for the current release. When these databases are originally created, the way that they obtain a discretionary access control list (DACL) prevents any account except the service account from having access to these databases. When the tempdb database is transferred to the current release of CTP, the new service account does not have access to this database. Therefore, the Setup process fails. When this problem occurs, you may receive an error message.


The DACL for the master database and the DACL for the msdb database are automatically changed to allow the service account to access them. However, the DACL for the tempdb database is not changed.

To work around this problem, delete the tempdb database files before you upgrade to the current CTP release of SQL Server 2005.

This problem may also affect user databases. If you try to access a user database from this kind of upgraded instance of CTP, you may receive the following error message:
Msg 945, Level 14, State 2, Server
ServerName, Line 1 Database 'DatabaseName' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
To work around this problem, you must change the DACL on the user database file to use the new service account. Then, you must restart the service.

To change the DACL on the file, follow these steps:
  1. Log in to SQL Server as the administrator of the computer that is running SQL Server 2005.
  2. Locate the database file.
  3. Right-click the file, and then click
    Properties.
  4. Click the Security tab.
  5. Click Add. Type the name of the service account. Click to select the Full Control check box, and then click OK.
Note For SQL Server 2005 CTP Express Edition, the default service account was changed in CTP 14 from LocalSystem to NetworkService. Therefore, if you upgrade from a pre-CTP14 release to a newer release by using the SAVESYSDB option and the USESYSDB option, and you select the default Service Accounts for these installations, you will experience this problem.

You cannot send query attachments in Database Mail on a named instance

When Database Mail on a named instance sends mail with a query attachment, Database Mail tries to run the query against the default instance. If no default instance exists, the query fails. If a default instance exists, the query may fail. Or, the query may run. However, the query may return incorrect results.

A jobs that contains active scripting job steps fails if the user is not a sysadmin fixed server role member

After you upgrade from the Beta 2 release and later releases of SQL Server 2005 to the June CTP release, some jobs may fail. Jobs may fail for users who are not members of the sysadmin fixed server role if the jobs contain job steps that use active scripting subsystem proxies. When this problem occurs, you receive the following error message:
sqlstubss90.exe - Unable To Locate Component This application has failed to start because ATXCORE90.dll was not found. Re-installing the application may fix this problem. OK
To resolve this problem, a user who is a member of the sysadmin fixed server role must use SQL Server Management Studio to clone existing proxy accounts for the active scripting subsystem. Then the user must grant the cloned proxy accounts to the active scripting subsystem. Optionally, the user may delete the cloned proxy accounts. The following script helps automate this task.
USE [msdb]
GO
create table #tmp_sp_help_proxy(proxy_id int null, name nvarchar(128) null, credential_identity nvarchar(128) null, enabled tinyint null, description nvarchar(1024) null, user_sid varbinary(40) null, credential_id int null, credential_identity_exists int null)
insert into #tmp_sp_help_proxy(proxy_id, name, credential_identity, enabled, description, user_sid, credential_id, credential_identity_exists) exec msdb.dbo.sp_help_proxy
GO
DECLARE @cred_id int
DECLARE @name sysname
DECLARE proxy_cursor CURSOR LOCAL
FOR
SELECT name, credential_id FROM msdb.dbo.sysproxysubsystem sps
JOIN #tmp_sp_help_proxy t on (t.proxy_id=sps.proxy_id)
WHERE subsystem_id=2
OPEN proxy_cursor
FETCH NEXT FROM proxy_cursor INTO @name, @cred_id
WHILE (@@fetch_status = 0)
BEGIN
SET @name = (N'____Copy'+@name)
EXEC msdb.dbo.sp_add_proxy @proxy_name=@name, @credential_id=@cred_id,@enabled=1
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=@name, @subsystem_id=2
EXEC msdb.dbo.sp_delete_proxy @proxy_name=@name
FETCH NEXT FROM proxy_cursor INTO @name, @cred_id
END
CLOSE proxy_cursor
DEALLOCATE proxy_cursor
GO
drop table #tmp_sp_help_proxy
GO

The Setup process may fail when you upgrade from an earlier CTP release of SQL Server 2005

This problem may occur if the earlier release includes a database that contains the following:
  • An EVENT NOTIFICATION object that is registered on an ASSEMBLY object or on another object.

    This problem affects the Beta-2 CTP release and the IDW11 CTP release.
  • An EVENT NOTIFICATION object that was created without specifying a broker instance.

    This problem affects the Beta-2 CTP release, the IDW11 CTP release, and the IDW13 CTP release.
To determine whether you are experiencing one of these problems, run the following catalog view queries. Any rows that are returned by either of these queries represent EVENT NOTIFICATION objects that are subject to this problem.
-- Run this query in every database that you want to upgrade.--

-- Detect event notifications that are created on objects.

SELECT e.name as 'Event Notification Name'
, parent_class_desc
, o.name as 'Object Name'
, o.type as 'Object Type'
, NULL as 'Assembly Name'
, broker_instance
FROM sys.event_notifications AS e, sysobjects AS o
WHERE e.parent_class = 1 AND e.parent_id = o.id AND o.type <> 'SQ'

UNION

-- Detect event notifications that are created on assemblies.
SELECT e.name as 'Event Notification Name'
, parent_class_desc
, NULL as 'Object Name'
, NULL as 'Object Type'
, a.name as 'Assembly Name'
, broker_instance
FROM sys.event_notifications AS e, sys.assemblies as a
WHERE e.parent_class = 5 AND e.parent_id = a.assembly_id

UNION

-- Detect event notifications that are created on a database that contains a NULL broker_instance.
SELECT e.name as 'Event Notification Name'
, parent_class_desc
, NULL as 'Object Name'
, NULL as 'Object Type'
, NULL as 'Assembly Name'
, broker_instance
FROM sys.event_notifications AS e
WHERE broker_instance IS NULL
GO

--
-- Run this query one time for each instance of SQL Server 2005.
--

-- Detect event notifications that are created on a server that contains a NULL broker_instance.
SELECT name, parent_class_desc, broker_instance
FROM sys.server_event_notifications AS e
WHERE broker_instance IS NULL ;
GO

To work around this problem, use the DROP statement to delete the EVENT NOTIFICATION objects. Then, re-create the EVENT NOTIFICATION objects after the upgrade Setup process has completed. For the correct syntax to use, see SQL Server Books Online.

An error occurs that involves the Sqlvdi.dll file

You may receive an error message that mentions the Sqlvdi.dll file after you upgrade SQL Server 7.0 to SQL Server 2005 CTP. To resolve the error, register the Sqlvdi.dll file after the upgrade Setup process has completed. To register the Sqlvdi.dll file, run the following command at a command prompt:
regsvr32 sqlvdi.dll

Some issues may occur when you use database mirroring

The following issues may occur when you use database mirroring together with SQL Server 2005 CTP.
Database mirroring may enter a state in which mirroring is no longer active
The databases on the primary server and on the mirror server may remain in the synchronizing state, in the disconnected state, or in the recovering state. Typically, this problem occurs after an operation is performed that triggers a state change in database mirroring. For example, this problem may occur after a mirroring suspend/resume operation or after a SQL restart on the mirror server. This problem may also occur if a server error or a network error occurs that has a similar effect on the mirroring state.


To work around this problem, reset the mirroring connection or restart the SQL Server service on the mirror service.
Manual failover may unexpectedly fail
When you try to perform a manual failover, you may experience one or more of the following symptoms:
  • The manual failover fails and generates an Error 1413.
  • The manual failover does not switch partners.
To work around this problem, follow these steps:
  1. Restart the SQL Server service on the mirror server.
  2. Wait for the databases to be synchronized.
  3. Issue the failover command again.
Note You may be able to resolve this problem by issuing the failover command again without restarting the SQL Server service.
You cannot create a database snapshot on the mirror server
The database snapshot may not start and a recovery error such as 9003 may be logged in the SQL Server error log for that database.

To reproduce this problem, create a database snapshot for a mirrored database on the mirror server.

To work around this problem, follow these steps:
  1. Restart the SQL Server service on the mirror server.
  2. Wait for the databases to be synchronized.
  3. Issue the snapshot command again.
Note You may be able to resolve this problem by issuing the database snapshot command again without restarting the SQL Server service.

Existing replication agent jobs may fail when you upgrade multiple instances on the same computer

When you use the Build Cleanup Wizard to remove multiple instances of an earlier CTP release of SQL Server 2005 from the same computer, you must install the instances of the latest CTP release in the same order that you installed the earlier instances. Otherwise, existing replication agent jobs may not run.

For more information about the Build Cleanup Wizard, see Section 5.13 of the SQL Server 2005 CTP (June 2005) Readme file.

Tail-log backup is required to restore a database (full recovery model)

When you restore an existing database, SQL Server 2005 requires that you back up the tail of the log before you restore the database under the full recovery model or the bulk-logged recovery model. If you try to restore a database before you back up the tail of the log, you receive an error message, unless the RESTORE statement contains either a WITH REPLACE clause or a WITH STOPAT clause.

The Report Server Configuration tool fails some configuration operations on 64-bit versions of SQL Server 2005 CTP 15

On 64-bit versions of SQL Server 2005 CTP 15, you cannot use the Reporting Services Configuration tool to create the report server database or to create virtual directories. This issue may affect SQL Server 2000 Reporting Services when SQL Server 2000 Reporting Services is installed on SQL Server 2005 CTP. To work around this issue, follow these steps:
  1. Locate the ReportingServicesSetupResources.rll file in the following folder:
    Program Files\Microsoft SQL Server\90\Shared\1033
  2. Copy the ReportingServicesSetupResources.rll file to the following folder:
    Program Files (x86)\Microsoft SQL Server\90\Shared\1033
  3. In Microsoft Internet Information Services (IIS) Manager, right-click the report server virtual directory, and then click
    Properties.
  4. On the Virtual Directory tab, click
    Configuration, and then click
    Edit.
  5. Locate the Aspnet_isapi.dll file in the following folder:
    C:\WINDOWS\Microsoft.NET\Framework\v2.0.50215
  6. Replace this Aspnet_isapi.dll file with the Aspnet_iaspi.dll file that is located in the following folder:
    C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50215

SQL Server 2000 Notification Services is not supported on SQL Server 2005 Workgroup Edition CTP 15

Although components of Notification Services are included with SQL Server 2005 Workgroup Edition in this CTP release, Notification Services is not supported in SQL Server 2005 Workgroup Edition. The Notification Services components will be removed from SQL Server 2005 Workgroup Edition before SQL Server 2005 is released

Binary data types cannot be replicated to IBM DB2 Subscribers

Because of an issue with the Host Integration Services 2004 (HIS) OLE DB provider that replication uses, binary data types cannot be reliably replicated to IBM DB2 Subscribers. Binary data types include binary and varbinary. This issue will be resolved in a future HIS hotfix.

Information that is not correctly documented in the Readme file

The following item was not correctly documented in the Readme file.

3.5.24 Jobs will not run if the SQL Server Agent service is set to Auto Start during installation

Section 3.5.24 of the Readme file is not correct. The following is the corrected text:

If the Auto start option has been selected for the SQL Server Agent service during installation, do not run the following procedure to remove all entries from the dbo.syssubsystems table in the msdb database before installation. Run the procedure only after the Setup process has completed.
  1. Stop the SQL Server Agent service.
  2. Use SQL Server Management Studio to connect to SQL Server as a member of the sysadmin fixed server role or as a member of the db_owner fixed database role in msdb.
  3. Execute the following Transact-SQL statements. These statements remove all entries from the dbo.syssubsystems table in the msdb database.
    USE msdb;
    GO
    DELETE FROM msdb.dbo.syssubsystems;
    GO
  4. Restart the SQL Server Agent service.

References

For more information, visit the following Microsoft Web site:
Properties

Article ID: 899436 - Last Review: 20 Jun 2014 - Revision: 1

Feedback