Folder Fails to Clone Due to SQL Server Row Size Restriction

Metastorm

 

The information in this article applies to:

Product: Metastorm BPM (e-Work)
Version: 6.x 7.x

Issue

• Version 6

  ErrorCode: '-2147217900' Description: 'Unclosed quotation mark before the character string ')'.'
  Source: 'Microsoft OLE DB Provider for SQL Server'
  SQL State: '42000' NativeErrorCode: '105'

  ErrorCode: '-2147217900' Description: 'Line 1: Incorrect syntax near '000000000000000000'.'
  Source: 'Microsoft OLE DB Provider for SQL Server'
  SQL State: '42000' NativeErrorCode: '170'

  A user will receive the message Failed to refresh form in the browser.

• Version 7:

  ErrorCode: '-2147217900' Description: 'Insert Error: Column name or number of supplied values does not match table definition.'
  Source: 'Microsoft OLE DB Provider for SQL Server'
  SQL State: '21S01' NativeErrorCode: '213'

  The following error is repeated for each custom variable in the table.

  ErrorCode: '-2147217900' Description: 'Invalid column name 'VariableName11'.'
  Source: 'Microsoft OLE DB Provider for SQL Server'
  SQL State: '42S22' NativeErrorCode: '207'

  A user will receive the message Failed to invoke action in the browser.

Discussion

• SQL Server's row size limit is 8060 bytes. This is the amount of data that can be stored in the row. However, you can create a table definition with 100 fields, all nvarchar(2000), and it will accept the creation of the table. But an attempt to populate a row with more than 8 KB of data will not succeed.

  If a map table contains columns (folder ID and custom variable names) whose combined column names exceed 8000 characters (4000 for Metastorm BPM version 7 due to unicode support), the INSERT statement built to clone a folder will exceed the maximum SQL Server row length of 8K bytes. This causes a truncation of the SQL statement, and the folder fails to clone.

Workaround

• Make a backup copy of the stored procedure psp\_clone\_custom\_table\_row. Then drop the existing stored procedure psp\_clone\_custom\_table\_row in the database and recreate it with the SQL in the appropriate attached file (version 6 or 7).