OFFXPDEV: Cannot Use Row Permissions on a Table That Has a BigInt Primary Key (290265)


The information in this article applies to:

  • Microsoft Office XP Developer
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 <Table Name>. <Table Name> 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:
  1. 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
  2. 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 "Symptoms" section of this article.
Modification Type:MajorLast Reviewed:10/30/2001
Keywords:kbbug kberrmsg KB290265
©2002 Microsoft Corporation. All rights reserved.