Hi All, 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 sometimes like 1 out of 5 time. 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. A little help will be highly appreciated
To investigate parallelism, try rerunning the query with "OPTION (MAXDOP 1)" at the end of each SELECT and UPDATE statement. That limits the query to a single thread. If that eliminates the duplication you are seeing, then yes, it was probably parallelism. However, in my test of this with a simplified version of your query, it was painfully slow. Perhaps the query can be restructured to work with set-based logic rather than the row-by-row model used here? Can you provide more information about how the document ID is generated? I think it might be possible to include that as part of a select statement against the table variable.
> 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)) I don't know about anyone else but when I run that code... SELECT Cast(datediff(millisecond,convert(varchar(8),getdate(),112),getdate() as varchar(9)) ... I get the following error... Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'as'. It's because the last GETDATE() is part of the DATEDIFF and it's missing a parenthesis right after that. The code should look like the following... SELECT Cast(datediff(millisecond,convert(varchar(8),getdate(),112),getdate()) as varchar(9))