inserted value on text field gets truncated after 255 chars

Hello,
I have a SP on SQL Server 2005 (Express Ed.) which performs an INSERT
statement over a table. In the table I have two 'text' fields with the
same properties, with just one difference: one field allows nulls, the
other one does not.
Well, one field actually accepts only the first 255 chars (the nullable
field), while the other field has no problems.
The "Length" property is set to 16 for both fields, as I said all the
properties but one (null/not null) are exactly the same, and also the
context is the same (same database, same table).
Many thanks for your help!
GiovanniHow does your SP look?
It sounds like you truncate it somewhere there. Maybe the parameter is
a varchar or something like that?|||How are you validating that only 255 characters are there? Are you using
SELECT DATALENGTH(col_name) FROM table? Or are you counting the number of
characters in the result set?
"gm1974" <gmascia@.gmail.com> wrote in message
news:1138737544.776002.219580@.f14g2000cwb.googlegroups.com...
> Hello,
> I have a SP on SQL Server 2005 (Express Ed.) which performs an INSERT
> statement over a table. In the table I have two 'text' fields with the
> same properties, with just one difference: one field allows nulls, the
> other one does not.
> Well, one field actually accepts only the first 255 chars (the nullable
> field), while the other field has no problems.
> The "Length" property is set to 16 for both fields, as I said all the
> properties but one (null/not null) are exactly the same, and also the
> context is the same (same database, same table).
> Many thanks for your help!
> Giovanni
>|||gm1974 wrote:
> Hello,
> I have a SP on SQL Server 2005 (Express Ed.) which performs an INSERT
> statement over a table. In the table I have two 'text' fields with the
> same properties, with just one difference: one field allows nulls, the
> other one does not.
> Well, one field actually accepts only the first 255 chars (the
> nullable field), while the other field has no problems.
> The "Length" property is set to 16 for both fields, as I said all the
> properties but one (null/not null) are exactly the same, and also the
> context is the same (same database, same table).
>
Are you testing it in QA? If so, you should modify the "maximum characters
per column" setting in the QA options dialog.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Oh, I must be really tired. I definitely forgot to change parameter
type in the SP, it was still set at VarChar(255), so the value was
truncated!
Better to get some sleep, many thanks for your help.|||Thanks for your help, it may be useful in the future.
Giovanni

Insert trigger not working...

can someone shed some light on this? i want this to work on update and
insert, but it only works on update. When i run a simple insert
statement, i get this
<root>The specified statement did not generate any data</root>
Here is the trigger:
CREATE TRIGGER trg_UpdateQty ON [fastpic].[FP_INVTRANS]
FOR UPDATE, INSERT
--This trigger is used to export an xml file with system_qty, part_name
AS
DECLARE @.trans_date datetime,
@.part_name varchar(50),
@.Q varchar (255),
@.file varchar (255)
--IF NOT UPDATE(system_qty)
--RETURN
SELECT @.trans_date =trans_date FROM Inserted
SELECT @.part_name = part_name From Inserted
SELECT @.Q = 'select system_qty, part_name FROM [fastpic].[FP_INVTRANS2]
as InvTrans WHERE part_name ='''+@.part_name + ''' and trans_date ='''
+ Convert(varchar(20), @.trans_date, 120) + ''' for xml auto, elements'
Exec sp_makewebtask @.outputfile ='c:\updatePart.xml',
@.query = @.Q,
@.templatefile = 'c:\scripts\template1.tpl'On 8 May 2006 15:26:56 -0700, lytung@.gmail.com wrote:

>can someone shed some light on this? i want this to work on update and
>insert, but it only works on update. When i run a simple insert
>statement, i get this
> <root>The specified statement did not generate any data</root>
Hi lytung,
Not a definitive answer, but I have some comments below:

>Here is the trigger:
> CREATE TRIGGER trg_UpdateQty ON [fastpic].[FP_INVTRANS]
>FOR UPDATE, INSERT
(snip)

>SELECT @.trans_date =trans_date FROM Inserted
>SELECT @.part_name = part_name From Inserted
This can give unexpected results for multi-row updates or multi-row
inserts. Since the trigger is fired only once per statement execution,
the inserted pseudo-table will hold several rows. You might well get
@.trans_date from one row and @.part_name from another. Plus, you probably
want to execute the trigger's logic for all affected rows.

>SELECT @.Q = 'select system_qty, part_name FROM [fastpic].[FP_INVTRANS2]
>as InvTrans WHERE part_name ='''+@.part_name + ''' and trans_date ='''
>+ Convert(varchar(20), @.trans_date, 120) + ''' for xml auto, elements'
Use style 126 for the converstion of @.trans_date. Style 120 is not
guaranteed to be unambiguous in all locale settings.

>Exec sp_makewebtask @.outputfile ='c:\updatePart.xml',
>@.query = @.Q,
>@.templatefile = 'c:\scripts\template1.tpl'
Add a PRINT @.Q statement before, after or instead of this statement for
debugging purposes. Or insert @.Q into a table, if you are testing from a
front-end that doesn't expose the output of PRINT. Check if the SQL that
is generated matches your expecations.
Hugo Kornelis, SQL Server MVP