"martin" wrote:

> 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

> 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!
> ***************************
>
>
> "martin" wrote:
>
> > 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

"martin" <mar***@discussions.microsoft.com> wrote in message
news:50C9488D-4DB3-4C8B-81CC-59B459FDA824@microsoft.com...
>
> "Cowboy (Gregory A. Beamer) - MVP" wrote:
>> 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)
>
> 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
>
>> 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!
>> ***************************
>>
>>
>> "martin" wrote:
>>
>> > 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