Alter Identity Column question

What is the syntax for changing an identity column in a table to "Not For Replication"?
Alter table x
?? identity column, Not For Replication
Thanx!
JLS,
this is not possible in TSQL. You can do it in EM, but if you run profiler you'll see that a huge amount of work goes on behind the scenes, including the creation, population and renaming of a temporary table.
HTH,
Paul Ibison
|||try this
sp_configure 'allow_updates', 1
go
reconfigure with override
go
update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
colstat & 0x0008 =0
go
sp_configure 'allow updates', 0
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23gElYLsMEHA.620@.TK2MSFTNGP10.phx.gbl...
What is the syntax for changing an identity column in a table to "Not For Replication"?
Alter table x
?? identity column, Not For Replication
Thanx!
|||I thought so...
I ran profiler and couldn't pick up any Alter statement, so I kinda expected this answer.
Thanx anyway!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:ONWdeetMEHA.2244@.tk2msftngp13.phx.gbl...
JLS,
this is not possible in TSQL. You can do it in EM, but if you run profiler you'll see that a huge amount of work goes on behind the scenes, including the creation, population and renaming of a temporary table.
HTH,
Paul Ibison
|||I'm sorry, but I don't understand what this will do. Where do I place the table/column name of the identity column that I want to change to "Not for Replication" in your script?
What will this change to syscolumns provide? A default of setting all my identity columns to "Yes (Not for Replication)"?
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:OL9Z4FxMEHA.740@.TK2MSFTNGP12.phx.gbl...
try this
sp_configure 'allow_updates', 1
go
reconfigure with override
go
update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
colstat & 0x0008 =0
go
sp_configure 'allow updates', 0
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23gElYLsMEHA.620@.TK2MSFTNGP10.phx.gbl...
What is the syntax for changing an identity column in a table to "Not For Replication"?
Alter table x
?? identity column, Not For Replication
Thanx!
|||run this in your publication database. Here I am setting the identity column for the jobs table to NFR
sp_configure 'allow updates', 1
GO
reconfigure with override
GO
update syscolumns set colstat = colstat | 0x0008 where colstat & 0x0001 <> 0 and colstat & 0x0008 = 0 and id=object_id('jobs')
GO
sp_configure 'allow updates', 0
"JLS" <jlshoop@.hotmail.com> wrote in message news:O3iV6P2MEHA.1608@.TK2MSFTNGP12.phx.gbl...
I'm sorry, but I don't understand what this will do. Where do I place the table/column name of the identity column that I want to change to "Not for Replication" in your script?
What will this change to syscolumns provide? A default of setting all my identity columns to "Yes (Not for Replication)"?
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:OL9Z4FxMEHA.740@.TK2MSFTNGP12.phx.gbl...
try this
sp_configure 'allow_updates', 1
go
reconfigure with override
go
update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
colstat & 0x0008 =0
go
sp_configure 'allow updates', 0
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23gElYLsMEHA.620@.TK2MSFTNGP10.phx.gbl...
What is the syntax for changing an identity column in a table to "Not For Replication"?
Alter table x
?? identity column, Not For Replication
Thanx!
|||AWESOME! That's the answer, THANK YOU!
Now I will pay you back by buying your book once it hits the market. :-)
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:%23f7rE0BNEHA.4036@.TK2MSFTNGP12.phx.gbl...
run this in your publication database. Here I am setting the identity column for the jobs table to NFR
sp_configure 'allow updates', 1
GO
reconfigure with override
GO
update syscolumns set colstat = colstat | 0x0008 where colstat & 0x0001 <> 0 and colstat & 0x0008 = 0 and id=object_id('jobs')
GO
sp_configure 'allow updates', 0
"JLS" <jlshoop@.hotmail.com> wrote in message news:O3iV6P2MEHA.1608@.TK2MSFTNGP12.phx.gbl...
I'm sorry, but I don't understand what this will do. Where do I place the table/column name of the identity column that I want to change to "Not for Replication" in your script?
What will this change to syscolumns provide? A default of setting all my identity columns to "Yes (Not for Replication)"?
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:OL9Z4FxMEHA.740@.TK2MSFTNGP12.phx.gbl...
try this
sp_configure 'allow_updates', 1
go
reconfigure with override
go
update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
colstat & 0x0008 =0
go
sp_configure 'allow updates', 0
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23gElYLsMEHA.620@.TK2MSFTNGP10.phx.gbl...
What is the syntax for changing an identity column in a table to "Not For Replication"?
Alter table x
?? identity column, Not For Replication
Thanx!