Solved

BULK INSERT question - error in input data - SQL Server 2000

Posted on 2008-06-10
2
563 Views
Last Modified: 2010-05-19
I have the following BULK INSERT STATEMENT #1 currently running. I am trying to rewrite it as BULK INSERT STATEMENT #2.
I came across a file today that appears to have an invalid row in it. The first statement issued the warning:
    Server: Msg 4863, Level 16, State 1, Line 1
    Bulk insert data conversion error (truncation) for row 818, column 29 (WO TYPE).
and dropped the record, which is acceptable.

The second statement however, generated the error:
    Server: Msg 8152, Level 16, State 9, Line 1
    String or binary data would be truncated.
    The statement has been terminated.
and no data was inserted. If the bulk insert comes across an invalid row, I would prefer it give an error such as the first statement, but insert what it can. Seems like with a format file, things get touchier. Is there a switch/option/parameter I can add to give me the behavior of the first statement?

BULK INSERT STATEMENT #1
	BULK INSERT [DISCO]
	FROM '\\pkdw0038L\Staging\DISH_DISCO.TXT'
	WITH(FIELDTERMINATOR = '|',
	     ROWTERMINATOR = '\n',
	     MAXERRORS = 100)
 
 
BULK INSERT STATEMENT #2
	BULK INSERT [dbo].[DISCO_RAW_TEST]
	FROM '\\pkdw0038L\Staging\DISCO.TXT'
	WITH(FORMATFILE='\\pkdw0038LStaging\Disconnect.FMT',
		MAXERRORS = 100)
 
The format file:
8.0
29
1       SQLCHAR       0        0      "|"          2     FILE_DATE              ""
2       SQLCHAR       0        0      "|"          3     PTNR_ACCT_NBR          ""
3       SQLCHAR       0        0      "|"          4     ACCT_NBR               ""
4       SQLCHAR       0        0      "|"          5     NAME                   ""
5       SQLCHAR       0        0      "|"          6     SA1                    ""
6       SQLCHAR       0        0      "|"          7     SA2                    ""
7       SQLCHAR       0        0      "|"          8     CITY                   ""
8       SQLCHAR       0        0      "|"          9     ST                     ""
9       SQLCHAR       0        0      "|"         10     ZIP                    ""
10      SQLCHAR       0        0      "|"         11     BA1                    ""
11      SQLCHAR       0        0      "|"         12     BA2                    ""
12      SQLCHAR       0        0      "|"         13     BLG_CITY               ""
13      SQLCHAR       0        0      "|"         14     BLG_ST                 ""
14      SQLCHAR       0        0      "|"         15     BLG_ZIP                ""
15      SQLCHAR       0        0      "|"         16     BILL_CYCLE             ""
16      SQLCHAR       0        0      "|"         17     PROMO_CODE             ""
17      SQLCHAR       0        0      "|"         18     WO_ID                  ""
18      SQLCHAR       0        0      "|"         19     ORIG_ACTV_DT           ""
19      SQLCHAR       0        0      "|"         20     WO_CREATE_DT           ""
20      SQLCHAR       0        0      "|"         21     SCHD_DISCO_DT          ""
21      SQLCHAR       0        0      "|"         22     DISCO_DT               ""
22      SQLCHAR       0        0      "|"         23     WO_ORIGINAL_OP_ID      ""
23      SQLCHAR       0        0      "|"         24     SALES_ID               ""
24      SQLCHAR       0        0      "|"         25     LAST_CHANGE_OP_ID      ""
25      SQLCHAR       0        0      "|"         26     REASON_CD_1            ""
26      SQLCHAR       0        0      "|"         27     REASON_CD_2            ""
27      SQLCHAR       0        0      "|"         28     REASON_CD_3            ""
28      SQLCHAR       0        0      "|"         29     REASON_CD_4            ""
29      SQLCHAR       0        0      "\r\n"      30     WO_TYPE                ""
 
 
DDL of table for first bulk insert:
	CREATE TABLE [DISCO] (
		FILE_DATE smalldatetime,
		[PTNR_ACCT_NBR] [VARCHAR] (25),
		[ACCT_NBR] [VARCHAR] (25),
		[NAME] [VARCHAR] (50),
		[SA1] [VARCHAR] (50),
		[SA2] [VARCHAR] (50),
		[CITY] [VARCHAR] (50),
		[ST] [VARCHAR] (25),
		[ZIP] [VARCHAR] (25),
		[BA1] [VARCHAR] (50),
		[BA2] [VARCHAR] (50),
		[BLG_CITY] [VARCHAR] (50),
		[BLG_ST] [VARCHAR] (25),
		[BLG_ZIP] [VARCHAR] (25),
		[BILL_CYCLE] [VARCHAR] (50),
		[PROMO_DESC] [VARCHAR] (255),
		[WO_ID] [VARCHAR] (50),
		[ORIG_ACTV_DT] [VARCHAR] (25),
		[WO_CREATE_DT] [VARCHAR] (25),
		[SCH_DISC_DT] [VARCHAR] (25),
		[DISC_DT] [VARCHAR] (25),
		[WO_ORIG_OP_ID] [VARCHAR] (50),
		[SALES_REP] [VARCHAR] (50),
		[LAST_CHNG_OP_ID] [VARCHAR] (50),
		[REASON_CD1] [VARCHAR] (50),
		[REASON_CD2] [VARCHAR] (50),
		[REASON_CD3] [VARCHAR] (50),
		[REASON_CD4] [VARCHAR] (50),
		[WO_TYPE] [VARCHAR] (50)
	) ON [PRIMARY]
 
 
DDL of table for second bulk insert:
	CREATE TABLE [dbo].[DISCO_RAW_TEST] (
		[ID_NUM] [int] IDENTITY(1, 1) NOT NULL ,
		[FILE_DATE] [SMALLDATETIME] NULL ,
		[PTNR_ACCT_NBR] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[ACCT_NBR] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[SA1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[SA2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[CITY] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[ST] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[ZIP] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[BA1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[BA2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[BLG_CITY] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[BLG_ST] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[BLG_ZIP] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[BILL_CYCLE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[PROMO_DESC] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[WO_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[ORIG_ACTV_DT] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[WO_CREATE_DT] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[SCH_DISC_DT] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[DISC_DT] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[WO_ORIG_OP_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[SALES_REP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[LAST_CHNG_OP_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[REASON_CD1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[REASON_CD2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[REASON_CD3] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[REASON_CD4] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[WO_TYPE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[ORIG_ACTV_DT] [smalldatetime] NULL ,
		[WO_CREATE_DT] [smalldatetime] NULL ,
		[SCH_DISC_DT] [smalldatetime] NULL ,
		[DISC_DT] [smalldatetime] NULL ,
		[GEN_IND] [bit] NOT NULL DEFAULT(0) ,
		[AHE_EID] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[DVR_IND] [bit] NOT NULL DEFAULT(0) ,
		[HD_IND] [bit] NOT NULL DEFAULT(0) ,
		[DHA_EID] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[ORD_TYP] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
	) ON [PRIMARY]

Open in new window

0
Comment
Question by:dbbishop
2 Comments
 
LVL 19

Accepted Solution

by:
Melih SARICA earned 500 total points
ID: 21754881
increase the Length of column 28 ..
Maybe in some lines there data on that line is longer then 50

Melih SARICA
0
 
LVL 15

Author Closing Comment

by:dbbishop
ID: 31465922
Melih,

That did it. It was occuring before the last column though, so I ended up just making them all 255. Seems like something downstream from this process chopped a chunk of data out of the middle of one of the records, so there were fewer than 29 delimited fields in the row. Thanks for the suggestion. Now I just have to figure out what hiccupped before it got to this point.

Doug
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

831 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