StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POBulk load data conversion error (overflow)
text
Body
copied!<p>I have been racking my brain trying to figure out this error. I just don't get it. </p> <p>SQL Server Database Error: Bulk load data conversion error (overflow) for row 1, column 1 (CUR_CLM_UNIQ_ID). </p> <p>Here is my code:</p> <pre><code>SET DATEFORMAT ymd; go BULK INSERT aco.PartAClaimsHeader FROM 'C:\Users\Derek\Documents\files\P.A1000.ACO.CCLF1.D120730.T1251070' WITH ( --DATAFILETYPE ='CHAR', FORMATFILE='C:\Users\Derek\Documents\files\HeaderFileFormat.xml', --ERRORFILE='C:\Users\Derek\Documents\files\ERRORS.TXT', ROWTERMINATOR='\r\n', CODEPAGE='RAW', -- KEEPNULLS, TABLOCK ) GO </code></pre> <p>It's fixed width. Here is some sample data</p> <pre><code>00190000777340000000000000109061100000924A 102012-01-182012-02-16323569 ~ 0000000003522.44 12010000~1154529360~ 1518945500~ 00158077735600000000000000100256140000646D 602011-07-222011-07-241141401 78650 0000000009238.12 12010247~1992759419159875707215987570721598757072 0022980857396000000000000010S191274000037A 602012-06-042012-06-08112988 2988 0000000002255.52 12010885~1760429880~ 1831184316~ </code></pre> <p>Here is my format file</p> <pre><code><?xml version="1.0"?> <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <RECORD> <FIELD ID="1" xsi:type="CharFixed" LENGTH="13"/> <FIELD ID="2" xsi:type="CharFixed" LENGTH="13"/> <FIELD ID="3" xsi:type="CharFixed" LENGTH="6"/> <FIELD ID="4" xsi:type="CharFixed" LENGTH="11"/> <FIELD ID="5" xsi:type="CharFixed" LENGTH="2"/> <FIELD ID="6" xsi:type="CharFixed" LENGTH="10"/> <FIELD ID="7" xsi:type="CharFixed" LENGTH="10"/> <FIELD ID="8" xsi:type="CharFixed" LENGTH="1"/> <FIELD ID="9" xsi:type="CharFixed" LENGTH="1"/> <FIELD ID="10" xsi:type="CharFixed" LENGTH="7"/> <FIELD ID="11" xsi:type="CharFixed" LENGTH="7"/> <FIELD ID="12" xsi:type="CharFixed" LENGTH="2"/> <FIELD ID="13" xsi:type="CharFixed" LENGTH="17"/> <FIELD ID="14" xsi:type="CharFixed" LENGTH="1"/> <FIELD ID="15" xsi:type="CharFixed" LENGTH="2"/> <FIELD ID="16" xsi:type="CharFixed" LENGTH="2"/> <FIELD ID="17" xsi:type="CharFixed" LENGTH="4"/> <FIELD ID="18" xsi:type="CharFixed" LENGTH="1"/> <FIELD ID="19" xsi:type="CharFixed" LENGTH="10"/> <FIELD ID="20" xsi:type="CharFixed" LENGTH="10"/> <FIELD ID="21" xsi:type="CharFixed" LENGTH="10"/> <FIELD ID="22" xsi:type="CharFixed" LENGTH="10"/> <FIELD ID="23" xsi:type="CharTerm" TERMINATOR="\r\n"/> </RECORD> <ROW> <COLUMN SOURCE="1" NAME="c1" xsi:type="SQLINT"/> <COLUMN SOURCE="2" NAME="c2" xsi:type="SQLINT"/> <COLUMN SOURCE="3" NAME="c3" xsi:type="SQLVARYCHAR"/> <COLUMN SOURCE="4" NAME="c4" xsi:type="SQLVARYCHAR" /> <COLUMN SOURCE="5" NAME="c5" xsi:type="SQLTINYINT" /> <COLUMN SOURCE="6" NAME="c6" xsi:type="SQLDATE"/> <COLUMN SOURCE="7" NAME="c7" xsi:type="SQLDATE"/> <COLUMN SOURCE="8" NAME="c8" xsi:type="SQLCHAR" LENGTH="1"/> <COLUMN SOURCE="9" NAME="c9" xsi:type="SQLCHAR" LENGTH="1" /> <COLUMN SOURCE="10" NAME="c10" xsi:type="SQLVARYCHAR" /> <COLUMN SOURCE="11" NAME="c11" xsi:type="SQLVARYCHAR"/> <COLUMN SOURCE="12" NAME="c12" xsi:type="SQLCHAR" LENGTH="2"/> <COLUMN SOURCE="13" NAME="c13" xsi:type="SQLDECIMAL" SCALE="2" PRECISION="17"/> <COLUMN SOURCE="14" NAME="c14" xsi:type="SQLCHAR" LENGTH="1" /> <COLUMN SOURCE="15" NAME="c15" xsi:type="SQLCHAR" LENGTH="2" /> <COLUMN SOURCE="16" NAME="c16" xsi:type="SQLCHAR" LENGTH="2"/> <COLUMN SOURCE="17" NAME="c17" xsi:type="SQLVARYCHAR"/> <COLUMN SOURCE="18" NAME="c18" xsi:type="SQLCHAR" LENGTH="1"/> <COLUMN SOURCE="19" NAME="c19" xsi:type="SQLCHAR" LENGTH="10"/> <COLUMN SOURCE="20" NAME="c20" xsi:type="SQLCHAR" LENGTH="10" /> <COLUMN SOURCE="21" NAME="c21" xsi:type="SQLCHAR" LENGTH="10"/> <COLUMN SOURCE="22" NAME="c22" xsi:type="SQLCHAR" LENGTH="10"/> <COLUMN SOURCE="23" NAME="c23" xsi:type="SQLCHAR" /> </ROW> </BCPFORMAT> </code></pre> <p><strong>UPDATE</strong>: I changed the 2 first INT's to VARCHAR in the db and format file and it worked no problem. Would like to keep them INT's though.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload