Hi,

I have a problem with a SQL script and I can't figure a solution.

If I run the script directly in the SQL server, it runs successfully.

But when I install the module on DNN I got this error

Cannot explicit value for identity column in table "table name" when IDENTITY_INSERT is set to OFF

In my code IDENTITY_INSERT is set to on.

The {DataBaseOwner} and {ObjectQualifier} is correctly set.

Any Idea???

Regards
Soft

this is a simplified version of my script.

this script work under SQL (if I replace {databaseOwner} and {objectQualifier})
In DNN does not work.

any idea?

CREATE TABLE {databaseOwner}Tmp_{objectQualifier}table_item
    (
    ModuleID int NOT NULL,
    itemid int NOT NULL IDENTITY (1, 1),
    itemdescription varchar(2000) NULL
    )  ON [PRIMARY]
GO
SET IDENTITY_INSERT {databaseOwner}Tmp_{objectQualifier}table_item ON
GO
IF EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}table_item)
     EXEC('INSERT INTO {databaseOwner}Tmp_{objectQualifier}table_item (ModuleID, itemid, itemdescription)
        SELECT ModuleID, itemid, itemdescription  FROM {databaseOwner}{objectQualifier}table_item WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT {databaseOwner}Tmp_{objectQualifier}table_item OFF
GO
DROP TABLE {databaseOwner}{objectQualifier}table_item
GO
EXECUTE sp_rename N'{databaseOwner}Tmp_{objectQualifier}table_item', N'{objectQualifier}table_item', 'OBJECT'
GO

your right thanks

from

CREATE TABLE {databaseOwner}Tmp_{objectQualifier}table_item
    (
    ModuleID int NOT NULL,
    itemid int NOT NULL IDENTITY (1, 1),
    itemdescription varchar(2000) NULL
    )  ON [PRIMARY]
GO
SET IDENTITY_INSERT {databaseOwner}Tmp_{objectQualifier}table_item ON
GO
IF EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}table_item)
     EXEC('INSERT INTO {databaseOwner}Tmp_{objectQualifier}table_item (ModuleID, itemid, itemdescription)
        SELECT ModuleID, itemid, itemdescription  FROM {databaseOwner}{objectQualifier}table_item WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT {databaseOwner}Tmp_{objectQualifier}table_item OFF
GO
DROP TABLE {databaseOwner}{objectQualifier}table_item
GO
EXECUTE sp_rename N'{databaseOwner}Tmp_{objectQualifier}table_item', N'{objectQualifier}table_item', 'OBJECT'
GO

to this

CREATE TABLE {databaseOwner}Tmp_{objectQualifier}table_item
    (
    ModuleID int NOT NULL,
    itemid int NOT NULL IDENTITY (1, 1),
    itemdescription varchar(2000) NULL
    )  ON [PRIMARY]
GO
SET IDENTITY_INSERT {databaseOwner}Tmp_{objectQualifier}table_item ON
              
IF EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}table_item)
     EXEC('INSERT INTO {databaseOwner}Tmp_{objectQualifier}table_item (ModuleID, itemid, itemdescription)
        SELECT ModuleID, itemid, itemdescription  FROM {databaseOwner}{objectQualifier}table_item WITH (HOLDLOCK TABLOCKX)')

SET IDENTITY_INSERT {databaseOwner}Tmp_{objectQualifier}table_item OFF
GO
DROP TABLE {databaseOwner}{objectQualifier}table_item
GO
EXECUTE sp_rename N'{databaseOwner}Tmp_{objectQualifier}table_item', N'{objectQualifier}table_item', 'OBJECT'
GO

now it's working fine thansk you

Regards
Soft