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 Publication from Monitoring Tool

Hi:

I removed the publication from the database but it is still showing in the monitoring tool. Does anyone know how to remove it from there?

Thanks

did you try refreshing? disconnect-reconnect?|||

Yes I tried that but still does not work.

|||how did you remove the publication, and what version of sql server are you using?|||

If replication is removed forcefully in publishing db, and distribution db is not accessible, it is possible to leave orphaned entries in some of the metadata tables at the distributor.

if this is SQL 2005, and you see publications in SQL Monitor that no longer exist, then you can try deleting the orphaned entries in table [MSpublications] in distribution db, as this is where SQL Monitor is retrieving the data from.

Removing Articles

I want to remove all articles from a subcription and publication. Will these
two commands do it for me?
EXEC sp_dropsubscription @.publication = 'EMOBILE_OLTP_OLAP', @.article =
'all', @.subscriber = 'all', @.destination_db = 'all'
EXEC sp_droparticle @.publication = 'EMOBILE_OLTP_OLAP', @.article = 'all',
@.force_invalidate_snapshot = 1
Thanks,
Mark
Yes, but why not just drop the publication after dropping the subscription?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"mrprice" <mrprice@.discussions.microsoft.com> wrote in message
news:985F6D57-2A8C-41C1-A2D7-31B60239DE0B@.microsoft.com...
>I want to remove all articles from a subcription and publication. Will
>these
> two commands do it for me?
> EXEC sp_dropsubscription @.publication = 'EMOBILE_OLTP_OLAP', @.article =
> 'all', @.subscriber = 'all', @.destination_db = 'all'
> EXEC sp_droparticle @.publication = 'EMOBILE_OLTP_OLAP', @.article = 'all',
> @.force_invalidate_snapshot = 1
> Thanks,
> Mark
|||I do that after the sp_droparticle. This is kind of how SQL Enterpise
Manager scipted it out so ...
Mark
"Hilary Cotter" wrote:

> Yes, but why not just drop the publication after dropping the subscription?
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "mrprice" <mrprice@.discussions.microsoft.com> wrote in message
> news:985F6D57-2A8C-41C1-A2D7-31B60239DE0B@.microsoft.com...
>
>

removing and adding objects from publication

I am replicating all tables and views in a database with snapshot
replication. It is giving an error because View2 is dependent on View1 but it
is trying to create View2 first. I was going to delete and recreate View2 so
it would come after View1 but when I went to delete View2 I got an error
saying that I couldn't delete the view because it is being used by
replication. Is there any way I can delete and recreate View2 without having
to delete all of my replication setup? Is there an easy way to remove and add
objects to a publication? Thanks!
Thanks Paul - I see that this will help me delete the view. When it is
recreated how can I add it back into the publication? Thanks!
"Paul Ibison" wrote:

> You should be able to use this type of approach:
> exec sp_dropsubscription @.publication
> = 'PDS_to_Cab_Tables'
> , @.article = 'SNAPSHOT_RELEASE_INFO'
> , @.subscriber = 'RSCOMPUTERXXX'
> , @.destination_db = 'delivery'
>
> for each subscriber, then...
> exec sp_droparticle @.publication = 'PDS_to_Cab_Tables'
> , @.article = 'SNAPSHOT_RELEASE_INFO'
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||sp_addarticle and sp_addsubscription will do it.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

removing a snapshot agent.

I have a snapshot agent that I cannot get rid of. The publication did
not exist, so I went into distribution.MSsnapshot_agents table and
delete it. When I refresh the snapshot agents folder, it is gone. At
some point, the damn thing gets recreated.
What is recreating this agent? How can I stop it for being recreated?
Larry...
Are you using dynamic snapshots? This is a merge replicaiton only feature.
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
"LPR-3rd" <lreames@.gmail.com> wrote in message
news:1115044813.754502.295220@.f14g2000cwb.googlegr oups.com...
> I have a snapshot agent that I cannot get rid of. The publication did
> not exist, so I went into distribution.MSsnapshot_agents table and
> delete it. When I refresh the snapshot agents folder, it is gone. At
> some point, the damn thing gets recreated.
> What is recreating this agent? How can I stop it for being recreated?
> Larry...
>
|||I am not using dynamic snapshots. It is a simple transactional pub.
What do you mean..'This is a merge replicaiton only feature.'?

removing a snapshot agent.

I have a snapshot agent that I cannot get rid of. The publication did
not exist, so I went into distribution.MSsnapshot_agents table and
delete it. When I refresh the snapshot agents folder, it is gone. At
some point, the damn thing gets recreated.
I am not using dynamic snapshots. It is a simple transactional pub.
What is recreating this agent? How can I stop it for being recreated?
Larry...
A stray snapshot agent job that runs periodically may be re-creating the
snapshot agent entry in MSsnapshot_agent. Can you check whether there is a
snapshot agent job step in sysjobsteps that doesn't correspond to any known
publications?
"LPR-3rd" wrote:

> I have a snapshot agent that I cannot get rid of. The publication did
> not exist, so I went into distribution.MSsnapshot_agents table and
> delete it. When I refresh the snapshot agents folder, it is gone. At
> some point, the damn thing gets recreated.
> I am not using dynamic snapshots. It is a simple transactional pub.
> What is recreating this agent? How can I stop it for being recreated?
>
> Larry...
>