It is indeed a bit unusual that the Create Table Utility would create a varchar with a negative length. Could you please provide some more details about:

• how the entity is defined (fields, data types, packing codes, ...),
• which connector options (USYS$MSS_PARAMS), and
• what Uniface version and patch level is used?

But the VC* packing code requires an overflow table and it therefore will not be mapped to varchar(max). In case you want to use varchar(max) then you need to use the packing code SC*. For details see the topic Microsoft SQL Server Data Packing in the Uniface Library. Hope this helps. Daniel

Uniface 9.6.06.02 (X603) Driver U5.0 The fields: Mail_ID, numeric, N9 Send_Status, string, VC2 Send_date, date & time Definition_id, String, VC32 Mail_from, string, VC255 Mail_reply, string, VC255 Mail_to, string, VC8000 Mail_cc, string, VC8000 Mail_bcc, string, VC8000 Mail_subject, string, VC255 Mail_html, string, VC1 Attachements, string, VC1000 mail_body, string, VC*  (gives varchar(-7...) , changed to SC* -> varchar(max) And why VC* to overflow tables, it is variable length so varchar(max) I suppose...

Thanks for the info. I'm afraid that the fixed part of a record is around 8000 bytes when using the MSS connector. When I calculate the record size for the above mentioned fields then it's more than 3x as much. For details of the DBMS Connector Limits see:

• Uniface Library > DBMS Support >Database Connectors > DBMS Connector Overview > DBMS Connector Limits

And normally you should get the following error when trying to generate a Create Table Script using the Create Table Utility:

• Fatal error: 8035 - Fixed part of table exceeds DBMS limit. drv=MSS, tbl=MAIL, siz=8000, max=8000

It seems that Uniface does not report this error anymore since the version 9.6.06 patch X601. I'll create a new BUG report for this issue. Anyway, when I change the field definitions (specifically for the fields Mail_to, Mail_cc, Mail_bcc and Mail_body) as follows:

Mail_ID, numeric, N9 Send_Status, string, VC2 Send_date, date & time Definition_id, String, VC32 Mail_from, string, VC255 Mail_reply, string, VC255 Mail_to, string, SC8000 Mail_cc, string, SC8000 Mail_bcc, string, SC8000 Mail_subject, string, VC255 Mail_html, string, VC1 Attachements, string, VC1000 Mail_body, string, SC* Then I can generate a valid Create Table Script for MSS.

stcourth said   And why VC* to overflow tables, it is variable length so varchar(max) I suppose...

As mentioned in my previous post this is documented behavior:

• Uniface Library > DBMS Support >Database Connectors > Microsoft SQL Server > Tables, Fields, Keys, and Indexes > Table and Field Management on Microsoft SQL Server

"Overflow Tables

You can create overflow tables on the fly. Entities that contain one of the following Uniface packing codes, require both a standard table and an overflow table:

   C*    R*    U*    W*    VC*    VR*    VU*    VW*

When a C* field is encountered, a standard table with a Varchar length of 7989 characters is created; an overflow table is created only when this number of characters is exceeded.

An overflow table contains the primary key from the base table and the overflow information from the field that causes the overflow table. The name of an overflow table consists of the same name as the lowercase entity name with the prefix o.

• Note:  The overflow mechanism is a technical encoding for larger amounts of data and is not transparent for other applications. When transparency is required, use segmented fields that result in Text and Image data types."

Hope this helps. Daniel

diseli said It seems that Uniface does not report this error anymore since the version 9.6.06 patch X601. I'll create a new BUG report for this issue. Anyway, when I change the field definitions (specifically for the fields Mail_to, Mail_cc, Mail_bcc and Mail_body) as follows:

Mail_ID, numeric, N9 Send_Status, string, VC2 Send_date, date & time Definition_id, String, VC32 Mail_from, string, VC255 Mail_reply, string, VC255 Mail_to, string, SC8000 Mail_cc, string, SC8000 Mail_bcc, string, SC8000 Mail_subject, string, VC255 Mail_html, string, VC1 Attachements, string, VC1000 Mail_body, string, SC* Then I can generate a valid Create Table Script for MSS. Daniel

  When I set SC8000 -> results in Varchar(max) in MSS -> this isn't correct, it the DB I can put 2GB of data in the one field, but Uniface will give errors on read and write I think. So I stick with the VC8000 And the 8K limit of SQL server record length is not active any more since SQL 2005, it will create itself overflow tables. So no more need to create overflow tables by Uniface (we don't like them also)

stcourth said When I set SC8000 -> results in Varchar(max) in MSS -> this isn't correct, it the DB I can put 2GB of data in the one field, but Uniface will give errors on read and write I think.

 That is actually correct and by design. The MSS connector will map fields with the packing code SC1-SC* to Varchar(Max). And as long as the corresponding field in the database does not hold more data than defined in Uniface then there won't be any problems. In case a non-Uniface program is writing to the same table then you need to put a constraint on the Varchar(Max) database field. For example:

ALTER TABLE [dbo].[MailTable] ADD CONSTRAINT [MaxLengthConstraint] CHECK (DATALENGTH([Mail_to]) <= 8000)

stcourth said So I stick with the VC8000 And the 8K limit of SQL server record length is not active any more since SQL 2005, it will create itself overflow tables. So no more need to create overflow tables by Uniface (we don't like them also)

You are raising a valid point here. I, however, was referring to a limit that is imposed by the MSS connector (i.e. Uniface). If you do not want Uniface to create any Overflow Tables then you should not use any packing code that will create a(n) Uniface unlimited variable length field (i.e. C*, R*, U*, W*, VC*, VR*, VU*, or VW*) - please note that a field with a fixed length definition (like e.g. VC1-VC8000) is in Uniface terms not a variable length field. But in case you are using an entity with Uniface unlimited variable length fields then the the fixed part of a record is limited to around 8000 bytes when using the MSS connector. And I also had a chat with our engineers. It seems that since the patch X601 it is possible to create records with the MSS connector that can have a length of up to 20MB. So it's no problem if you decide to stick with to the VC8000 fields. Unfortunately the new limit is not (yet) documented properly. I'll ask that the Uniface Library is updated accordingly. And I'll also create a new BUG report (as previously mentioned) for the problem with the Create Table Utility that creates Varchar fields with a negative size when the fixed record size is >8000 bytes and Uniface variable length fields are used. I hope this helps - and makes sense. Daniel   Edit: Unlimited variable-length fields (e.g. VC*) will create overflow tables, limited variable-length fields (e.g. VC1 - VC8000) will not.



Just a quick update: I've created in the meantime, as promised, a new BUG report for this problem:

• BUG: 30942 - X601: MSS connector might create varchar fields with negative size for C* fields

And we will fix this issue in one of our future patches or service packs. In case this problem is time critical for someone then please log a call with support. Hope this helps. Daniel