Microsoft KB Archive/308733

= FIX: Queue Reader Agent Fails with &quot;Invalid Cursor State&quot; Error Message =

Article ID: 308733

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Service Pack 3a

-



This article was previously published under Q308733



BUG #: 354351 (SHILOH_BUGS)



SYMPTOMS
The Queue Reader Agent may fail with an error message similar to the following:

Failed while applying queued message to publisher

Server: PUBLISHER, Database QueuedPublisher : ODBC Error:Invalid cursor state

Worker Thread 556 : Aborted Queue Transaction

The error message may occur when a query that produces a result set exists in a user-defined trigger on the Publisher. Additionally, the error message only occurs in multi-statement INSERT transactions, and not with UPDATE or DELETE activity.



RESOLUTION
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack



WORKAROUND
To work around this problem, remove any query that produces a result set in user-defined triggers on publishing tables.



STATUS
Microsoft has confirmed that this is a problem in Microsoft SQL Server 2000. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.



Steps to Reproduce Behavior
  Run the following script to set up the database objects: CREATE TABLE Table1(   [col1]  [integer]  PRIMARY KEY CLUSTERED NOT NULL,   [col2]  [char](10) NULL,   [msrepl_tran_version] [uniqueidentifier] NOT NULL DEFAULT NEWID ) GO

-- Create a trigger on the user table that includes a SELECT that returns a result set CREATE TRIGGER T1 ON [dbo].[table1] FOR INSERT AS

DECLARE @var1 AS CHAR(20) SELECT @var1 = 'reproduction'

--This is the statement that causes the Queue Reader to fail. SELECT @var1  Create a new transactional publication and enable Queued Updating. Create a new push subscription for the publication.  At the Subscriber, run the following multi-command INSERT statement. The Queue Reader fails with the error message shown in the &quot;Symptoms&quot; section. BEGIN TRANSACTION

INSERT INTO Table1 (col1,col2) VALUES (1,'test') INSERT INTO Table1 (col1,col2) VALUES (2,'test')

COMMIT TRANSACTION 

Keywords: kbbug kbfix KB308733

-

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

© Microsoft Corporation. All rights reserved.