[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Bcp Failing

Posted on 2009-04-28
6
Medium Priority
?
650 Views
Last Modified: 2012-05-06
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
0
Comment
Question by:jkavx
  • 3
  • 3
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24253876
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...
0
 

Author Comment

by:jkavx
ID: 24254243
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
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24254265
hmmm. what language is the contents of that .txt file?
I could not read it (only sqares).
how was that file generated?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:jkavx
ID: 24254328
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.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 24254398
I see, it worked with wordpad.

now, from what I can see, the file contains newlines... which is deadly for bcp (in), you cannot do that.

you will need either some XML format, or another method to get the data copied.
0
 

Author Closing Comment

by:jkavx
ID: 31575617
yes, it definitely contains new lines.  I didn't realize that would break the bcp.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question