Hi chaps / chapesses,

I've been playing with BCP using SQL2008R2x64 and have run into an issue. Essentially there is a system I have no control over whatsoever but need to get extracts off. Just about every kind of character appears to have been entered in free text fields somewhere or other, even (surprisingly) tab and ",", |, ^ and just about every useful delineator you would use. Ascii character 167 = should be OK but when I export this using BCP the turns into .

xp_cmdshell 'bcp "[Database].dbo.[Object]" out "D:\Data\Object.txt" /c /t "" /S "Server\instance" -T' -- Works but the column delineator character changes to .

Looking at the reference for BCP it becomes clear why:

-C code_page

Supported for backward compatibility only. Instead, specify a collation name for each column in the format file or in interactive bcp.

Specifies the code page of the data in the data file. code_page is relevant only if the data contains char, varchar, or text columns with character values greater than 127 or less than 32.

Code page value	Description
ACP	ANSI/Microsoft Windows (ISO 1252).
OEM	Default code page used by the client. This is the default code page used by bcp if -C is not specified.
RAW	No conversion from one code page to another occurs. This is the fastest option because no conversion occurs.
<value>	Specific code page number, for example, 850.

So, anything over CHAR(127) will hit trouble. Of course, I could change the /c switch to /w (wide so Unicode file) or /N (native so Unicode file) but I dont want a Unicode file because itll be twice as big!

xp_cmdshell 'bcp "[Database].dbo.[Object]" out "D:\Data\Object.txt" /w /t "" /S "Server\instance" -T' -- Works but means I have a Unicode file - twice as big!

But what was that option above? /C? Sounds great! Ill just drop the /c or /w or /N and use /C1252 or maybe CRAW or CACP. Except, it doesnt work! No matter what I type, with a space after the /C or an equals or putting the codepage in . Nothing works. It just asks me:

Enter the file storage type of field ExportColumn [char]:

xp_cmdshell 'bcp "[Database].dbo.[Object]" out "D:\Data\Object.txt" /C1252 /t "" /S "Server\instance" -T' -- Doesn't work: Enter the file storage type of field ExportColumn [char]:

xp_cmdshell 'bcp "[Database].dbo.[Object]" out "D:\Data\Object.txt" /CRAW /t "" /S "Server\instance" -T' -- Doesn't work: Enter the file storage type of field ExportColumn [char]:

xp_cmdshell 'bcp "[Database].dbo.[Object]" out "D:\Data\Object.txt" /CACP /t "" /S "Server\instance" -T' -- Doesn't work: Enter the file storage type of field ExportColumn [char]:

xp_cmdshell 'bcp "[Database].dbo.[Object]" out "D:\Data\Object.txt" /C "1252" /t "" /S "Server\instance" -T' -- Doesn't work: Enter the file storage type of field ExportColumn [char]:

xp_cmdshell 'bcp "[Database].dbo.[Object]" out "D:\Data\Object.txt" /C "RAW" /t "" /S "Server\instance" -T' -- Doesn't work: Enter the file storage type of field ExportColumn [char]:

xp_cmdshell 'bcp "[Database].dbo.[Object]" out "D:\Data\Object.txt" /C "ACP" /t "" /S "Server\instance" -T' -- Doesn't work: Enter the file storage type of field ExportColumn [char]:

Eh? What? Is this because the /C function is in fact now defunct and in effect, useless or have I messed something up?

Incidentally, I also tried (after reading: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/52ac08d4-87da-4a4a-a723-a2fee26a375d/issue-on-using-section-sign-as-sql-server-bcp-column-terminator?forum=sqltools)

xp_cmdshell 'chcp 1252

bcp "[Database].dbo.[Object]" out "D:\Data\Object.txt" /c /t "" /S "Server\Instance" -T'

It didnt export but it did give the message: Active code page: 1252.

Any pointers very gratefully received.

Cheers,

J

• Edited by JCEH 16 hours 52 minutes ago Typo

Hi,

I had no errors (SQL 2008 R2 SP2) running the following command

bcp MyBase.dbo.MyTable out c:\temp\myfile.txt -c -t "" -S MyServer\Instance -T -C 1252

And it generated an ASCII file (8 bits per character)

Be aware that I left a space between -C and 1252, and there are no double-quotes

except for the character separator "

Strange. Two questions:

1) Did you check the delineator in the output file? (Sure you did but you never know)

2) If you open a DOS window and type chcp and hit the <RETURN> key, what does it return?

There is a slight complication to this I didn't mention. I'm trying to output well over 1024 columns in a single file. This meant I had to create 3 views concatenating all the columns with the delineator between them and then concatenate those 3 views into a final view (otherwise it couldn't handle it) and then output that using BCP. I also tried placing the results into a single column table and then exporting that and it still gave the same result as the view.

Cheers,

J

Just thought of a third question:

3) Are you using BCP from a DOS prompt or through xp_cmdshell in SSMS as I did?

Should be the same result but you never know.

I agree with Sebastian. It works.

...unless you really want a file in the OEM character set. That is, the § sign will be something else if you type the file from the command-line window, but if you look at the file from a regular Windows program, you will see the §.

So the weird thing is that while you are in command-line mode, the § is actually interpreted according to the ANSI code page!

This is a prime example of staring too long at something and missing the obvious. After a sleep and reading your posts again, the error was obvious: I changed /c to /C 1252. I NEED THE /c STILL! Just needed to add the -C 1252 to the end. What a muppet!

xp_cmdshell 'bcp "[Database].dbo.[Object]" out "D:\Data\Object.txt" /c /t "" /S "Server\instance" -T -C 1252' -- works!

Many thanks for your patience and help!

Cheers,

J