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?
 
Anthony PerkinsConnect With a Mentor Commented:
See here:
DataPump truncates delimited fields to 255 characters
http://www.sqldts.com/297.aspx
0
 
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.