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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy