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
>
>