FIX: Queue Reader Agent Fails with "Invalid Cursor State" Error Message (308733)

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

MORE INFORMATION

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 "Symptoms" section.

BEGIN TRANSACTION

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

COMMIT TRANSACTION