0

I am facing a very strange issue while working with while loop and table variable. Please find below my code in short.

A table variable - uniqueconsolidationkey, with following columns

  1. Rowid int identity primary
  2. DocumentId varchar value
  3. Consolidationkey.

First we inserting distinct consolidation value in our table variable.

It is a simple insert. Selecting data from another table.

Now, we apply a while loop through each row and update table variable with documentid. Please see the code below.

Rowno=1 and rowcount is no of row in our table variable

While(rowcount>rowno)
    Begin
    select @docid=some query to fetch milli seconds value plus some other varchar data

    If(@prevdocid<>@docid)
        Begin
            Update @uniqueconsolidationkey
            Set documentid=@docid
            Where rowid=rowno

            Set rowno=rowno+1
            Set @prevdocid=@docid
    End
End

Now the issue is Same documentid is assigned to different rows. I am not sure how.

And one more thing to note is, it happens for 4 rows alternatively. For ex. If row 385 has docid as ABC123 and row 386 has docid as DEF345 then at 387 docid again came as ABC123 and for 388 DEF345.

How this happened when we have all checks in place. This code run fine but sometime as I say once in five we see such issue.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Lokesh Lehkara
  • 19
  • 1
  • What are your "*all checks in place*"? – PM 77-1 Dec 18 '14 at 05:07
  • Update only happens when if lastdocid is not equal to docid.. So in any case docid is not incremented then it will nit update. – Lokesh Lehkara Dec 18 '14 at 05:19
  • Without knowing your business rule, should rowno not be incremented outside the if statement? – Spock Dec 18 '14 at 05:23
  • If previousdocid is not equal to docid then only update is done. Plus docid itself hold milliseconds how is it possible that we get some docid say 123 for row1 and 124 for row2. Then row3 again 123. How time gets rolled back...thats impossible... What I am thinking is for some reason it is picking up two rows or there is some problem in looping – Lokesh Lehkara Dec 18 '14 at 05:24
  • Hi spoc.. I tried that too... I incremented the rowno outside if and trust me it gave me more abrupt result... Loop moves on and and get null docid for most of the rows.. Ya that is strange but true. – Lokesh Lehkara Dec 18 '14 at 05:26
  • I just wanted to update one more thing. This issue happens on our qa and production region only.. For test region this issue doesn't appear at all. Can it be some backend architectural or something how data stored in pages. – Lokesh Lehkara Dec 18 '14 at 05:39
  • Just an update issue doesn't appear if I use delay of .02 millisecond right after select @documentid. I think I might be because of parallelism two rows are getting updated at one run – Lokesh Lehkara Dec 18 '14 at 18:25
  • SQL Server's DATETIME data type only has [a precision of about 1/300th of a second](http://stackoverflow.com/a/715484/1225845) - if you [use a DATETIME2](http://rwgarrison.com/PASS/TimeDateTime2/) data type, does the issue go away? – AHiggins Dec 18 '14 at 18:37
  • Can you post how you are getting the milliseconds? – dan b Dec 23 '14 at 11:52
  • Hi Dan and everyone problem lies in millisecond calculation... I am pretty sure.. Please find below the code we using – Lokesh Lehkara Dec 24 '14 at 20:05
  • Hi everyone. Problem lies in the millisecond part.. I am not sure how but if I replace it with any other varchar it work fine. Please find below the exact code which we are using to get time difference in millisecond. Cast(datediff(millisecond,convert(varchar(8),getdate(),112),getdate() as varchar(9)) – Lokesh Lehkara Dec 24 '14 at 20:44

0 Answers0