473,394 Members | 1,012 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

Weird errors when trying to insert with IDENTITY_INSERT on!

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

Oct 30 '06 #1
10 8189
MC
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

<te********@hotmail.comwrote in message
news:11********************@m73g2000cwd.googlegrou ps.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

Oct 30 '06 #2
Did you notice that you have put the prefix "fld-" on all the columns?
This is not just a great way to destroy a data dictioanry and violate
ISO-11179 rules, but it also tells us that you have not idea waht
columns are nothing like fields. Likewise, the silly, redundant "tbl-"
prefix.

You have no key on the table. Identity cannot ever be a relational
key. I can insert the same user data 1000 times and you will not
detect the redundancy. You have no defaults or constraints. What the
he3ck is a batch? It looks like a flag of some kind, but we do not use
those in SQL.

What you did was mimic a deck of punch cards or a magnetic tape file.
Clean up the data element and get yourself a key and constraints, more
like this:

CREATE TABLE AdminUsers
(user_name VARCHAR(20) NOT NULL PRIMARY KEY,
password VARCHAR(20) NOT NULL
CHECK (LEN(password) 5), -- other rules?
full_name VARCHAR(50) NOT NULL, -- trim spaces?
permission_code INTEGER DEFAULT 0 NOT NULL,
email_addr VARCHAR(50) NOT NULL
CHECK (<<grep pattern match>>),
user_initials VARCHAR(3) DEFAULT ' ' NOT NULL,
lastlogon_date DATETIME
DEFAULT CURRENT_TIMESTAMP NOT NULL,
batch_foobarflag CHAR(1) NOT NULL); what is it?

Oct 30 '06 #3
I can insert the same user data 1000 times and you will not
detect the redundancy. You have no defaults or constraints. What the
Then put a unique constraint on the column that has the IDENTITY propety.
email_addr VARCHAR(50) NOT NULL
CHECK (<<grep pattern match>>),
How on earth are you going to do a <<grep pattern match>when SQL Server
can only access external stuff like that via CLR? Remember you are the
advocate who says there should be no CLR, everything should be standard SQL.

Like the other post in this group we are waiting on an answer - this can
easily, supported and maintainable oh and re-useable outside the database
using a CLR function and the regex .NET class.
password VARCHAR(20) NOT NULL
CHECK (LEN(password) 5), -- other rules?
Again, for implementing a strict password (simulate windows strict policy)
how would you do that in standard SQL in a constraint without resorting to
lots of LIKES, CASTS and CASE statements? Short answer- you can't without
using a CLR function again.
What you did was mimic a deck of punch cards or a magnetic tape file.
Clean up the data element and get yourself a key and constraints, more
like this:
What you advocate is dated programming techniques, not following Microsofts
recommendations on product use and not following sound and professional
strategies for development, maintainability and support - sounds like a
cowboy approach to me.
email_addr VARCHAR(50) NOT NULL
Where is it defined that an email address can only be 50 characters long? Is
that an industrial standard?

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jc*******@earthlink.netwrote in message
news:11*********************@f16g2000cwb.googlegro ups.com...
Did you notice that you have put the prefix "fld-" on all the columns?
This is not just a great way to destroy a data dictioanry and violate
ISO-11179 rules, but it also tells us that you have not idea waht
columns are nothing like fields. Likewise, the silly, redundant "tbl-"
prefix.

You have no key on the table. Identity cannot ever be a relational
key. I can insert the same user data 1000 times and you will not
detect the redundancy. You have no defaults or constraints. What the
he3ck is a batch? It looks like a flag of some kind, but we do not use
those in SQL.

What you did was mimic a deck of punch cards or a magnetic tape file.
Clean up the data element and get yourself a key and constraints, more
like this:

CREATE TABLE AdminUsers
(user_name VARCHAR(20) NOT NULL PRIMARY KEY,
password VARCHAR(20) NOT NULL
CHECK (LEN(password) 5), -- other rules?
full_name VARCHAR(50) NOT NULL, -- trim spaces?
permission_code INTEGER DEFAULT 0 NOT NULL,
email_addr VARCHAR(50) NOT NULL
CHECK (<<grep pattern match>>),
user_initials VARCHAR(3) DEFAULT ' ' NOT NULL,
lastlogon_date DATETIME
DEFAULT CURRENT_TIMESTAMP NOT NULL,
batch_foobarflag CHAR(1) NOT NULL); what is it?

Oct 31 '06 #4

--CELKO-- wrote:
[incredibly valuable insight snipped]

Many thanks for the lesson O great master. The very words of wisdom
that fall from your lips are enough to render us mere mortals shriven
in your sight. We, your humble acolytes, realise that you are
omnicscient, and that therefore you know that we sometimes inherit data
structures that were generated by others, who do not have your
boundless wisdom, but it is not always possible to rewrite applications
end-to-end in order to conform to your Holy Writ.

However, could you not find it within your bountiful beneficence to
answer the question?

Edward

Oct 31 '06 #5

MC wrote:
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
Thanks for at least trying to answer the question! Unfortunately, your
suggestion:

set identity_insert BSAVA_Archive_Test_2006.dbo.GPS_CHAR OFF

returns the error:

'BSAVA_Archive_Test_2006.dbo.GPS_CHAR' is not a user table. Cannot
perform SET operation.

Since the intent of the operation is to create an archive database
(which is, for this purpose, read-only) I have removed the IDENTITY
attribute from the fldUserID column.

However, I'm still interested to know why this has happened, if anyone
has any ideas!

Edward

Oct 31 '06 #6
Hi Edward,

Just ignore celko, he's an arrogant idiot with little real industrial
experience; he teaches standard sql and database design and that's it, but
that seems to have been picked up through doing a maths degree or something;
the old self-taught problem some people have....

Anyway, MC's answer should give you what you need.

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
<te********@hotmail.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...
>
--CELKO-- wrote:
[incredibly valuable insight snipped]

Many thanks for the lesson O great master. The very words of wisdom
that fall from your lips are enough to render us mere mortals shriven
in your sight. We, your humble acolytes, realise that you are
omnicscient, and that therefore you know that we sometimes inherit data
structures that were generated by others, who do not have your
boundless wisdom, but it is not always possible to rewrite applications
end-to-end in order to conform to your Holy Writ.

However, could you not find it within your bountiful beneficence to
answer the question?

Edward

Oct 31 '06 #7

Tony Rogerson wrote:
Hi Edward,

Just ignore celko, he's an arrogant idiot with little real industrial
experience; he teaches standard sql and database design and that's it, but
that seems to have been picked up through doing a maths degree or something;
the old self-taught problem some people have....

Anyway, MC's answer should give you what you need.

Tony.
Thanks Tony - I'd sort of worked out that Celko had self-esteem issues!

However, MC's answer doesn't give me what I need - in fact, the more I
delve, the weirder it gets.

I tried re-scripting the database without the Defaults - since it's
going to be a read-only archive they're not important.

I hadn't noticed that there was something really weird about the INSERT
error:

IDENTITY_INSERT is already ON for table
'BSAVA_Archive_Test_2006.dbo.GPS_CHAR'. Cannot perform SET operation
for table 'tblAdminUsers'

It references a database called "'BSAVA_Archive_Test_2006". However
this is NOT either of the two databases that I'm operating on! In
fact, it's an old test database so I dropped it.

Now I get the following error message:

"Could not find database ID 56. Database may not be activated yet or
may be in transition."

I'm completely baffled!

Edward

Oct 31 '06 #8
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

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
<te********@hotmail.comwrote in message
news:11*********************@k70g2000cwa.googlegro ups.com...
>
Tony Rogerson wrote:
>Hi Edward,

Just ignore celko, he's an arrogant idiot with little real industrial
experience; he teaches standard sql and database design and that's it,
but
that seems to have been picked up through doing a maths degree or
something;
the old self-taught problem some people have....

Anyway, MC's answer should give you what you need.

Tony.

Thanks Tony - I'd sort of worked out that Celko had self-esteem issues!

However, MC's answer doesn't give me what I need - in fact, the more I
delve, the weirder it gets.

I tried re-scripting the database without the Defaults - since it's
going to be a read-only archive they're not important.

I hadn't noticed that there was something really weird about the INSERT
error:

IDENTITY_INSERT is already ON for table
'BSAVA_Archive_Test_2006.dbo.GPS_CHAR'. Cannot perform SET operation
for table 'tblAdminUsers'

It references a database called "'BSAVA_Archive_Test_2006". However
this is NOT either of the two databases that I'm operating on! In
fact, it's an old test database so I dropped it.

Now I get the following error message:

"Could not find database ID 56. Database may not be activated yet or
may be in transition."

I'm completely baffled!

Edward

Oct 31 '06 #9

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

Oct 31 '06 #10
go for it - to**********@sqlserverfaq.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********@hotmail.comwrote in message
news:11**********************@m7g2000cwm.googlegro ups.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

Oct 31 '06 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

15
by: For example John Smith | last post by:
I'm doing a data transfer from Access to SQL Server, I wish to keep the identity column (autonumber) values as all the data is already related. I tried the first table append query including the...
7
by: Jon Combe | last post by:
I have created the following test SQL code to illustrate a real problem I have with some SQL code. CREATE TABLE JCTable ( CustomerName varchar(50) ) ALTER TABLE JCTable ADD CustomerNo int...
10
by: Douglas Buchanan | last post by:
I am using the following code instead of a very lengthly select case statement. (I have a lot of lookup tables in a settings form that are selected from a ListBox. The data adapters are given a...
14
by: serge | last post by:
I have a scenario where two tables are in a One-to-Many relationship and I need to move the data from the Many table to the One table so that it becomes a One-to-One relationship. I need to...
3
by: Jim in Arizona | last post by:
I'm going insane! I don't know if it's just that the .net 2.0 framework is buggy or if it really is my code. This is pretty hard to explain since I can't even begin to nail down why this is...
4
by: Kevin O'Donovan | last post by:
Hi, Is anyone else experiencing this, or better yet, have a fix for it: We have VS.Net 2003 from the MSDN Universal subscription installed on 3 workstations. We have about 10 or so projects...
3
by: nicolas.bouchard | last post by:
I am developing an integration process between two databases. One of them is a SQL Server 2000 and the other is using MSDE 2000. The integration process is done in C# (VS2003). The main database...
6
by: Christopher Lusardi | last post by:
How can I fix this? When I do the below I get the error message: "Cannot insert explict value for identity column in table 'Employees' when IDENTITY_INSERT is set to OFF." To get this message,...
2
by: TamaThps | last post by:
Hi, I'm using visual studio 2008 and normally when I get an error it shows what line it is on and which file etc. The error I'm getting I don't know how to solve or even what the problem is. This...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.