SEO

#1 (**permalink**) 05-16-2008, 01:39 PM

Hi,
I recently detached and subsequently reattached a database, and now
I've had to go back to each table and re-establish the primary keys,
as well as the identity="yes" where needed. I am currently getting
the following error message, although this table's identity_insert is
set to ON ("yes").
I'm currently re-checking all other tables to see if they would
perhaps impact this.
Would there be anything else I could do at this point? (error msg
below

-------------------------
Microsoft OLE DB Provider for SQL Server error '80040e14'
Cannot insert explicit value for identity column in table
'AMS_Courses' when IDENTITY_INSERT is set to OFF.
-------------------------

Thanks for your help in this,
Louis

#2 (**permalink**) 05-16-2008, 01:39 PM

"ll" <barn104_1999@yahoo.com> wrote in message
news:83f11287-63e8-4f94-8e48-03aaf9f6d856@8g2000hse.googlegroups.com...
> Hi,
> I recently detached and subsequently reattached a database, and now
> I've had to go back to each table and re-establish the primary keys,
> as well as the identity="yes" where needed.

Sounds a little strage. There should be no need to "re-establish" primary
keys when a database has been detached and attached. Are you saying the
constraints disappeared?

--
David Portas

#3 (**permalink**) 05-16-2008, 01:39 PM

>> I recently detached and subsequently reattached a database, and now I've had to go back to each table and re-establish the PRIMARY KEYs, as well as the IDENTITY="YES" where needed. <<

That makes no sense. The PRIMARY KEY of a table is a subset of the
attributes that uniquely identify the entities. Do you mean that you
are validating the keys against a trusted external/internal source or
what? Surely, you did not use IDENTITY as a key! There a posting on
another SQL Server site at ITToolbox.com on a problem like this.

>> Would there be anything else I could do at this point? <<

First, update your resume and then read a book on basic RDBMS design,
in case you are ever hired again.

#4 (**permalink**) 05-16-2008, 01:39 PM

On Thu, 15 May 2008 23:07:16 +0100, "David Portas"
<REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote:

This is not uncommon in bad SQL Server database designs where a junior
designer forgot the primary keys. Of course he/she should meet with
some tar and feathers very soon.
Then when one wants to attach such tables to MsAcces, it recognizes no
PK, and asks the user which fields should be considered PK, so it can
keep the table updatable.

If the above scenario is true, the obvious solution is to first fix
the SQL Serve database design.

-Tom.

>"ll" <barn104_1999@yahoo.com> wrote in message
>news:83f11287-63e8-4f94-8e48-03aaf9f6d856@8g2000hse.googlegroups.com...
>> Hi,
>> I recently detached and subsequently reattached a database, and now
>> I've had to go back to each table and re-establish the primary keys,
>> as well as the identity="yes" where needed.
>
>Sounds a little strage. There should be no need to "re-establish" primary
>keys when a database has been detached and attached. Are you saying the
>constraints disappeared?

#5 (**permalink**) 05-16-2008, 01:39 PM

Hi Louis,

Detaching a database simple checkpoints it and removes the database from the
system catalogue - it does not touch or change any of your schema,
properties or even database properties.

Attaching a database simply puts the entry for the database back into
master..sysdatabases and recovers the database - it does not touch or change
any of your schema, properties or even database properties.

The only to get the situation you have is for somebody to do it manually.

Tony.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

"ll" <barn104_1999@yahoo.com> wrote in message
news:83f11287-63e8-4f94-8e48-03aaf9f6d856@8g2000hse.googlegroups.com...
> Hi,
> I recently detached and subsequently reattached a database, and now
> I've had to go back to each table and re-establish the primary keys,
> as well as the identity="yes" where needed. I am currently getting
> the following error message, although this table's identity_insert is
> set to ON ("yes").
> I'm currently re-checking all other tables to see if they would
> perhaps impact this.
> Would there be anything else I could do at this point? (error msg
> below

> -------------------------
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Cannot insert explicit value for identity column in table
> 'AMS_Courses' when IDENTITY_INSERT is set to OFF.
> -------------------------
>
> Thanks for your help in this,
> Louis
>

#6 (**permalink**) 05-16-2008, 01:39 PM

> what? Surely, you did not use IDENTITY as a key! There a posting on
> another SQL Server site at ITToolbox.com on a problem like this.

Stop spreading myth's and disinformation - they are not helpful to the OP.

You should be ashamed of yourself celko; that said - it's you all over; the
"follow my ideas or be dammed" attitude - very arrogant.

I'd like to see the URL to the article you talk about so I can educate you
further on using MICROSOFT SQL SERVER.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Reattaching database - identity_insert set to off