Posted by MC on 10/30/06 20:01

As far as I can see, you have set identity_insert on for another table. You
first need to set it to off before inserting into tblAdminUsers.
So (if I'm not missing something):

set identity_insert BSAVA_Archive_Test_2006.dbo.GPS_CHAR OFF

and then go with the
SET IDENTITY_INSERT tblAdminUsers ON
insert...

SET IDENTITY_INSERT tblAdminUsers OFF

<teddysnips@hotmail.com> wrote in message
news:1162233110.592872.8530@m73g2000cwd.googlegroups.com...
> SQL Server 2000 (DDL below)
>
> If I try to run this code in QA:
>
> SET IDENTITY_INSERT tblAdminUsers ON
> INSERT INTO tblAdminUsers
> (fldUserID,
> fldUsername,
> fldPassword,
> fldFullname,
> fldPermission,
> fldEmail,
> fldInitials,
> fldLastLogon,
> fldBatch)
> SELECT
> fldUserID,
> fldUsername,
> fldPassword,
> fldFullname,
> fldPermission,
> fldEmail,
> fldInitials,
> fldLastLogon,
> fldBatch
> FROM
> [BSAVA_26-10-2006].dbo.tblAdminUsers
> SET IDENTITY_INSERT tblAdminUsers OFF
>
> I get an error:
> IDENTITY_INSERT is already ON for table
> 'BSAVA_Archive_Test_2006.dbo.GPS_CHAR'. Cannot perform SET operation
> for table 'tblAdminUsers'.
>
> If I try to run:
> INSERT INTO tblAdminUsers
> (fldUserID,
> fldUsername,
> fldPassword,
> fldFullname,
> fldPermission,
> fldEmail,
> fldInitials,
> fldLastLogon,
> fldBatch)
> SELECT
> fldUserID,
> fldUsername,
> fldPassword,
> fldFullname,
> fldPermission,
> fldEmail,
> fldInitials,
> fldLastLogon,
> fldBatch
> FROM
> [BSAVA_26-10-2006].dbo.tblAdminUsers
>
> I get the error:
> Cannot insert explicit value for identity column in table
> 'tblAdminUsers' when IDENTITY_INSERT is set to OFF.
>
> Anyone any ideas? FYI the tables I'm INSERTing into were scripted from
> the [BSAVA_26-10-2006] tables.
>
> TIA
>
> Edward
>
> =====================
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblAdminUsers]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblAdminUsers]
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[GPS_CHAR]') and OBJECTPROPERTY(id, N'IsDefault') =
> 1)
> drop default [dbo].[GPS_CHAR]
> GO
>
> create default dbo.GPS_CHAR AS ''
>
> CREATE TABLE [dbo].[tblAdminUsers] (
> [fldUserID] [int] IDENTITY (1, 1) NOT NULL ,
> [fldUsername] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
> [fldPassword] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
> [fldFullname] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [fldPermission] [smallint] NULL ,
> [fldEmail] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [fldInitials] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
> [fldLastLogon] [smalldatetime] NULL ,
> [fldBatch] [char] (1) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY]
> GO
>

[Back to original message]