|
|
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
[Back to original message]
|