Tuesday, September 6, 2011

Meta field limit in EPiServer Commerce

In a EPiServer Commerce project i'm working on we have catalog entries with a good amount of custom meta fields. When we did a new release to stage last week and added one more meta field we got the following error back from .NET on one entry,


Must declare the scalar variable "@ShortDe".
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

and this error message on another entry,

Must declare the scalar variable "@".
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

We had apperantly reched an internal limit in EPiServer Commerce on the number of meta fields you could add to an meta class, or rather the total length of the names of all meta fields added to an meta class. After some reflection of the code and searching in the database we found the stored procedure that was responsible for the error, "mdpsp_sys_CreateMetaClassProcedure", in there we found a long list of variables declared to be "NVARCHAR(4000)", this were responsible for truncating our field list. The fix we did was to change this from NVARCHAR(4000) to VARCHAR(MAX) (e.g. VARCHAR(8000)). This will give us some more room to add new meta fields.

...
DECLARE @OPEN_SYMMETRIC_KEY varchar(MAX)
DECLARE @CLOSE_SYMMETRIC_KEY varchar(MAX)

SET @OPEN_SYMMETRIC_KEY = ''
SET @CLOSE_SYMMETRIC_KEY = ''

DECLARE @MetaClassFieldList varchar(MAX)
DECLARE @MetaClassFieldList_E varchar(MAX)
DECLARE @MetaClassFieldListWithAt varchar(MAX)
DECLARE @MetaClassFieldListSet varchar(MAX)
DECLARE @MetaClassFieldListInsert varchar(MAX)

DECLARE @MetaClassFieldList_L varchar(MAX)
DECLARE @MetaClassFieldListSet_L1 varchar(MAX)
DECLARE @MetaClassFieldListSet_L2 varchar(MAX)
DECLARE @MetaClassFieldList_LI varchar(MAX)
DECLARE @MetaClassFieldListInsert_L1 varchar(MAX)
DECLARE @MetaClassFieldListInsert_L2 varchar(MAX)
...

This bug is present in all current versions of EPiServer Commerce up to 1.1 R2 SP1.