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.

Re-name a Column in an Anonymous Merge Publication

Upsized an Access .MDB and Published successfully, EXCEPT forgot to make
visible the sysobjects, so two Tables each have four Columns named: Expr1,
Expr2, Expr3, Expr4. When I try to rename them with SSEM in the SQL Database,
I get errors like this:
'tblNoteAdmit' table
- Unable to rename column from 'Expr1' to 'Pump'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot rename the
table because it is published for replication.
It looks like time to Create a Script, that the Snapshot Agent can
understand ...
Sometime back, I blundered through that process to Add a Table
(Successfully) but am having trouble finding the SP_ to trick Column Names.
Aubrey Kelley
How do I Rename a Column in an Active Replicated Database?
24 hr update: Got this far: (SP is sp_rename, but still blocked by
Replication.)
USE CareData
GO
EXEC sp_rename 'tblNoteAdmit.Expr1', 'Pump', 'COLUMN'
Server: Msg 15051, Level 11, State 1, Procedure sp_rename, Line 172
Cannot rename the table because it is published for replication.
Aubrey Kelley
"Aubrey" wrote:

> Upsized an Access .MDB and Published successfully, EXCEPT forgot to make
> visible the sysobjects, so two Tables each have four Columns named: Expr1,
> Expr2, Expr3, Expr4. When I try to rename them with SSEM in the SQL Database,
> I get errors like this:
> 'tblNoteAdmit' table
> - Unable to rename column from 'Expr1' to 'Pump'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot rename the
> table because it is published for replication.
> It looks like time to Create a Script, that the Snapshot Agent can
> understand ...
> Sometime back, I blundered through that process to Add a Table
> (Successfully) but am having trouble finding the SP_ to trick Column Names.
> --
> Aubrey Kelley
|||You will have to do sp_repladdcolumn to add a dummy column with the same
data type as the column you wish to rename. Then update this temp column
with the values in the column you are wishing to rename. Then drop the
column you wish to rename using sp_repldropcolumn. Then readd the column
with the new name using sp_repladdcolumn. Then update this column with the
values in the temp column. Then drop the temp column using
sp_repldropcolumn.
Alternatively you may wish to create a temp table with the values in the
column you wish to rename along with the PK. Then drop the column you wish
to rename using sp_repldropcolumn, and then add it back with the new name
using sp_repladdcolumn. Update this new column with the values in the temp
table.
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
"Aubrey" <miscuates@.online.nospam> wrote in message
news:51D04366-E78F-463C-A7C0-17E2CE461867@.microsoft.com...[vbcol=seagreen]
> How do I Rename a Column in an Active Replicated Database?
> 24 hr update: Got this far: (SP is sp_rename, but still blocked by
> Replication.)
> USE CareData
> GO
> EXEC sp_rename 'tblNoteAdmit.Expr1', 'Pump', 'COLUMN'
> Server: Msg 15051, Level 11, State 1, Procedure sp_rename, Line 172
> Cannot rename the table because it is published for replication.
> --
> Aubrey Kelley
>
> "Aubrey" wrote:
Expr1,[vbcol=seagreen]
Database,[vbcol=seagreen]
the[vbcol=seagreen]
Names.[vbcol=seagreen]
|||Thanks, Hilary! and only 43 minutes after my updated post, Sunday early AM?
That was what it looked like in your SS2K T&SR Book. Was not sure it applied
to Anonymous Merge. Will try it Real-Soon-Now ...
Aubrey Kelley
"Hilary Cotter" wrote:

> You will have to do sp_repladdcolumn to add a dummy column with the same
> data type as the column you wish to rename. Then update this temp column
> with the values in the column you are wishing to rename. Then drop the
> column you wish to rename using sp_repldropcolumn. Then readd the column
> with the new name using sp_repladdcolumn. Then update this column with the
> values in the temp column. Then drop the temp column using
> sp_repldropcolumn.
> Alternatively you may wish to create a temp table with the values in the
> column you wish to rename along with the PK. Then drop the column you wish
> to rename using sp_repldropcolumn, and then add it back with the new name
> using sp_repladdcolumn. Update this new column with the values in the temp
> table.
> --
> 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
> "Aubrey" <miscuates@.online.nospam> wrote in message
> news:51D04366-E78F-463C-A7C0-17E2CE461867@.microsoft.com...
> Expr1,
> Database,
> the
> Names.
>
>
|||What I did: Opened Query Analyzer
USE CareData
GO
EXEC sp_repladdcolumn N'Orders', N'IsSaved', 'bit', N'CareData'
[RUN]
Results:
Warning: only Subscribers running SQL Server 2000 can synchronize with
publication 'CareData' because schema replication is performed.
Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'sp_sel_B469A5B2466148AC3170B37FE7C24426_pal'.
The stored procedure will still be created.
Updated Values and Tested from Remote Client. Voila! Worked like a charm.
Why did you suggest a Temp column? Seemed superfluous to Copy from OldColumn
to Temp, then copy Temp to NewColumn, when OldColumn directly to NewColumn
works great.
Aubrey Kelley
"Aubrey" wrote:
[vbcol=seagreen]
> Thanks, Hilary! and only 43 minutes after my updated post, Sunday early AM?
> That was what it looked like in your SS2K T&SR Book. Was not sure it applied
> to Anonymous Merge. Will try it Real-Soon-Now ...
> --
> Aubrey Kelley
>
> "Hilary Cotter" wrote:
|||I'm glad it worked.
The temp table solution works great if you are able to kick your users off
while you do it. If not and the table was huge, the temp table could take
come time to populate and will get progressively out of sync if people are
banging away at it. So you would loose consistency as you do it.
However, my approach will "break" the table as you are making the changes so
the app which is using this column will fail as you do your renaming.
So neither solutions are perfect - mine takes longer but might guarantee
better consistency than yours. Your method is shorter, but your method is
preferred if you can kick all of your users off your system.
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
"Aubrey" <miscuates@.online.nospam> wrote in message
news:A9C71B7B-F1AF-4DAD-A202-6CF3DD2703FB@.microsoft.com...
> What I did: Opened Query Analyzer
> USE CareData
> GO
> EXEC sp_repladdcolumn N'Orders', N'IsSaved', 'bit', N'CareData'
> [RUN]
> Results:
> Warning: only Subscribers running SQL Server 2000 can synchronize with
> publication 'CareData' because schema replication is performed.
> Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object
'sp_sel_B469A5B2466148AC3170B37FE7C24426_pal'.
> The stored procedure will still be created.
> Updated Values and Tested from Remote Client. Voila! Worked like a charm.
> Why did you suggest a Temp column? Seemed superfluous to Copy from
OldColumn[vbcol=seagreen]
> to Temp, then copy Temp to NewColumn, when OldColumn directly to NewColumn
> works great.
> --
> Aubrey Kelley
>
> "Aubrey" wrote:
AM?[vbcol=seagreen]
applied[vbcol=seagreen]
same[vbcol=seagreen]
column[vbcol=seagreen]
column[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
wish[vbcol=seagreen]
name[vbcol=seagreen]
temp[vbcol=seagreen]
to make[vbcol=seagreen]
named:[vbcol=seagreen]
SQL[vbcol=seagreen]
rename[vbcol=seagreen]
Column[vbcol=seagreen]
|||Correct; I was fortunate that ALL Users are Merge Clients, No ONE is directly
updating the Publisher Database.
Just to Test, I added a couple of Rows while all this was running, and they
were missing the Values in the New Columns. Fortunately I had WHERE Clauses
in the Data Copy Form, Event Procedures just in case the Project Locked Up or
Aborted.
Now, onward and upward ... Have a Great Week!
Aubrey Kelley
"Hilary Cotter" wrote:

> I'm glad it worked.
> The temp table solution works great if you are able to kick your users off
> while you do it. If not and the table was huge, the temp table could take
> come time to populate and will get progressively out of sync if people are
> banging away at it. So you would loose consistency as you do it.
> However, my approach will "break" the table as you are making the changes so
> the app which is using this column will fail as you do your renaming.
> So neither solutions are perfect - mine takes longer but might guarantee
> better consistency than yours. Your method is shorter, but your method is
> preferred if you can kick all of your users off your system.
> --
> 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
> "Aubrey" <miscuates@.online.nospam> wrote in message
> news:A9C71B7B-F1AF-4DAD-A202-6CF3DD2703FB@.microsoft.com...
> 'sp_sel_B469A5B2466148AC3170B37FE7C24426_pal'.
> OldColumn
> AM?
> applied
> same
> column
> column
> the
> the
> wish
> name
> temp
> to make
> named:
> SQL
> rename
> Column
>
>

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 rows on subscribers, but not on publishers

Hello.
I'm looking for a way to remove data from a merge replicated database
that would be filtered out if it was synchronized for the first time
today. In addition, I don't want the new rows to be deleted from the
publisher's database.
Let me explain. We have several tables that look similar to the
following:
TableA:
Id uniqueidentifier
Value varchar(50)
TimeStamp datetime
We have a merge replication publication with filters that specify that
data with a TimeStamp older than 14 days should not be synchronized,
similar to the following:
exec sp_addmergefilter @.publication = N'MyPub', @.article = N'TableA',
@.filtername = N'TableA_TableB', @.join_articlename = N'TableB',
@.join_filterclause = N'{snipped}... and TableA.TimeStamp > ( GETDATE()
- 14 )', @.join_unique_key = 0
What ends up happening is that new rows with a recent TimeStamp is
synchronized to the client as desired. If the client syncs again after
14 days, however, this now "stale" data is still present on the client
-- it does not become deleted.
If we manually try to remove the stale data by running a query such as
"DELETE from TableA where TimeStamp > (GETDATE() - 30)", the data is
deleted. But the next time the database is synchronized, the data is
also deleted from the publishing database. This is not acceptable in
our application.
Some of our clients use SQL CE on Pocket PC's with limitted storage
capacity and thus will not be able to continue to store this old data.
Any ideas on how to delete the old data from the SQL CE subscriber's
databases without having the side effect of removing the data from the
publishing database?
Thanks in advance for your time.
- Eli Tucker
I think what you want to do is to manipulate your agents so that they change
to download only (-ExchangeType download) when you delete rows on your
subscriber, and then both (-ExchangeType both) under normal operations.
This will only work if the deletes occur in isolation. For instance if the
only activity occuring on your subscriber was the delete, this would work.
If there were deletes and updates and inserts occuring on your subcriber
with the ExchangeType of download the inserts and deletes would not make it.
"Eli Tucker" <eli-news@.nerdmonkey.com> wrote in message
news:1105046696.732620.46610@.f14g2000cwb.googlegro ups.com...
> Hello.
> I'm looking for a way to remove data from a merge replicated database
> that would be filtered out if it was synchronized for the first time
> today. In addition, I don't want the new rows to be deleted from the
> publisher's database.
> Let me explain. We have several tables that look similar to the
> following:
> TableA:
> Id uniqueidentifier
> Value varchar(50)
> TimeStamp datetime
> We have a merge replication publication with filters that specify that
> data with a TimeStamp older than 14 days should not be synchronized,
> similar to the following:
> exec sp_addmergefilter @.publication = N'MyPub', @.article = N'TableA',
> @.filtername = N'TableA_TableB', @.join_articlename = N'TableB',
> @.join_filterclause = N'{snipped}... and TableA.TimeStamp > ( GETDATE()
> - 14 )', @.join_unique_key = 0
> What ends up happening is that new rows with a recent TimeStamp is
> synchronized to the client as desired. If the client syncs again after
> 14 days, however, this now "stale" data is still present on the client
> -- it does not become deleted.
> If we manually try to remove the stale data by running a query such as
> "DELETE from TableA where TimeStamp > (GETDATE() - 30)", the data is
> deleted. But the next time the database is synchronized, the data is
> also deleted from the publishing database. This is not acceptable in
> our application.
> Some of our clients use SQL CE on Pocket PC's with limitted storage
> capacity and thus will not be able to continue to store this old data.
>
> Any ideas on how to delete the old data from the SQL CE subscriber's
> databases without having the side effect of removing the data from the
> publishing database?
> Thanks in advance for your time.
> - Eli Tucker
>
|||Hilary, thanks for the info. I'll keep this tip in mind, but it isn't
ideal for us since the client application both adds and updates new
rows between syncs.
Does anyone have any other ideas of how to do this?
|||Eli,
Instead of synchronizing from your subscriber code, if you *reinitialize*
the subscription
with the option to upload your latest changes before refreshing the
subscription tables,
your subscriptions will always contain only the 14 days of data your
publication filter dictates.
Take a look at the boolean value that ReinitializeSubscription takes...
rep.ReinitializeSubscription(True)
-- Darren Shaffer
"Eli Tucker" <eli-news@.nerdmonkey.com> wrote in message
news:1105409204.644780.269050@.c13g2000cwb.googlegr oups.com...
> Hilary, thanks for the info. I'll keep this tip in mind, but it isn't
> ideal for us since the client application both adds and updates new
> rows between syncs.
> Does anyone have any other ideas of how to do this?
>
|||Thanks, Darren. I haven't looked at the ReinitializeSubscription
option before -- it very well could work for us.
Is it basically equivalent to the following?
1. Synchronizing upload only
2. Deleting the SDF file
3. Performing an initial sync
Or is less data passed when using the ReinitializeSubscription(true)
method?
Thanks again!
- Eli
Darren Shaffer wrote:
> Eli,
> Instead of synchronizing from your subscriber code, if you
*reinitialize*
> the subscription
> with the option to upload your latest changes before refreshing the
> subscription tables,
> your subscriptions will always contain only the 14 days of data your
> publication filter dictates.
> Take a look at the boolean value that ReinitializeSubscription
takes...[vbcol=seagreen]
> rep.ReinitializeSubscription(True)
> -- Darren Shaffer
>
> "Eli Tucker" <eli-news@.nerdmonkey.com> wrote in message
> news:1105409204.644780.269050@.c13g2000cwb.googlegr oups.com...
isn't[vbcol=seagreen]

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 ]