Microsoft KB Archive/290265

= OFFXPDEV: Cannot Use Row Permissions on a Table That Has a BigInt Primary Key =

Article ID: 290265

Article Last Modified on 2/12/2007

-

APPLIES TO


 * Microsoft Office XP Developer Edition

-



This article was previously published under Q290265



SYMPTOMS
When you try to add row based permissions to a Workflow table, you receive the following error message:

Row based permissions cannot be enabled for Table .  must have a single column Primary Key or Identity column compatible with the integer datatype.



CAUSE
The Workflow table has a primary key with the BigInt data type. The Workflow Designer does not support the BigInt data type.



RESOLUTION
To use the BigInt data type as the primary key field, you must make the following two changes:   Modify the modCheckNumericDatatypeCompat stored procedure to the following: CREATE PROCEDURE [dbo].modCheckNumericDatatypeCompat @ColTypeCompatWITH sysname,    --The name of the col to be compatible WITH @ColTypeChecking   sysname,    --The column you're checking! @ColTypeCompatWITHPrec smallint, @ColTypeCompatWITHScale smallint, @ColTypeCheckingPrec   smallint, @ColTypeCheckingScale  smallint, @Compat        bit OUTPUT AS SET NOCOUNT ON IF (@ColTypeChecking IN ('int', 'smallint', 'tinyint', 'numeric', 'decimal', 'bigint') AND    -- One of the folling MUST be true!    ((@ColTypeChecking IN ('bigint', 'int', 'smallint', 'tinyint') AND (@ColTypeChecking = @ColTypeCompatWITH)) OR (@ColTypeCompatWITH = 'bigint' AND @ColTypeChecking IN ('int','smallint', 'tinyint', 'numeric', 'decimal')) OR (@ColTypeCompatWITH = 'int' AND @ColTypeChecking IN ('smallint', 'tinyint', 'numeric', 'decimal')) OR (@ColTypeCompatWITH = 'smallint' AND @ColTypeChecking = 'tinyint') OR (@ColTypeCompatWITH IN ('numeric', 'decimal')        AND @ColTypeCompatWITHPrec >= @ColTypeCheckingPrec AND @ColTypeCompatWITHScale = 0 AND @ColTypeCheckingScale = 0))) SET @Compat = 1    --Yes these numeric datatypes are compatible. ELSE SET @Compat = 0    --NO - these numeric datatypes are NOT compatible.

GO  Change the RowID column data type from int to BigInt in the modPermissions table.

After you have made these two changes, you can add row permissions to the Workflow table.

NOTE: The modCheckNumericDatatypeCompat stored procedure and modPermissions table reside in the Workflow database.



MORE INFORMATION
Supported primary key data types for a Workflow table include int, smallint, tinyint, numeric, and decimal.

Steps to Reproduce the Behavior

 * 1) Start the Microsoft Development Environment.
 * 2) On the File menu, point to New, and then click Project.
 * 3) Click the SQL Workflow Application icon, and then click OK.
 * 4) Choose the appropriate server and database, and then click OK.
 * 5) Right-click Workflow Processes in the Solution Explorer, and then click Add New Workflow.
 * 6) Choose the table with the BigInt primary key as the main table, and then click OK.
 * 7) Right-click the newly added table under the Tables node in the Solution Explorer, and then click Properties.
 * 8) Set the Row Permissions property to True.

Note that you receive the error message that is mentioned in the &quot;Symptoms&quot; section of this article.

Additional query words: pra

Keywords: kbbug kberrmsg kbnofix KB290265

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.