Renaming a subscriber

Are there any "gotchas" to renaming a database (sp_renamedb) that is a subscriber to merge replication. I can't seem to find much info on this on google.
Yes. The merge agent will fail because it cannot find the original database.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Rename SQL Agent Jobs for Replication

I have SEVERAL replication publications/subscriptions on a server.
The names that are assigned to these jobs are vague and cryptic at best. I
went ahead and renamed them to something a little more telling. I noe get the
following error:
TITLE: Replication Monitor
Replication Monitor could not start the job
'100602-DB4-NavigatorSuiteReplicate-NavigatorSuiteReplicate_TPA3-28'.
The specified @.job_name
('100602-DB4-NavigatorSuiteReplicate-NavigatorSuiteReplicate_TPA3-28') does
not exist. (Microsoft SQL Server, Error: 14262)
Is there some other place I need to update the names of these jobs? Or do I
just need to go back and name them what they were before?
Thanks!
have a look at MSsnapshot_agents
MSlogreader_agents
MSdistribution_agents
MSmerge_agents
MSqreader_agents
in the distribution database,
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
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:D4EE3A17-FEE2-40C4-809F-9AD799E52E27@.microsoft.com...
>I have SEVERAL replication publications/subscriptions on a server.
> The names that are assigned to these jobs are vague and cryptic at best. I
> went ahead and renamed them to something a little more telling. I noe get
> the
> following error:
>
> TITLE: Replication Monitor
> --
> Replication Monitor could not start the job
> '100602-DB4-NavigatorSuiteReplicate-NavigatorSuiteReplicate_TPA3-28'.
> The specified @.job_name
> ('100602-DB4-NavigatorSuiteReplicate-NavigatorSuiteReplicate_TPA3-28')
> does
> not exist. (Microsoft SQL Server, Error: 14262)
> Is there some other place I need to update the names of these jobs? Or do
> I
> just need to go back and name them what they were before?
> Thanks!
>
sql

Removing system replication tables.

Hi, i restored a backup from a database thas has replication configured.
When i restored it the system tables that the merge replication creates
are restored too. I was investigating on internet and I found that i can
delete it using this query:
sp_configure 'allow updates', 1
go
reconfigure with override
go
DROP TABLE aonflict_SiacDataEEC_security_info
...
sp_configure 'allow updates', 0
go
reconfigure with override
go
Do somebody know if i use this queries to delete this tables i can
damage the database or is correct to use it.
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***
Maria,
most system metadata is removed by sp_removedbreplication, but this is an
exception and it is common practice to drop these tables manually as part of
a cleanup.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||If the database is no longer replicating then it will not damage the
database to delete the old replication objects.
In fact, if you do not delete the replication triggers you will
probably encounter problems.
|||I agree. sp_MSdroparticletriggers is called from sp_removedbreplication but
has been known to not work in rare circumstances. I have a script which
removed redundant duplicate triggers that may be of interest in these cases:
http://www.replicationanswers.com/Script8.asp
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Removing system replication tables.

Hi, i restored a backup from a database thas has replication configured.
When i restored it the system tables that the merge replication creates
are restored too. I was investigating on internet and I found that i can
delete it using this query:

sp_configure 'allow updates', 1
go
reconfigure with override
go

DROP TABLE aonflict_SiacDataEEC_security_info
...

sp_configure 'allow updates', 0
go
reconfigure with override
go

Do somebody know if i use this queries to delete this tables i can
damage the database or is correct to use it.

Thanks a lot for your help.

*** Sent via Developersdex http://www.developersdex.com ***You might get a better response in
microsoft.public.sqlserver.replication

Simon

Removing replications

I have two servers (let's call them SQL01 and SQL02), and SQL01 is our main SQL-server.

I have been doing som replication to SQL02, and I want to remove ALL replications. I have an publication on a database called DB1.

The distributor is set to be SQL02.

I used the wizard 'disable....', and stupid as I was I removed the distributor before removing the publications.

Something went wrong, and now the distributor is removed, but the publications are still there (not working, but they are there).

When I try to remove them I get the error:

'Error 2812: Could not find stored procedure ''

I am in deep trouble, and I hope that someone can help me with the problem.

Best regards...
SrenGo to the menu Tools -> Replication -> Generate SQL Script (select disable/drop components). You can use this to run to drop replication. Follow the stored procedures as listed in the order they are provided.|||Hi,
Thanks for the answer, but when I try to do that, I get the same error...

I have really messed things up, and I you have other suggestions, I would really thankful.

Sren|||Were you able to generate the script ? If so, run the script line by line - let me know where it fails - that will give us an indication as to what is broken.|||Hi,
Sorry for not writing back earlier. I have been on a businesstrip to Finland.

I unfortunately still have the problem, and if I uncheck "Distributor attributes", then I can generate the following script:

<B> use [MarliDK]
GO

-- Dropping the merge subscription
exec sp_dropmergesubscription @.publication = N'MarliDK_NEW', @.subscriber = N'ODSSQL02', @.subscriber_db = N'MarliDK', @.subscription_type = N'push'
GO

-- Dropping the merge publication
exec sp_dropmergepublication @.publication = N'MarliDK_NEW'
GO

-- Disabling the replication database
use master
GO

exec sp_replicationdboption @.dbname = N'MarliDK', @.optname = N'merge publish', @.value = N'false'
GO
</B>

When i Run the first statement I get the following:

<B> Server: Msg 2812, Level 16, State 62, Procedure sp_dropmergesubscription, Line 401
Could not find stored procedure ''.
Server: Msg 14056, Level 16, State 1, Procedure sp_dropmergesubscription, Line 444
The subscription could not be dropped at this time.</B>

I would like to thank you many times for trying to help me. I really appreciate that.

Best regards and greetings from Denmark
Sren

Removing replication while db in use?

Hello,
Can you remove replication while the publisher to be
removed is in use or will that cause problems?
Any help would be greatly appreciated!
Thanks in advance.
yes, you can do this through tools - replication - configure publishers,
subscribers, and distributor, click on the published databases, and clear
the merge or transactional check boxes.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Jim V." <anonymous@.discussions.microsoft.com> wrote in message
news:28f1a01c4643a$5bed5de0$a301280a@.phx.gbl...
> Hello,
> Can you remove replication while the publisher to be
> removed is in use or will that cause problems?
> Any help would be greatly appreciated!
> Thanks in advance.

Removing Replication Triggers.

Does anyone know how to remove 'Replication Triggers'?

I have a database created by export of data object. This process does not export any system tables to do with replication but there still seems to be something in the database which references them. There is no poblem if I backup and restore the dataabse.

Any advice?

Thanks Edno such monkey as a replication trigger. My guess here is that you replicating a table with triggers associated with it and when the publisher was defined the table in question had a trigger that did not have the trigger defined with the NOT FOR REPLICATION option and now you are getting conflicts.

If this is the case, ALTER TRIGGER should set you on the right path. Look up it's arguments in Books Online.

If this is not the case, why do you think triggers are the problem?

removing replication table

Hi all,

Can Enterprise Manager remove a table from replicating to another server? I've tried looking at the article tab of the property of the publication but couldn't find a way to delete a table from the article.

Any idea?

Thanks!
Hi Alan,

In SQL Server 2000, if there is an active subscription or the publication allows anonymous subscriptions, you will not be able to drop an article either through Enterprise manager or through scripts.

This has been relaxed in SQL Server 2005 and you can freely add/drop articles and in some cases you need to reinitialize the subscriptions.|||Many thanks for the info!
|||

Hello Mahesh,

Background:

I installed MS CRM 1.2 last year for the company to test out. When MS released CRM 3.0 to us (we are part of the MS PP via the Action Pack) I tried installing that. I had a database issue (error on my part in SQL Server with null values) that I corrected. However when I went back into to reinstall CRM 3.0 the app complained about database replication. I have not used replication at anytime prior to any of this. So when I went into the database to look sure enough a database MyCompanyName_MSCRMDistrubution was there and had database replication on it. However when I looked into the tables there was nothing in there (no publishers, no subscribers, null, ect). So I first tried to us the MS KB article on removing database replication however the first couple of stored_procs need pubs and subs params (args) which I could not include. So I then found a stored_proc named sp_removedreplication which I ran successfully. However through EM the offending database still is showing replication and hence I can not detach and remove it.

Someone mentioned that I should remove the MyCompanyName_METABASE (which I can at this point) to see if this helped. Another mentioned I should open up a Support Case with MSB.

Before opening up a case do you have any recommendations? (or anyone else for that matter)

Thank you in advance.

|||

Not sure if this is a recommended approach but you can try and update the category column in the sysdatabases table (for your db) to 0 and that should take care of the problem. Also have the Allow modifications to be made directly to the system catalogs option checked before you do the update.

update sysdatabases

set category = 0

where name = <your database name>

|||

A better way is to try:

If using Merge replication:

exec sp_replicationdboption 'MyCompanyName_MSCRMDistrubution ', 'merge publish', 'false'

If Tran replication:

exec sp_replicationdboption 'MyCompanyName_MSCRMDistrubution ', 'publish', 'false'

See if that serves the purpose.

|||

Mahesh,

I tried this and it ran successfully, but to no avail. The replication was still on at this point. However see my replied to Sachin

|||

Sachin,

Thank you. What I did was the following:

1. I ran Mahesh stored_proc for merged replication, it ran fine but the replication was still there.

2. I attempted to run your script above but I did not have ad hoc updates on.

3. Being curious I rans some simple selects and verifyed that you were correct that for that particular db and the category was a value of 16. So after turning on the ad hoc updates I created an update table and changed the cat to 0.

4. Then I created a drop database query and ran that.

5. That dropped the table from the system just fine!

Thank you.

|||

Don,

I am glad I could help. :)

|||

Sachin,

Me too. Step 5 should have been "dropped the database" instead of table.

Best Regards~

Don

removing replication table

Hi all,

Can Enterprise Manager remove a table from replicating to another server? I've tried looking at the article tab of the property of the publication but couldn't find a way to delete a table from the article.

Any idea?

Thanks!
Hi Alan,

In SQL Server 2000, if there is an active subscription or the publication allows anonymous subscriptions, you will not be able to drop an article either through Enterprise manager or through scripts.

This has been relaxed in SQL Server 2005 and you can freely add/drop articles and in some cases you need to reinitialize the subscriptions.|||Many thanks for the info!
|||

Hello Mahesh,

Background:

I installed MS CRM 1.2 last year for the company to test out. When MS released CRM 3.0 to us (we are part of the MS PP via the Action Pack) I tried installing that. I had a database issue (error on my part in SQL Server with null values) that I corrected. However when I went back into to reinstall CRM 3.0 the app complained about database replication. I have not used replication at anytime prior to any of this. So when I went into the database to look sure enough a database MyCompanyName_MSCRMDistrubution was there and had database replication on it. However when I looked into the tables there was nothing in there (no publishers, no subscribers, null, ect). So I first tried to us the MS KB article on removing database replication however the first couple of stored_procs need pubs and subs params (args) which I could not include. So I then found a stored_proc named sp_removedreplication which I ran successfully. However through EM the offending database still is showing replication and hence I can not detach and remove it.

Someone mentioned that I should remove the MyCompanyName_METABASE (which I can at this point) to see if this helped. Another mentioned I should open up a Support Case with MSB.

Before opening up a case do you have any recommendations? (or anyone else for that matter)

Thank you in advance.

|||

Not sure if this is a recommended approach but you can try and update the category column in the sysdatabases table (for your db) to 0 and that should take care of the problem. Also have the Allow modifications to be made directly to the system catalogs option checked before you do the update.

update sysdatabases

set category = 0

where name = <your database name>

|||

A better way is to try:

If using Merge replication:

exec sp_replicationdboption 'MyCompanyName_MSCRMDistrubution ', 'merge publish', 'false'

If Tran replication:

exec sp_replicationdboption 'MyCompanyName_MSCRMDistrubution ', 'publish', 'false'

See if that serves the purpose.

|||

Mahesh,

I tried this and it ran successfully, but to no avail. The replication was still on at this point. However see my replied to Sachin

|||

Sachin,

Thank you. What I did was the following:

1. I ran Mahesh stored_proc for merged replication, it ran fine but the replication was still there.

2. I attempted to run your script above but I did not have ad hoc updates on.

3. Being curious I rans some simple selects and verifyed that you were correct that for that particular db and the category was a value of 16. So after turning on the ad hoc updates I created an update table and changed the cat to 0.

4. Then I created a drop database query and ran that.

5. That dropped the table from the system just fine!

Thank you.

|||

Don,

I am glad I could help. :)

|||

Sachin,

Me too. Step 5 should have been "dropped the database" instead of table.

Best Regards~

Don

removing replication on SP

I have my own SP in the master database that had sp_MS_marksystemobject applied to it. It now needs revising but it cannot be renamed. It cannot be dropped because it is used for replication (apparently).
When I try to drop it, get message: Server: Msg 3724, Level 16, State 2, Line 1
Cannot drop the procedure '<sp_name>' because it is being used for replication.
Anyone know how the replication can be removed
use sp_MSunmarkreplinfo against this proc and then drop it from within isqlw.

Removing Replication MSSQL 2000

Hi Guys, I have recently removed replication from the SQL server (2k)
and everything seemed to be working at first. Initially, the database
still contained all the conflict tables etc... but I got rid of all
those. However, now, when attempting to insert into some tables in the
database, they are failing without errors. If I re-enable replication
for the database the inserts and updates work fine. Anyone ever heard
of this? Any help or suggestions would be appreciated. Thanks!
Regards,
Troy
Troy,
please try running sp_removedbreplication and also take a look at the
tables - do they have any remaining triggers?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Removing Replication

Hi,
I'm trying to remove the distribution database froma server, and it tells
me that I can't b/c its used for replication. What table stores this info,
its half cooked and I'm trying to manually cleanup the removal of the
distributor and replication.
-mike
Its stored in sysdatabases.
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
"michael" <michael@.discussions.microsoft.com> wrote in message
news:A5925AC9-C3E0-4C87-B27D-90775F422DCF@.microsoft.com...
> Hi,
> I'm trying to remove the distribution database froma server, and it
tells
> me that I can't b/c its used for replication. What table stores this
info,
> its half cooked and I'm trying to manually cleanup the removal of the
> distributor and replication.
> -mike
>

Removing replicated data on a remote device without deleting it on the server

In the replication scenario I am dealing with, a mobile device (PDA)
uses SQL Server Mobile to synchronize data with a SQL Server database
located on some server. The mobile user can enter new data on the PDA.
Data replicated from the server to the PDA cannot be modified.
However, after a long period of time, it is possible that the amount
of new data entered on the PDA could be potentially large.
Is it possible with replication that once new data is entered into the
PDA and then gets replicated back to the server, that it can be
removed from the PDA but without this removal causing the data to be
deleted on the server?
Thanks for your help
Polaris
You could try disabling the triggers before deleting the records. However
this means that modifications to these rows on the publisher will fail when
being applied to the subscriber. I'd archive the data on the publisher then
delete it on the publisher so the delete flows to the subscriber. If you
want to see the data on the publisher you could use a view which amalgamates
the data there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||I think you need to use a technology like RDA for this.
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
"Polaris431" <polaris431@.gmail.com> wrote in message
news:1172129049.331836.154340@.a75g2000cwd.googlegr oups.com...
> In the replication scenario I am dealing with, a mobile device (PDA)
> uses SQL Server Mobile to synchronize data with a SQL Server database
> located on some server. The mobile user can enter new data on the PDA.
> Data replicated from the server to the PDA cannot be modified.
> However, after a long period of time, it is possible that the amount
> of new data entered on the PDA could be potentially large.
> Is it possible with replication that once new data is entered into the
> PDA and then gets replicated back to the server, that it can be
> removed from the PDA but without this removal causing the data to be
> deleted on the server?
> Thanks for your help
> Polaris
>

Removing Merge Replication

Hi,
I have two databases which have previously been replicating via merge replication.
I have removed replacation from one of the databases (the smaller) one, and noticed during this process the system slowed to an absolute crawl.
My problem is, i need to do the second (and much larger database) but cant afford for the system to a) take so long b) be virtually unusable during this period.
Is there any way around this? Can it be done manually? Can you change the priority or limit the maximum i/o usage during the removal period?
Also, is it normal for replicated databases to be double the size of non-replicated databases?
Thanks,
Andrew
Andrew,
can you try disabling the merge agent then dropping the subscriber out of
hours?
As for the size of the database, have a look at the main merge replication
tables:
sp_spaceused msmerge_contents
sp_spaceused msmerge_tombstone
sp_spaceused MSmerge_genhistory
This is probably where you'll find the extra space is used up. Cleaning up
metadata can aid in this issue.
HTH,
Paul Ibison
|||Unfortunately we dont have an after hours period, we are a 24/7 business. Any suggestions?
Thanks,
Andrew
"Paul Ibison" wrote:

> Andrew,
> can you try disabling the merge agent then dropping the subscriber out of
> hours?
> As for the size of the database, have a look at the main merge replication
> tables:
> sp_spaceused msmerge_contents
> sp_spaceused msmerge_tombstone
> sp_spaceused MSmerge_genhistory
> This is probably where you'll find the extra space is used up. Cleaning up
> metadata can aid in this issue.
> HTH,
> Paul Ibison
>
>
|||dropping replication should not be causing this problem. I would run
profiler to determine exactly where it is failing.
You can drop a merge subscription by using
sp_dropmergepullsubscription
sp_dropmergesubscription and set the ignore_distribution parameter to true.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:7ED41D2D-F59F-4F37-BDD2-DF5DDDACEC5A@.microsoft.com...
> Unfortunately we dont have an after hours period, we are a 24/7 business.
Any suggestions?[vbcol=seagreen]
> Thanks,
> Andrew
> "Paul Ibison" wrote:
of[vbcol=seagreen]
replication[vbcol=seagreen]
up[vbcol=seagreen]
|||dropping replication should not be causing this problem. I would run
profiler to determine exactly where it is failing.
You can drop a merge subscription by using
sp_dropmergepullsubscription
sp_dropmergesubscription and set the ignore_distribution parameter to true.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:7ED41D2D-F59F-4F37-BDD2-DF5DDDACEC5A@.microsoft.com...
> Unfortunately we dont have an after hours period, we are a 24/7 business.
Any suggestions?[vbcol=seagreen]
> Thanks,
> Andrew
> "Paul Ibison" wrote:
of[vbcol=seagreen]
replication[vbcol=seagreen]
up[vbcol=seagreen]
|||Hi Hilary,
Thanks for your reply.
Does the procedure you describe do the same as deleting the subscription via enterprise manager?
If I start the procedure described via Query Analyser, can it be stopped? (If the load on the server becomes too high?)
Andrew
"Hilary Cotter" wrote:

> dropping replication should not be causing this problem. I would run
> profiler to determine exactly where it is failing.
> You can drop a merge subscription by using
> sp_dropmergepullsubscription
> sp_dropmergesubscription and set the ignore_distribution parameter to true.
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Andrew" <Andrew@.discussions.microsoft.com> wrote in message
> news:7ED41D2D-F59F-4F37-BDD2-DF5DDDACEC5A@.microsoft.com...
> Any suggestions?
> of
> replication
> up
>
>
|||Hi Hilary,
Thanks for your reply.
Does the procedure you describe do the same as deleting the subscription via enterprise manager?
If I start the procedure described via Query Analyser, can it be stopped? (If the load on the server becomes too high?)
Andrew
"Hilary Cotter" wrote:

> dropping replication should not be causing this problem. I would run
> profiler to determine exactly where it is failing.
> You can drop a merge subscription by using
> sp_dropmergepullsubscription
> sp_dropmergesubscription and set the ignore_distribution parameter to true.
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Andrew" <Andrew@.discussions.microsoft.com> wrote in message
> news:7ED41D2D-F59F-4F37-BDD2-DF5DDDACEC5A@.microsoft.com...
> Any suggestions?
> of
> replication
> up
>
>

Removing merge replication

Hello!
I've got quite fu**ed up merge replication still sitting on one DB and I
cannot remove it. When I run sp_removedbreplication db_name I get an error
that it cannot delete conflict table. Now, as I was checking how to correct
this error, I see that sp_MSarticlecleanup is trying to delete those tables.
But the problem is, that when it tries to do this, it uses owner name of the
real table; here's an example:
I have table1 who's owner is joe. Now SQL thinks that the conflict table for
this table1 should also be owned by joe, but it's not. My case is like this:
Original table: joe.table1
Conflict table: dbo.conflict_pub_name_table1
So if you'll check the sp_MSarticlecleanup it tries to find the owner of
original table and then it tries to delete owner.conflict_table table and I
cannot change this anywhere. I also cannot delete those tables manualy as
they are marked as System tables ...
Any hintS?! Can I alter the MSarticlecleanup just for this, so I can
'hardcode' the owner of conflict tables just for this one time run? I tried
to give my self a permission to alter this stored procedure, but no luck; I
can only set EXEC permission.
Any hints greatly appreciated!
Kind regards,
Dejan
--
AKTON Communications d.o.o.
Tbilisijska 81, 1000 Ljubljana, Slovenia
Tel.: +386 1 200 200 1
Fax.: +386 1 200 2011
Dejan,
have a look at Hilary's reply to a post from last week on Removing
Replication. He has a pretty comprehensive script which will as far as I
remember, remove the conflict tables amongst many other things.
Regards,
Paul Ibison
|||Thanks Paul and Hilary, ofcourse!
It did drop everything, including few indexes it shouldn't drop; but I've
recreated them and it works OK now.
Kind regards,
Dejan
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eLcVMUYFEHA.2768@.tk2msftngp13.phx.gbl...
| Dejan,
| have a look at Hilary's reply to a post from last week on Removing
| Replication. He has a pretty comprehensive script which will as far as I
| remember, remove the conflict tables amongst many other things.
| Regards,
| Paul Ibison
|
|
|||which indexes?
"Dejan Markic" <dejan@.akton.is> wrote in message
news:xMV9c.7161$%x4.956323@.news.siol.net...
> Thanks Paul and Hilary, ofcourse!
> It did drop everything, including few indexes it shouldn't drop; but I've
> recreated them and it works OK now.
> Kind regards,
> Dejan
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:eLcVMUYFEHA.2768@.tk2msftngp13.phx.gbl...
> | Dejan,
> | have a look at Hilary's reply to a post from last week on Removing
> | Replication. He has a pretty comprehensive script which will as far as I
> | remember, remove the conflict tables amongst many other things.
> | Regards,
> | Paul Ibison
> |
> |
>
|||The following code line has to be fixed:
select @.qualified_name = QUOTENAME(@.ownername) + '.' +
QUOTENAME(@.conflict_table)
Could be replaced with something like that :
IF EXISTS(
SELECT 1
FROM SYSOBJECTS O
JOIN SYSUSERS U
ON O.UID = U.UID
WHERE O.NAME = @.conflict_table
AND U.NAME = @.ownername )
BEGIN
select @.qualified_name = QUOTENAME(@.ownername) + '.' +
QUOTENAME(@.conflict_table)
END
ELSE
BEGIN
select @.qualified_name = QUOTENAME(@.conflict_table)
END
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Dejan Markic" <dejan@.akton.is> wrote in message
news:L6T9c.7144$%x4.954553@.news.siol.net...
> Hello!
> I've got quite fu**ed up merge replication still sitting on one DB and I
> cannot remove it. When I run sp_removedbreplication db_name I get an error
> that it cannot delete conflict table. Now, as I was checking how to
correct
> this error, I see that sp_MSarticlecleanup is trying to delete those
tables.
> But the problem is, that when it tries to do this, it uses owner name of
the
> real table; here's an example:
> I have table1 who's owner is joe. Now SQL thinks that the conflict table
for
> this table1 should also be owned by joe, but it's not. My case is like
this:
> Original table: joe.table1
> Conflict table: dbo.conflict_pub_name_table1
> So if you'll check the sp_MSarticlecleanup it tries to find the owner of
> original table and then it tries to delete owner.conflict_table table and
I
> cannot change this anywhere. I also cannot delete those tables manualy as
> they are marked as System tables ...
> Any hintS?! Can I alter the MSarticlecleanup just for this, so I can
> 'hardcode' the owner of conflict tables just for this one time run? I
tried
> to give my self a permission to alter this stored procedure, but no luck;
I
> can only set EXEC permission.
> Any hints greatly appreciated!
> Kind regards,
> Dejan
> --
> --
> AKTON Communications d.o.o.
> Tbilisijska 81, 1000 Ljubljana, Slovenia
> Tel.: +386 1 200 200 1
> Fax.: +386 1 200 2011
>

Removing Merge Replication

Several years ago I set up Merge Replication on a SQL 2000 database and an MSDE Client on a Laptop. The Laptop frequently travelled out of the country and would "synch" up when it came back in.

Well the Laptop received some Viruses that we cannot get to remove so putting it on the network is not possible. Also, the owner of the laptop received a new one. He wants the database set up on the new laptop but it is not a priority at the moment and we plan to wait till the "Yemen" project is finished.

In the mean time, I want to remove the old laptop out of the Merge Replication scenario. We are not concerned about losing the data in the database on the laptop.

Does anyone know the steps I need to perform to "unreplicate" my database without being able to connect them?From the publisher's standpoint, it should be a pretty simple matter to delete the subscriber (uncheck the option to remove replication artifacts at the subscriber). See sp_dropmergesubscription in BOL.

If you are trying to remove the merge subscription artifacts from the subscriber database, then you simply execute sp_removedbreplication '[DB_NAME]'.

Regards,

hmscott|||For my situation, the subscriber was dead. So doing any procedure on the subscriber side was not an option. I had a bit of difficulty removing the subscriptions from the publisher side (sp_DropMergesubscription would not run) but I think I have it accomplished now.

The prescribed way of removing replication is to 1. Drop subscriptions, 2. Drop publications, 3. Drop Distributor - see

However I couldn't get the subscriptions to remove because I couldn't attach to them so I skipped step 1 and went to the part in the article on dropping a merge publication. I was able to run the sp_dropMergePublication and sp_Relicationdboption procedures. After that I was able to delete the subscriptions (via Enterprise Manager) as well.

I still have some garbage in the Replication Monitor on my agents that haven't cleaned up. Perhaps a restart will resolve this. Thanks for your input.|||Sorry, my URL didn't display on the last post.
http://http://support.microsoft.com/kb/324401

removing deactivated subscriptions

Hi

We are using merge replication - how do we remove deactivated subscriptions from the replication monitor at the publisher. These are anonymous HTTPS subscribers....

Thanks

Bruce

I use this stored procedure which runs against the distribution database:

CREATE PROCEDURE [dbo].[usp_DeleteMergeAgentSessions]

@.Publication [nvarchar](50),

@.Subscriber [nvarchar](50),

@.Database [nvarchar](50) = NULL

AS

SET NOCOUNT ON

DELETE FROM [msmerge_sessions] WHERE [agent_id] IN (SELECT [id] FROM [msmerge_agents] WHERE [publication] = @.Publication AND [subscriber_name] = @.Subscriber AND (@.Database IS NULL OR @.Database = [subscriber_db]))

DELETE FROM [msmerge_agents] WHERE [publication] = @.Publication AND [subscriber_name] = @.Subscriber AND (@.Database IS NULL OR @.Database = [subscriber_db])

Removing articles from Merge replication

I do have a merge replication and a number of anonymous subscribers
In the publisher, I have a number of articles (tables) that are published
When the publisher was defined, I could tick in the tables I wanted to
publish.
Now I want to remove 2-3 of these tables. Apparently, when I go into the
publishers property window, I can't untick the tables, so how do I remove
these tables?
Anders
You have to drop the subscriptions to be able to do this in SQL 2000. In SQL
2005 you can drop them, but the subscriptions will be re-initialized.
RelevantNoise.com - dedicated to mining blogs for business intelligence.
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
"Anders Balslev" <Anders Balslev@.discussions.microsoft.com> wrote in message
news:9832A46E-D419-496E-B65E-36397009C7FC@.microsoft.com...
>I do have a merge replication and a number of anonymous subscribers
> In the publisher, I have a number of articles (tables) that are published
> When the publisher was defined, I could tick in the tables I wanted to
> publish.
> Now I want to remove 2-3 of these tables. Apparently, when I go into the
> publishers property window, I can't untick the tables, so how do I remove
> these tables?
> Anders
|||Hi Hilary
- Sorry to say, but it still don't work.
I have now - on the subscriber sides - dropped the subscriptions, so I know
for sure there are no subscribers.
Then I go to the publisher, rightclick on the publication, go to the
"articles" section. The tables I want to remove are marked active in the
checkbox, but i'm still unable to unmark these tables.
PS! don't forget that the subscribers are anonymous
Wha can I do
Anders
"Hilary Cotter" wrote:

> You have to drop the subscriptions to be able to do this in SQL 2000. In SQL
> 2005 you can drop them, but the subscriptions will be re-initialized.
> --
> RelevantNoise.com - dedicated to mining blogs for business intelligence.
> 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
> "Anders Balslev" <Anders Balslev@.discussions.microsoft.com> wrote in message
> news:9832A46E-D419-496E-B65E-36397009C7FC@.microsoft.com...
>
>
|||On Nov 14, 1:30 am, Anders Balslev
<AndersBals...@.discussions.microsoft.com> wrote:
> Hi Hilary
> - Sorry to say, but it still don't work.
> I have now - on the subscriber sides - dropped the subscriptions, so I know
> for sure there are no subscribers.
> Then I go to the publisher, rightclick on the publication, go to the
> "articles" section. The tables I want to remove are marked active in the
> checkbox, but i'm still unable to unmark these tables.
> PS! don't forget that the subscribers are anonymous
> Wha can I do
> Anders
>
> "Hilary Cotter" wrote:
>
>
>
> - Show quoted text -
have you tried the stored procedure to drop an article? I don't use
the gui much anymore...
http://technet.microsoft.com/en-us/library/ms179926.aspx
sp_dropmergearticle [ @.publication= ] 'publication'
, [ @.article= ] 'article'
[ , [ @.ignore_distributor= ] ignore_distributor
[ , [ @.reserved= ] reserved
[ , [ @.force_invalidate_snapshot= ] force_invalidate_snapshot ]
[ , [ @.force_reinit_subscription = ] force_reinit_subscription ]
[ , [ @.ignore_merge_metadata = ] ignore_merge_metadata ]

Removing and adding articles by code

Hello there
On my live replication, I need, sometimes, to change schema of replicated
tables.
Because, as long as the table is replicated, i can't change the schema, I
simple do:
1. Remove the article from the parent table
2. change the schema of the current table
3. add it again to the schema
I would like to do this with code.
Is there a way to do this?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.il
Roy,
please take a look at this article:
http://www.replicationanswers.com/AddColumn.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)