BI dataconversion error - 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)

BI dataconversion error

Author

Topic

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-12-05 : 16:35:17

Hi, I'm doing a Bulk Insert from a Script I'm making myself.
The first table Instr_Details creates and loads fine but the second one(building) does not. Here is the script straigth from QA with the error below it:

USE MASTER
GO
DROP DATABASE G4h
GO
CREATE DATABASE G4h
GO
USE G4h
GO
create table Instr_Details
(
Instructor_First varchar(50),
Instructor_Initial varchar(50),
)
GO
BULK INSERT Instr_Details FROM 'c:\scripts\ii.txt'
WITH (ROWTERMINATOR = '\n')
GO
ALTER TABLE Instr_Details ADD
--Add a PRIMARY KEY identity column.
ID INT IDENTITY
CONSTRAINT ID PRIMARY KEY
GO
--The Building Table Creation,BI and PK Constraint
create table building(

Building varchar(200)null,
RoomNO varchar(200)null,
)

GO
BULK INSERT building FROM 'c:\scripts\build.txt'
WITH (ROWTERMINATOR = '\n')
GO
ALTER TABLE building ADD
--Add a PRIMARY KEY identity column.
LocID INT IDENTITY
CONSTRAINT LocID PRIMARY KEY

(1392 row(s) affected)
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 910, column 1 (Building).

I've been putting in different values for varchar and get different results, here i used (200) and got one line of error, other values I get more than one line

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 16:39:44

Increase to 4000 and see of that works.
If it does, make the table with half the length.
If it doesn't, make the table with 25% less the original length.

Peter Larsson
Helsingborg, Sweden

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-12-05 : 16:57:56

Yes sir that did the trick Peso, my thanks and compliments.
Of course I have to ask why this worked.
Also I have more tables and BI's in this script so I may be posting some more of the same.

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 17:00:38

Some row(s) in your text file has a length that exceed the data length for the column.

Peter Larsson
Helsingborg, Sweden

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-12-05 : 17:00:55

Im not sure what you mean by half the length. Because every row is being used. I intend on putting in fk's later, won't that mess up the RI?

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-12-05 : 17:03:03

Well, either column has plenty of nulls but there are no empty record sets. Each column is 1392 records exactly

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 17:08:22

The column you want to insert into, must have a length that is greater or equal to the matching column in any of the rows in the file.

Peter Larsson
Helsingborg, Sweden

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-12-05 : 18:05:48

Peso ,
thanks, I didn't know that. I will check their lengths from now on

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources