SQL Server - How to avoid BCP from adding BOM at the beginning of output file?

Asked By Deniz

27-Jan-10 08:24 PM

Hello,

I use SQL 2000 on Windows 2003, and I have a scheduled job in my SQL
Agent that runs this:

declare @cmd varchar(2048)

SET @cmd = 'bcp "EXEC sp_select_import" queryout import.txt -c -T'

EXEC master..xp_cmdshell @cmd

This creates my tab delimited file 'import.txt'. However importing
this file into some 3rd party application does not work because of the
Byte Order Mark at the beginning of the file. My current workaround is
to resave the file in Excel. Is there any way to eliminate the BOM
while generating the file?

Thanks in advance,

Deniz

27-Jan-10 08:24 PM

Deniz (deniz.turkmen@gmail.com) writes:

Since you use -c which is ANSI character output, I would not expect
this to produce any BOM at all. Does the result set you produce include
any nvarchar columns? Maybe BCP is smart to construct a Unicode file
in this case?

Anyway, I think you use a format file to specify the format, there
should be no BOM, but I have not tested this.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

27-Jan-10 08:24 PM

x

No, there are no nvarchar fields. I will give format file a try.
Thanks.