• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 583
  • Last Modified:

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??
0
SteveRed
Asked:
SteveRed
  • 3
  • 3
1 Solution
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now