SQLTeam.com | Weblogs | Forums

Bcp fails with SQLState = 22005


#1

Hello, I am struggling on using bcp to import data, here is my steps:

  1. I created a Test database on my localhost
  2. In the Test database, I created a Test table, the query is here for your convenience:

CREATE TABLE [dbo].[Test](
[id] [int] IDENTITY(1,1) NOT NULL,
[network_group_name] varchar NULL,
[IP] varchar NULL,
[OS] varchar NULL,
[App_name] varchar NULL,
[vuln_name] varchar NULL,
[host_score] [int] NULL,
[recordsWritten] [datetime] NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I then create the format file used in bcp:
bcp Test.dbo.Test format nul -c -t, -f C:\RXie\SQL\Test.fmt –T
Here is the format file:

9.0
8
1 SQLCHAR 0 12 "," 1 id ""
2 SQLCHAR 0 128 "," 2 network_group_name SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 15 "," 3 IP SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 128 "," 4 OS SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 512 "," 5 App_name SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 512 "," 6 vuln_name SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 12 "," 7 host_score ""
8 SQLCHAR 0 24 "\r\n" 8 recordsWritten ""

The data file is called 20150902FullTest.rpt and the first couple lines (first line is the header and followed by two rows) are posted here:

network_group_name,IP,OS,App_Name,vuln_name,host_score,recordswritten

Domestic,10.216.56.88,Windows XP SP3,Adobe / Macromedia Flash Player,APSB14-17: Adobe Flash Player CVE-2014-0537 Vulnerability,4350,2015-09-01 09:55:07.720
Domestic,10.216.56.88,Windows XP SP3,Adobe / Macromedia Flash Player,APSB14-17: Adobe Flash Player CVE-2014-0539 Vulnerability,4350,2015-09-01 09:55:07.720

With the format file and the data file, I use the following bcp command:
bcp Test.dbo.Test in C:\Rxie\SQL\20150902FullTest.rpt -f C:\Rxie\SQL\Test.fmt -T

I got the following error messages:

Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification

BCP copy in failed


#2

I would change all the columns in your [TEST] table into VARCHAR, and make them "decently wide" so nothing is truncated - indeed VARCHAR(8000) would be OK [and possibly preferable to VARCHAR(MAX) ]

Once the data has been imported, hopefully without error!, you can then look at it see if any values for [host_score] are not valid as INT or [recordsWritten] contains invalid date/time.

We add two columns ErrNo (default 0) and ErrMsg VARCHAR(8000) and update those for any rows where the column does not pass our validation tests (which would include column length). Then we either only process rows where ErrNo = 0, or we abort the whole batch if ANY rows have ErrNo <> 0 (depends on the referential integrity you need as to which outcome would be suitable for you)


#3

Actually just looking at the date data:

2015-09-01 09:55:07.720

may not convert implicitly to datetime. You need either

20150901 09:55:07.720
or
2015-09-01T09:55:07.720

although depending on Locale of the server etc. you might get away with this date format.