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??
QPR
can you show the query? What data type (and length) is the string that is being created?
ASKER
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
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
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.
I'm pretty sure that querystring data is restricted to 255 chars for security reasons.
ASKER
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.
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.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Thank you again. Steve