Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
jparker
Posting Yak Master
118 Posts |
Posted - 2005-10-27 : 07:38:19
|
I have the following scriptcreate table tblestimateusage (id_num int IDENTITY(1,1), est_id varchar(50), title varchar(200), flatsize varchar(50), finishedsize varchar(50), pages varchar(50), client_name varchar(150), process varchar(7000) )insert into tblestimateusage (est_id,title,flatsize,finishedsize,pages, client_name, process) select e.est_id, title, flatsize, finishedsize, pages, client_name, [dbo].[ufn_GetProcesses] ( [est_id] ) from tbltest ejoin tblcl c on c.client_id = e.client_idwhere est_datetime > '20050901' select * from tblestimateusage Which uses the following functionALTER FUNCTION [dbo].[ufn_GetProcesses] ( @ID INT ) RETURNS VARCHAR(6000) AS BEGIN DECLARE @ProcessValue VARCHAR(6000) SELECT @ProcessValue = ISNULL(@ProcessValue + ', ', '') + [process_name] + ' ' + [process_value] FROM [dbo].[tblmyprocess] WHERE [est_id] = @ID RETURN @ProcessValueENDThis works fine on some occassions but has a problem when either the line hits 8060 bytes then it bombs out or truncating the string via the function.How do I allow truncation? |
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-10-27 : 07:59:26
|
have your process filed srt to varchar 6000 and not 7000.Go with the flow & have fun! Else fight the flow |
|
|
jparker
Posting Yak Master
118 Posts |
Posted - 2005-10-27 : 08:10:15
|
sorry my example is a little misleading they should be the same.The problem arises when the function attempts to add byte 7001 to @ProcessValue. Rather than noticing it can't add any more and simply returning a truncated variable it decides to blow up with a warning. I want it to return that truncated string (which I know and understand to be down to the page file) and wanted to know how to make sql server accept this |
|
|
|
|
|
|
|