ActiveX Transformation Task Error

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

Import/Export (DTS) and Replication (2000)

ActiveX Transformation Task Error

Author

Topic

cmcilvoy
Starting Member

8 Posts

Posted - 2002-04-16 : 12:01:02

When I test my DTS Tranformation ActiveX Script i get this

Error:
Data Source is too large for specified buffer size.

This is a simple trim function on a varchar[2000]. is this an inefficient way to strip out spaces?
thanks for the help

chris mcilvoy

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-16 : 12:20:15

You can just copy the data into the column, then use the LTRIM and RTRIM functions in an update:

UPDATE myTable SET col1=LTRIM(TRIM(col1))

You can add this to your DTS package as an Execute SQL Task, after the data transformation runs.

cmcilvoy
Starting Member

8 Posts

Posted - 2002-04-16 : 12:54:53

thanks for the quick answer.

i am unable to use TRIM in a SQL Task Update Statement, just LTRIM or RTRIM?

chris mcilvoy

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2002-04-16 : 14:26:09

All you should need to do is the LTRIM because right hand spaces are not kept in a varchar column by default. If you still want to trim both sides, do this.

UPDATE myTable SET col1=LTRIM(RTRIM(col1))

Jeff Banschbach
Consultant, MCDBA

cmcilvoy
Starting Member

8 Posts

Posted - 2002-04-16 : 14:48:04

sorry, i should of been clearer. i ned to get rid of multiple spaces in a varchar[2000] field. the spaces are random.

thanks

chris mcilvoy

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-04-16 : 15:16:45

quote:

sorry, i should of been clearer. i ned to get rid of multiple spaces in a varchar[2000] field. the spaces are random.

LTRIM/RTRIM returns a character expression after removing leading/trailing blanks. All of 'em. If you have spaces that need to be removed that are not leading or trailing, then you should look at REPLACE.


declare @example varchar(2000)
set @example = '              hello'
select ltrim(@example)

quote:

-------------------------------------
hello

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources