INSERTing long TEXTs into MSSQL 8.00.2039

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a table with columns of types datetime, varchar, text, text, text,
text.
The text columns will contain XML documents, there is no primary key in the
table but they varchar column contains foreign keys.

If I insert trivial text values like "hello" everything works fine but when
I use real XML documents weird things start to happen. The row gets inserted
with no error message but when I view it in enterprise manager the particular
column that was supposed to contain the XML document is empty (the datetime
and all text columns with trivial values like "hello" or "world" is correctly
displayed).

After some experimentation I noticed that it appears to be the length of the
text field that determines whether that column will get a value or not. For
text values of length 900 chars and shorter it's no problem, but for text
values with a length of 901 chars that column will not be set but instead
empty. The actual content of the long text value does not matter, I tried to
insert a 900 char string of the letter "c" only and that worked, while 901
consecutive "c" chars causes the row to be inserted with a empty value where
the string of "c"s should have been.

My question is, how can I insert TEXT values longer than 900 chars? I know
this datatype should handle alot longer values...

FWIW; I'm using MSSQL 8.00.2039 and I'm doing the INSERTs using standard
..NET component (ver 1.1) such as SqlConnection and SqlCommand.


regards,
martin
 
How are you inserting. Manually creating parameters with the proper SQL Type?
If so, you should not have a problem. Potential problem areas:

Using CommandBuilder to discern fields and set up parameters.
Using generic parameter objects (not specifically typed)
Using varchar

I am sure there are other gotchas I am missing. You might want to post a bit
of your data insert code to get better help as I can only poke around in the
dark right now.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
Cowboy (Gregory A. Beamer) - MVP said:
How are you inserting. Manually creating parameters with the proper SQL Type?
If so, you should not have a problem. Potential problem areas:

Using CommandBuilder to discern fields and set up parameters.
Using generic parameter objects (not specifically typed)
Click to expand...

I was using generic parameters, like:
command.Parameters.Add("fieldName", myTextValue);

I found the problem last night though; apparently Enterprise Manager has an
undocumented maxlimit on the field length it displays. If a TEXT value has
more than 900 chars it will not be displayed at all (no "this field contains
a long TEXT value" or anything like that just a blank value). Very confusing
imho.. :(


regards,
martin
 
You've just encountered one of the very good reasons to NOT use EM for other
than administrative tasks.
 
Back
Top