DTS Query string truncating result field at 255 characters

I am running a sql query Concatenating a string with results which should be approx 2000 characters. 255 characters. In results the string is being truncated at 255 char.  Am using enterprise manager and dts routine.  How can I get acound this 255 limit.  I know I can use Query Analyzer (done that) but I need to have this run each night vea DTS.  Thoughts??
SteveRedAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QPRCommented:
can you show the query? What data type (and length) is the string that is being created?
0
SteveRedAuthor Commented:
The query is buiding a string which is a series of urls, seperated by commas.
a simplified version is:

declare @vin varchar (20)
select numphotos,
-- begin string part 1
         'http://images.rapidinteractive.com/images/autos/' + LEFT(dbo.vehix.vin, 4)
         + '/' + dbo.vehix.vin + '/' + dbo.vehix.vin + 'f-' + '1' + '.jpg'       +',' +
-- begin string part 2
          'http://images.rapidinteractive.com/images/autos/' + LEFT(dbo.vehix.vin, 4)
          + '/' + dbo.vehix.vin + '/' + dbo.vehix.vin + 'f-' + '2' + '.jpg'       +',' +
-- begin string part 3
          'http://images.rapidinteractive.com/images/autos/' + LEFT(dbo.vehix.vin, 4)
           + '/' + dbo.vehix.vin + '/' + dbo.vehix.vin + 'f-' + '3' + '.jpg'      +',' +
-- begin string part 4
           'http://images.rapidinteractive.com/images/autos/' + LEFT(dbo.vehix.vin, 4)
            + '/' + dbo.vehix.vin + '/' + dbo.vehix.vin + 'f-' + '4' + '.jpg'  
-- ..continues
        as imagelist

from vehicleimages
where vin = @vin

end of example
0
QPRCommented:
sorry, just to clarify... is it the querystring (in the browser window) being truncated or the select statement?
I'm pretty sure that querystring data is restricted to 255 chars for security reasons.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

SteveRedAuthor Commented:
The truncating is occuring on the result of the query (field "imagelist") ie the data returned.

I can do this in Query Analyzer, but not in DTS.  There must be a setting which is limiting this.  Just have no idea what / where it is.
0
QPRCommented:
so where does imagelist end up? In a variable? is the variable length long enough.
Have you tried running a profiler trace when running the DTS package/step, it may show you where the truncating is happening.

When you say the data is returned... returned to where? What is the destination for the query?
Under the properties for the transform data task, click on destination and then "define columns" what is the value for size?

Sorry if this isn't applicable, I'm just trying to pictur how this query is being used within the DTS package.
0
Anthony PerkinsCommented:
See here:
DataPump truncates delimited fields to 255 characters
http://www.sqldts.com/297.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveRedAuthor Commented:
Thank you ACPerkins !  I just joined the EE today as I was fumbling with this problem.  You pointed me to the right solution!
Thank you again.  Steve
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.