Reattaching database - identity_insert set to off

[ll]

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

[David Portas]

Re: Reattaching database - identity_insert set to off

"ll" <barn104_1999@y ahoo.comwrote in message
news:83f11287-63e8-4f94-8e48-03aaf9f6d856@8g 2000hse.googleg roups.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

[--CELKO--]

Re: Reattaching database - identity_insert set to off

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

[Tom van Stiphout]

Re: Reattaching database - identity_insert set to off

On Thu, 15 May 2008 23:07:16 +0100, "David Portas"
<REMOVE_BEFORE_ REPLYING_dporta s@acm.orgwrote:

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@y ahoo.comwrote in message
>news:83f1128 7-63e8-4f94-8e48-03aaf9f6d856@8g 2000hse.googleg roups.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?

[Tony Rogerson]

Re: Reattaching database - identity_insert set to off

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..sysdata bases 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@y ahoo.comwrote in message
news:83f11287-63e8-4f94-8e48-03aaf9f6d856@8g 2000hse.googleg roups.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
>

[Tony Rogerson]

Re: Reattaching database - identity_insert set to off

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]