bulk inserting uniqueidentifier column

Hi at all,
I'm trying to bulk insert a uniqueidentifier column from unicode file.
In my file I have guid generated from c# application and they are
formatted in this way (separated by "|") :

guid | field1 | field2
fc0c0c42-438e-4897-96db-8b0489e873ef|field1|field2

In my destination table I have three column:
id (uniqueidentifier)
field1 (nvarchar)
field2 (nvarchar)

I use in bulk insert a format file like this :

9.0
3
1 SQLNCHAR 0 0 "|\0" 1 ID Latin1_General_CI_AS
2 SQLNCHAR 0 0 "|\0" 2 Field1 Latin1_General_CI_AS
3 SQLNCHAR 0 0 "|\0" 3 Field2 Latin1_General_CI_AS

and I use this script

BULK INSERT [dbo].[KWTA2] FROM 'd:\WTA2.txt'
WITH (FORMATFILE = 'd:\wta2Format.FMT')

It doesn't work, it prints out
Msg 8152, Level 16, State 13, Line 2
String or binary data would be truncated.

I've also tried to specify in FMT file SQLUNIQUEID instead of SQLNCHAR
and it works perfectly but it imports another data. For example the
guid fc0c0c42-438e-4897-96db-8b0489e873ef became
00350031-0039-0033-3100-300030003000

Please can you help me?
Why sql converts alphanumerical GUID into only numbers ID?
How can I bulk insert GUID? (I didn't find anything googling around :
\ )

Thanks!

Bob

Re: bulk inserting uniqueidentifier column

(bob.speaking@gmail.com) writes:
> I'm trying to bulk insert a uniqueidentifier column from unicode file.
> In my file I have guid generated from c# application and they are
> formatted in this way (separated by "|") :
>
> guid | field1 | field2
> fc0c0c42-438e-4897-96db-8b0489e873ef|field1|field2
>
> In my destination table I have three column:
> id (uniqueidentifier)
> field1 (nvarchar)
> field2 (nvarchar)
>
> I use in bulk insert a format file like this :
>
> 9.0
> 3
> 1 SQLNCHAR 0 0 "|\0" 1 ID Latin1_General_CI_AS
> 2 SQLNCHAR 0 0 "|\0" 2 Field1
Latin1_General_CI_AS
> 3 SQLNCHAR 0 0 "|\0" 3 Field2
Latin1_General_CI_AS

Does the file really consist of one single line?

Assuming that you have one record per line in the file, the terminator
for field 3 should be \r\0\n\0. What happens now is that Field2 in the
first record extends into the GUID in the second record, and then it
goes downhill from there.

> I've also tried to specify in FMT file SQLUNIQUEID instead of SQLNCHAR
> and it works perfectly but it imports another data. For example the
> guid fc0c0c42-438e-4897-96db-8b0489e873ef became
> 00350031-0039-0033-3100-300030003000

SQLUNIQUEID is what you would use in a binary file. It's not applicable
here.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: bulk inserting uniqueidentifier column

>
> Does the file really consist of one single line?
>
> Assuming that you have one record per line in the file, the terminator
> for field 3 should be \r\0\n\0. What happens now is that Field2 in the
> first record extends into the GUID in the second record, and then it
> goes downhill from there.

I'm sorry, cut and pasting sample text file I've removed the correct
syntax :\
In fact the last line has the terminator you specified. :)

> > I've also tried to specify in FMT file SQLUNIQUEID instead of SQLNCHAR
> > and it works perfectly but it imports another data. For example the
> > guid fc0c0c42-438e-4897-96db-8b0489e873ef became
> > 00350031-0039-0033-3100-300030003000
>
> SQLUNIQUEID is what you would use in a binary file. It's not applicable
> here.

I'm migrating this bulk insert frm sql server 2000... in 2005 this
doesn't work. Is it caused by more strictly rules in 2005 engine?
Is sql converting my "char" guid in binary?

Thanks for the prompt reply :)

bob

Re: bulk inserting uniqueidentifier column

(bob.speaking@gmail.com) writes:
> I'm sorry, cut and pasting sample text file I've removed the correct
> syntax :\
> In fact the last line has the terminator you specified. :)

OK, here is the next guess: your data file has a byte-order mark. Byte-order
marks is no good when you use a format file, because with a format
file, the data file is treated as a stream of bytes (as testified of
the funky way to specify the delimiters in a Unicode file.)

Since you have consistent delimiters, you may not need a format file,
but you could use

WITH (FIELDTERMINATOR = '|', DATAFILETYPE = 'widechar')

I created a data file according to your description, and indeed I got
"string or binary data would be truncated" at first when I used your
format file, because of the BOM. When I used FIELDTERMINATOR and
DATAFILETYPE, I was able to load my test file.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx