bcp rows missing or out of order
-
I have a stored procedure that writes data to a global temp table (##LN) which I end up copying it with bcp to a csv file. Most of the time the proc works perfectly. Sometimes though it will write the first 600 rows in order then it will skip 300 or so rows write 100 new rows and then it will repeat rows 500-600 and continue through to the end of the data. This is for the first temp table of two. For the second table (##WM) it works for the first 400 or so rows and then it just truncates the last 500 rows.
I'm at a bit of a loss as this proc works correctly 95% of the time. Any suggestions? I logged the data to a log file and the entire data file exists and just seems to get messed up in the bcp step. Also I copy ##WM into ##LN via "insert into ##LN select * from ##WM" just prior to the bcp command. I don't know if that messes things up for some reason.
fyi the logging I did was:
insert into ut_IR_LogTable(wh, item, qtyhnd, qtyblk) select t_cwar, t_item, t_qhnd, t_qblk from #LN order by t_item
insert into ut_IR_LogTable(item, qtyhnd, qtyblk) select w_item, w_qty, w_blckd from #WM order by w_item
select * from ut_IR_LogTable order by item
The ut_IR_LogTable had all the records from ##LN and ##WM and in the correct order. The processing I did after these "logging" commands were just some inserts into the two temp tables which added some headers and accumulated totals prior to display of the raw data.
Thx
-
This is just a guess but is it possible you have some non-printable characters stored in text or char fields? I have had a problem in the past manipulating large amounts of rows with BCP where there were "tab" characters embedded in the data.
-
So far all I can see is just plain characters or integers as data.
-
Is the BCP using a query or are you dumping the entire contents of the table? Is it possible you have a blown index? You might try rebuilding the indexes. If you are using a query in your bcp, try it without any where clause or order by and see if you get all of the data.
-
try this, change ##temp tables to #temp or tempdb..temp and then bcp.
also, have you tried to take a count(*) before and after bcp on those tables? just to ensure that no one else is messing up with them.
if no one is touching your ##tables, then the data should remain there for all sessions till sql restart. run bcp multiple times from command prompt and see if you can repro the missing rows issue from there. if so, there could be a potential bug with bcp utility. you many need to consider apporaching Microsoft SQL Support with repro steps.
-
I think I may have figured out the particulars of this error. I was displaying the results of the table such as select * from ##LN and then bcp'ing it to a csv. I had forgotten that if the table gets a split of any sort then the data is not guarenteed to be in the order I entered it. I just need to add an identity column and then do a sort by on the identity.
I think this may explain why it worked most of the time but not all. It probably did not work when the table (or database?) had undergone a split that day. But after a physical reorg the data would have been in order again.
This is working in dev but I have not recreated the production problems yet on dev. But I think the "solution" is sound in theory at least. Do you think I'm on the right track? Thanks for the suggestions.
Dave
-
I have been bit several times (but few enough that I remember the lesson) when I try to rely on a row order that I KNOW is correct. I think the lesson is that a table/view is an unordered set of rows and you should never expect anything different.
-
Yes. I work on sql infrequently and forget the nature of the data is not like a text file. I guess if you get hit on the head often enough you may remember not to do it eventually.
Thanks
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply