Weird errors when trying to insert with IDENTITY_INSERT on!

[teddysnips]

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.tblAdminUs ers
SET IDENTITY_INSERT tblAdminUsers OFF

I get an error:
IDENTITY_INSERT is already ON for table
'BSAVA_Archive_ Test_2006.dbo.G PS_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.tblAdminUs ers

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

[Tony Rogerson]

go for it - to**********@sq lserverfaq.com
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
<te********@hot mail.comwrote in message
news:11******** **************@ m7g2000cwm.goog legroups.com...

>
Tony Rogerson wrote:

>Could it be that you are prefixing the wrong database on the SET IDENTITY
INSERT ?

SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON |
OFF }

Can you post the complete script you are trying to run.

Also, the output from PRINT @@VERSION

I can't post the whole script as it's more than 5,000 lines, though if
you're amenable I could email it to you and you could post your
findings back here?!?

The output from PRINT @@VERSION is:

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

Edward