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)
 how many charaters can save in text datatype?

Author  Topic 

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-04-14 : 03:14:16
i have datacolumn(datatype is text 16),it can't store whole long sentense .
for example :i want to save "Have a nice day!",it can save and show "Have a nice"
what's wrong?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 03:50:17
Books Online says 2147483647 characters. More than 2 billion that is.
Text are stored on a different place in the server and the 16 bytes you see is just a pointer to the place where the data is stored.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-04-14 : 03:56:52
so how can i retrieve all text from database ?
it also can't save all text of user input.
any suggession?

quote:
Originally posted by Peso

Books Online says 2147483647 characters. More than 2 billion that is.
Text are stored on a different place in the server and the 16 bytes you see is just a pointer to the place where the data is stored.



E 12°55'05.25"
N 56°04'39.16"


Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 03:59:44
I think your stored procedure has another dataype and that parameter length is set to 11 characters.
Or this "bug" is located in the front-end application.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 04:00:45
Or you are using a user-defined data type? Which in turn is a varchar/char datatype?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-04-14 : 04:07:00
i can't set lenth for text in storedprocedure.
i've shown just sample sentense.
Actually datatype is text(16) .
when i try to save long sentense more then 16chars ,it can only save 16chars or lessthan 16.
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-04-14 : 04:08:45
no i use built-in datatype text.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 04:11:06
Show us the stored procedure code.
Show us the database table definition.
Show us the front-end application code.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-04-14 : 04:18:14
This is stored procedure
CREATE PROCEDURE [dbo].[sp_AddAnsTemp]
@id int, @proid int, @prodesc text, @proansw text,@proreso text,@chk int
AS
insert into TempAns(UserID,ProblemID,ProblemDescription,ProblemAnswer,ProblemResolution,Chk)
values (@id, @proid, @prodesc, @proansw,@proreso,@chk)
GO

this is coding
Dim paramans As New SqlParameter("@proansw", SqlDbType.Text, 16)
Dim txtbox As New TextBox
txtbox = CType(item.FindControl("txtAns"), TextBox)
paramans.Value = txtbox.Text.Trim
cmd.Parameters.Add(paramans)


This is table

TempAns
(---,ProblemAnswer(text 16),--)

can u help me!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 04:20:43
This is the offending line!
Dim paramans As New SqlParameter("@proansw", SqlDbType.Text, 16)
You have told ADO to use a maximum of 16 characters.

Use
Dim paramans As New SqlParameter("@proansw", SqlDbType.Text ) instead


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-04-14 : 04:28:17
thanks alot

now works well for short sentense like:
"Blinking ADSL LED on router"

But still in problem for such long sentense:

"Problem was found when po/smtp.ti-sin.com was pinged and it was found that it was pointing to QALA IP. After a procedure from TL Sham, their mail client was configured to use the Proteus IP address for both incoming and outgoing server names. User now able to receive external emails on Outlook Express and Webmail."
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 04:36:23
Dim paramans As New SqlParameter("@proansw", SqlDbType.Text, 2147483647 )



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-04-14 : 04:51:40
some long sentense are saved but some aren't saved even shorter than long one which is saved.
what's wrong ?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 05:06:24
You tell me.

Run this code and post back the result
SELECT	COLUMN_NAME,
ORDINAL_POSITION,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TempAns'



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-04-14 : 05:17:41
in other blank page?
just run as u write?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 05:23:54
Replace 'TempAns' with the table you really are using.
I put 'TempAns' as search table because that is the name you posted earlier.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-04-14 : 05:30:28
in my table defnition ,datatype is text(16)
error is because of such length?
but i can't change
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 05:36:13
Post the result, not your interpretation !



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 05:39:55
Also, which long sentences are stored, and which are not stored?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-04-14 : 05:39:59
what's ordinal_position and information_sechema.columns
i don't understan well what u mean?

quote:
Originally posted by Peso

You tell me.

Run this code and post back the result
SELECT	COLUMN_NAME,
ORDINAL_POSITION,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TempAns'



E 12°55'05.25"
N 56°04'39.16"


Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 05:42:51
Run the query on your server.
Then post here the result of the query.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
    Next Page

- Advertisement -