Can this stored procedure loose data? "FOR XML EXPLICIT"

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,
Anders


CREATE PROCEDURE [dbo].[B2B$GetOutboundMessages]  AS
BEGIN
      SET TRANSACTION ISOLATION LEVEL READ COMMITTED
     
      /* Read up to 10 messages from the queue */
      BEGIN TRANSACTION
            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
      COMMIT TRANSACTION
END

Open in new window

LVL 4
AndersHedegaardAsked:
Who is Participating?
 
Anthony PerkinsCommented:
I don't see any problems with your code.  You could write it more efficiently, but the Stored Procedure as written is sound.
0
 
elimesikaCommented:
HI
Well the Tag & Parent are not well formed
see
http://msdn.microsoft.com/en-us/library/ms189068.aspx
Alternatively, you can use a much simpler method described in
http://msdn.microsoft.com/en-us/library/ms189885.aspx
0
 
Anthony PerkinsCommented:
>>Well the Tag & Parent are not well formed <<
Why would you say that?
0
 
AndersHedegaardAuthor Commented:
Thank you so far but do you see any problems with the stored procedure regarding transaction?
0
 
AndersHedegaardAuthor Commented:
More information could be good.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.