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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 BCP error invalid character value for cast specif
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dcunningham
Starting Member

25 Posts

Posted - 04/30/2008 :  18:32:55  Show Profile  Reply with Quote
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]





Edited by - dcunningham on 04/30/2008 18:38:26

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 05/06/2008 :  09:22:19  Show Profile  Reply with Quote
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?
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 1.09 seconds. Powered By: Snitz Forums 2000