Problems with text column in SQL SERVER 2000

Hi,
I am trying to access the data inside a text column in the database and display it on a page.
The problem is, I cannot see the full data when I execute the stored procedure in SQL Server and also on the asp.net page.
it seems Sql server clips the data if it is more than 3000 characters. Is there anyway I can see the whole data that is stored in that particular column.
I do not understand why asp.net is also clipping the data. What container can I use to display the text? I have tried Literal, string, placeholder but nothing shows the full text that is supposed to be in the database.
I have seen some people using byte arrays but I am not sure how I can use them to display data on a page.
If anyone has done this before or had a similar problem please reply back.
Thanks.

Are you sure it is with the page and not the size of the parameters or the size of the field. my guess is that one of those are varchar(3000)

|||

To add to the previous post Char is fixed length, while Varchar is variable length so, I would change to Char if it is Varchar 3000. Byte arrays is for Binary data in SQL Server. Hope this helps.

|||The data type of the column is text and its length shows as 16. As far as I know, a text column can take any length of text. But I am not sure why it's not inserting the whole text and clipping it after certain characters.
I think now it's something on the database side, not on the asp.net page.
Any ideas why Sql Server is not inserting the whole data?
I tried retrieving the results to a text file and tried to see if it displays the whole text. But it shows only a small chunk of the data.
I am not sure what's wrong.|||What type of structure are retrieving the data into within your asp.net app? DataSet? Custom object? If you are using dataadapters to load your data into a dataset I would take a look at the length of the parameter representing this text field within your dataadapter commands.
Oscar|||Text datatype can grow to 2gig in size and is usually not table space based which means it is not for data stored in a column. SQL Server creates an Arithmetic pointer to the data in your file system. If you know your data from Asp.net is ASCII you can use Varchar 4000 and Char 4000, if it is Unicode you can use Nvarchar 2000 and Nchar 2000. The other option is use image which can also grow to 2gig and store the data in Word. Hope this helps.|||

I am having the same problem. It is not caused by varchar(3000) or anything else. I have change the field from text to varchar(7900) and still have the problem.
I am loading the data into a dataset variable. When you look at the dataset's tables(0).rows(0).item("field_name"), it has length 3000.
Any one else have any thoughts?

|||

orderdog,
if your loading data into a dataset where the length of the fiedl is defined as 3000, then that is all the data your going to get in or out of your dataset once loaded. Typically, if you create a dataadapter using a table from your database your text columns will be automatically converted to varchar with a size of 2million and some odd characters.
oscar

Problems with a SP with a Temporary Table in .NET

Hy people, i hava a problem with add a SP with a tempory table inside in a
SQLDataAdapter in VS .NET 2003. Is this the point of sintax that occur the
error:
(...)
if object_id('tempdb.dbo.#tempprod') is not null drop table #tempprod
create table #tempprod (seq int, sit varchar(6),Atividade varchar(20),
historico varchar(6))
EXEC SP_FI_ListToTable '#tempprod','atividade','^',@.Atividade
EXEC SP_FI_ListToTable '#tempprod','sit','^', @.SituacaoProposta
EXEC SP_FI_ListToTable '#tempprod','historico','^', @.HistoricoFinanceiro
(...)
The error is "Invalid object name #tempprod" and this occurs 3 times.
Is this the full code of the SP SP_FI_ListToTable:
"ALTER PROC SP_FI_ListToTable
@.table varchar(12),
@.campo varchar(12),
@.sep char,
@.texto varchar(5000)
as
set nocount on
declare @.sub varchar(100)
declare @.posini int
declare @.seq int
select @.seq = 1
while len(@.texto) > 0
begin
select @.posini = charindex(@.sep,@.texto)
if @.posini > 0
begin
select @.sub = LEFT(@.texto,@.posini-1)
select @.texto = substring(@.texto,@.posini+1,len(@.texto))
end
else
begin
select @.sub = @.texto
select @.texto = ''
end
exec (' if exists(select * from ' + @.table + ' where seq = ' + @.seq + ')
'+
' update '+@.table + ' set ' + @.campo + ' = '''+@.sub+''' where seq =
' + @.seq +
' else ' +
' insert into '+@.table + ' (seq,'+@.campo+') values (' + @.seq +
','''+@.sub+''')')
select @.seq = @.seq + 1
End
set nocount off
return "
Tks for help.
Bruno Renato
C# .NET DeveloperHi
It is better if you do not call your stored procedure sp_...
I have not tried this through the adapter, but...
Your temporary table will be dropped when it goes out of scope, so testing
the existance should not be necessary.
You will not need to use dynamic SQL if you only access the same temporary
table from a procedure that creates the temporary table. You may want to put
begin/end around your batch.
This works fine:
DECLARE @.Atividade varchar(5000)
DECLARE @.SituacaoProposta varchar(5000)
DECLARE @.HistoricoFinanceiro varchar(5000)
SELECT @.Atividade = 'Un',
@.SituacaoProposta = 'dos',
@.HistoricoFinanceiro= 'tres'
CREATE TABLE #tempprod (seq int, sit varchar(6),Atividade varchar(20),
historico varchar(6))
EXEC uSP_FI_ListToTable '#tempprod','atividade','^',@.Atividade
EXEC uSP_FI_ListToTable '#tempprod','sit','^', @.SituacaoProposta
EXEC uSP_FI_ListToTable '#tempprod','historico','^', @.HistoricoFinanceiro
SELECT * FROM #tempprod
DROP TABLE #tempprod
You may want to look at:
http://www.sommarskog.se/arrays-in-sql.html
John
"Bruno Renato" wrote:

> Hy people, i hava a problem with add a SP with a tempory table inside in a
> SQLDataAdapter in VS .NET 2003. Is this the point of sintax that occur the
> error:
> (...)
> if object_id('tempdb.dbo.#tempprod') is not null drop table #tempprod
> create table #tempprod (seq int, sit varchar(6),Atividade varchar(20),
> historico varchar(6))
> EXEC SP_FI_ListToTable '#tempprod','atividade','^',@.Atividade
> EXEC SP_FI_ListToTable '#tempprod','sit','^', @.SituacaoProposta
> EXEC SP_FI_ListToTable '#tempprod','historico','^', @.HistoricoFinancei
ro
> (...)
> The error is "Invalid object name #tempprod" and this occurs 3 times.
> Is this the full code of the SP SP_FI_ListToTable:
> "ALTER PROC SP_FI_ListToTable
> @.table varchar(12),
> @.campo varchar(12),
> @.sep char,
> @.texto varchar(5000)
> as
> set nocount on
> declare @.sub varchar(100)
> declare @.posini int
> declare @.seq int
> select @.seq = 1
> while len(@.texto) > 0
> begin
> select @.posini = charindex(@.sep,@.texto)
> if @.posini > 0
> begin
> select @.sub = LEFT(@.texto,@.posini-1)
> select @.texto = substring(@.texto,@.posini+1,len(@.texto))
> end
> else
> begin
> select @.sub = @.texto
> select @.texto = ''
> end
> exec (' if exists(select * from ' + @.table + ' where seq = ' + @.seq + '
)
> '+
> ' update '+@.table + ' set ' + @.campo + ' = '''+@.sub+''' where seq
=
> ' + @.seq +
> ' else ' +
> ' insert into '+@.table + ' (seq,'+@.campo+') values (' + @.seq +
> ','''+@.sub+''')')
> select @.seq = @.seq + 1
> End
> set nocount off
> return "
> Tks for help.
>
> Bruno Renato
> C# .NET Developer