Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 703
  • Last Modified:

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

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
arphin
Asked:
arphin
2 Solutions
 
skhorshidCommented:
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
 
skhorshidCommented:

These two lines should read..

Set p = cmd.CreateParameter("input", adLongVarChar, adParamInput, 9000, str)
cmd.CommandText = "inserttext"
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
chigrikCommented:
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
 
CleanupPingCommented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now