Insert to a Linked Server possible via Service Broker?

I have configured a non-SQL linked server (via an OLE DB provider) and I wish to insert data into it via Service Broker but I am getting the following error in the SQL Server log:

The activated proc [dbo].[sp_ mytableServiceProgram] running on queue TestDB.dbo.mytableQueue output the following:'Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.'

As you see below, my strored proc. is not issuing any 'save trans' statements, so why is it not allowing me to wrap my code in a transaction? How else can I use a transaction (in order to not lose anything from the queue) and yet still be able to insert to the linked server?

CREATE PROC sp_mytableServiceProgram

AS

SET NOCOUNT ON;

DECLARE

@.XML XML,

@.MessageBody VARBINARY(MAX),

@.MessageTypeName nvarchar(256),

@.Dialog UNIQUEIDENTIFIER;

-- This procedure continues to process messages in the queue until the

-- queue is empty.

WHILE (1 = 1)

BEGIN

BEGIN TRANSACTION;

--BEGIN DISTRIBUTED TRANSACTION; --Tried this but didn't help.

-- Receive the next available message

WAITFOR (

RECEIVE TOP(1) -- just handle one message at a time

@.MessageTypeName = message_type_name,

@.MessageBody = message_body,

@.Dialog = conversation_handle

FROM mytableQueue

), TIMEOUT 2000 ;

-- If RECEIVE did not return a message, roll back the transaction

-- and break out of the while loop, exiting the procedure.

IF (@.@.ROWCOUNT = 0)

BEGIN

ROLLBACK TRANSACTION;

BREAK;

END ;

SET @.XML = CAST(@.MessageBody AS XML);

INSERT INTO LINKEDSERVER.dbname.user.mytable

SELECT tbl.rows.value('@.doc_no', 'INT') AS doc_no,

tbl.rows.value('@.queryid', 'NVARCHAR(50)') AS queryid,

tbl.rows.value('@.ar_num', 'NVARCHAR(50)') AS ar_num,

tbl.rows.value('@.status', 'NVARCHAR(20)') AS status,

tbl.rows.value('@.creationtime', 'DATETIME') AS creationtime,

tbl.rows.value('@.note', 'NVARCHAR(250)') AS note,

tbl.rows.value('@.posted', 'NCHAR(1)') AS posted,

tbl.rows.value('@.kms', 'INT') AS kms,

tbl.rows.value('@.schresid', 'NVARCHAR(50)') AS schresid,

tbl.rows.value('@.resolution_code', 'NCHAR(8)') AS resolution_code,

tbl.rows.value('@.page_count', 'INT') AS page_count,

tbl.rows.value('@.new_serial_number', 'NVARCHAR(20)') AS new_serial_number,

tbl.rows.value('@.taskresolution', 'NVARCHAR(250)') AS taskresolution

FROM @.XML.nodes('/inserted') tbl(rows);

-- If the INSERT did not insert any rows, rollback.

IF @.@.ROWCOUNT = 0

BEGIN

ROLLBACK TRANSACTION;

BREAK;

END

COMMIT TRANSACTION;

END

GO

Is the procedure runnning fine if is invoked from a user connection (e.g. using exec sp_mytableServiceProgram from a Management Studio query) but it fails when called from activation context?

Also, what OLDDB provider are you using for the linked server?

BTW, your procedure should handle the [http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog] and [http://schemas.microsoft.com/SQL/ServiceBroker/Error] message types.

HTH,
~ Remus

|||

No, I see the same error when run as myself (but with more detail):

Msg 3933, Level 16, State 1, Procedure sp_mytableServiceProgram, Line 38

Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.

Line #38 is the insert to the linked server and the OLEDB provider is set to 'Ifxoledbc' which is for an IBM Informix database server.

And yes, you are correct, I do handle the other two message types, (just removed for posting simplicity).

Is the WAITFOR...RECEIVE statement perhaps doing an implicit SAVE TRANS for each and every message that it receives?

Regards,

Ron

|||

Is the WAITFOR that creates an internal savepoint. Remove the WAITFOR and it should work, using a plain RECEIVE. It does work for me, using an SQLNCLI linked server.

HTH,
~ Remus

|||

Right, I was able to see the same thing, removing WAITFOR allowed the insert to the linked server to go through.

But I wonder what are the repercussions to not using WAITFOR are?

Thanks,

Ron

|||

WAITFOR in activated procedures has the purpose of lingering the proedure around for a few seconds when he queue is empty, in hope another message comes anew. This avoids the situation when the rate of incoming messages is just slow enough to keep activating the procedure immedeatly after it just finished.

You should be fine, the benefits of WAITFOR show up only at one particular rate of incomming messages and the gains offered by it are not earth shatering in the first place anyway...

HTH,
~ Remus

|||Excellent, thank you.sql