bcp unicode data using format file problem

[Bernd Lambertz]

I have a problem with bcp and format files.

We changed our databases from varchar to nvarchar to support unicode. No

problems so fare with that. It is working fine.

But now I need a format file for the customer table and and it is not
working. It is working fine with the old DB with varchar, but with
nvarchar I'm not able to copy the data. The biggest problem is, that I
got no error message. BCP starts copying to table and finished without
error message.

This is my table:

CREATE TABLE [dbo].[Customer] (
[ID] [int] NOT NULL ,
[CreationTime] [datetime] NULL ,
[ModificationTim e] [datetime] NULL ,
[DiscoveryTime] [datetime] NULL ,
[Name_] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AI NULL ,
[Class] [int] NULL ,
[Subclass] [int] NULL ,
[Capabilities] [int] NULL ,
[SnapshotID] [int] NOT NULL ,
[CompanyName] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AI NOT
NULL ,
[TargetRCCountry] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AI
NOT NULL ,
[LocationID] [int] NULL ,
[MirrorID] [binary] (16) NULL ,
[DeleteFlag] [bit] NULL ,
[AdminStatus] [bit] NULL
) ON [PRIMARY]
GO

and this is the format file:

8.0
13
1 SQLINT 1 12 "#~@~#" 1 ID ""
2 SQLDATETIME 1 24 "#~@~#" 2 CreationTime ""
3 SQLDATETIME 1 24 "#~@~#" 3 ModificationTim e ""
4 SQLDATETIME 1 24 "#~@~#" 4 DiscoveryTime ""
5 SQLNCHAR 2 510 "#~@~#" 5 Name_
SQL_Latin1_Gene ral_CP1_CI_AS
6 SQLINT 1 12 "#~@~#" 6 Class ""
7 SQLINT 1 12 "#~@~#" 7 Subclass ""
8 SQLINT 1 12 "#~@~#" 8 Capabilities ""
9 SQLINT 1 12 "#~@~#" 9 SnapshotID ""
10 SQLNCHAR 2 510 "#~@~#" 10 CompanyName
SQL_Latin1_Gene ral_CP1_CI_AS
11 SQLNCHAR 2 510 "#~@~#" 11 TargetRCCountry
SQL_Latin1_Gene ral_CP1_CI_AS
12 SQLINT 1 12 "#~@~#" 12 LocationID ""
13 SQLBINARY 1 33 "#~@~# \r \r \n"13 MirrorID ""

"#~@~#" is the field terminator. We have a lot of text files with all
kind of charachers in it. So we think this is a set that will never
occur in our files.

Thanks for your help!