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.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Concatenating text column over multiple rows

Author  Topic 

roslen
Starting Member

5 Posts

Posted - 2008-02-06 : 03:03:25
Dear All,

I recently had a need to concatenate values of a text column over multiple rows. I did it by creating a function that is defined as follows:


CREATE function fn_ConcatSiteInspectionObservations
(
@site_inspection_schedule_id int,
@site_id int
)
returns varchar(8000)
as
begin

declare @txtObservations varchar(8000);

set @txtObservations = '';

select
@txtObservations = @txtObservations + --coalesce(convert(varchar(8000), todos), '')
case coalesce(convert(varchar(8000), observations), '')
when 'None.' then ''
else coalesce(convert(varchar(8000), observations), '')
end

from
site_inspection
where
site_inspection_schedule_id = @site_inspection_schedule_id
and site_id = @site_id

return @txtObservations;

end


This function can then be included in the field clause of a select statement such as:


select
site_inspection_schedule_id, site_id,
dbo.fn_ConcatSiteInspectionObservations(site_inspection_schedule_id, site_id) as observations
from
site_inspection
where
...


My problem is results of this function are always truncated to 256 characters. I have already defined this function to return varchar(8000). However, that specification seem to have no effect.

Please help.

Thanks.

Roslen

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-06 : 04:36:02
I think your function is working fine. Its the problem with default settings of your Sql Server Mangement Studio. Can you chcek this?
Goto MgmntSudio
Click Tools->Options
Expand QueryResults->SQLServer
and check the value provided for Results to Text and Results to Grid. It must be 256 (default value). Change it to 8000.


If you are using SQL 2000
take Query Anslyser
go to Tools->Options, Result tab and change value of max characters percolumn from 256 to convenient value
Go to Top of Page

roslen
Starting Member

5 Posts

Posted - 2008-02-06 : 22:06:08
Thanks for your insights. I'm using SQL Server 2000 and the changes you suggested for SQL Query Analyzer settings did work! My select queries when ran in SQLQA work perfectly.

Our DB admin already made your suggested changes to Sql Server Management Studio but it did not solve the problem. The data driven webpage that shows the results of the db query still shows truncated columns.

I'm not a DBA so I have very limited knowledge on MSSQL server settings. I tried to look at PHP settings that may lead to a solution, but nothing in the [MSSQL] section seems to be the key. I also tried restarting IIS hoping to refresh whatever cached connections there are between the application server and the db server but the results remain the same.

Go to Top of Page
   

- Advertisement -