May 10, 2007 at 7:29 am
Hi
I am using a bcp command within a stored procedure to output some data to a text file for importing into another system. The problem seems to be that the text file always ends with a blank line which is causing the import routine in the other system a few problems.
Is there any way I can prevent the BCP producing an unnecessary LF at the end of the text file?
Thanks in advance
James
May 10, 2007 at 8:26 am
None that I am aware of unless you use something other than CR/LF to mark then end of your line. That is why there appears to be one extra line. Really your import in the other system should only look at lines that in in CR/LF and ignore all others as being a non-row.
May 10, 2007 at 11:43 pm
Look in BOL for using a Format file for BCP, or use the -r row terminator parameter to define the file format that you desire.
If you are saying that you are getting an extra (empty) row verify the query or table does not return/contain an empty row. You can also use the specify the -L parameter to limit the number of rows to remove any "ghost" row.
I first noticed the "ghost" row issue in SQL 2K sp4 when a query has a 3rd nested LEFT JOIN, I swear, but never could prove it this did not happen with sp3. This version of a "ghost" row happens when all 3 nested LEFT JOINs do not have matching record with the FROM table.
I fixed this using:
FROM table1
LEFT JOIN table2
LEFT JOIN table3 ON ...
LEFT JOIN table4 ON ...
ON ...
Instead of:
FROM table1
LEFT JOIN table2 ON ...
LEFT JOIN table3 ON ...
LEFT JOIN table4 ON ...
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply