Spent WAY too much time today figuring out why my BCP import was putting spaces in my varchar columns. Still not sure I have it completely figured out, but sounds like it's by design.

The ANSI_PADDING setting is honored when a table is **created**, and will dictate what happens when data is inserted into varchar columns. Seems odd that this setting is saved from when the table is created, not when the data is being inserted.

I ended up having to do a brute force method. After BCP import, I go through the columns, and rtrim them. Have to watch out for a column that had no data, so rtrim leaves a single space?

Something like this:

osql -E -d Sales -Q "update orderdetail set PONum = rtrim(PONum)"

osql -E -d Sales -Q "update orderdetail set PONum = '' where PONum = char(0)"

Google Groups : microsoft.public.sqlserver.server

Posted 3:33 PM    |    0 comments    |    Permalink