asked on

Bcp Failing

I'm trying to bcp in from a very wide table with data I'm not too familiar with and getting this result:

C:\office\db\bcp>bcp mydb..test_2 in test.txt -S -Usa
-Pxx -c -b1000

Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22018, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specifica
tion
....

What switches should I be using? I've tried -k but had the same result.

jkavx

Guy Hengel [angelIII / a3]

we would need to know the structure of the both the table and the file contents...
which would allow then to create a format file, and specify that for the bcp command...

jkavx

ASKER

I'm attaching a portion of the text file from the bcp out. Here's the ddl for the table:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[test](
      [DOCNO] [int] IDENTITY(1,1) NOT NULL,
      [REFACQID] [int] NULL,
      [BATCHNAME] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [BATCHNUM] [int] NULL,
      [PARENTDOCNO] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ATTACHCOUNT] [int] NULL,
      [RECORDTYPE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [DOCUMENTTYPE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [DOCUMENTCLASS] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [CTOPICINDEX] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [CTOPIC] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [DISPLAYNAME] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [FOLDERLOCATION] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [FILENAME] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [STOREID] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ENTRYID] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [CUSTODIAN] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [MEDIA] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [LABEL] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [DATAFILE] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [CREATTIMEDATE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [SENTONDATE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [RCVDTIMEDATE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [GENCREATED] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [GENMODIFIED] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [GENACCESSED] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [STACREATED] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [STAMODIFIED] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [STAPRINTED] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [STATOTEDITTIME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [SUBJECT] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [TITLE] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [SENSITIVITY] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [IMPORTANCE] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [SEARCHKEY] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [AUTHOREMAIL] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [AUTHORNAME] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ORIGINALAUTH] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [RECIPIENTS] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [SENTTO] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [CC] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [BCC] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [COMPANY] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [MSGSIZE] [int] NULL,
      [EMAILHEADER] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ATTACHMENTS] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [BODY] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ATTTEXT] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ATTFPROPERTY] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ALLFIELDS] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OIVERSION] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OIEXCEPTLOG] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OIPROCTIME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [MLDATA] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [BUCKET] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [MD5HASH] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ATTACHMENTMD5] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [CONCATATTACHMD5] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [MASTERMD5] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [MASTERDATE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [DOCIDS] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ATTACHIDS] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [PARENTID] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [PRDNO] [int] NULL,
      [DELFLAG] [int] NOT NULL,
      [DUPFLAG] [int] NOT NULL,
      [PROCESSDONOTPROCESS] [int] NOT NULL,
      [RESPONSIVE] [int] NOT NULL,
      [BATCH] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [WKSTATION] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [MSGFSIZE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [GPARENTFNAMEGU] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [INTFNAMEGU] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ACTFEXT] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [FEXT] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [DTFEXT] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [DTFPROP] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [DIRECTORY] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [RELPATH] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [EXTRTYPE] [int] NULL,
      [ISBDYEMPTY] [int] NULL,
      [RAWBDYSIZE] [int] NULL,
      [IFPRO] [int] NULL,
      [SYN] [int] NULL,
      [PGCOUNT] [int] NULL,
      [PPROCNAME] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [PRPROCTIMEBEG] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [PRPROCTIMEEND] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [CASENUM] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [DUPHASH] [nvarchar](300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [MULTSEARCHKEY] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [CUSTOM1] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ORIGFPATH] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ORIGFOLDER] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [SSCREATED] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [SSRECORDTYPE] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [SSTITLE] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [MSGFILE] [image] NULL,
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
      [DOCNO] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

sampleBcpOut.txt

Guy Hengel [angelIII / a3]

hmmm. what language is the contents of that .txt file?
I could not read it (only sqares).
how was that file generated?

jkavx

ASKER

English. It was generated with a bcp out:

bcp myDb..abc out test.txt -Sxxx -Uyyy -Pzzz -c -b1000

I open it in TextPad without problem.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jkavx

ASKER

yes, it definitely contains new lines. I didn't realize that would break the bcp.