BUG: SQL Enterprise Manager Displays Incorrect Database for QueueReader Subsystem Job (815123)

MORE INFORMATION

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:

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