remove article from trans replication?

Hi,
Using sql server 2005, we've got transactional replication going between two
servers. if i need to remove a couple of tables from the replication, do i
have to reinitialize the snapshot every time i do that? or is there an
easier way to achieve this?
thanks for any help on this,
Fred
Hi Paul, and thanks for your response,
I've checked out the page your referenced, and because I'm just learning
about replication, it's not clear to me if it applies in our situation, and
also to Sql Server 2005. So, allow me to ask again with more details.
In our next release procedure, we're changing the name of a dozen fields in
a dozen tables. some of them are primary key fields. we're also dropping a
couple of constraints. (I had thought we were dropping tables but just
learned differently.) Can these things be done without reinitializing the
snapshot.
what I've done this time, is to remove all the tables from replication, and
reinit the snapshot. Now the developer can make the schema changes
mentioned above, and then immediately after the release, I'll add the tables
back into the replication and reinit the snapshot again. Not pretty, but it
works. but that's why I'm asking about a way to make changes and have them
propagated thru replication, without breaking the replication.
I hope this is clear, and makes some sense! thanks for your thoughts.
Fred
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:enB1dfLsHHA.3492@.TK2MSFTNGP02.phx.gbl...
> Please take a look here: http://www.replicationanswers.com/AddColumn.asp
> HTH,
> Paul Ibison
>
|||Hi Paul,
If you're still reading this, might it work to turn off DDL replication for
the publication, make the changes, then turn DDL replication back on?
I'm starting to implement via stored procedures now...
thanks,
Fred
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OGPTFsSsHHA.3480@.TK2MSFTNGP02.phx.gbl...
> The way involving the least amount of work for SQL Server would be to drop
> the subscriptions to those tables which you are going to change radically
> (several fields and/or changing the PK fields) then drop the articles.
> Make the changes then add the articles, add the subscription and then run
> the snapshot agent and distribution agent. This effectively is a
> reinitialization of just those articles you are changing. For minor
> changes you'd use sp_repladdcolumn and sp_repldropcolumn.
> HTH,
> Paul Ibison
>

Remove Article from Merge replication

Hi NG
Publisher:
Win 2003 Server
SQL 2000 - SP3a
Subscriber:
Win 2000 - SP4
SQL 2000 - SP3a
After many headaches with dog servers and virtually non-existent bandwidth,
I have finally got my Merge Replication up and running OK.
However, I have inadvertently selected 2 tables for replication which should
not be replicated. The reason for this is that these tables are used as
staging tables when consolidating large amounts of data. They get 200,000
inserts/updates/deletes per day. Normally we only expect to be replicating
1,000 inserts/updates/deletes per day.
Is there a way to remove these 2 articles form the replication process.
If not, I will simply have to create 2 new tables, and ensure that all
references to the existing tables are changed to the new non-replicated
tables. However, I dislike workarounds and orphan items.
I have seen a couple of posts indicating sp_MSunmarkreplinfo. However, I
have been unable to find any reference to this SP in Books online.
Regards
Des Norton
Des,
unlike transactional replication this is not possible via normal means. Your
best bet is to drop the publication and recreate without the 2 tables. This
can be made more palatable - especially if you have large tables - by doing a
nosync one, provided you can prevent updates to the data during this period.
The other option is to disable the merge triggers on these tables, but again
this is a bit of a hack.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Kick the users off the subscribers, drop the subscriptions, drop the
articles using sp_dropmergerarticle, or by right clicking on your
publication and selecting properties, then in the articles tab uncheck the
articles you wish to drop.
Then redeploy your subscribers doing a no-sync.
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
"Des Norton" <desREMOVEnortonUPPERCASE@.gmail.com> wrote in message
news:uncu3HIAGHA.140@.TK2MSFTNGP12.phx.gbl...
> Hi NG
> Publisher:
> Win 2003 Server
> SQL 2000 - SP3a
> Subscriber:
> Win 2000 - SP4
> SQL 2000 - SP3a
>
> After many headaches with dog servers and virtually non-existent
> bandwidth, I have finally got my Merge Replication up and running OK.
> However, I have inadvertently selected 2 tables for replication which
> should not be replicated. The reason for this is that these tables are
> used as staging tables when consolidating large amounts of data. They get
> 200,000 inserts/updates/deletes per day. Normally we only expect to be
> replicating 1,000 inserts/updates/deletes per day.
> Is there a way to remove these 2 articles form the replication process.
> If not, I will simply have to create 2 new tables, and ensure that all
> references to the existing tables are changed to the new non-replicated
> tables. However, I dislike workarounds and orphan items.
> I have seen a couple of posts indicating sp_MSunmarkreplinfo. However, I
> have been unable to find any reference to this SP in Books online.
>
> Regards
> Des Norton
>

Remove Article

sp_droparticle

>--Original Message--
>How do I remove a table (aka article) from replication?
I have 19 tables replicating to 15 servers, i need to
remove 4 of the 19 tables from the replication. How?
>Thanks so much for your assistance,
>Adam
>.
>
(applies to snapshot or transactional)
Paul
sql

Remote database

How can I connect to a remote database, based on Microsoft sql server? Is there any article that can help? I have read few over the net, but not working for me yet

Try this link

http://plutonium.cs.umanitoba.ca/ntlabs/MSSQLserver/MSSQLserver-home.html

|||

Prashant Kumar:

Try this link

http://plutonium.cs.umanitoba.ca/ntlabs/MSSQLserver/MSSQLserver-home.html

What I really wanted here is connecting to remote server (via IP address). I'm running windows xp. Which should be something that has to be configured in my connection string

|||

Refer to the following

http://connectionstrings.com/?carrier=sqlserver2005

search for "Connect via an IP address"