Posted by teddysnips on 10/30/06 18:31

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

• Next in forum: Need to declare multiple values
• Prev in forum: Re: Help: Stored Procedure performance issue.
• Thread view: Weird errors when trying to insert with IDENTITY_INSERT on!

[Reply to this message]