BCP out not unloading all records
-
I have a SQL Server Agent job running nightly that unloads data from tables on SQL Server 2008 R2 using bcp out to a text file. This is then loaded into tables on SQL Server 2014 using bcp in. Once loaded, there is a comparison between the source and target table and I get an email alert if they differ. In the main, this has worked perfectly for about 2 years.
Sometimes though, the table counts do not match and it's because the bcp out has not unloaded all the records into the text file from the source table. This has happened the last two nights. It has happened before, but has then gone away and of course with being so busy......I don't follow up. 🙂
So, as a simple example, I have a source table called tableX (made up of course) with 4 columns and it has 9 records:
The bcp out creates a text file with 7 lines and the bcp in loads 6 records into the target table. The text file looks like this:
28422|1|8|8
28422|2|8|8
28422|3|4|4
28422|4|8|8
28422|5|8|8
28422|6|4|4
28424|1|Record 7 has been partially unloaded and record 9 onwards is missing.
On a bigger scale with real figures:
Database Table Load Date Source Count Target Count Live table1 2017-08-25T03:15:00.753 2002976 1998185 Live table2 2017-08-25T03:15:00.753 486453 482727 Live table3 2017-08-25T03:15:00.753 746796 743395 Live table4 2017-08-25T03:15:00.753 8395 7377 Live table5 2017-08-25T03:15:00.753 577501 576753 Live table6 2017-08-25T03:15:00.753 5228919 5203837 Live table7 2017-08-25T03:15:00.753 98572 98092 Live table8 2017-08-25T03:15:00.753 1374032 1367761 Live table9 2017-08-25T03:15:00.753 2398397 2387843 Live table10 2017-08-25T03:15:00.753 512407 508577 Live table11 2017-08-25T03:15:00.753 61719 61315 Live table12 2017-08-25T03:15:00.753 61523 61218 Live table13 2017-08-25T03:15:00.753 127566 126739 I had 31 tables with this issue last night. Maybe it will go away again? 😉
My unload code uses dynamic SQL to construct a bcp out:
SELECT @strSQL = N'bcp ' + @strTable +
' out ' + @strFilePath + @strTable + @strExtension +
' -e' + @strFilePath + @strErrorLog + @strTable + '.err' +
' -d' + @strDB +
' -c' +
' -T -S' + @strServer +
' -t"' + @strDelimiter + '"'EXEC @intResult = master..xp_cmdshell @strSQL, no_output
The @strDelimiter is pipe "|"
The weird thing is if I execute this very same code in SSMS, it's fine. It's only when running as a SQL Server Agent job that I have the problem.
I've added the error log to the bcp now, so hopefully that may come up with something, but has anyone else had this? Any advice? Any advice on how to trace it?
-
Sorry, I meant to say " record 8 onwards is missing" when talking out the text file content.
-
Have you seen any errors in any of the SQL Server logs? Also, are you using a BCP format file? If you have a row that won't fit the format, I'm not sure what happens, and it only occurred to me because the 8th row in your example had a 2 digit value where all previous records had just one. Might be something, might be nothing...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
BCP error output file (.err) is the best place to start the analysis.
- Hi Steve,
Thanks for your reply.
Have you seen any errors in any of the SQL Server logs?
I've not seen anything so far in any logs I can find, including the Windows ones. I've now altered the error log from bcp to record a log for each table as it is done. Before I had it logging to a single file and it was probably overwriting anything it found. Not sure if it'll help though yet.Also, are you using a BCP format file?
I use a format file for just one of the tables, but that one unloads fine OK! Other tables without a format file unload fine too.8th row in your example had a 2 digit value where all previous records had just one. Might be something, might be nothing...
Sadly, it's going to be the nothing as I made up that data to illustrate clearly what was happening.I've had this before, but then it has just gone away. I want to understand it this time though. I don't think it's the data in the source system, unless it has simultaneously happened to 31 tables all at the same time, even though they are not particularly related. Can never rule it out completely though...
-
i suspect some records might be with different page code, it usually happens when you change you database collation while data is in tables. I suggest try specifying page code when doing bcp out.
- goher2000 - Friday, August 25, 2017 11:45 AM
Hi there,
I haven't changed the database collation and it still doesn't explain why it has been working fine and then suddenly isn't working fine.
For instance, it worked fine on Saturday and Sunday (it runs at 3am) and then went back to the same behaviour yesterday and today. It's the same SQL Server Agent job and the same code.
I will try what you suggest though, just in case.....
-
OK, I left things running over the weekend and checked just now. The job runs every day at 3am. and it is the same job with the same code that runs.
On Saturday and Sunday, bcp unloaded all of the data into the text files correctly. Yesterday and today, it's back to incomplete records being unloaded. It's not all tables affected. 21 out of 97 were affected today, 25 out of 97 yesterday. It's not always the same tables affected either, but most are regular culprits.
There is nothing in the bcp error logs for the "out" except this single line:
#@ Row 1, Column 13: Warning: BCP import with a format file will convert empty strings in delimited columns to NULL. @#
But, I get this in the error file for those tables that unloaded correctly too.
Nothing in the "in" error logs.
Had a good comprehensive sweep of the server's event log and error logs (the server running the job), but can't see anything at all. I did see some event log messages about disk space being low on the drive where the text files are created, but at the moment it has 5GB free.
I'll check the target SQL Server's logs too, but at this moment, I'm stumped. Any suggestions on how to audit or trace this?
-
It is starting to sound like a data problem. I'd start tracking exactly which tables are involved and then looking at that data, to see if you can find anything in common.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply