Identity columns no longer identities

Hi,
I have an entire db that for no reason I can see has had every identity
column's identity property set to 'no'. I have lots of inserts into the
affected tables which obviously produces errors inserting null into a column
that can't be null.
I could let 1 or 2 tables go as a wayward click in ent manager but *every*
identity in the db has been turned off. Is there a db wide setting that can
do this? Has anyone ever seen anthying like this before?
The scarey thing is this box goes into production next week and now Im
living in fear of the same thing happening again.
Any help appreciated.
Al"badlydressedboy" <badlydressedboy@.discussions.microsoft.com> wrote in
message news:D9C6489B-70E2-42A7-B643-DDA55BD74077@.microsoft.com...
> Hi,
> I have an entire db that for no reason I can see has had every identity
> column's identity property set to 'no'. I have lots of inserts into the
> affected tables which obviously produces errors inserting null into a
> column
> that can't be null.
> I could let 1 or 2 tables go as a wayward click in ent manager but *every*
> identity in the db has been turned off. Is there a db wide setting that
> can
> do this? Has anyone ever seen anthying like this before?
> The scarey thing is this box goes into production next week and now Im
> living in fear of the same thing happening again.
> Any help appreciated.
> Al
Umm, unless someone specifically scripted it, the only real way I can see
that happening is if this database was created as a replication subscriber.
In cases like that, default behavior is for the replicated tables to be
created w/o IDENTITY columns.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||On Mar 23, 12:06 pm, badlydressedboy
<badlydressed...@.discussions.microsoft.com> wrote:
> Hi,
> I have an entire db that for no reason I can see has had every identity
> column's identity property set to 'no'. I have lots of inserts into the
> affected tables which obviously produces errors inserting null into a column
> that can't be null.
> I could let 1 or 2 tables go as a wayward click in ent manager but *every*
> identity in the db has been turned off. Is there a db wide setting that can
> do this? Has anyone ever seen anthying like this before?
> The scarey thing is this box goes into production next week and now Im
> living in fear of the same thing happening again.
> Any help appreciated.
> Al
Was this DB "copied" from another DB by doing "SELECT * INTO" '|||I've never heard about this before and there's no database wise setting. In fact, you cannot remove
the identity property from a column. Most likely, someone used a script - export - import tool (like
DTS or SSIS) to move the data into a new database and missed configuring the tool to bring over the
identity attribute.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"badlydressedboy" <badlydressedboy@.discussions.microsoft.com> wrote in message
news:D9C6489B-70E2-42A7-B643-DDA55BD74077@.microsoft.com...
> Hi,
> I have an entire db that for no reason I can see has had every identity
> column's identity property set to 'no'. I have lots of inserts into the
> affected tables which obviously produces errors inserting null into a column
> that can't be null.
> I could let 1 or 2 tables go as a wayward click in ent manager but *every*
> identity in the db has been turned off. Is there a db wide setting that can
> do this? Has anyone ever seen anthying like this before?
> The scarey thing is this box goes into production next week and now Im
> living in fear of the same thing happening again.
> Any help appreciated.
> Al|||"Tracy McKibben" wrote:
> On Mar 23, 12:06 pm, badlydressedboy
> <badlydressed...@.discussions.microsoft.com> wrote:
> > Hi,
> >
> > I have an entire db that for no reason I can see has had every identity
> > column's identity property set to 'no'. I have lots of inserts into the
> > affected tables which obviously produces errors inserting null into a column
> > that can't be null.
> > I could let 1 or 2 tables go as a wayward click in ent manager but *every*
> > identity in the db has been turned off. Is there a db wide setting that can
> > do this? Has anyone ever seen anthying like this before?
> > The scarey thing is this box goes into production next week and now Im
> > living in fear of the same thing happening again.
> >
> > Any help appreciated.
> >
> > Al
> Was this DB "copied" from another DB by doing "SELECT * INTO" '
>
The crazy thing is that db creation was fine - its been up and running using
all the affected tables for a couple of weeks. We ran a bunch of data in (its
an ETL/OLAP db) in the morning and then went to do the same in the afternoon
but the identities had left the building.
There was an event that deleted them as they were definately there after db
creation otherwise the ETL wouldn't have worked at all. Its looking like that
event was somebody accidentally running a script (quite a bizarre script that
I am sure they would remember writing/running) but nobody is owning up.
Pretty weird stuff.|||On Mar 23, 12:45 pm, badlydressedboy
<badlydressed...@.discussions.microsoft.com> wrote:
> "Tracy McKibben" wrote:
> > On Mar 23, 12:06 pm, badlydressedboy
> > <badlydressed...@.discussions.microsoft.com> wrote:
> > > Hi,
> > > I have an entire db that for no reason I can see has had every identity
> > > column's identity property set to 'no'. I have lots of inserts into the
> > > affected tables which obviously produces errors inserting null into a column
> > > that can't be null.
> > > I could let 1 or 2 tables go as a wayward click in ent manager but *every*
> > > identity in the db has been turned off. Is there a db wide setting that can
> > > do this? Has anyone ever seen anthying like this before?
> > > The scarey thing is this box goes into production next week and now Im
> > > living in fear of the same thing happening again.
> > > Any help appreciated.
> > > Al
> > Was this DB "copied" from another DB by doing "SELECT * INTO" '
> The crazy thing is that db creation was fine - its been up and running using
> all the affected tables for a couple of weeks. We ran a bunch of data in (its
> an ETL/OLAP db) in the morning and then went to do the same in the afternoon
> but the identities had left the building.
> There was an event that deleted them as they were definately there after db
> creation otherwise the ETL wouldn't have worked at all. Its looking like that
> event was somebody accidentally running a script (quite a bizarre script that
> I am sure they would remember writing/running) but nobody is owning up.
> Pretty weird stuff.
SELECT INTO has nothing to do with DB creation, but it could have
everything to do with the creation of the TABLES. Does this ETL
process use SELECT INTO to build/populate the tables? Consider the
following example:
CREATE TABLE MyTable
(
IDColumn INT IDENTITY(1,1),
SomeVal1 CHAR(10),
SomeVal2 CHAR(10)
)
INSERT INTO MyTable (SomeVal1, SomeVal2) VALUES ('A', 'B')
SELECT * INTO MyNewTable FROM MyTable
SELECT * FROM MyNewTable
The structure of MyNewTable will appear to be the same as MyTable, but
it's NOT. The identity property of the IDColumn column doesn't get
copied.
If your ETL process involves the dropping of tables and then re-
creating them using SELECT INTO, that's your problem.