IDENTITY_INSERT Error

Question

We have recently migrated to Coldfusion 8 and have found that certain ALTER SQL commands that ran just fine in our old Coldfusion 7 environment will generate errors in CF8. We verified that all permission are given in both Coldfusion 8 Administrator Datasource Settings and SQLServer 2005 for ALTER rights to this table but we continue to get the following error.

[Macromedia][SQLServer JDBC Driver][SQLServer]Cannot find the object "dbo.SCI_Group" because it does not exist or you do not have permissions.

Here is our SQL Query that we are executing:

      DECLARE @BaSE INT
      DECLARE @fyr INT

      SET @BaSE = IDENT_CURRENT('dbo.SCI_Group')
      SET @fyr = 2009

      SET IDENTITY_INSERT dbo.SCI_Group ON

      INSERT INTO SCI_Group
        (ID, ParID, OldID, DowID, FYR, Name, Sort, Description, f_Input, f_Output, f_Enabled, f_Parent)
        SELECT
        ID + @BaSE,
        ParID + @BaSE,
        ID,
        DowID,
        @fyr,
        Name,
        Sort,
        Description,
        f_Input,
        f_Output,
        f_Enabled,
        f_Parent
      FROM
        SCI_Group
      WHERE
        FYR = (@fyr - 1)


      SET IDENTITY_INSERT dbo.SCI_Group OFF

Any insight from prior experience on this error type is much appreciated.

Thanks!

Mike

JR__Bob__Dobbs-qSBHQ2 · Accepted Answer

Things to check:1. Does the code execute correctly without SET IDENTITY_INSERT statements? 2. Check permissions for your login. From the Microsoft documentation for SET IDENTITY_INSERT: "User must own the object, or be a member of the sysadmin fixed server role, or the db_owner and db_ddladmin fixed database roles."http://msdn.microsoft.com/en-us/library/ms188059(SQL.90).aspxFrom the Microsoft documentation for IDENT_CURRENT: "Returns NULL on error or if a caller does not have permission to view the object. In SQL Server 2005, a user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as IDENT_CURRENT may return NULL if the user does not have any permission on the object. For more information, see Metadata Visibility Configuration and Troubleshooting Metadata Visibility."http://msdn.microsoft.com/en-us/library/ms175098(SQL.90).aspx3. Can you wrap your SQL into a stored procedure and using that instead of using a batch of SQL statements?Were there any database, network, or ColdFusion changes in the upgrade other then the move from CF7 to CF8?

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded