January 29, 2004 at 10:00 am
Yesterday out of the blue a code that has been in production for months now suddenly was no longer working properly. See code below. if I perform the select it returns the 15 records that it is supposed to return but if I change the select to the update it does not update any records and the number records affected returned by the query is 0. I know using the OPENXML in joins is probably not the best practice since SQL sees it as a remote query but I would expect it to be slow in this case but it should still work. Any ideas? Am I missing anything?
DECLARE @IDOC int
EXEC sp_xml_preparedocument @IDOC OUTPUT,
'
<DOC><INVC INVC_ID="7366"/></DOC>
'
-- UPDATE ICR
-- SET BATCH_INVC_REL_ID = BIR.BATCH_INVC_REL_ID
SELECT *
FROM INVC I
JOIN OPENXML (@IDOC, '/DOC/INVC', 2)
WITH (INVC_ID int '@INVC_ID') XT
ON XT.INVC_ID = I.INVC_ID
JOIN INVC_ACCT_REL IAR
ON IAR.INVC_ID = I.INVC_ID
JOIN INVC_CHG_REL ICR
ON ICR.INVC_ACCT_REL_ID = IAR.INVC_ACCT_REL_ID
JOIN BATCH_INVC_REL BIR
ON BIR.INVC_ID = IAR.INVC_ID AND BIR.BATCH_ID = 111
WHERE I.INVC_REVIEW = 1
AND ICR.INVC_CHG_LDGR_APRVD_FLG = 1
AND ICR.BATCH_INVC_REL_ID IS NULL
-- --SELECTING ALL SUMMARY FEES THAT SHOULD BE PART OF THE BATCH
EXEC sp_xml_removedocument @IDOC
February 2, 2004 at 8:00 am
This was removed by the editor as SPAM
February 2, 2004 at 1:43 pm
Just to reduce the variables in this, could you use a temporary table to hold the id that you currently have in the doc? Move the OpenXML out and replace it with the temp table.
Russel Loski, MCSD
Russel Loski, MCSE Business Intelligence, Data Platform
February 2, 2004 at 2:20 pm
Thanks for the reply.
We are moving the direction suggestion already but this change has go through the regular release process and we wanted to understand what is happening with the current implementation until the modification can get to production.
Does that mean I cannot trust OPENXML when participating in joins?
February 2, 2004 at 3:26 pm
To be honest, I have no clue what is going on. By going to a temp table, you eliminate one unknown. Let's say that going to a temp table doesn't help, then something other than the OPENXML is causing the problem. If it does work with a temp table, then OPENXML is likely the problem.
Good luck. I could quote half the bosses I have worked under: "Don't understand the problem just fix it!" But I think that is dumb advice.
Russel Loski, MCSD
Russel Loski, MCSE Business Intelligence, Data Platform
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply