varchar datatype not writing all data to table

Question

varchar datatype not writing all data to table

dblasio

Grasshopper

Points: 16
More actions
January 3, 2008 at 3:39 pm

#93141

I've been using SQL 2000 for a few year now, but never encountered this particular problem.
I'll try to make it short.........
I have a table that contains a field using varchar 4000 as its datatype. My .NET web application creates a string up to 3,600
characters (no more than this) and writes to this field (varchar 4000), but for some reason only about a third of the string gets
written to the database.
I am using a data adapter that is a straight select statement with the delete, update and insert commands set. I've checked
the properties of these update commands to verify that the parameters are using the correct datatype as the SQL field
(varchar 4000) and they all are.
In addition, while debugging my web application I am able to view the contents of the variable used to write the string to the
database. I've taken the contents of that variable and copied it into word. Then I did a word count.
The word count is around 3,600 characters including spaces.
My string does contain carriage returns, but I don't see this being a problem, however I am not the expert.
Is there some limitations to the varchar field that I am not aware of? I believe my total row size is less than 8k.
Any help on this matter would be greatly appreciated.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

You need also check total byte of your record (row). Remember the max bytes of a row is 8000. If the total bytes of your row exceed this value, your data may be truncated.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

I think if you were going over the row size, the entire update/insert would fail.

If you go to query analyser and run

SELECT LEN(VarcharColumn) from TheTable

what do you get?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Maple07-598122 SSC Enthusiast Points: 156 More actions · Answer 3

I have a similar problem. The column length for cIDs was set as nvarchar 700, and when I run query below, the value in the cIDs got truncated to

1,3,4,6,7,8,9,10,11,12,13,14,15,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,54,55,56,57,58,59,60,61,62,63,65,66,67,68,69,70,71,72,73,74,77,126,127,128,137,138,139,141,142,143,144,145,146,147,1

I used SELECT LEN(cIDs) from RPT_BatchReportList, the max value is 338.

I can not figure out what is wrong here. Any suggestions?

INSERT INTO RPT_BatchReportList (BegDate

, EndDate

, cIDs

)

Values ('10/1/2007 12:00:00 AM'

, '12/31/2007 12:00:00 AM'

,'1,3,4,6,7,8,9,10,11,12,13,14,15,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,54,55,56,57,58,59,60,61,62,63,65,66,67,68,69,70,71,72,73,74,77,126,127,128,137,138,139,141,142,143,144,145,146,147,148,149,151,156,157,160,162,163,164,167,168,169,170,171,172,173,193,199,201,233,236'

)

dblasio Grasshopper Points: 16 More actions · Answer 4

Thank you all for your great responses....I found my issue to be with my SqlDataAdapter and not with SQL 2000. If you change anything on the database side, those changes must be reflected in your SqlDataAdapter.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

Interesting problem and thanks for the feed back...

Now, what's an "SQLDataAdapter"??? :blink:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

It's a .net object that acts as a link between the connection and a data set. The adaptor gets told what the select, update, insert and delete

statements for the data set are. They can be adhoc statements or stored proc calls

Essentially, the DataAdapter is responsible for syncronising the data in the dataset (In memory tables on client PC) with the server.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChiragNS One Orange Chip Points: 26137 More actions · Answer 7

hi

any other columns in the db. did u check properly.

"Keep Trying"

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 8

Is the truncation in Query Analyser or your app? If the former, check the Query Analyser setting in Tools/Options/Results tab/Max characters per column.

If you are returning a dataset, I can't think of what the problem might be offhand -it's always worked for me with long strings (!). If returning parameters, obviously check the parameter size property.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

GilaMonster (1/8/2008)
It's a .net object that acts as a link between the connection and a data set. The adaptor gets told what the select, update, insert and delete
statements for the data set are. They can be adhoc statements or stored proc calls
Essentially, the DataAdapter is responsible for syncronising the data in the dataset (In memory tables on client PC) with the server.

Thanks, Gail.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

Stewart Joslyn (1/9/2008)
Is the truncation in Query Analyser or your app? If the former, check the Query Analyser setting in Tools/Options/Results tab/Max characters per column.
If you are returning a dataset, I can't think of what the problem might be offhand -it's always worked for me with long strings (!). If returning parameters, obviously check the parameter size property.

OP found the problem in the interface between the app and the db. Read a couple of posts above yours...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Maple07-598122 SSC Enthusiast Points: 156 More actions · Answer 11

Mine is in the query analyzer. It is the problem of the settings. thanks.:)

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 12

Thanks Jeff, it had scrolled off the side of my monitor!

High level tools (like the data adapter) are great for productivity but its easy to end up with hidden issues. Not the case here but, as a generality, I always feel that nobody should be allowed to use productivity tools until they understand how to do the job the hard way first !