varchar(2048) truncates to 392? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

varchar(2048) truncates to 392?

Author

Topic

rokosz
Starting Member

4 Posts

Posted - 2006-06-14 : 00:14:50

I don't know why this happens to me.

I have a table with a column varchar 7850. It holds text for help subjects.

I have an asp that allows developer maintenance of the table. the 7850 column is gleaned from a <form>'s <textarea>. No problem with mulit-K length content inserts and updates on the 7850 column here.

I have another asp. Some strange things have been happening in a table maintained by the asp that necessitated (after exhausting other "features" of SQL-T, triggers, SPs and the like) brute force debug attempts.

Created a table into which the suspect "update" statement will be inserted each time this asp does any sql updates. The receiving table column is varchar 2048. Problem is only 392 chars of the update statement make it in when the true length is easily near 500. Well, apparently not, sometimes its truncated at 389, sometimes at 382 as I mess around with the contents of the update statement -- but never the complete string. Ohhh, so maybe its a problem with quotes or some nitpick thing I missed.

Nope. via QueryAna I attempted an update of the ilk:
"update theDebugTable set theSQL='000000....000'" where the zeros
count well beyond 4 or 500. What's the length of zeros updated?
352? Wha, Wha What?! Oh. I see. SQL has now decided that there's only a certain number of inches available and well, sorry, but zeros are wider than spaces in a proportional font (cynical grin here).

So what is it? can any newbies help? because at this point I feel like I've got to learn it all over again, and I might as well start at the beginning... Why does one asp update a varchar(2048) without truncation and another doesn't. What besides length allows a varchar column to actually hold the length in characters?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-14 : 01:03:23

I would make sure that ADO Command object is in use for ASP page and then investigate how .CreateParameter is set up. There is an parameter to set max length of field.

Peter Larsson
Helsingborg, Sweden

rokosz
Starting Member

4 Posts

Posted - 2006-06-14 : 01:14:20

The asp were only the beginning of the problem.

Perhaps I wasn't clear. And I apologize for that.

As I tried to figure out what was going on I ended up
in Query Analyzer creating update and insert statements
that refused to "put" the entire 500+ character string into a
table column defined as varchar(2048). Instead it would put a truncated version of anywhere from 352 to 392 in.

Almost like there's something about SQL-Server that isn't set properly.

But! and this is where the asp comes in, an asp I run has no problems passing a _really_ long string to sql to chew on.

And in a similar vein, a DTS, used to initially populate the table with those really long strings has no trouble either...

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-06-14 : 02:45:37

Could it have something to do with page overflow? I haven´t really ever tried what error you might get (or lack here of) if you try to store more than 8000 (and a tad) bytes in a row. What is the the combined theoretical row size of your table?

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-14 : 03:13:42

Can you post your update-query you use in QA, here?
Also, post your table definition of the destination table for update-query.

Peter Larsson
Helsingborg, Sweden

rokosz
Starting Member

4 Posts

Posted - 2006-06-14 : 09:17:55

Ahhh. good point. but no, the overall record length is about 2300.

And if you'll please excuse me I'll remove my head from an unfortunate part of my body.

Apparently this isn't an issue of data truncation. But rather some
strange Enterprise Manager clipboard truncation.

1) here's the table definition (nothing fancy just a plain table):
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DenialTemp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DenialTemp]
GO

CREATE TABLE [dbo].[DenialTemp] (
[theUser] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[thedate] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[thetime] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[theASP] [varchar] (55) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[transactionid] [char] (55) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CARRIERCODE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DENIALEXIST1] [bit] NULL ,
[theSQL] [varchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[THEDATETIME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

2) Now in SQL-Query Analyzer:
insert into DenialTemp (theuser,theSQL) values ('pr2','0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000')

(That's 487 zeros).

3) In enterprise manager: select * from denialtemp
scroll over to theSQL column and select the contents by placing the cursor at the beginning and Shift-End, and then copy.

4) paste that into a new QA window inside a print len('0000...')
and run it. I consistently get a result of 352.

5) but, (and this is my new discovery - what a wonderful Microsoft world it is btw) if I query: select theuser,thesql from denialtemp where len(rtrim(thesql))=352 I get nothing returned.
change it to =487 and voila! there they are.

6) so the question has become why does EM not allow selection/copy of the entire column contents via Shift-End. Because, I just tried this, If I shift-end and then continue holding the shift key and use the right arrow key to move to the end then copy i get 487.
And EM, upon entry to the column does highlight the entire contents yielding 487.

So its just the long-winded way of placing the cursor at the beginning of the column, shift-end (which normally means -- select from cursor posn to end of contents) that causes the problem. But why 352 for a string of 487 zeros. and 382 or 389 or 392 for a more realworld example of a long "update payors set x=..., y='medicaid',z=adfsad etc." where the that string is also in the 480-500 character length?

I will commit ritual suicide on the internet, live, if someone says rtfm -- look at page nnn...

thank you all.

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-06-14 : 09:26:34

quote:
Oh. I see. SQL has now decided that there's only a certain number of inches available and well, sorry, but zeros are wider than spaces in a proportional font (cynical grin here).

Hmm that may not be so far of than you thought, try 500 spaces and a none-space and see if that works.

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-14 : 09:36:06

try datalength instead of len.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources