Bulk Insert Failure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Bulk Insert Failure

Author

Topic

peddi_praveen
Starting Member

48 Posts

Posted - 2003-11-18 : 12:20:56

hi guys,
im encountering following error:
Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 19715, column 34 (ListingDataListPriceCurrencyCode).

I i did not find any abnormality in that row.

I deleted that row and ran again encountered error again
im encountering following error:
Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 19714, column 34 (ListingDataListPriceCurrencyCode).

i went on deleteing records.... kept on getting same error.

then i changed my bulk insert stmt , specified firstrow , lastrow param...
first 1- last 19714-- successful
first 19715 - last 19715 -- failure, same error
first 19716 - last end of file -- successful.

I thought i narrowed down the error n copied that record into new file,
tried to bulk insert from that file.....
...... Successful.

Im at cross roads.....

any one encountered this...?
Please Help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-18 : 12:24:31

Perhaps if you showed us the row that has the error and a couple of rows that do not error out, then we'd be able to help.

Tara

peddi_praveen
Starting Member

48 Posts

Posted - 2003-11-18 : 12:31:06

CREATE TABLE LISTING (
[ListingDataREOfficeID] [varchar] (32) ,
[AMListingRecordStatus] [char] (20) ,
[AMListingID] [bigint],
[ListingListingID] [varchar] (32) ,
[PropType] [char] (18) ,
[ListingCopyrightNotice] [text] ,
[ListingAddressBoxNumber] [varchar] (25) ,
[ListingAddressCarrierRoute] [text] ,
[ListingAddressCity] [varchar] (50) ,
[ListingAddressCountry] [varchar] (50) ,
[ListingAddressPostalCode] [char] (10) ,
[ListingAddressStateOrProvince] [varchar] (25) ,
[ListingAddressStreetAdditionalInfo] [text] ,
[ListingAddressStreetDirPrefix] [varchar] (25) ,
[ListingAddressStreetDirSuffix] [varchar] (25) ,
[UpdateDateTime] [DATETIME] ,
[CreationDateTime] [DATETIME] ,
[ListingAddressStreetName] [varchar] (50) ,
[ListingAddressStreetNumber] [char] (10) ,
[ListingAddressStreetSuffix] [varchar] (10) ,
[ListingAddressUnitNumber] [varchar] (25) ,
[ListingDisclaimer] [text] ,
[ListingParcelNumber] [varchar] (25) ,
[ListingZoning] [varchar] (50) ,
[ListingModificationTimeStamp] [varchar] (25) ,
[ListingView] [text] ,
[ListingViewPresent] [char] (3) ,
[ListingDataListingType] [varchar] (10) ,
[ListingDataPublicRemarks] [text] ,
[ListingDataRemarks] [text] ,
[ListingDataShowingInstructions] [text] ,
[ListingDataListDate] [varchar] (25) ,
[ListingDataListPrice] [decimal](18, 2) ,
[ListingDataListPriceCurrencyCode] [char] (3) ,
[ListingDataExpirationDate] [varchar] (25) ,
[ListingDataCommission] [decimal](18, 2) ,
[ListingDataCommissionValue] [char] (10) ,
[ListingDataCommissionCurrencyCode] [char] (3) ,
[ElementarySchool] [text] ,
[HighSchool] [text] ,
[SchoolDistrict] [text] ,
[MiddleSchool] [text] ,
[TaxDataCounty] [varchar] (50) ,
[TaxDataLegalDescription] [text] ,
[TaxDataOwnersName] [varchar] (50) ,
[TaxDataParcelMapURL] [text] ,
[TaxDataPropertyZoning] [varchar] (50) ,
[TaxDataTaxID] [varchar] (25) ,
[TaxDataModificationTimeStamp] [varchar] (25) ,
[TaxDataAssessedValuation] [decimal](18, 2) ,
[TaxDataAssessedValuationCurrencyCode] [char] (3) ,
[MLSInfoListingArea] [varchar] (50) ,
[MLSInfoListingServiceName] [text] ,
[MLSInfoListingStatus] [char] (18) ,
[MLSInfoListingStatusStatus] [char] (18) ,
[MLSInfoOriginalListPrice] [decimal](18, 2) ,
[MLSInfoOriginalListPriceCurrencyCode] [char] (3) ,
[MLSInfoStatusChangeDate] [varchar] (25) ,
[MLSInfoDaysOnMarket] [int],
[GEOCounty] [varchar] (50),
[GEODirections] [text],
[GEOMapCoordinate] [varchar] (20),
[GEOLatitude] [decimal](18, 8),
[GEOLongitude] [decimal](18, 8),
[GEOURL] [text],
[SalesDataAMOfficeID] [bigint],
[SalesDataContractDate] [varchar] (25) ,
[SalesDataClosePrice] [decimal](18, 2) ,
[SalesDataClosePriceCurrencyCode] [char] (3) ,
[SalesDataCloseDate] [varchar] (25) ,
[LotSizeArea] [decimal](18, 2) ,
[LotSizeAreaUnits] [varchar] (20) ,
[LotSizeDimensions] [varchar] (100) ,
[LotSizeDimensionsUnits] [varchar] (20) ,
[LotSizeLength] [decimal](18, 2) ,
[LotSizeWidth] [decimal](18, 2) ,
[DataSourceID] [int],
[AMOfficeID] [bigint],
[MLSID] [char] (15),
[ListingAddressUnstructured] [text],
[ListingDataInternetRemarks] [text],
[FinancialInformationLeaseOption] [varchar] (50),
[FinancialInformationLeaseOptionConsidered] [char] (3),
[FinancialInformationTradeOption] [varchar] (50),
[FinancialInformationTradeOptionConsidered] [char] (3),
[FinancialInformationRentalPrice] [decimal](18, 2) ,
[FinancialInformationRentalPriceCurrencyCode] [char] (3),
[FinancialInformationRentalPricePeriod] [varchar] (10),
[DisplayInformationListingDisplay] [varchar] (50),
[DisplayInformationListingDisplayDisplay] [char] (3),
[DisplayInformationAddressDisplay] [varchar] (50),
[DisplayInformationAddressDisplayDisplay] [char] (3),
[JrHighSchool] [text],
[PrimarySchool] [text],
[LotSizeAcres] [decimal](18, 2) ,
[LotSizeAcresType] [varchar] (50),
[LotSizeAcresRange] [varchar] (100),
[ListingResourceEntity] [varchar] (32),
[RecordHashValue] [varchar] (128)
)
go
sample records:
1003512!@!INSERT!@!18595068!@!2012806!@!RP!@!!@!!@!!@!Seffner!@!USA!@!33584!@!FL!@!!@!!@!!@! !@! !@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!1!@!!@!!@!!@!!@!103000!@!USD!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!Seffner!@!!@!!@!ACTIVE!@!!@!!@!!@!!@!Hillsborough!@!!@!!@!!@!!@!!@!819181!@!!@!!@!!@!!@!!@!!@!70 X 100!@!Feet!@!100!@!70!@!837!@!819179!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!YES!@!!@!!@!!@!!@!!@!!@!1535322317161154118225886366192216983380
---- second record
1002217!@!INSERT!@!18595069!@!2018585!@!RP!@!!@!!@!!@!Tampa!@!USA!@!33647!@!FL!@!!@!!@!!@! !@! !@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!1!@!!@!!@!!@!!@!329900!@!USD!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!Tampa!@!!@!!@!ACTIVE!@!!@!!@!!@!!@!Hillsborough!@!!@!!@!!@!!@!!@!819186!@!!@!!@!!@!!@!!@!!@!116x177!@!Feet!@!177!@!116!@!837!@!819184!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!YES!@!!@!!@!!@!!@!!@!!@!8818325121438862441661311975215124777125221
--- culprit record
1006968!@!INSERT!@!18614630!@!91231!@!RP!@!!@!!@!!@!Palmetto!@!USA!@!34221!@!FL!@!!@!!@!!@! !@! !@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!1!@!BEAUTIFUL upgraded Terra Ceia Club condo - lots of upgrades with imported tile, tiled bar, tiled deck overlooking pool and course. Master is large with walk-ins. Custom draperies and professionally decorated - shows like a model. With covered parking and amenities - pool, clubhouse, golf course, gated - Perfect.!@!!@!!@!!@!114900!@!USD!@!!@!!@!!@!!@!Palmetto!@!Palmetto!@!!@!!@!!@!UNIT 202 THE TERRA CEIA CLUB A CONDO PI#24168.056!@!!@!!@!!@!!@!!@!!@!!@!Palmetto!@!!@!!@!ACTIVE!@!!@!!@!!@!!@!Manatee!@!!@!!@!!@!!@!!@!917006!@!!@!!@!!@!!@!!@!!@!!@!!@!0!@!0!@!837!@!917004!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!!@!YES!@!!@!!@!!@!!@!!@!!@!7451141101591251201671932547618617314166208

field delimitor --!@!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-18 : 12:33:41

The problem is with the size of one of your columns. You are trying to insert data into a column, but the data is too big to fit into the column. It is probably the column where BEAUTIFUL upgraded... is going into. You'll need to change the column's data type to correct this (that's if I am correct).

Tara

peddi_praveen
Starting Member

48 Posts

Posted - 2003-11-18 : 12:42:02

Hi Tara,
I have changed the content in that column ("beautiful.....") to NULL.
still its giving the same error
Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 19715, column 34 (ListingDataListPriceCurrencyCode).

interestingly,
when i copied the row with original data into new text file n tried to bulk insert, it was successful....????

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-18 : 12:48:02

You propably had a hidden character in your row. Which editor are you using to see the rows?

Tara

peddi_praveen
Starting Member

48 Posts

Posted - 2003-11-18 : 12:53:51

EditPlus

parames
Starting Member

1 Post

Posted - 2006-04-24 : 21:17:16

Can You Plz send your bulk insert qry?
I aslo Used this bulk insert method.

quote:
Originally posted by peddi_praveen

Hi Tara,
I have changed the content in that column ("beautiful.....") to NULL.
still its giving the same error
Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 19715, column 34 (ListingDataListPriceCurrencyCode).

interestingly,
when i copied the row with original data into new text file n tried to bulk insert, it was successful....????

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources