Microsoft KB Archive/815123

= BUG: SQL Enterprise Manager Displays Incorrect Database for QueueReader Subsystem Job =

Article ID: 815123

Article Last Modified on 9/25/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



BUG #: 364290 (SHILOH_BUGS)



SYMPTOMS
SQL Server Enterprise Manager (SEM) displays an incorrect database for the Queue Reader subsystem job.



WORKAROUND
To work around the problem, do not click OK or Apply in the Edit Job Step dialog box when the wrong database is selected. If you click Cancel when SQL Server prompts you to save changes, you can click No if the wrong database is displayed.



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.



Steps to Reproduce the Problem
To see the problem, follow these steps:


 * 1) Right-click the QueueReader job in SEM.
 * 2) Click Properties.
 * 3) Click the Steps tab.
 * 4) Select step 2 (the QueueReader type).
 * 5) Click Edit.
 * 6) Click the General tab. SEM displays the wrong database.

The database that displays is the first alphabetical database on the server instead of the distribution database. For example, if the following databases exist on the server


 * AA
 * BB
 * Distribution, and so on..

SEM displays AA instead of Distribution.

Running the following query validates that the correct database is saved in the system tables, but SEM displays it incorrectly.

select b.database_name, a.name, a.job_id,b.step_id, b.step_name, b.subsystem, b.command from msdb..sysjobsteps b, msdb..sysjobs a where b.job_id in    (select c.job_id from msdb..sysjobs c        where c.description like '%Reads queues for Queued updating subscriptions%') and a.job_id = b.job_id and b.step_id = 2 go

The problem occurs if you click OK or Apply in the Edit Job Step dialog box. The database changes from the distribution database to the database that is currently displayed in the dialog box. This causes the Queue Reader job to fail. If you click Cancel, SEM prompts you with this message:

Do you want to save your changes?

If you click No, there is no adverse affect. However, if you click Yes, SQL Server updates the database with the database that is currently selected in the dialog box. The database that is selected can be the wrong database for the Queue Reader.

The following script sets up the queued updating transactional replication that generates the QueueReader job.

Use master go if exists (select name from master.dbo.sysdatabases where name='PubDb1') drop database PubDb1 if exists (select name from master.dbo.sysdatabases where name='SubDb1') drop database SubDb1 if exists (select name from master.dbo.sysdatabases where name='AA') drop database AA go -- create databases create database PubDb1 go create database SubDb1 go create database AA go -- Set up Distributor exec sp_adddistributor @@servername go -- Add the distribution database exec sp_adddistributiondb 'Distribution' go -- Add the distribution publisher exec sp_adddistpublisher @@servername, 'distribution', @working_directory='c:\temp' go -- Add the registered subscriber exec sp_addsubscriber @@servername go -- Enable the replication database exec sp_replicationdboption 'PubDb1', 'publish', 'true' go Use PubDb1 go -- Create table and insert data create table t1 (col1 int, col2 varchar(20), primary key(col1)) go insert into t1 (col1, col2) values (50, 'Value50') go -- Add the transactional publication exec sp_addpublication @publication = N'PubDb1-Tran-Updateable', @restricted = N'false', @sync_method = N'native', @repl_freq = N'continuous', @description = N'Transactional publication of pubs database from Publisher .', @status = N'active', @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @independent_agent = N'true', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'true', @retention = 336, @allow_queued_tran = N'true', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_dts = N'false', @allow_subscription_copy = N'false', @conflict_policy = N'pub wins', @centralized_conflicts = N'true', @conflict_retention = 14, @queue_type = N'sql', @add_to_active_directory = N'false', @logreader_job_name = N'MyLogReaderJob', @qreader_job_name = N'MyQueueReaderJob' go exec sp_addpublication_snapshot @publication = N'PubDb1-Tran-Updateable',@frequency_type = 0x40, @frequency_interval = 1, @frequency_relative_interval = 0, @frequency_recurrence_factor = 1, @frequency_subday = 1, @frequency_subday_interval = 0, @active_start_date = 0, @active_end_date = 0, @active_start_time_of_day = 225000, @active_end_time_of_day = 0, @snapshot_job_name = N'MySnapshotJob' go -- Update PAL exec sp_grant_publication_access @publication = N'PubDb1-Tran-Updateable', @login = N'BUILTIN\Administrators' exec sp_grant_publication_access @publication = N'PubDb1-Tran-Updateable', @login = N'distributor_admin' exec sp_grant_publication_access @publication = N'PubDb1-Tran-Updateable', @login = N'sa' go -- Add the transactional articles exec sp_addarticle @publication = N'PubDb1-Tran-Updateable', @article = N't1', @source_owner = N'dbo', @source_object = N't1', @destination_table = N't1', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000000CEF3, @status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_t1', @del_cmd = N'XCALL sp_MSdel_t1', @upd_cmd = N'XCALL sp_MSupd_t1', @filter = null, @sync_object = null, @auto_identity_range = N'false' go -- Add the transactional subscription exec sp_addsubscription @publication = N'PubDb1-Tran-Updateable', @article = N'all', @subscriber = @@servername, @destination_db = N'SubDb1', @sync_type = N'automatic', @update_mode = N'queued tran', @offloadagent = 0, @dts_package_location = N'Distributor' go use master go

Additional query words:

Keywords: kbbug KB815123

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.