Identity columns

Hi,
Quick question, Can I use the 'Alter table mytable alter column' to
change the Identity column to include the 'not for replication' clause? Or
do I need to drop and recreated the table?
thanks,
Nope, but you can use 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
this will fix your identity keys on all of your tables for the not for
replication switch
If you want to limit it to a set of tables you are best to do 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 and id=object_id('tablename')
go
sp_configure 'allow updates', 0
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
"Joe" <jkdriscoll@.qg.com> wrote in message
news:d2hjin$2kfu$1@.sxnews1.qg.com...
> Hi,
> Quick question, Can I use the 'Alter table mytable alter column' to
> change the Identity column to include the 'not for replication' clause? Or
> do I need to drop and recreated the table?
> thanks,
>
|||Hilary,
Thank you for the response. I can't begin to tell you how much work this
will save me. We've had this one SQL Server, that is production and was
originally only used a little bit. But, as you can guess, over time it's
grown in use. Now we need to set up replication. And what a project this will
be. This server now as 23 production databases, none of which where designed
with replication in mind.
So, once again I say Thanks.
Joe.
"Hilary Cotter" wrote:

> Nope, but you can use 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
>
> this will fix your identity keys on all of your tables for the not for
> replication switch
>
> If you want to limit it to a set of tables you are best to do 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 and id=object_id('tablename')
> go
> sp_configure 'allow updates', 0
>
> --
> 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
> "Joe" <jkdriscoll@.qg.com> wrote in message
> news:d2hjin$2kfu$1@.sxnews1.qg.com...
>
>
|||I have thought of a follow up question.
Will this sql affect any current or future Identity column values?
thanks again.
Joe
"Hilary Cotter" wrote:

> Nope, but you can use 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
>
> this will fix your identity keys on all of your tables for the not for
> replication switch
>
> If you want to limit it to a set of tables you are best to do 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 and id=object_id('tablename')
> go
> sp_configure 'allow updates', 0
>
> --
> 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
> "Joe" <jkdriscoll@.qg.com> wrote in message
> news:d2hjin$2kfu$1@.sxnews1.qg.com...
>
>
|||no it won't.
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
"JD" <John316@.online.nospam> wrote in message
news:3715E8B9-22F3-40EB-BDBF-09036E45A71D@.microsoft.com...[vbcol=seagreen]
> I have thought of a follow up question.
> Will this sql affect any current or future Identity column values?
> thanks again.
> Joe
> "Hilary Cotter" wrote:
and[vbcol=seagreen]
and[vbcol=seagreen]
to[vbcol=seagreen]
clause? Or[vbcol=seagreen]