February 17, 2019 at 9:17 am
Hello SQLServerCentral colleagues, I'm getting the error: Invalid character value for cast specification trying to bcp in a large file to a table.
The table has identity and money columns. I did not use the -f parm when creating the file. I used the -c parm to export as char
Here's the string used to create the file: bcp " SELECT * FROM table d WITH (index (index_name)) inner join other_table h on d.key1 = h.key2 WHERE d.DATE >= '11/15/2015' " queryout tablename.dat -T -c -S servername -d DB_name
Here's some of the error file, is it complaining about the cr/lf? Any help appreciated.
#@ Row 1, Column 24: Invalid character value for cast specification @#
1844330468 87922889 1447968 2016-05-16 00:00:00.000 2016-05-16 17:18:49.000 B1 P .000 .0000 12.8900 .0000 0 Duplicate 2016-05-16 00:00:00.000 2016-05-21 06:02:34.000 012312 01 1558465500 <NULL> 0 87922889 1447968 2016-06-30 00:00:00.000 2016-07-02 01:14:30.000 0
#@ Row 2, Column 24: Invalid character value for cast specification @#
1851348847 87922889 1447968 2016-05-25 00:00:00.000 2016-05-25 14:15:42.000 B1 P .000 .0000 10.1200 .0000 0 Duplicate 2016-05-25 00:00:00.000 2016-05-27 01:03:41.000 012312 01 1558465500 <NULL> 0 87922889 1447968 2016-06-30 00:00:00.000 2016-07-02 01:14:30.000 0
#@ Row 3, Column 24: Invalid character value for cast specification @#
bcp_in.txt:
d:\Temp>bcp table_name in table_name.dat -T -c -S server_name -d DB_name -b 20000
Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid character value for cast specification
DDL:
CREATE TABLE [dbo].[bcp_table](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[ID2] [bigint] NULL,
[NUM1] [varchar](7) NOT NULL,
[NUM2] [varchar](11) NOT NULL,
[NUM3] [varchar](12) NULL,
[NUM4] [varchar](2) NOT NULL,
[DATE] [datetime] NOT NULL,
[DATE2] [datetime] NOT NULL,
[CD] [varchar](2) NULL,
[STATUS] [varchar](1) NULL,
[QTY] [numeric](10, 3) NOT NULL,
[COST] [money] NULL,
[COST2] [money] NULL,
[UNIT] [varchar](2) NULL,
[CHARGE] [money] NULL,
[QTY2] [int] NULL,
[REASON] [varchar](50) NULL,
[DATE3] [datetime] NULL,
[DATE4] [datetime] NULL,
[NUM5] [varchar](6) NULL,
[ID] [varchar](2) NULL,
[NUM5] [varchar](10) NULL,
[NUM6] [varchar](15) NULL,
[FLAG] [tinyint] NULL,
Error log:
#@ Row 1, Column 24: Invalid character value for cast specification @#
1844330468 87922889 1447968 2016-05-16 00:00:00.000 2016-05-16 17:18:49.000 B1 P .000 .0000 12.8900 .0000 0 Duplicate 2016-05-16 00:00:00.000 2016-05-21 06:02:34.000 012312 01 1558465500 <NULL> 0 87922889 1447968 2016-06-30 00:00:00.000 2016-07-02 01:14:30.000 0
#@ Row 2, Column 24: Invalid character value for cast specification @#
1851348847 87922889 1447968 2016-05-25 00:00:00.000 2016-05-25 14:15:42.000 B1 P .000 .0000 10.1200 .0000 0 Duplicate 2016-05-25 00:00:00.000 2016-05-27 01:03:41.000 012312 01 1558465500 <NULL> 0 87922889 1447968 2016-06-30 00:00:00.000 2016-07-02 01:14:30.000 0
#@ Row 3, Column 24: Invalid character value for cast specification @#
1869921248 87922889 1447968 2016-06-23 00:00:00.000 2016-06-23 14:17:53.000 B1 P .000 .0000 4.0300 .0000 0 Duplicate 2016-06-23 00:00:00.000 2016-06-25 01:23:15.000 012312 01 1558465500 <NULL> 0 87922889 1447968 2016-06-30 00:00:00.000 2016-07-02 01:14:30.000 0
#@ Row 4, Column 24: Invalid character value for cast specification @#
1748447462 87922889 1447968 2015-12-21 00:00:00.000 2015-12-21 18:42:27.000 B1 P .000 .0000 7.7800 .0000 0 Duplicate 2015-12-21 00:00:00.000 2015-12-23 01:06:01.000 012312 01 1558465500 <NULL> 0 87922889 1447968 2016-06-30 00:00:00.000 2016-07-02 01:14:30.000 0
#@ Row 5, Column 24: Invalid character value for cast specification @#
1782357485 87922889 1447968 2016-02-10 00:00:00.000 2016-02-10 15:36:31.000 B1 P .000 .0000 7.6700 .0000 0 Duplicate 2016-02-10 00:00:00.000 2016-02-12 01:39:20.000 012312 01 1558465500 <NULL> 0 87922889 1447968 2016-06-30 00:00:00.000 2016-07-02 01:14:30.000 0
#@ Row 6, Column 24: Invalid character value for cast specification @#
1784819942 87922889 1447968 2016-02-15 00:00:00.000 2016-02-15 19:53:57.000 B1 P .000 .0000 366.4000 .0000 0 Duplicate 2016-02-15 00:00:00.000 2016-02-17 01:16:21.000 012312 01 1558465500 <NULL> 0 87922889 1447968 2016-06-30 00:00:00.000 2016-07-02 01:14:30.000 0
#@ Row 7, Column 24: Invalid character value for cast specification @#
1803503206 87922889 1447968 2016-03-14 00:00:00.000 2016-03-14 15:40:38.000 B1 P .000 .0000 254.9700 .0000 0 Duplicate 2016-03-14 00:00:00.000 2016-03-16 01:27:50.000 012312 01 1558465500 <NULL> 0 87922889 1447968 2016-06-30 00:00:00.000 2016-07-02 01:14:30.000 0
#@ Row 8, Column 24: Invalid character value for cast specification @#
1804859235 87922889 1447968 2016-03-15 00:00:00.000 2016-03-15 15:13:52.000 B1 P .000 .0000 5.8900 .0000 0 Duplicate 2016-03-15 00:00:00.000 2016-03-17 01:14:10.000 012312 01 1558465500 <NULL> 0 87922889 1447968 2016-06-30 00:00:00.000 2016-07-02 01:14:30.000 0
#@ Row 9, Column 24: Invalid character value for cast specification @#
1869921181 133852194 1447968 2016-06-23 00:00:00.000 2016-06-23 14:17:42.000 B1 P .000 .0000 .9400 .0000 0 Duplicate 2016-06-23 00:00:00.000 2016-06-25 01:23:15.000 012312 01 1558465500 <NULL> 0 133852194 1447968 2016-06-30 00:00:00.000 2016-07-02 01:14:30.000 0
#@ Row 10, Column 24: Invalid character value for cast specification @#
1859403964 133852194 1447968 2016-06-07 00:00:00.000 2016-06-07 15:37:34.000 B1 P .000 .0000 7.4600 .0000 0 Duplicate 2016-06-07 00:00:00.000 2016-06-09 01:15:59.000 012312 01 1558465500 <NULL> 0 133852194 1447968 2016-06-30 00:00:00.000 2016-07-02 01:14:30.000 0
February 17, 2019 at 9:38 am
No sure if that is your full DDL but you, it appears you're trying to insert the string value "<null>" into a column of the data type datetime. You'll probably want to insert your data into a staging table and then transform it afterwards into the actual table.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 17, 2019 at 10:36 am
Thanks ThomA, actually, the only NULL in that row is the [NUM6] [varchar](15) NULL, column. Are NULL values not transferable with bcp?
Hmm, after taking a look at the rows in the .DAT file it appears the identity column is confusing the bcp_in process. I need the value of the column, it doesn't look like it was exported.....
Upon further review, and using the -E parm, bcp now complaining about line termination. I'm using the default /n, tried /r/n, no luck.
February 18, 2019 at 2:52 pm
jb_sql_geek - Sunday, February 17, 2019 10:36 AMThanks ThomA, actually, the only NULL in that row is the [NUM6] [varchar](15) NULL, column. Are NULL values not transferable with bcp?Hmm, after taking a look at the rows in the .DAT file it appears the identity column is confusing the bcp_in process. I need the value of the column, it doesn't look like it was exported.....
Upon further review, and using the -E parm, bcp now complaining about line termination. I'm using the default /n, tried /r/n, no luck.
Not sure if that's a typo or not but you would use backslashes - such as \n or \r\n
Sue
February 18, 2019 at 3:49 pm
right thanks, as it turns out, I selected too many columns for the bcp_in to handle. I thought it was the line terminator, then re-read SQL from the bcp_out, realized I had too many columns from the join.
BCP was trying to tell me, "TOO MANY COLUMNS" But it came out as Invalid character value for cast specification.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply