I have a problem with the stored procedure attached.
The sp will copy records from a table and set status to 3 (signal that record is retrieved).
The problem is that sometimes the status is updated but the data is not copied.
I am not familiar with the "FOR XML EXPLICIT" and wonder it this is OK to use in a transaction.
Is there a transaction problem in this stored procedure?
If so is there an easy way to avoid it or should we just rewrite the whole thing?
NB: The sp is fired from Biztalk, but I am not sure how.
Thank you for your efforts,
CREATE PROCEDURE [dbo].[B2B$GetOutboundMessages] AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
/* Read up to 10 messages from the queue */
SELECT TOP 10 ID INTO #B2B_NAV_TEMP FROM dbo.[GSC$Outbound Message] WHERE Status=1 AND Folder IN ('RSN','X12','B2B') AND Type='DT2000' ORDER BY [Date]
SELECT 1 AS Tag, NULL AS Parent,
'http://XX.XXXX.B2B/Navision/OutboundMessage' AS [Message!1!xmlns],
ID AS [Message!1!ID],
Folder AS [Message!1!Folder],
[Document No_] AS [Message!1!OutboundDocumentNo],
[Message] AS [Message!1!!CDATA]
FROM dbo.[GSC$Outbound Message]
WHERE ID IN (SELECT ID FROM #B2B_NAV_TEMP)
FOR XML EXPLICIT, BINARY BASE64
UPDATE dbo.[GSC$Outbound Message] SET Status=3 WHERE ID IN (SELECT ID FROM #B2B_NAV_TEMP)
DROP TABLE #B2B_NAV_TEMP