Truncate 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)

Truncate Error

Author

Topic

s15199d
Starting Member

15 Posts

Posted - 2006-03-21 : 12:30:24

"The statement has been terminated. string or binary data would be truncated."

Is there a way to determine what field is causing this error, and if so is there a work around beside changing the column width?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-21 : 12:32:55

Can you post your statement and the table structure?

s15199d
Starting Member

15 Posts

Posted - 2006-03-21 : 12:46:11

This is the destination table

CREATE TABLE [WebSurvey] (
[SubmitDate] [datetime] NULL ,
[Content] [tinyint] NULL ,
[Appearance] [tinyint] NULL ,
[Ease] [tinyint] NULL ,
[RatingComment] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OtherComment] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmailOption] [bit] NULL ,
[FirstName] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[find1] [bit] NULL ,
[find2] [bit] NULL ,
[find3] [bit] NULL ,
[find4] [bit] NULL ,
[find12] [bit] NULL ,
[find5] [bit] NULL ,
[find6] [bit] NULL ,
[find7] [bit] NULL ,
[find11] [bit] NULL ,
[find8] [bit] NULL ,
[searchenginecomment] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[find9] [bit] NULL ,
[Websitelinkcomment] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[find10] [bit] NULL ,
[findcomment] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WhyVisited1] [bit] NULL ,
[WhyVisited2] [bit] NULL ,
[WhyVisited3] [bit] NULL ,
[WhyVisited4] [bit] NULL ,
[WhyVisited11] [bit] NULL ,
[WhyVisited5] [bit] NULL ,
[WhyVisited12] [bit] NULL ,
[WhyVisited8] [bit] NULL ,
[WhyVisited9] [bit] NULL ,
[WhyVisited10] [bit] NULL ,
[WhyVisited6] [bit] NULL ,
[WhyVisited7] [bit] NULL ,
[WhyVisitedComment] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SubmittedFrom] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Website] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO

s15199d
Starting Member

15 Posts

Posted - 2006-03-21 : 12:49:12

This is the Source Table:

CREATE TABLE [WebSurvey] (
[SubmitDate] [datetime] NULL ,
[Content] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Appearance] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Ease] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RatingComment] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OtherComment] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmailOption] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[find1] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[find2] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[find3] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[find4] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[find12] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[find5] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[find6] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[find7] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[find11] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[find8] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[searchenginecomment] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[find9] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Websitelinkcomment] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[find10] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[findcomment] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[findcomment_text] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WhyVisited1] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WhyVisited2] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WhyVisited3] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WhyVisited4] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WhyVisited11] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WhyVisited5] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WhyVisited12] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WhyVisited8] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WhyVisited9] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WhyVisited10] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WhyVisited6] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WhyVisited7] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WhyVisitedComment] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WhyVisitedComment_Text] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SubmittedFrom] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Website] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPAddress] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UNID] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

s15199d
Starting Member

15 Posts

Posted - 2006-03-21 : 12:52:30

clearly there are fields in the source table that are larger than the destination table...

but the data should more or less fit into the new data structure...obviously there is a record or two out there that don't fit the new structure and I wan to identify those...

or if possible I'd be happy with truncating the data and forcing the insert...is this possible?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-21 : 12:58:42

How would you expect a varchar(254) to fit into a bit field? You may need to evaluate any of these varchar's to 1 or 0 in your code..

s15199d
Starting Member

15 Posts

Posted - 2006-03-21 : 13:04:12

b/c the data is a 1 or a 0 that's how.

s15199d
Starting Member

15 Posts

Posted - 2006-03-21 : 13:10:24

the source table was TERRIBLY designed...that's why I'm going through the trouble of redesigning it...I change the data types in a SQL statement that happens in my DTS before the data is insterted into the destination table. However, when that final insert takes place one of my fields isn't large enough...I was hoping there was a way to determine what field was causing the error.

And if it's possible I'd love for it to force the insert, and go ahead and do the truncate.

s15199d
Starting Member

15 Posts

Posted - 2006-03-21 : 13:30:12

I ended up doing this on all of the varchar columns

SELECT COUNT(<<fieldname>>) FROM <<tablename>> WHERE LEN(<<fieldname>>) > <<varchar length>>

This told me how many records I have over the varchar length...so I'll go back into my table...and increase the varchar legths on the columns that returned at least 1 record that was too long...and I'll keep doing that until I get them all to return 0 records too long...

this is a long process...but sometimes the long way is the easy way...

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-22 : 05:56:11

Just check that the length of the fields you've changed to bit datatypes aren't > 1 even if they are supposed to be 1 or 0..

You may be easier using this on your destination table:


DECLARE @sql VARCHAR(1024)
SELECT 	@sql = 	isnull(@sql, '') + 
		CASE 	WHEN ORDINAL_POSITION=1 THEN 
			'SELECT ' 
		ELSE 	', ' 
		END 
	+ 'CONVERT(bit,' + COLUMN_NAME + ')'
FROM 	INFORMATION_SCHEMA.COLUMNS
WHERE 	TABLE_NAME = 'WebSurvey'
and DATA_TYPE = 'bit'

--PRINT 	@sql
SELECT 	@sql

and running the SQL which is produced on your source table..

Someone on here posted a version of this code, but can't remember who..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources