SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


bcp unicode


bcp unicode

Author
Message
manole_i
manole_i
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 389
Hello, I have a great mystery about bcp command.I have this table:
CREATE TABLE myTable
(
PersonID smallint,
FirstName nvarchar(50) ,
LastName nvarchar(50)
);
I create a format file with bcp:
exec xp_cmdshell 'bcp AdventureWorks2012.dbo.myTable format nul -f D:\Test\MyTableFormatFile.fmt -S instance\SERVER -w -t, -T'

The file with data is:
1,skip,firstname1,lastname1
1,skip,firstname2,lastname2

I've changed the format file to include the skiped column(the data file has one extra column and I want to skip this column on import, the data file is Unicode):

9.0
4
1 SQLNCHAR 0 14 ",\0" 1 PersonID ""
2 SQLNCHAR 0 1000 ",\0" 0 FirstName SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 1000 ",\0" 2 FirstName SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 1000 "\r\0\0" 3 LastName SQL_Latin1_General_CP1_CI_AS

When I execute:
exec xp_cmdshell 'bcp AdventureWorks2012.dbo.myTable in D:\Test\MyTableData.dat -S instance\SERVER -T -e D:\Test\error_bcp.txt -f D:\Test\MyTableFormatFile.fmt'

I get the error:

NULL
Starting copy...
SQLState = 22018, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
NULL
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1
NULL

instead this query works perfectly:
select * from openrowset(bulk 'D:\Test\MyTableData.dat', FormatFile = 'D:\Test\MyTableFormatFile.fmt') t1

Which might be the problem? If I generate the format file with "-c" instead of "-w" and the file from which I load the data in Saved As "ANSI" the bcp works fine.
The test was made in SQL 2012 Evaluation, i used bcp version 100(sql2008R2) but if I switch to "C:\Program Files\Microsoft SQL Server\110\Tools\Binn\bcp.exe" the result is the same.
manole_i
manole_i
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 389
Actually if data file is unicode and I use -F option let say -F2 everything is ok, no error:
exec xp_cmdshell 'bcp AdventureWorks2012.dbo.myTable in D:\Test\MyTableData.dat -S instance\SERVER -T -F2 -f D:\Test\MyTableFormatFile.fmt'
?!?
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2115 Visits: 872
This line:

4 SQLNCHAR 0 1000 "\r\0\0" 3 LastName SQL_Latin1_General_CP1_CI_AS


is missing a backslash-n, but that seems to be a trick from SQL Server Central. I need to dig further.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2115 Visits: 872
Sorry for the nonsense post, and thanks a lot for the question. It came very timely with a text I'm writing, and made me aware of problem with BCP that I had missed.

The problem is the BOM, byte-order-mark. A unicode file typically start with a BOM, so that an application can see if the encoding is UTF-16LE (the normal on Windows), UTF16BE or UTF-8. Without it, the program needs to try heuristics.

BULK INSERT and OPENROWSET understand the BOM, and can read both UTF-16LE and UTF-16BE. They also understand to skip the BOM and not import it.

BCP on the other hand, does not understand the BOM, but thinks that those two bytes are just data. If the first field is a character field, the BOM will be imported as part of the data. Here, where the first field is a number, everything goes bad. BCP can only handle UTF-16LE files without BOM.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2115 Visits: 872
I did some further testing, and I found that when you use the -w option and do not use a format file, BCP skips the BOM. It still does not really understand it, because I am not able to load a big-endian file.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
manole_i
manole_i
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 389
Thank you for you answer, i will try to understand the BOM stuff. I've tested also the import without format file and I saw that it works.

Best regards
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search