Err Msg: Microsoft OLE DB Provider for ODBC Drivers Error '80040e14' (223758)

SYMPTOMS

When you attempt to add or update an item in a Microsoft Site Server 3.0 Commerce store, an error similar to one of the following occurs:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Cursor open failed because the size of the keyset row exceeded maximum allowed row size. /storename/manager/product_edit.asp, line 27

-or-

CAUSE

The total number of bytes allocated for all product attributes exceeds the maximum number of bytes per row that SQL Server allows. SQL Server 6.5 allows a maximum row size of 1962 bytes and SQL Server 7.0 allows a maximum row size of 8060 bytes.

By default, the Site Builder Wizard allocates the following:

765 bytes (255 bytes each) for product name, description and image filename
100 bytes for SKU
20 bytes (4 bytes each) for list_price, image_width, image_height, sale_price and sale_end

By default, each static single valued attribute requires 255 bytes. Without exceeding the maximum number of bytes per row, a maximum of 4 static single valued attributes can be created in SQL Server 6.5 and a maximum of 28 attributes can be created in SQL Server 7.0.

RESOLUTION

The maximum number of bytes per row is hard coded into SQL Server, so the only way to overcome this problem is to override the default allocation of 255 bytes per static single valued attribute. To do this, perform the following steps:

WARNING: Performing these steps will delete all records from the existing store tables.

Edit the Schema.sql script in the %storeroot%\Config\SQL\SQLSvr directory. Schema.sql is a text file and can be edited in Notepad.

Locate the section that looks similar to the following:

CREATE TABLE yourstorename_product(
    sku VARCHAR(100) NOT NULL,
    name VARCHAR(255) NULL,
    description VARCHAR(255) NULL,
    list_price INT NULL,
    image_file VARCHAR(255) NULL,
    image_width INT NULL,
    image_height INT NULL,
    sale_price INT NULL,
    sale_start DATETIME NULL,
    sale_end DATETIME NULL,
    customattribute1 VARCHAR(255) NULL,
    customattribute2  VARCHAR(255) NULL,
    customattribute3  VARCHAR(255) NULL,
    customattribute4  VARCHAR(255) NULL,
    customattribute5  VARCHAR(255) NULL,
    customattributeN  VARCHAR(255) NULL,
    PRIMARY KEY (sku)
) GO

Decrease the amount of bytes allocated to each custom attribute. Each attribute can have a different number of bytes allocated to it; however, the total number of bytes for all variables can not exceed the maximum row size for your version of SQL Server.

CREATE TABLE yourstorename_product(
    sku VARCHAR(100) NOT NULL,
    name VARCHAR(255) NULL,
    description VARCHAR(255) NULL,
    list_price INT NULL,
    image_file VARCHAR(255) NULL,
    image_width INT NULL,
    image_height INT NULL,
    sale_price INT NULL,
    sale_start DATETIME NULL,
    sale_end DATETIME NULL,
    customattribute1 VARCHAR(10) NULL,
    customattribute2  VARCHAR(20) NULL,
    customattribute3  VARCHAR(30) NULL,
    customattribute4  VARCHAR(30) NULL,
    customattribute5  VARCHAR(50) NULL,
    customattributeN  VARCHAR(100) NULL,
    PRIMARY KEY (sku)
) GO

Open ISQL_W and select the Commerce database that contains the yourstore_product table.
Click File , and then select Open from the menu bar.
Browse to the %storeroot%\Config\SQL\SQLSvr directory and open Schema.sql.
Select Query , and then select Execute from the menu bar.
When the Schema.sql script completes, the store will be configured properly.