Solved

Data (string) being truncated when written to an SQL table

Posted on 2003-10-29
16
424 Views
Last Modified: 2008-03-17
SQL 7 & Access 2000
I have a problem that I an trying to sort out at teh moment.

I have an SQL table defined with in SQL terms it has a datatype of Ntext length of 16, which in effect appears to mean a very large field up to 64K in size I think. In terms of a MS-Access Table thsi field would be a Memo datatype.

My interface take this field and displays it in a Text box in an Unbound form. The entire field data is successfully retrieved and displayed. If however the field data length is greater than 250 characters (lets say 400), and I want to update this field byt typing in the extra data, then when the data gets written back to teh SQL table, it gets truncated at 250 chars.

The update is done using an SQL statement

update <table> fieldname = "......", where key = xxxx

The update appears to work successfully, however when you next look at the data the information is truncated as 250 characters.

Am I missing anything obvious here?
0
Comment
Question by:bmtech
  • 4
  • 4
  • 4
  • +2
16 Comments
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9641380
Is this a text field in MS SQL SERVER?

If so,
on the sql session, try

SET TEXTSIZE 2048
update <table> fieldname = "......", where key = xxxx

If does not work, see example below of using writetext function

USE pubs
GO
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'
GO
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(pr_info)
FROM pub_info pr, publishers p
WHERE p.pub_id = pr.pub_id
   AND p.pub_name = 'New Moon Books'
WRITETEXT pub_info.pr_info @ptrval 'New Moon Books (NMB) has just released another top ten publication. With the latest publication this makes NMB the hottest new publisher of the year!'
GO
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'
GO

HTH
0
 
LVL 2

Author Comment

by:bmtech
ID: 9641478
This may be the wrong way to go about it, but this is how my code works:

stSQL = 'update <table> SET fieldname = '....' Where key = xxx"

cncurrent.Execute stsql

I am not sure where to place the TEXTSIZE statement in that context.
0
 
LVL 15

Expert Comment

by:SRigney
ID: 9641725
ntext is similar to the memo field in Access.  You don't need to use Ntext unless you need Unicode values.  If you are just using Ascii values then 'text' would suffice for the field size.  It also would only take up half as much space on the server.

There should be nothing that would cause a problem between 250 and 400 characters.  If the value were much larger(more than 8000 characters) I could possibly see a problem.  Is it possible that you have a value in your ... that is causing problems.  Maybe a single quote?  In order to insert single quotes there needs to be 2 consecutive single quotes.

For Example.

stField = "Hello I'm Steve"
stSQL = "update <table> SET fieldname = '" & stField &"' Where key = xxx"

This will not work because the single quote in "I'm" will cause a problem.

stField = "Hello I'm Steve"
stField = Replace(stField, "'", "''")
stSQL = "update <table> SET fieldname = '" & stField &"' Where key = xxx"

This will work because it replaces all ' with '' (that's 2 consecutive single quotes).

If this continues, you could make a stored procedure and use a command object to set the input parameter for the stored procedure.
0
 
LVL 2

Author Comment

by:bmtech
ID: 9641892
Okay thanks for the popint about ntext - I have just found this myself, however the Upsizing Wizard in MS-Access converts Memo fields to ntext by default. Changing the data type in SQL Enterprise Manager to be Text does not appear to make any difference.

I have already come across the issue of quotes thanks and my code already makes allowances for this using pretty much the technique you have outlined here. (In fact I make the user to the work themselves rather than have Replace make assumptions, but it comes out to teh same thing).

I have now converted the string to be a single piece ot text with no spaces or odd characters in it (as an experiment). It definitly seems to want to chop the length at 252 or 251 characters (havnt found out quite why it changes here).
0
 
LVL 1

Expert Comment

by:RadimHampel
ID: 9642118
What is exactly your code? Don't you have something like
declare @var varchar

update table set col = @var where .... ???


0
 
LVL 15

Expert Comment

by:SRigney
ID: 9642123
What version of Sql Server are you using?
0
 
LVL 15

Expert Comment

by:SRigney
ID: 9642127
nevermind I see it.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 15

Accepted Solution

by:
SRigney earned 84 total points
ID: 9642161
Without seeing any code directly the next suggestion I can make is to try using a sored procedure.
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9642454
Please check the datatype in your MS SQL Database? Is that varchar(250) or TEXT?
0
 
LVL 2

Author Comment

by:bmtech
ID: 9642672
Okay I have a better handle on this now.

The data type is indeed TEXT and nor varchar.

(BTW the upsize wizard in MS-Access assumes that all Text style fields are unicode eg Memo becomes nText and Text becomes nVarchar etc,.... not great but useful to know :-)

If I update an SQL field that is larger than 250 chracters by using the following technique:

Set cnCurrent2 = CurrentProject.Connection
Set rsUpdate2 = New adodb.Recordset
'
stSQL = "select * from [Table1] where [key]= " & Me.key
rsUpdate2.Open stSQL, cnCurrent2, adOpenKeyset, adLockPessimistic

cnCurrent2.Execute stSQLUpdatestring, intTemp

(Where stSQLUpdate string contains:

Update [Table1] SET bigfield = "more than 252 characters" Where key = " & me.key)

rsUpdate2.Close
cnCurrent2.Close

Okay now this is a bit verbose but it shows the basis of the code.

This then causes 'bigfield' to get truncated at 252 in length.

HOWEVER.....

If I do this:
Set cnCurrent2 = CurrentProject.Connection
Set rsUpdate2 = New adodb.Recordset
'
stSQL = "select * from [Table1] where [key]= " & Me.key
rsUpdate2.Open stSQL, cnCurrent2, adOpenKeyset, adLockPessimistic

rsupdate2!bigfield = me.bigfield              ' The field on the unbound form
rsupdate2.Update

rsUpdate2.Close
cnCurrent2.Close

then this works correctly and no truncation appear to be happening. I may have to rewrite the code.

This suggests to me that namasi is on the right track with his TEXTSIZE, but quite how I control that in this instance I dont know.

The default TEXTSIZE in SQL 7 is 2147483647 which is a big number.

I will carry on looking at this. In the mean time if anyone has anyother thoughts let me know please.

Thanks for your input so far.
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9642808
SELECT @@TEXTSIZE
will give what the value for TEXTSIZE is on you current session.

Before Excuting Try This.

This may or may not help,
cnCurrent2.Execute 'SET TEXTSIZE 64512'


HTH

Namasi Navaretnam

0
 
LVL 34

Assisted Solution

by:arbert
arbert earned 83 total points
ID: 9643956
Also, if you are using linked tables, make sure you relink them when you change the structure in sql server........
0
 
LVL 15

Assisted Solution

by:namasi_navaretnam
namasi_navaretnam earned 83 total points
ID: 9647184
If this works, it should be

couble quote around. single quote is considered comment is ASP.
cnCurrent2.Execute "SET TEXTSIZE 64512"
0
 
LVL 2

Author Comment

by:bmtech
ID: 9670728
OK thanks I will try this out on Wednesday.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
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
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now