BCP error invalid character value for cast specif

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

BCP error invalid character value for cast specif

Author

Topic

dcunningham
Starting Member

25 Posts

Posted - 2008-04-30 : 18:32:55

I'm usung bcp to move data from one table to another. 202 million rows. The source/original table had 32 fields. The new table has 33 fields. I added a PK column to the destination/new table. The new PK column is the first field in the new table, it is set to autoincrement by 1 on each insert. The data file only has 32 fields of data per row.

The error is :SQLState = 22018, NativeError = 0 Error = [Microsoft][SQL Native Client]Invalid character value for cast specification

10 times, then bcp shuts off (default errors is 10).

C:\>bcp dbname.schema.TP_LOG_NEW in e:\alltplog.dat -S
dbsvr -T -f tplognew.fmt

tplognew.fmt:
9.0
33
1 SQLCHAR 0 41 "\t" 1 TP_LOGID_PK ""
2 SQLCHAR 0 41 "\t" 2 TP_LOG_ID ""
3 SQLCHAR 0 20 "\t" 3 PROCESS_TYPE SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 20 "\t" 4 SEVERITY_CODE SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 41 "\t" 5 TP_ERROR_REF_ID ""
6 SQLCHAR 0 41 "\t" 6 TP_PARTNER_ID ""
7 SQLCHAR 0 41 "\t" 7 TP_CUSTOMER_ID ""
8 SQLCHAR 0 500 "\t" 8 DESCRIPTION SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 255 "\t" 9 PROCESS_REF_ID SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 255 "\t" 10 PROCESS_NAME SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 24 "\t" 11 PROCESS_ARGUMENT_1 ""
12 SQLCHAR 0 24 "\t" 12 PROCESS_ARGUMENT_2 ""
13 SQLCHAR 0 24 "\t" 13 PROCESS_ARGUMENT_3 ""
14 SQLCHAR 0 24 "\t" 14 PROCESS_ARGUMENT_4 ""
15 SQLCHAR 0 41 "\t" 15 PROCESS_ARGUMENT_5 ""
16 SQLCHAR 0 41 "\t" 16 PROCESS_ARGUMENT_6 ""
17 SQLCHAR 0 41 "\t" 17 PROCESS_ARGUMENT_7 ""
18 SQLCHAR 0 41 "\t" 18 PROCESS_ARGUMENT_8 ""
19 SQLCHAR 0 500 "\t" 19 PROCESS_ARGUMENT_9 SQL_Latin1_General_CP1_CI_AS
20 SQLCHAR 0 500 "\t" 20 PROCESS_ARGUMENT_10 SQL_Latin1_General_CP1_CI_AS
21 SQLCHAR 0 500 "\t" 21 PROCESS_ARGUMENT_11 SQL_Latin1_General_CP1_CI_AS
22 SQLCHAR 0 500 "\t" 22 PROCESS_ARGUMENT_12 SQL_Latin1_General_CP1_CI_AS
23 SQLCHAR 0 41 "\t" 23 USER1 ""
24 SQLCHAR 0 24 "\t" 24 USER2 ""
25 SQLCHAR 0 255 "\t" 25 USER3 SQL_Latin1_General_CP1_CI_AS
26 SQLCHAR 0 255 "\t" 26 USER4 SQL_Latin1_General_CP1_CI_AS
27 SQLCHAR 0 255 "\t" 27 USER5 SQL_Latin1_General_CP1_CI_AS
28 SQLCHAR 0 32 "\t" 28 CREATED_BY SQL_Latin1_General_CP1_CI_AS
29 SQLCHAR 0 24 "\t" 29 CREATED_DATE ""
30 SQLCHAR 0 32 "\t" 30 CREATED_PROCESS SQL_Latin1_General_CP1_CI_AS
31 SQLCHAR 0 32 "\t" 31 MODIFIED_BY SQL_Latin1_General_CP1_CI_AS
32 SQLCHAR 0 24 "\t" 32 MODIFIED_DATE ""
33 SQLCHAR 0 32 "\r\n" 33 MODIFIED_PROCESS SQL_Latin1_General_CP1_CI_AS

New table definition:

CREATE TABLE [schema].[TP_LOG_NEW](
[TP_LOGID_PK] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[TP_LOG_ID] [numeric](18, 0) NOT NULL,
[PROCESS_TYPE] [varchar](20) NOT NULL DEFAULT (),
[SEVERITY_CODE] [varchar](20) NOT NULL DEFAULT ('0'),
[TP_ERROR_REF_ID] [numeric](18, 0) NOT NULL DEFAULT ((0)),
[TP_PARTNER_ID] [numeric](18, 0) NULL,
[TP_CUSTOMER_ID] [numeric](18, 0) NULL,
[DESCRIPTION] [varchar](500) NULL,
[PROCESS_REF_ID] [varchar](255) NULL,
[PROCESS_NAME] [varchar](255) NULL,
[PROCESS_ARGUMENT_1] [datetime] NULL,
[PROCESS_ARGUMENT_2] [datetime] NULL,
[PROCESS_ARGUMENT_3] [datetime] NULL,
[PROCESS_ARGUMENT_4] [datetime] NULL,
[PROCESS_ARGUMENT_5] [numeric](18, 0) NULL,
[PROCESS_ARGUMENT_6] [numeric](18, 0) NULL,
[PROCESS_ARGUMENT_7] [numeric](18, 0) NULL,
[PROCESS_ARGUMENT_8] [numeric](18, 0) NULL,
[PROCESS_ARGUMENT_9] [varchar](500) NULL,
[PROCESS_ARGUMENT_10] [varchar](500) NULL,
[PROCESS_ARGUMENT_11] [varchar](500) NULL,
[PROCESS_ARGUMENT_12] [varchar](500) NULL,
[USER1] [numeric](18, 0) NULL,
[USER2] [datetime] NULL,
[USER3] [varchar](255) NULL,
[USER4] [varchar](255) NULL,
[USER5] [varchar](255) NULL,
[CREATED_BY] [varchar](32) NULL,
[CREATED_DATE] [datetime] NULL,
[CREATED_PROCESS] [varchar](32) NULL,
[MODIFIED_BY] [varchar](32) NULL,
[MODIFIED_DATE] [datetime] NULL,
[MODIFIED_PROCESS] [varchar](32) NULL,
CONSTRAINT [PK_TP_LOG_NEW] PRIMARY KEY CLUSTERED
(
[TP_LOGID_PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-05-06 : 09:22:19

I think, as you do not have TP_LOGID_PK in the data file you need to set the order to 0


1 SQLCHAR 0 41 "\t" 1 TP_LOGID_PK ""

should be


1 SQLCHAR 0 41 "\t" 0 TP_LOGID_PK ""

Or have you tried removing this line completely from the format file?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources