Microsoft KB Archive/931815

From BetaArchive Wiki

Article ID: 931815

Article Last Modified on 11/4/2007



APPLIES TO

  • Microsoft SQL Server 2005 Notification Services
  • Microsoft SQL Server Notification Services 2.0 Service Pack 1
  • Microsoft SQL Server Notification Services 2.0



Bug #: 50000713 (SQL Hotfix)


INTRODUCTION

Note The updated script of August 2007 supersedes and replaces the script of February 2007. The updated script of August 2007 must be run against a Notification Services (NS) instance even if the script of February 2007 was applied to the NS instance. The following changes are introduced in the script of August 2007:

  • The February 2007 script has an issue with the US time zone DST calculations for 2008 and later years. This has been updated.
  • The February 2007 script does not have DST updates for international time zones. This script has been updated to match Windows international time zone DST updates as described in Microsoft Knowledge Base article 933360.

For more information about the DST changes for the Windows operating system, visit the following Microsoft Web site:

Note This Web site lists the products that are affected by DST and the articles in the Microsoft Knowledge Base that describe the latest cumulative DST and time zone update for Windows.

This article applies to the following versions of Microsoft SQL Server Notification Services:

  • Microsoft SQL Server 2000 Notification Services 2.0
  • Microsoft SQL Server 2000 Notification Services 2.0 Service Pack 1 (SP1)
  • Microsoft SQL Server 2005 Notification Services
  • Microsoft SQL Server 2005 Notification Services Service Pack 1 (SP1)
  • Microsoft SQL Server 2005 Notification Services Service Pack 2 (SP2)


Note This article does not apply to new instances that are created in SQL Server 2005 Notification Services SP2. New instances will have the correct DST information. However, users who upgrade existing instances to SQL Server 2005 SP2 must follow the steps that are listed in the "Resolution" section.

For more information about DST changes for the Windows operating system, click the following article number to view the article in the Microsoft Knowledge Base:

933360 August 2007 cumulative time zone update for Microsoft Windows operating systems


RESOLUTION

To resolve this issue, follow these steps:

  1. Stop the event provider, the Notification Services generator service, and all the Notification Services distributor services on all computers.

    Note For more information about how to start and stop a service, visit the following Microsoft Developer Network (MSDN) Web site:
  2. Download the following file from the Microsoft Download Center, and then extract the script to your computer.

    Note If you use a nondefault database schema, you may have to modify the self-extracting script. If use the default database schema, you do not have to modify the script.

    The following file is available for download from the Microsoft Download Center:
    [GRAPHIC: Download]Download the DSTNSAug07.exe package now. For more information about how to download Microsoft support files, click the following article number to view the article in the Microsoft Knowledge Base:

    119591 How to obtain Microsoft support files from online services

    Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help prevent any unauthorized changes to the file.
  3. Save the file as DST-NS.sql
  4. Click Start, click Run, type cmd, and then click OK.
  5. Type the following command, and then press ENTER:

    osql -E -i DST.NS.sql -d NSMainDatabase

    Note NSMainDatabase represents the name of the main database of the instance of SQL Server Notification Services.
  6. Repeat step 2 through step 5 for the main database of each instance of Notification Services that is installed.
  7. If you are running SQL Server 2000 Notification Services 2.0, stop and then restart SQL Server on the computer on which the database is hosted.

    Note If you are running SQL Server 2005 Notification Services, skip this step.
  8. Start the Notification Services distributor services on all the computers.
  9. Start the Notification Services generator.
  10. Start the event providers.


MORE INFORMATION

SQL Server Notification Services stores DST information in the NSTimeZoneDstOffsets table of the main application database. This table is created when an instance of SQL Server Notification Services is created. This table contains the DST start time, end time, and offset information for each time zone in each year up to the year 2025.

Scheduled subscriptions in SQL Server Notification Services use the NSTimeZoneDstOffsets table to determine which schedules are in scope.

In time zones where there has been a change in daylight saving time start or end dates, scheduled subscriptions that occur between the old start or ends dates and the new start or ends dates may be delayed. These subscriptions may be delayed by one hour after the cumulative time zone update is applied to the host operating system.

You should install the Windows DST update on all computers that are running instances of Notification Services. Notice that Notification Services does not use local time. Notification Services uses Coordinated Universal Time (UTC). However, Notification Services depends on some SQL Server components that use local time. Therefore, you should install both the Notification Services DST update and the Windows DST update.

The order in which you install these DST updates is not important. However, we recommend that you first install the Windows DST update and then install the Notification Services DST update. Microsoft has tested this recommended order. You should install both DST updates before March 11, 2007.

How to determine whether Notification Services is installed

How to determine whether SQL Server 2000 Notification Services is installed

To determine whether SQL Server 2000 Notification Services is installed, use one of the following methods:

Method 1

In Control Panel, open the Add or Remove Programs item. If you notice the following entry, the original version of SQL Server 2000 Notification Services is installed:

Microsoft SQL Server Notification Services (2.0.2114.0)


If you notice the following entry, SQL Server 2000 Notification Services Service Pack 1 (SP1) is installed:

Microsoft SQL Server Notification Services 2.0 SP1


Method 2

In Registry Editor, locate the following registry subkey:

HKEY_CLASSES_ROOT\Installer\Products


If you notice the following registry subkey, the original version of SQL Server 2000 Notification Services is installed:

64942737F6AC0D146AF0B91F262A693C


If you notice the following registry subkey, SQL Server 2000 Notification Services Service Pack 1 (SP1) is installed:

26ABA4AB609E2B94EBB48C824974CA02


Method 3

In Registry Editor, locate the following registry subkey:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\NotificationServices\InstalledVersions


If you notice the 2.0.2114.0 registry subkey, the original version of SQL Server 2000 Notification Services is installed. Also, the Path registry entry of the 2.0.2114.0 registry subkey contains the following value:

<InstallPath>\Microsoft SQL Server Notification Services\v2.0.2114.0\Bin


Note The placeholder <InstallPath> represents the folder where you install SQL Server 2000 Notification Services.

If you notice the 2.0.3008.0 registry subkey, SQL Server 2000 Notification Services SP1 is installed. Also, the Path registry entry of the 2.0.3008.0 registry subkey contains the following value:

<InstallPath>\Microsoft SQL Server Notification Services\v2.0.3008.0\Bin


Note The placeholder <InstallPath> represents the folder where you install SQL Server 2000 Notification Services.

How to determine whether SQL Server 2005 Notification Services is installed

To determine whether SQL Server 2005 Notification Services is installed, locate the following registry entry in Registry Editor:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\NS\Setup\Version


If the value is 9.00.1399.06, the original version of SQL Server 2005 Notification Services is installed.

If the value is 9.1.2047.00, SQL Server 2005 Notification Services SP1 is installed.

Automated installation of this update

You can write code to automate the installation of this update. In the code, you must first determine whether the instance of Notification Services is deployed and then install the update. However, different components of the instance can be installed on different computers. In the code, you must check whether the NS$<InstanceName> service is running on each computer. If the NS$<InstanceName> service is running on any computer, the instance has been deployed on that computer.

Notice that there will be one computer that is running the database component of the instance. This computer may be running the NS$<InstanceName> service. You can write code to determine whether this computer is running the NS$<InstanceName> service. However, if you take this action, you will add complexity to your code. Therefore, you may prefer to manually determine whether this computer is running the NS$<InstanceName> service.

Afterward, you can use the code to automate the installation of this update.

REFERENCES

For more information about how to manage services for SQL Server Notification Services, visit the following Microsoft Developer Network (MSDN) Web site:

Technical revisions

The revision number and the review dates for this article may reflect minor editorial revisions or minor structural revisions to this content. The major technical revisions to this content are listed in this table.

Date Revisions
January 26, 2007 Original version
February 7, 2007 Added the "More information" section
February 15, 2007 Replaced the cross-reference to the outdated Knowledge Base article 928388 with a cross-reference to Knowledge Base article 931836
February 27, 2007 In the "More Information" section, updated the date "March 3, 2007" to "March 11, 2007"
March 1, 2007 In the "Resolution" section, updated the URL in the script from "http://www.microsoft.com/windows/timezone/dst2007.mspx%22 to "http://support.microsoft.com/gp/cp_dst%22
September 3, 2007 Added note to beginning of "Introduction" section. In "Resolution" section, replaced step 2, replaced file name in step 3, and replaced command in step 5.
December 14, 2007 In the "Introduction" section, replaced the link to Knowledge Base article 933360 with a link to the list of products that are affected by daylight saving time.



Additional query words: notification services DST 2007 sql

Keywords: atdownload kbdatetime kbexpertiseinter kbfix kbqfe kbbug kbinfo KB931815