Hi,
I have a merge replication going on. Say, I have a subscriber with an
identity range from 1000 to 1200 and I have only used 100 identities of that
range. For some what reason I have to reinitialise the subscription. What
publisher does is it allocates a new set of range to the table i.e. 1300 -
1400 to the article on subscription. As you can see I have simply wasted 100
identities i.e. from 1100 to 1200. Please note identity allocation is
happening automatically.
Is there a way that I can avoid the wastage?
How can I deduce using system stored procedure that what identities are in
use for each article on each subscriber?
Thanks
Vivek
This is normally not a concern.
Basically replication does not track which identity ranges are in place by
subscribers. All it does is track the last deployed identity range and the
range in use on the publication database.
While I don't recommend it, you can go to the subscriber and do a dbcc
checkident('TableName',reseed,101) and then run a sp_help TableName identify
the replication constraint which limits the identity values which can be
used in the subscriber, and modify it for the range you wish to use.
Again I don't recommend doing this.
You might want to check this link for more on this feature.
http://www.simple-talk.com/2005/07/05/replication/
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vivek Sharma" <vivek_nz76@.hotmail.com> wrote in message
news:e97gXp$3FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a merge replication going on. Say, I have a subscriber with an
> identity range from 1000 to 1200 and I have only used 100 identities of
that
> range. For some what reason I have to reinitialise the subscription. What
> publisher does is it allocates a new set of range to the table i.e. 1300 -
> 1400 to the article on subscription. As you can see I have simply wasted
100
> identities i.e. from 1100 to 1200. Please note identity allocation is
> happening automatically.
> Is there a way that I can avoid the wastage?
> How can I deduce using system stored procedure that what identities are in
> use for each article on each subscriber?
> Thanks
> Vivek
>
Showing posts with label identities. Show all posts
Showing posts with label identities. Show all posts
Monday, March 12, 2012
Friday, March 9, 2012
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.
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.
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" ?
|||"Tracy McKibben" wrote:
> On Mar 23, 12:06 pm, badlydressedboy
> <badlydressed...@.discussions.microsoft.com> wrote:
> 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:
>
>
> 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.
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" ?
|||"Tracy McKibben" wrote:
> On Mar 23, 12:06 pm, badlydressedboy
> <badlydressed...@.discussions.microsoft.com> wrote:
> 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:
>
>
> 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.
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|||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 - ex
port - 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 messa
ge
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 colu
mn
> 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 ca
n
> 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|||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 colu
mn
> 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 ca
n
> 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" '|||"Tracy McKibben" wrote:
> On Mar 23, 12:06 pm, badlydressedboy
> <badlydressed...@.discussions.microsoft.com> wrote:
> 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 (it
s
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 tha
t
event was somebody accidentally running a script (quite a bizarre script tha
t
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:
>
>
>
>
> The crazy thing is that db creation was fine - its been up and running usi
ng
> 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 afterno
on
> but the identities had left the building.
> There was an event that deleted them as they were definately there after d
b
> creation otherwise the ETL wouldn't have worked at all. Its looking like t
hat
> event was somebody accidentally running a script (quite a bizarre script t
hat
> 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.
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|||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 - ex
port - 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 messa
ge
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 colu
mn
> 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 ca
n
> 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|||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 colu
mn
> 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 ca
n
> 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" '|||"Tracy McKibben" wrote:
> On Mar 23, 12:06 pm, badlydressedboy
> <badlydressed...@.discussions.microsoft.com> wrote:
> 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 (it
s
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 tha
t
event was somebody accidentally running a script (quite a bizarre script tha
t
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:
>
>
>
>
> The crazy thing is that db creation was fine - its been up and running usi
ng
> 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 afterno
on
> but the identities had left the building.
> There was an event that deleted them as they were definately there after d
b
> creation otherwise the ETL wouldn't have worked at all. Its looking like t
hat
> event was somebody accidentally running a script (quite a bizarre script t
hat
> 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.
Subscribe to:
Posts (Atom)