Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jason Holt
Starting Member
2 Posts |
 Posted - 2006-08-01 : 05:52:40
|
When using BCP with the -w parameter to export data to a file via a query that returns no records, BCP adds two characters to the file (hex values FF and FE). The bad characters show up in Excel but not in Notepad, etc. For example, this statement is OK.bcp "select * from pubs..titles" queryout c:\temp\testpub1.csv -Sserver -Uuser -Ppassword -w This statement should return no records but the output file contains bad data.bcp "select * from pubs..titles where price > 1000" queryout c:\temp\testpub1.csv -Sserver -Uuser -Ppassword -w My question is: why does BCP do this and how can I overcome the problem? I don't want to use the -c parameter as this does not include any delimeters when the query returns data.Many thanks in advance.Jason Holt |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-08-01 : 07:09:45
|
| I presume FF + FE is the Unicode End-of-file marker, but I don't know how to prevent it being output.Kristen |
 |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2006-08-01 : 07:32:54
|
| Somebody set up us the BOM.It's a Unicode byte order mark. It's indicating that the UTF-16 output is in little-endian order. Applications that handle UTF-16 encoded Unicode text input should understand BOMs.http://en.wikipedia.org/wiki/Byte_Order_Mark |
|
|
|
Jason Holt
Starting Member
2 Posts |
Posted - 2006-08-01 : 08:52:11
|
Many thanks for the responses. I have delved a little deeper and discovered that I don't need to worry about the spurious data displayed in Excel.As the replies state, the FF and FE characters are used as a header to denote that Unicode data follows. (search for 'Unicode character format' in SQL Books Online).As long as the data is imported with an appropriate switch, the data file is treated as empty and a '0 row(s) affected' message appears. I used this statement:BULK INSERT tablename FROM 'c:\temp\emptydatafile.csv' WITH (DATAFILETYPE = 'widechar', FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n') |
|
|
|
|
|
|
BCP outputs bad characters