Custom Fields on Sales Order

[SBarnes]

Hi Mike

Is there any reason why the contents field is SO_CustomSettingValues is only 255 varchar and can it be made any bigger, my reason is we may need to store another file format (EDIFACT) against an order to pass to anther system, these orders would come in through the XML import?

[Mike.Sheen]

Is there any reason why the contents field is SO_CustomSettingValues is only 255 varchar and can it be made any bigger, my reason is we may need to store another file format (EDIFACT) against an order to pass to anther system, these orders would come in through the XML import?

The reason for the limit was consistency with other varchar fields in the system storing long descriptions, et cetera.

SQL Server has row size limits - 8060 I think is what SQL 2014 has a limit of - but you can (as of SQL 2005 I think) use VARCHAR(MAX) to utilise row overflow storage automatically. If you want to store documents, in a custom field you should probably instead insert an entry into SY_File - that has an image column which can store up to 2GB files, and then set the SO_CustomSettingValues value to be the SY_File.FileID.

You could *try* altering the SO_CustomSettingValues.Contents column to be VARCHAR(MAX) and see if the software doesn't truncate on insert or update - I can't check right now, unfortunately (a hardware fault took some of our machines offline, including my Version 6 sourcecode VM - I'm still working on getting that back online). I'll check it when I can, but it would be easy to test it out yourself -

Code: Select all

ALTER TABLE SO_CustomSettingValues ALTER COLUMN Contents VARCHAR(MAX) NULL

And to put it back:

Code: Select all

ALTER TABLE SO_CustomSettingValues ALTER COLUMN Contents VARCHAR(255) NULL

Of course - as always - don't do this testing on a production database!

Mike

[SBarnes]

Hi Mike,

Short answer is don't try it in any database essentially Jiwa gives an error about truncating binary to text and SQL server's not happy about the index on the field for anything over 900 in length.

Thanks for the help.

[Mike.Sheen]

Short answer is don't try it in any database essentially Jiwa gives an error about truncating binary to text and SQL server's not happy about the index on the field for anything over 900 in length.

Ahh - thanks for testing that out yourself - at least we know the answer now - my apologies for not being able to give you the answer at the time.

Do need some guidance on how to use the SY_File technique to achieve what you want?

Mike

[SBarnes]

Hi Mike,

that would be great.

Thanks

[Mike.Sheen]

Hi Mike,

that would be great.

Thanks

Hi Stuart - I'm afraid you will have to afford me a day or two to provide that - I'm pretty snowed under at the moment - but as soon as I can I'll provide you with a sample - do you want it in .NET or in VB6, or in vbscript?

Mike

[Ernst]

The Contents(MAX) seems to work well in IN_Custom