Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Data getting truncated while saving data in table of text datatype.

Posted on 2002-04-11
6
Medium Priority
?
699 Views
Last Modified: 2011-10-03
Hi All

I am having table with column description of text datatype.When i save the character data using insert statement of length 20000 it gets truncated to only 5000 charaters in the table.

In the table definition it shows datatype text and length 16
awaiting your replies.

shamshularphin.



0
Comment
Question by:arphin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 2

Accepted Solution

by:
skhorshid earned 100 total points
ID: 6933466
I would not use text or ntext unless it was completely nessicary.

Howver if you need 20000 chars in one field then you will need to use a stored procedure to insert the text.

create procedure inserttext(@input text) as
insert into mytable(textcol) values(@input)

If you are using ADO and VB you would execute your procedure like this

Then you will need to use a command object to pass the text value byref.

'this inserts 9000 long character string

Dim cn As New ADODB.Connection
Dim p As New ADODB.Parameter
Dim cmd As New ADODB.Command
Dim str As String * 9000
cn.Open "DSN=yourdsn or connect string"
cmd.ActiveConnection = cn
str = "1fvbrsghb"
Set p = cmd.CreateParameter("tt", adLongVarChar, adParamInput, 9000, str)
cmd.CommandText = "iinsert"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append p
cmd.Execute






0
 
LVL 2

Expert Comment

by:skhorshid
ID: 6933471

These two lines should read..

Set p = cmd.CreateParameter("input", adLongVarChar, adParamInput, 9000, str)
cmd.CommandText = "inserttext"
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points
ID: 6933489
look at this q:

http://www.experts-exchange.com/mssql/Q.20286017.html

or read this "solution":
CREATE PROCEDURE UpdateJobResult
@JobID Int,
@StatusID TinyInt,
@JobResult Text
As

DECLARE @ptrval binary(16)
DECLARE @offset int

UPDATE Jobs
Set LastUpdatedTime = GetDate(),
 StatusID= @StatusID,
 ResultSize = DataLength(@JobResult) + ResultSize
Where JobID= @JobID

update Jobs
set JobResult = '' where
JobID = @JobID and JobResult is null

SELECT @ptrval = TEXTPTR(JobResult),
     @offset = DATALENGTH(JobResult)
FROM Jobs
WHERE JobID= @JobID


UPDATETEXT [Jobs].[JobResult] @ptrval @offset NULL @JobResult
GO


CHeers
0
 
LVL 8

Expert Comment

by:chigrik
ID: 6938554
You can insert a text value of length 20000 by using insert statement.
Maybe you should use SET TEXTSIZE statement to specify the size of
text and ntext data returned with a SELECT statement.
The default setting is 4Kb.
To see the real size of the particular column you can use DATALENGTH function.


With best regards,
Alexander Chigrik
http://www.MSSQLCity.com/ - all about MS SQL
(SQL Server Articles, FAQ, Scripts, Tips and Test Exams).
0
 

Expert Comment

by:CleanupPing
ID: 9280636
arphin:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question