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:
- 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: - 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:
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
- Save the file as DST-NS.sql
- Click Start, click Run, type cmd, and then click OK.
- Type the following command, and then press ENTER:
osql -E -i DST.NS.sql -d
NSMainDatabase
NSMainDatabase
represents the name of the main database of the instance of SQL Server Notification Services. - Repeat step 2 through step 5 for the main database of each instance of Notification Services that is installed.
- 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. - Start the Notification Services distributor services on all the computers.
- Start the Notification Services generator.
- 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