SQL Server BCP - Nulls getting exported as spaces as opposed to blanks

SQL Server just drove me crazy with a BCP issue today and though I usually don't put a lot of tech-stuff on my blog, this one is well worth it I guess.

The issue:

I had a stored procedure that uses bcp to export data from a table and surprisingly all nulls were being exported as spaces. I tried explicitly using ISNULL to populate an empty string, tried replacing nulls with empty string in the table itself; but no luck. Google didn't have anything useful either.

The solution:

Specifying the COLLATE attribute on all the character columns to use COLLATE SQL_Latin1_General_CP1_CI_AS as shown below:

[EMAIL_ADDR] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

After adding this to the source table, all nulls were exported as blanks with a simple bcp out.

Dedicated to that lone programmer somewhere breaking his/her head to figure out the solution to this issue :)