Home Dashboard Directory Help
Search

BCP produces incorrect warning about empty strings by Erland Sommarskog


Status: 

Closed
 as Duplicate Help for as Duplicate


3
0
Sign in
to vote
Type: Bug
ID: 777920
Opened: 1/30/2013 2:34:12 PM
Access Restriction: Public
0
Workaround(s)
view
1
User(s) can reproduce this bug

Description

This is a followup to
http://connect.microsoft.com/SQLServer/feedback/details/687332/bcp-out-throws-error-when-null-values-are-there-in-the-table#details
which was incorrectly closed by the design.

If you use BCP in SQL 11 and you use field terminators - with or without format files - and there is a NULL value in the data, BCP produces the warning:

Error = [Microsoft][SQL Server Native Client 11.0]Warning: BCP import with a format file will convert empty strings in delimited columns to NULL.

Strictly speakning the warning is correct, but it's useless. The NULL produces empty strings in the output file, and of course you expect to get them back as NULL. And if there empty strings in the data, they are exported as char(0). (And if you have rows with char(0) in, they will be reimported as empty strings.)

From what I can tell, the error is not produced when you use fixed-length or prefix-length. The one case where the warning make sense is when you use fixed length format - in this case you will not get NULL back, so for fixed-length format the warning makes sense.

I recommend that you review the message in the next major release of SQL Server.
Details
Sign in to post a comment.
Posted by Microsoft on 4/16/2013 at 8:34 AM
Thanks for your feedback. Our dev team is taking a look at this.
Sign in to post a workaround.