[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-10-04
5
Medium Priority
?
167 Views
Last Modified: 2012-08-13
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

0
Comment
Question by:AndersHedegaard
  • 2
  • 2
5 Comments
 
LVL 19

Expert Comment

by:elimesika
ID: 36913361
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36913971
>>Well the Tag & Parent are not well formed <<
Why would you say that?
0
 
LVL 4

Author Comment

by:AndersHedegaard
ID: 36938253
Thank you so far but do you see any problems with the stored procedure regarding transaction?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1500 total points
ID: 36938840
I don't see any problems with your code.  You could write it more efficiently, but the Stored Procedure as written is sound.
0
 
LVL 4

Author Closing Comment

by:AndersHedegaard
ID: 37052764
More information could be good.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question