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
>

Remote connection problems

Hi
I'm having problems connecting remotely to my sql 2005 server
sql instance is atl64\compaq
ip is x
and i would like to use a different port let's say 62322
how do i configure my server and how do i connect using either sql
query analyzer or Sql server management studio
i tried to :
set the port to 62322 in sql server configuration manager
set local and remote connection using tcp/ip only in remote connection
in surface area connection
i tried to connect to server management studio with
server name : ipadress,62322
or
server name : ipadress:62322
both not working
i still get error ... sql does not allow remote connection ...
any help
thanks
Forgot to mention
xp firewall if off
router will route port 62322 to 1433 (i tried with routing and without
nothing works)
also what would be the correct server name to connect using a different
port ?
servername,port
or
servername:port
thanks
frederic.nou...@.gmail.com wrote:
> Hi
> I'm having problems connecting remotely to my sql 2005 server
> sql instance is atl64\compaq
> ip is x
> and i would like to use a different port let's say 62322
> how do i configure my server and how do i connect using either sql
> query analyzer or Sql server management studio
> i tried to :
> set the port to 62322 in sql server configuration manager
> set local and remote connection using tcp/ip only in remote connection
> in surface area connection
> i tried to connect to server management studio with
> server name : ipadress,62322
> or
> server name : ipadress:62322
> both not working
> i still get error ... sql does not allow remote connection ...
> any help
> thanks
|||The "sql does not allow remote connection" error makes me think that you
need to restart SQL Server. It doesn't listen on the new port until
restarted.
Also, you can check the error log to see what port it's listening on.
servername,port should do the trick.
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
<frederic.nourry@.gmail.com> wrote in message
news:1138697240.871053.309700@.g44g2000cwa.googlegr oups.com...
> Forgot to mention
> xp firewall if off
> router will route port 62322 to 1433 (i tried with routing and without
> nothing works)
> also what would be the correct server name to connect using a different
> port ?
> servername,port
> or
> servername:port
> thanks
>
> frederic.nou...@.gmail.com wrote:
>
|||Thanks Rick
looking at the event viewer I found that the server is listening to
2414
change to server,2414 worked fine
i noticed that, that is the port configured under IPAll, dynamic ports
in server configuration manager
shouldn't i change the port for ip1 and ip2 ?
thanks now i can work
|||This is a confusing aspect of the tool. If the TCP/IP Properties, Protocol
tab has Listen All as Yes, then the individual settings for IP1 and IP2 are
not necessary. If you want to make do different things, you must first
change Listen All to No, and then change the IP settings.
And since one of the IP addresses is the loopback adapter 127.0.0.1, which
isn't visible over the network, you can ignore that. So right now, it sounds
like you are listening on dynamic ports and it is currently listening on
port 2414. If you stop and restart SQL Server, it might come up on a
different port. If that's a problem, remove the 0 from TCP Dynamic Ports,
and pick a port number you like, and then restart SQL Server.
Sorry this is confusing. SP1 will have some new wording in the help topic to
try and clarify this. (No, I don't know when SP1 will be released. <g>)
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
<frederic.nourry@.gmail.com> wrote in message
news:1138768634.322274.24780@.o13g2000cwo.googlegro ups.com...
> Thanks Rick
> looking at the event viewer I found that the server is listening to
> 2414
> change to server,2414 worked fine
> i noticed that, that is the port configured under IPAll, dynamic ports
> in server configuration manager
> shouldn't i change the port for ip1 and ip2 ?
> thanks now i can work
>