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 to add a table in merge replication

Hi,
I would like to add a new table in merge replication (200 tables already in
replication).
I followed the steps.
Step 1: In publication properties I included the new table
Step 2: System shows the following messages
One or more tables have already been published. Do you want to update those
articles with the new default?
I gave ‘NO’
Step 3: When I click Apply it shows the following message.
SQL Server Enterprise Manager could not change the properties of article
‘Table name’
Based on object ‘Table name’.
Do you want to continue saving other changes to the publication?
Error 21416: Property ‘destination_owner’ of article ‘Table Name’ cannot be
changed.
Since I have 200 tables in merge replication it asks for 200 times and I
have ‘Yes’ and finally it shows the following error message.
SQL Server Enterprise Manager could not create article ‘NewTablename’ on
object ‘NewTablename’.
Do you want to continue saving other changes to the publication?
Error 20086: Publication ‘DatabaseName’ does not support the nosync type
because it contains a table that does not have a rowguidcol column.
Finally,
I created a new column ‘rowguid’ after that followed the same steps.
Its working fine.
Need clarification:
1.Should I create a similar table in subscriber also? (else it replication
fails)
2.Am I going in a correct way?
Please advise
Thanks,
Soura.
Sometimes the GUI is not your friend. This is one of these cases. I think
what is going on is that this new table is related via DRI to some of the
other tables and the tracking metadata has to be updated. This means a new
snapshot; but for some reason - possibly a bug you are unable to do this
through the GUI.
Your options won't work. Basically you are creating two tables which are
configured somewhat correctly (but not completely) for merge replication,
but are missing the necessary merge replication metadata in the system
tables
What I would do is create a seperate publication. This could be problematic
if you have a lot of subscribers.
Another option which probably is the way to go is to use sp_addmergearticle
like this
sp_addmergearticle 'northwind2','authors','authors',
@.force_invalidate_snapshot=1
This will generate a mini snapshot with just the authors table in it.
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
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:825361FD-DF36-4D78-9E79-6938A34A9CE2@.microsoft.com...
> Hi,
> I would like to add a new table in merge replication (200 tables already
in
> replication).
> I followed the steps.
> Step 1: In publication properties I included the new table
> Step 2: System shows the following messages
> One or more tables have already been published. Do you want to update
those
> articles with the new default?
> I gave 'NO'
> Step 3: When I click Apply it shows the following message.
> SQL Server Enterprise Manager could not change the properties of article
> 'Table name'
> Based on object 'Table name'.
> Do you want to continue saving other changes to the publication?
> Error 21416: Property 'destination_owner' of article 'Table Name' cannot
be
> changed.
> Since I have 200 tables in merge replication it asks for 200 times and I
> have 'Yes' and finally it shows the following error message.
> SQL Server Enterprise Manager could not create article 'NewTablename' on
> object 'NewTablename'.
> Do you want to continue saving other changes to the publication?
> Error 20086: Publication 'DatabaseName' does not support the nosync type
> because it contains a table that does not have a rowguidcol column.
> Finally,
> I created a new column 'rowguid' after that followed the same steps.
> Its working fine.
> Need clarification:
> 1. Should I create a similar table in subscriber also? (else it
replication
> fails)
> 2. Am I going in a correct way?
> Please advise
> Thanks,
> Soura.
>
|||Actually it seems to generate a completely new snapshot.
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
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:825361FD-DF36-4D78-9E79-6938A34A9CE2@.microsoft.com...
> Hi,
> I would like to add a new table in merge replication (200 tables already
in
> replication).
> I followed the steps.
> Step 1: In publication properties I included the new table
> Step 2: System shows the following messages
> One or more tables have already been published. Do you want to update
those
> articles with the new default?
> I gave 'NO'
> Step 3: When I click Apply it shows the following message.
> SQL Server Enterprise Manager could not change the properties of article
> 'Table name'
> Based on object 'Table name'.
> Do you want to continue saving other changes to the publication?
> Error 21416: Property 'destination_owner' of article 'Table Name' cannot
be
> changed.
> Since I have 200 tables in merge replication it asks for 200 times and I
> have 'Yes' and finally it shows the following error message.
> SQL Server Enterprise Manager could not create article 'NewTablename' on
> object 'NewTablename'.
> Do you want to continue saving other changes to the publication?
> Error 20086: Publication 'DatabaseName' does not support the nosync type
> because it contains a table that does not have a rowguidcol column.
> Finally,
> I created a new column 'rowguid' after that followed the same steps.
> Its working fine.
> Need clarification:
> 1. Should I create a similar table in subscriber also? (else it
replication
> fails)
> 2. Am I going in a correct way?
> Please advise
> Thanks,
> Soura.
>
sql

Need support/help to setup replication

Hello,
Anybody know who I might call to pay for help setting up replication. I am
under a time constraint.
Rick Rushing
System Administrator
D & J Construction
What type of replication, and how long do you have?
Describe the scenario please :-)
I am available by the hour or by the minute...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Rick Rushing" <RickRushing@.discussions.microsoft.com> wrote in message
news:0EBE4237-238C-48DD-AA7C-E9096AC4B9C4@.microsoft.com...
> Hello,
> Anybody know who I might call to pay for help setting up replication. I am
> under a time constraint.
> --
> Rick Rushing
> System Administrator
> D & J Construction
|||Rick,
where abouts are you geographically? If in the UK or you're happy working
remotely, please drop me a line - email address at
www.replicationanswers.com.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Need Repliation Help ASAP Please

Steve,
both VPN and internet replication are possible. Have a
look at this article for more info:
http://support.microsoft.com/?id=321822
If you don't use a VPN then you'll need to do FTP
initialization and use SQL Server security.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Paul, this is perfect. Thanks for your help and the info. The only thing
that concerns me now is that I was up on Microsoft a few times and never
ran across this document.
I guess I need to practice my search skills too!!
Thanks,
Steve
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Need Query For 'Enforce relationship fo replication'

Hi,
I am dealing with merge replication.
In enterprise manager --> design --> relationship - there is an option
'Enforce relationship for replication'.
Is it possible to identify the status (whether checked/unchecked) of this
option for a particular table through query? I need to identify this for all
the tables in the database.
Please advise
Thanks,
Soura
Soura,
I keep a list of this type of query at
http://www.replicationanswers.com/Scripts.asp. For your needs, I think this
should be it...
select table_name, constraint_name from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where constraint_type = 'foreign key'
and objectproperty(object_id(constraint_name),'CnstIsN otRepl') = 0
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thank you Paul its working fine.
Thanks,
Soura
"Paul Ibison" wrote:

> Soura,
> I keep a list of this type of query at
> http://www.replicationanswers.com/Scripts.asp. For your needs, I think this
> should be it...
> select table_name, constraint_name from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> where constraint_type = 'foreign key'
> and objectproperty(object_id(constraint_name),'CnstIsN otRepl') = 0
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>

Need Optimal Schedule for Snapshot and Merge Replications

I need to schedule a snapshot replication of some tables and a merge
replication of a couple other tables. I would like to know the best
way to schedule the various replication agents.
My questions are:
- Should I arrange the schedule in this order:
Step 1. Run Snapshot Agent for snapshot replication
Step 2. Run Distribution Agents: one for each branch office
Step 3. Run Snapshot Agent for merge replication
Step 4. Run Merge Agents: one for each branch office
The reason I ask is that I am under the impression that
Distribution Agent has everything to do with snapshot
replication and has nothing to do with merge replication.
Therefore, I may want to run Distribution Agent right
after Snapshot Agent for snapshot replication -- just to
group related tasks together and out of the way.
Is my understanding correct?
What is the correct order anyway?
- I have one Distribution Agent for each branch office.
Likewise, I have one Merge Agent for each branch office.
I have two branch offices; this means I have two
Distribution Agents and two Merge Agents.
Should I start the two Distribution Agents at the same time?
Should I start the two Merge Agents at the same time?
Of course, I could have separate those two Distribution
Agents in two different time slots. But I don't want to do
this in order to avoid keeping track with two different sets
of schedules.
I have a feeling that I should be able to run those two agents
at the same time because the bottleneck is the T1 line between
the central office and each branch office. Is my understanding
correct?
Thanks in advance for any info.
Jay Chan
In general you can run all agents simultaneously if you have a small number of subscribers.
You can leave the distribution and merge agents running continoulsy and they will detect the new snapshot is available for distribution and will distribute it.
Many DBAs like to
1)run their distribution and merge agents in continuous loops
2) or schedule them to restart every 10 minutes or so and have them run continoss
This makes them more resilitent to failure.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

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
>

NEED HELP! SQL Merge Replication

I have a merge replication process that is having some major performance issues. The replication process seems to run just fine for about a half hour (200k records), and then it hits a brick wall. I continually get the message:
"The process is running and is waiting for a response from one of the backend connections"
Once I get this message, the process will then go through 4 more batch downloads (2000 records per batch), and then give me that message 2 or 3 more times and then continue to download. This appears to only happen on a specific table. I have exhausted m
y thoughts on this and would really appreciate ANY help.
The subscriber is running MSDE 2000 on a pentium 3 1.7GHtz machine with 1GB of RAM.
Thank you,
Thanks!
Tony D
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
Set QueryTimeout to a larger value. You might want to post replication
related questions to microsoft.public.sqlserver.replication
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Tony DiGiorgio" <tony.digiorgio@.immedient.com> wrote in message
news:Okua7om$EHA.2568@.TK2MSFTNGP11.phx.gbl...
>I have a merge replication process that is having some major performance
>issues. The replication process seems to run just fine for about a half
>hour (200k records), and then it hits a brick wall. I continually get the
>message:
> "The process is running and is waiting for a response from one of the
> backend connections"
> Once I get this message, the process will then go through 4 more batch
> downloads (2000 records per batch), and then give me that message 2 or 3
> more times and then continue to download. This appears to only happen on
> a specific table. I have exhausted my thoughts on this and would really
> appreciate ANY help.
> The subscriber is running MSDE 2000 on a pentium 3 1.7GHtz machine with
> 1GB of RAM.
> Thank you,
> Thanks!
> Tony D
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
> ASP.NET resources...