Hi, Is there a way to tell bcp to insert a single space character as a
field terminator in an output?  BOL seems to indicate it's possible,
saying that you can use "any printable character" as the delimiter,
but they don't give an example of using a space.  I've tried

bcp tablename out filename.txt -c -t  -S servername -T

but that produces no terminator instead of a space.  Do I need to use
a control or escape character somehow?  I'm using SQL 2005.

Cheers,
Stavros

Even on a good day I am not a very reliable BCP guy, but maybe you need to
use a format file to control this?  Initially I was going to suggest using "
" instead of just a space.  Also, maybe you need to make sure there are
three spaces between -t and -S, not two?




On 12/16/08 7:19 PM, in article
d89c2093-540f-48ec-9e07-c6a5799eb***@w24g2000prd.googlegroups.com, "stavros"
<stav***@mailinator.com> wrote:

Show quoteHide quote

Correct suggestion, use " " to specify space as delimiter. It should be:

bcp tablename out filename.txt -c -t " " -S servername -T

Thanks Aaron and Plamen for the suggestion.  That works as you
suggest, but then (ugh) bcp changes its behavior, and strips out
trailing spaces from my columns.  In other words,

bcp tablename out filename.txt -c -t -S servername -T

leaves columns formatted the way I want (trailing spaces intact), but
has no delimiter.  The alternative,

bcp tablename out filename.txt -c -t " " -S servername -T

does include a space as a delimiter, but strips out all other trailing
spaces, which messes up my column layout.

I'm really just trying to find a quick and dirty solution (bcp) to a
problem that probably would be better served with an SSIS package.  If
anyone has suggestions about how to overcome my bcp problem, please
offer them up.  Otherwise I'll give up and start working on the SSIS
solution.

I did a quick test on both SQL Server 2005 & 2008 and both preserve
trailing spaces with CHAR and VARCHAR columns. Not sure why you get
different results. You can try creating a format file to specify fixed
length for columns.

testing 2000 to 2005 update
Query is very slow then fast
Partial restore without restoring PRIMARY FG
Transaction Log Backups
selective application of condition in an SQL querry
NVARCHAR Limit
sql2005 log file is full
I don't want my DML queries to wait until lock is released, insteadd I want my DML to abort
What build number should i be on?
sql agent job name