BUG: sp_getqueuedrows Stored Procedure Does Not Return Any Rows Although Queued Rows Exist (271234)
The information in this article applies to:
- Microsoft SQL Server 2000 (all editions)
This article was previously published under Q271234
BUG #: 235706 (SHILOH)
SYMPTOMS
Subscribers with the queued updating option enabled use either Microsoft SQL Server 2000 queue or Microsoft Message Queuing version 2.0 on Microsoft Windows 2000 Server as the queuing mechanism. When using SQL Server 2000 queue, each Subscriber has its own queue in the form of a SQL Server 2000 table (MSreplication_queue) in the subscription database.
MSreplication_queue uses packed bytestream to store information about the queued commands. A user might use the sp_getqueuerows stored procedure to retrieve a list of pending updates within the queue.
The Microsoft SQL Server 2000 sp_getqueuerows stored procedure does not return a list of queued commands even though queued rows exist. Instead, the following error message occurs:
Server: Msg 50000, Level 16, State 1, Procedure sp_getqueuedrows, Line
67 sp_getqueuedrows(debug): table [queued_sub].[dbo].[T1] is not part
of any active initialized queued subscription. Make sure your queued
subscriptions are properly initialized
CAUSE
The subscribed table owner is incorrectly marked as NULL in the MSsubscription_articles table. This prevents the sp_getqueuerows stored procedure from recognizing the subscription.
WORKAROUND
You can use the sp_changearticle stored procedure to change an article's owner. After you change the owner you must reinitialize the subscription. The sp_getqueuedrows stored procedure should show the queued rows after reinitializing.
Example:
sp_changearticle 'pubs', 'authors', destination_owner, 'dbo'
STATUS
Microsoft has confirmed this to be a problem in SQL Server 2000.
| Modification Type: | Major | Last Reviewed: | 10/16/2003 |
|---|
| Keywords: | kbBug kbpending KB271234 |
|---|
|