|
|
You're in the
sql server replication
group:Re-name a Column in an Anonymous Merge Publication
sql server replication:
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. --
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 [quoted text, click to view] "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. > > --
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 [quoted text, click to view] "Aubrey" <miscuates@online.nospam> wrote in message news:51D04366-E78F-463C-A7C0-17E2CE461867@microsoft.com... > 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
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 [quoted text, click to view] "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... > > 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 > >
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 [quoted text, click to view] "Aubrey" wrote:
> 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... > > > 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 > > > >
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 [quoted text, click to view] "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 > to Temp, then copy Temp to NewColumn, when OldColumn directly to NewColumn > works great. > -- > Aubrey Kelley > > > "Aubrey" wrote: > > > 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... > > > > 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 > > > > > > > > >
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 [quoted text, click to view] "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 > > to Temp, then copy Temp to NewColumn, when OldColumn directly to NewColumn > > works great. > > -- > > Aubrey Kelley > > > > > > "Aubrey" wrote: > > > > > 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... > > > > > 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 > > > > > > > > > > > > > >
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |