How do i stop nvarchar(4000) from being used when passing parameters to the database

[E DiPietro]

Hello, 

I have the following mapping...

public SalesMap ( )

{

this.Table ( "sales" );

this.LazyLoad ( );

this.Id ( x => x.SalesNo ).GeneratedBy.Identity ( ).Column ( "sales_no" );

this.References ( x => x.MasterSalesNo ).Column ( "master_sales_no" );

this.Map ( x => x.Record ).Column ( "record" ).Not.Nullable ( ).Not.Insert ( ).Not.Update ( ).Generated.Always ( );

this.Map ( x => x.Timestamp ).Column ( "timestamp" ).Not.Nullable ( ).Not.Insert ( ).Not.Update().Generated.Always()  ;

this.Map ( x => x.CreatedBy ).Column ( "created_by" ).Not.Nullable ( ).Length ( 8 ).CustomSqlType ( "varchar(8)" );

this.Map ( x => x.CreateTime ).Column ( "create_time" ).Not.Nullable ( );

this.Map ( x => x.UpdatedBy ).Column ( "updated_by" ).Not.Nullable ( ).Length ( 8 );

this.Map ( x => x.UpdateTime ).Column ( "update_time" ).Not.Nullable ( );

this.Map ( x => x.EntryDate ).Column ( "entry_date" ).Not.Nullable ( );

this.Map ( x => x.OrderSts ).Column ( "order_sts" ).Not.Nullable ( ).Length ( 2 );

this.Map ( x => x.PrevOrderSts ).Column ( "prev_order_sts" ).Not.Nullable ( ).Length ( 2 );

this.Map ( x => x.FixedRateFlag ).Column ( "fixed_rate_flag" ).Not.Nullable ( );

this.Map ( x => x.RequestTime ).Column ( "request_time" );

this.Map ( x => x.BillOnShipFlag ).Column ( "bill_on_ship_flag" ).Not.Nullable ( );

this.Map ( x => x.CustomerPoNo ).Column ( "customer_po_no" ).Not.Nullable ( ).Length ( 30 );

this.Map ( x => x.Document ).Column ( "document" ).Not.Nullable ( ).Length ( 20 );

this.Map ( x => x.UserDefined1 ).Column ( "user_def1" ).Not.Nullable ( ).Length ( 40 );

this.Map ( x => x.UserDefined2 ).Column ( "user_def2" ).Not.Nullable ( ).Length ( 40 );

this.Map ( x => x.UserDefined3 ).Column ( "user_def3" ).Not.Nullable ( ).Length ( 40 );

this.HasMany ( x => x.SalesSet ).KeyColumn ( "master_sales_no" );

}

when it saves it to the database, i get the following error...

Msg 8152, Level 16, State 2, Line 1

String or binary data would be truncated.

The statement has been terminated.

This is the query that is generated by NHibernate...

exec sp_executesql N'INSERT INTO sales (created_by, create_time, updated_by, update_time, entry_date, order_sts, prev_order_sts, fixed_rate_flag, request_time, bill_on_ship_flag, customer_po_no, document, user_def1, user_def2, user_def3, master_sales_no) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15); select SCOPE_IDENTITY()',N'@p0 nvarchar(4000),@p1 datetime,@p2 nvarchar(4000),@p3 datetime,@p4 datetime,@p5 nvarchar(4000),@p6 nvarchar(4000),@p7 bit,@p8 datetime,@p9 bit,@p10 nvarchar(4000),@p11 nvarchar(4000),@p12 nvarchar(4000),@p13 nvarchar(4000),@p14 nvarchar(4000),@p15 int',@p0=N'nexterna',@p1='2012-06-28 12:53:31',@p2=N'nexterna',@p3='2012-06-28 12:53:31',@p4='2012-06-28 12:53:31',@p5=N'ordersts',@p6=N'prevordersts',@p7=1,@p8='2012-06-28 12:53:31',@p9=1,@p10=N'123',@p11=N'document',@p12=N'userdef1',@p13=N'userdef2',@p14=N'userdef3',@p15=5000002

How do i stop it from creating the nvarchar(4000) fields?

Thanks

[Carl Bussema]

Looking at that, I don't see what's triggering the TRUNCATED warning. If anything, having nvarchar(4000) where you only need nvarchar(20) will avoid that error. Can you show the CREATE TABLE generated by Export? Do you have any conventions in use that might be overriding your column lengths? Personally I use a StringLengthConvention and decorate the domain properties with the [StringLength] attribute to control it.

[StringLength(40000)]

public string SomeLongHtml { get; set; }

public class ColumnLengthConvention : AttributePropertyConvention<StringLengthAttribute>

    {

        protected override void Apply(StringLengthAttribute attribute, IPropertyInstance instance)

        {

            // override the default column length

            if (attribute.MaximumLength != default(int)) instance.Length(attribute.MaximumLength);

        }

    }

--
You received this message because you are subscribed to the Google Groups "Fluent NHibernate" group.
To view this discussion on the web visit https://groups.google.com/d/msg/fluent-nhibernate/-/ZWBzH-J31xUJ.
To post to this group, send email to fluent-n...@googlegroups.com.
To unsubscribe from this group, send email to fluent-nhibern...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/fluent-nhibernate?hl=en.

[Oskar Berggren]

2012/6/28 E DiPietro <edip...@gmail.com>:

> this.Map ( x => x.PrevOrderSts ).Column ( "prev_order_sts" ).Not.Nullable (
> ).Length ( 2 );

In the query p6 goes to this column, but contains 12 characters. This
is your problem (maybe there are more column like this, I stopped at
the first one).

>
> when it saves it to the database, i get the following error...
> Msg 8152, Level 16, State 2, Line 1
> String or binary data would be truncated.

This happens when the actual value (not the type) of a (n)varchar
parameter is longer than what the column would accept. I.e. having
nvarchar(4000) is not a problem.

>
> How do i stop it from creating the nvarchar(4000) fields?

The parameter size is longer than the specified length to e.g. allow
for like patterns, which might in some cases be longer than the
defined column size.


/Oskar