Weird errors when trying to insert with IDENTITY

Weird errors when trying to insert with IDENTITY_INSERT on!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

teddysnips@hotmail.com - 30 Oct 2006 19:31 GMT

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

Reply to this Message

MC - 30 Oct 2006 21:01 GMT

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

> SQL Server 2000 (DDL below)
>
[quoted text clipped - 92 lines]
> ) ON [PRIMARY]
> GO

Reply to this Message

teddysnips@hotmail.com - 31 Oct 2006 09:46 GMT

> 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.
[quoted text clipped - 7 lines]
>
> 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

Reply to this Message

--CELKO-- - 30 Oct 2006 22:26 GMT

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?

Reply to this Message

Tony Rogerson - 31 Oct 2006 09:12 GMT

> 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?

Signature

Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

> 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
[quoted text clipped - 24 lines]
> DEFAULT CURRENT_TIMESTAMP NOT NULL,
> batch_foobarflag CHAR(1) NOT NULL); what is it?

Reply to this Message

teddysnips@hotmail.com - 31 Oct 2006 09:17 GMT

[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

Reply to this Message

Tony Rogerson - 31 Oct 2006 09:48 GMT

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.

Signature

Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

> [incredibly valuable insight snipped]
>
[quoted text clipped - 10 lines]
>
> Edward

Reply to this Message

teddysnips@hotmail.com - 31 Oct 2006 10:52 GMT

> Hi Edward,
>
[quoted text clipped - 6 lines]
>
> 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

Reply to this Message

Tony Rogerson - 31 Oct 2006 11:26 GMT

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

Signature

Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

>> Hi Edward,
>>
[quoted text clipped - 36 lines]
>
> Edward

Reply to this Message

teddysnips@hotmail.com - 31 Oct 2006 12:38 GMT

> Could it be that you are prefixing the wrong database on the SET IDENTITY
> INSERT ?
[quoted text clipped - 4 lines]
>
> 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

Reply to this Message

Tony Rogerson - 31 Oct 2006 12:43 GMT

go for it - tonyrogerson@sqlserverfaq.com

Signature

Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

>> Could it be that you are prefixing the wrong database on the SET IDENTITY
>> INSERT ?
[quoted text clipped - 18 lines]
>
> Edward

Reply to this Message