Need to alter identity property

I need to alter all our identity columns to be not for replication (currently they do not have this condition). I checked how Microsoft is performing this task by recording a script while doing this manually in SSMS. They simply create another table with identity column not for replication, pour data into new table, drop the old one and sp_rename for new one. This is not the case for us - some our tables have billions of records so we can't afford keeping these tables off-line for so long.

Are there any ways to alter it ? Any wok arounds, except directly updating sys tables which is not recommended ?

Thanks

There is no TSQL statement to modify identity column properties or add not for replication to existing column. There might be a replication system SP that will do this for you. So I am moving this thread over to the SQL Server Replication forum.|||

I believe you can do it using T-SQL: You probably need sp4 for this. I know it works in SQL 2005. I was told it was added in SP4. Check it out though.

alter table dbo.yourTable

alter column [yourIDColumn] add NOT FOR REPLICATION

|||

Thanks a lot Dinakar, that's very helpful.

Is there similar scripts for check and foreign keys constraints ?

Need interface for displaying condition of subscriber DB to users

Does anyone know how to create something like this ...
I have transactional replication set up to maintain copies of several
production databases (located in regional offices) in our corporate office.
The users in the corporate office use the copies for reporting.
Transactional replication is set to push every 30 mins, but with WAN
connections sometimes being down, that might not always be the case. I want
to provide the corporate office users with a simple display of how current
the copies are. For example:
"The Sydney reporting database was last synchronized with the live database
22 mins ago.
The Melbourne reporting database was last synchronized with the live
database 1 day 2 hours 15 mins ago."
etc
Ideally this would take the form of a web page. Any SQL scripts out there
that can give me this data?
on your publisher issue the following in your master database
sp_serveroption 'SubscriberServerName','data access','true'
and then in your distribution database run the following
select time, entry_time from
SubScriberServerName.SubscriberDatabaseName.dbo.MS replication_subscriptions,
msrepl_transactions
where transaction_timestamp=xact_seqno
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:uzgQp%23huEHA.2096@.tk2msftngp13.phx.gbl...
> Does anyone know how to create something like this ...
> I have transactional replication set up to maintain copies of several
> production databases (located in regional offices) in our corporate
office.
> The users in the corporate office use the copies for reporting.
> Transactional replication is set to push every 30 mins, but with WAN
> connections sometimes being down, that might not always be the case. I
want
> to provide the corporate office users with a simple display of how current
> the copies are. For example:
> "The Sydney reporting database was last synchronized with the live
database
> 22 mins ago.
> The Melbourne reporting database was last synchronized with the live
> database 1 day 2 hours 15 mins ago."
> etc
> Ideally this would take the form of a web page. Any SQL scripts out there
> that can give me this data?
>
|||Thanks, that works.
Is there any way to get this information from the subscriber? Our
distribution databases are on the same server as the publishers, and
therefore subject to the same problem that this solution is alerting people
to - that the WAN link is sometimes down.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u3eLn8iuEHA.2016@.TK2MSFTNGP15.phx.gbl...
> on your publisher issue the following in your master database
> sp_serveroption 'SubscriberServerName','data access','true'
> and then in your distribution database run the following
> select time, entry_time from
> SubScriberServerName.SubscriberDatabaseName.dbo.MS replication_subscriptions,
> msrepl_transactions
> where transaction_timestamp=xact_seqno
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:uzgQp%23huEHA.2096@.tk2msftngp13.phx.gbl...
> office.
> want
> database
>
|||This proc is run on your publisher and queries your subscriber. You could
also run it on your subscriber and query the publisher like this
On your subscriber run this:
sp_serveroption 'PublisherServerName','data access','true'
select time, entry_time from
MSreplication_subscriptions,
PublisherServerName.distribution.dbo.msrepl_transa ctions
where transaction_timestamp=xact_seqno
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%23w96THnuEHA.2172@.TK2MSFTNGP14.phx.gbl...
> Thanks, that works.
> Is there any way to get this information from the subscriber? Our
> distribution databases are on the same server as the publishers, and
> therefore subject to the same problem that this solution is alerting
people[vbcol=seagreen]
> to - that the WAN link is sometimes down.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u3eLn8iuEHA.2016@.TK2MSFTNGP15.phx.gbl...
SubScriberServerName.SubscriberDatabaseName.dbo.MS replication_subscriptions,[vbcol=seagreen]
there
>
|||OK thats good, I was just wondering if there was a way of getting the
information from the subcriber only - in the event that the distributor or
publisher were unreachable.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ulXfReouEHA.444@.TK2MSFTNGP10.phx.gbl...
> This proc is run on your publisher and queries your subscriber. You could
> also run it on your subscriber and query the publisher like this
> On your subscriber run this:
> sp_serveroption 'PublisherServerName','data access','true'
> select time, entry_time from
> MSreplication_subscriptions,
> PublisherServerName.distribution.dbo.msrepl_transa ctions
> where transaction_timestamp=xact_seqno
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:%23w96THnuEHA.2172@.TK2MSFTNGP14.phx.gbl...
> people
> SubScriberServerName.SubscriberDatabaseName.dbo.MS replication_subscriptions,
> there
>