Varchar(2000) Truncated at around 500 Characters 2

[jonbrown]

I am creating a stored procedure which selects data from various tables and builds up 5 strings which will be used as the contents of an email.

The output strings are declared as varchar(2000). The problem appears when the number of characters gets to around 500 - Everything after that seems to be getting truncated from the output even though the Len(displaystring) reports more than 500 characters.

What am I doing wrong. Any advice would be much appreciated.

Jon

 

[aalmeida]

I think you have a mistake in your code, could you post your code so we would be able to help you better? AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[jonbrown]

Here is the SP code - Note if I use SELECT to output the results the results are truncated, If I use print, they display correctly - Is there an output buffer setting that could be changed??


CREATE PROCEDURE SMI_ChangeSelect @id varchar(20) AS

declare @detailstring varchar(2000)
declare @createdate integer
declare @createdate2 datetime
declare @requestername varchar(150)
declare @region varchar(150)
declare @site varchar(150)
declare @department varchar(150)
declare @phone varchar(150)
declare @submittedby varchar(150)
declare @category varchar(150)
declare @type varchar(150)
declare @item varchar(150)

declare @cr varchar(10)

SELECT @cr = Char(10)

-- gather together the details of the ticket that are to be displayed
SELECT @createdate = create_date,
@requestername = CASE When requester_name_ is null THEN 'Unknown Requester'
ELSE requester_name_
END,
@region = CASE WHEN region is null THEN 'Unknown Region'
ELSE region
END,
@site = CASE WHEN site is null THEN 'Unknown Site'
ELSE site
END,
@department = CASE WHEN department is null THEN 'Unknown Department'
ELSE department
END,
@phone = CASE WHEN phone_number is null THEN 'Unknown Phone Number'
ELSE phone_number
END,
@submittedby = CASE WHEN submitted_by is null THEN 'Unknown Submitter'
ELSE submitted_by
END,
@category = CASE WHEN category is null THEN 'Unknown Category'
ELSE category
END,
@type = CASE WHEN type is null THEN 'Unknown Type'
ELSE type
END,
@item = CASE WHEN item is null THEN 'Unknown Item'
ELSE item
END
FROM CHG_CHANGE
WHERE Change_ID_ = @id

-- convert the date from int to datetime for display
SELECT @createdate2 = DateAdd(ss, @createdate,'1970/01/01 00:00:00' )

-- build up the string and pad as required to get even formatting.
SELECT @detailstring = 'DETAILS FOR CHANGE ' + @id + @cr
+ 'Create Date: ' + Space(13) + CONVERT(Varchar(20),@createdate2) + @cr
+ 'Requester Name: ' + Space(10) + @requestername + @cr
+ 'Region: ' + Space(18) + @region + @cr
+ 'Site: ' + Space(20)+ @site + @cr
+ 'Department: ' + Space(14)+ @department + @cr
+ 'Phone: ' + Space(19)+ @phone + @cr
+ 'Submitted By: ' + Space(12)+ @submittedby + @cr
+ 'Category: ' + Space(16)+ @category + @cr
+ 'Type: ' + Space(20)+ @type + @cr
+ 'Item: ' + Space(20)+ @item + @cr

-- output the result ( this gets truncated)
SELECT @detailstring

-- print the results (this does not get truncated)
Print @detailstring

 

[dbmsguy]

from your description, the truncation is happening in the display ( I assume this becuase you say the print works ok). if you are running this in EM, i would suggestion you check the options, one of the options is max characters per column (in the advanced tab). check the value of this. Paul

http://www.dbmsguy.com

 

[aalmeida]

I just run a study case and I was successfull on both the Select statment and the Print Statment, I found some mistakes on your code and here is the revised code(if you want I can send also my study case):

CREATE PROCEDURE SMI_ChangeSelect @id varchar(20)
AS
declare @detailstring varchar(2000)
declare @createdate integer
declare @createdate2 datetime
declare @requestername varchar(150)
declare @region varchar(150)
declare @site varchar(150)
declare @department varchar(150)
declare @phone varchar(150)
declare @submittedby varchar(150)
declare @category varchar(150)
declare @type varchar(150)
declare @item varchar(150)
declare @cr varchar(10)

SELECT @cr = Char(10)

-- gather together the details of the ticket that are to be displayed
SELECT @createdate = create_date,
@requestername = CASE When requester_name_ is null THEN 'Unknown Requester'
ELSE requester_name_
END,
@region = CASE WHEN region is null THEN 'Unknown Region'
ELSE region
END,
@site = CASE WHEN site is null THEN 'Unknown Site'
ELSE site
END,
@department = CASE WHEN department is null THEN 'Unknown Department'
ELSE department
END,
@phone = CASE WHEN phone_number is null THEN 'Unknown Phone Number'
ELSE phone_number
END,
@submittedby = CASE WHEN submitted_by is null THEN 'Unknown Submitter'
ELSE submitted_by
END,
@category = CASE WHEN category is null THEN 'Unknown Category'
ELSE category
END,
@type = CASE WHEN type is null THEN 'Unknown Type'
ELSE type
END,
@item = CASE WHEN item is null THEN 'Unknown Item'
ELSE item
END
FROM CHG_CHANGE
WHERE Change_ID_ = @id

-- convert the date from int to datetime for display
SELECT @createdate2 = DateAdd(ss, @createdate,'1970/01/01 00:00:00' )

-- build up the string and pad as required to get even formatting.
SELECT @detailstring = 'DETAILS FOR CHANGE ' + CONVERT(Varchar(20), @id) + @cr
+ 'Create Date: ' + Space(13) + CONVERT(Varchar(20),@createdate2) + @cr
+ 'Requester Name: ' + Space(10) + @requestername + @cr
+ 'Region: ' + Space(18) + @region + @cr
+ 'Site: ' + Space(20) + @site + @cr
+ 'Department: ' + Space(14) + @department + @cr
+ 'Phone: ' + Space(19) + @phone + @cr
+ 'Submitted By: ' + Space(12) + @submittedby + @cr
+ 'Category: ' + Space(16) + @category + @cr
+ 'Type: ' + Space(20) + @type + @cr
+ 'Item: ' + Space(20) + @item + @cr


-- output the result ( this gets truncated)

SELECT @detailstring
-- print the results (this does not get truncated)

Print @detailstring
GO AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[jonbrown]

Problem Solved - It was purely a setting on Query Analyser only allowing 256 Chars per column.

Thanks for your help and sugestions.

Jon