bcp unicode files using format files

[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 ,
[ModificationTime] [datetime] NULL ,
[DiscoveryTime] [datetime] NULL ,
[Name_] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[Class] [int] NULL ,
[Subclass] [int] NULL ,
[Capabilities] [int] NULL ,
[SnapshotID] [int] NOT NULL ,
[CompanyName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NOT
NULL ,
[TargetRCCountry] [nvarchar] (255) COLLATE SQL_Latin1_General_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 ModificationTime ""
4 SQLDATETIME 1 24 "#~@~#" 4 DiscoveryTime ""
5 SQLNCHAR 2 510 "#~@~#" 5 Name_
SQL_Latin1_General_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_General_CP1_CI_AS
11 SQLNCHAR 2 510 "#~@~#" 11 TargetRCCountry
SQL_Latin1_General_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!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Simon Hayes]

Bernd Lambertz <vi***@securiy.com> wrote in message news:<3f*********************@news.frii.net>...

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 ,
[ModificationTime] [datetime] NULL ,
[DiscoveryTime] [datetime] NULL ,
[Name_] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[Class] [int] NULL ,
[Subclass] [int] NULL ,
[Capabilities] [int] NULL ,
[SnapshotID] [int] NOT NULL ,
[CompanyName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NOT
NULL ,
[TargetRCCountry] [nvarchar] (255) COLLATE SQL_Latin1_General_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 ModificationTime ""
4 SQLDATETIME 1 24 "#~@~#" 4 DiscoveryTime ""
5 SQLNCHAR 2 510 "#~@~#" 5 Name_
SQL_Latin1_General_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_General_CP1_CI_AS
11 SQLNCHAR 2 510 "#~@~#" 11 TargetRCCountry
SQL_Latin1_General_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!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Your table has 15 columns, but the format file has 13 - according to
"Using a Data File with Fewer Fields" in Books Online you should still
have all the columns in the format file, but use 0 for the field
length and column number of the columns you don't want to load. You
should also not specify a terminator for those columns.

If doing that doesn't resolve the issue, perhaps you could post the
BCP command you're using, as well as the error and out files?

Simon

[Bernd Lambertz]

I tried it with 15 columns but with the same result.

I'm wondering if you have a look at the Online Books page you mentioned they have only SQLCHAR typs for
everything. Okay it is a text file with char in it. But if I have a look at the description on the BCP
page the use SQLINT and SQLBINARY....

What is the right way here. I tried both without success......

The bcp command is running as a batch job and I get no error. It just looks like a time out,
but no message. So I tried BULK COPY in the query analyzer now:
BULK INSERT unicode_db..customer FROM 'C:\inbox\csv_import\customer.csv'
WITH (FORMATFILE = 'C:\inbox\csv_import\formats\customer.fmt', DATAFILETYPE = 'widechar')

and this is the result:

Server: Msg 4832, Level 16, State 1, Line 1
Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider
did not give any information about the error.].
The statement has been terminated.

By the way, I'm using the same stuff for NON-UNICODE with a formatfile with 13 columns all SQLCHAR and
the same table (just VARCHAR instead of NVARCHAR)
and it works fine........

Is that the problem that I have a .csv file saved as unicode and the .fmt file saved as ANSI???
Is the field terminator '#~@~#' a problem and not supported?
BCP only except ANSI format files????
Simon Hayes wrote:

Bernd Lambertz <vi***@securiy.com> wrote in message news:<3f*********************@news.frii.net>...

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 ,
[ModificationTime] [datetime] NULL ,
[DiscoveryTime] [datetime] NULL ,
[Name_] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[Class] [int] NULL ,
[Subclass] [int] NULL ,
[Capabilities] [int] NULL ,
[SnapshotID] [int] NOT NULL ,
[CompanyName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NOT
NULL ,
[TargetRCCountry] [nvarchar] (255) COLLATE SQL_Latin1_General_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 ModificationTime ""
4 SQLDATETIME 1 24 "#~@~#" 4 DiscoveryTime ""
5 SQLNCHAR 2 510 "#~@~#" 5 Name_
SQL_Latin1_General_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_General_CP1_CI_AS
11 SQLNCHAR 2 510 "#~@~#" 11 TargetRCCountry
SQL_Latin1_General_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!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Your table has 15 columns, but the format file has 13 - according to
"Using a Data File with Fewer Fields" in Books Online you should still
have all the columns in the format file, but use 0 for the field
length and column number of the columns you don't want to load. You
should also not specify a terminator for those columns.

If doing that doesn't resolve the issue, perhaps you could post the
BCP command you're using, as well as the error and out files?

Simon