Replication in SQL: alter

Showing posts with label alter. Show all posts

Monday, March 26, 2012

Replication removed but server says otherwise

I've got a big and urgent problem...
I need to alter some tables in a database. I had those
tables replicated. I removed the replication to make
thoses changes.
All the objects created by the replication are still
there, and the fields added in the tables also. When I
want to change a clustered index, It says :
- Unable to delete index 'PK_rel_cfp_sar'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
Server]Cannot alter the table 'rel_cfp_sar' because it is
being published for replication.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not
drop constraint. See previous errors.
The problem is, there is no more replication active!!
I've even removed the server as a distributor. Rebooted
the server, nothing works!!
What can I do? HELP ME!!!
Thanks in advance, Hussein.
hussein@.inforoutefpt.org
Hussein,
Running sp_removedbreplication can be used to remove all traces of
replication in the subscriber database, but obviously must only be done if
this database is not also configured as a publisher.
If sp_removedbreplication can't be used, there is a stored procedure to do
this called sp_MSunmarkreplinfo which takes a tablename as a parameter.
Alternatively, setting replinfo to 0 in sysobjects for the particular table
should do it.
HTH,
Paul Ibison
|||Thanks Paul writing me back, but it didn't worked.
The problem is at the publisher, I've disabled the server to be a Publisher
or Distributor. So it's very weird. The fields added in the tables for the
replication are still there, and I can't remove them. I can't change the
indexes either.
I've tried the two SP who told me, it says "The command(s) completed
successfully." and "1 row(s) affected", but still can't do anything...
Do you have another idea?
Thanks, Hussein
"Paul Ibison" wrote:

> Hussein,
> Running sp_removedbreplication can be used to remove all traces of
> replication in the subscriber database, but obviously must only be done if
> this database is not also configured as a publisher.
> If sp_removedbreplication can't be used, there is a stored procedure to do
> this called sp_MSunmarkreplinfo which takes a tablename as a parameter.
> Alternatively, setting replinfo to 0 in sysobjects for the particular table
> should do it.
> HTH,
> Paul Ibison
>
>
|||This is very strange. Can you query replinfo in sysobjects for the problem
table and tell me the value.
TIA,
Paul Ibison
|||The value is set to 1 for the table.
I also have many strored procedures who have a value of
512.
Hussein

>--Original Message--
>This is very strange. Can you query replinfo in
sysobjects for the problem
>table and tell me the value.
>TIA,
>Paul Ibison
>
>.
>
|||Hussein - OK set it to zero for the problem table and then you should be
able to change the table schema.
HTH,
Paul Ibison
"Hussein Abd-Rabbo" <hussein@.inforoutefpt.org> wrote in message
news:137b01c48c2f$e02c2ec0$a601280a@.phx.gbl...[vbcol=seagreen]
> The value is set to 1 for the table.
> I also have many strored procedures who have a value of
> 512.
> Hussein
> sysobjects for the problem
|||also try to run
sp_msforeachtable 'sp_MSunmarkreplinfo ''?'''
in your publication database.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:udaTGdBjEHA.1184@.TK2MSFTNGP12.phx.gbl...
> This is very strange. Can you query replinfo in sysobjects for the problem
> table and tell me the value.
> TIA,
> Paul Ibison
>

Friday, March 23, 2012

Replication question - unable to modify table

I have disabled publisher, distributor and subscriber; no publications exist
on publisher. However, when I try to alter a table on former publisher I get
this error:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the
table 'dbo.MAIN' because it is being used for replication.
Please help, thank you in advance for your time!
Leon ShargorodskyRun stored proc sp_removedbreplication and see if that fixes your problem.
Mike
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:37DFDC3E-7435-456E-A137-D94EDFED5915@.microsoft.com...
> I have disabled publisher, distributor and subscriber; no publications
exist
> on publisher. However, when I try to alter a table on former publisher I
get
> this error:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the
> table 'dbo.MAIN' because it is being used for replication.
> Please help, thank you in advance for your time!
> Leon Shargorodsky|||No, I'm still getting the same error: "table is being used for replication"
"Mike Wengler" wrote:
> Run stored proc sp_removedbreplication and see if that fixes your problem.
> Mike
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
> message news:37DFDC3E-7435-456E-A137-D94EDFED5915@.microsoft.com...
> > I have disabled publisher, distributor and subscriber; no publications
> exist
> > on publisher. However, when I try to alter a table on former publisher I
> get
> > this error:
> >
> > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the
> > table 'dbo.MAIN' because it is being used for replication.
> >
> > Please help, thank you in advance for your time!
> >
> > Leon Shargorodsky
>
>|||NOT RECOMMENDED Under normal circumstances only after trying ALL other
solutions . . but I have had this problem resolved in the past by reseting
the value in replinfo column in sysobjects to 0.
HTH
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:8460093D-8C06-43AD-964B-06592F231ADE@.microsoft.com...
> No, I'm still getting the same error: "table is being used for
replication"
> "Mike Wengler" wrote:
> > Run stored proc sp_removedbreplication and see if that fixes your
problem.
> >
> > Mike
> >
> > "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote
in
> > message news:37DFDC3E-7435-456E-A137-D94EDFED5915@.microsoft.com...
> > > I have disabled publisher, distributor and subscriber; no publications
> > exist
> > > on publisher. However, when I try to alter a table on former publisher
I
> > get
> > > this error:
> > >
> > > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop
the
> > > table 'dbo.MAIN' because it is being used for replication.
> > >
> > > Please help, thank you in advance for your time!
> > >
> > > Leon Shargorodsky
> >
> >
> >|||THANK YOU SO MUCH! It worked!!!
"Olu Adedeji" wrote:
> NOT RECOMMENDED Under normal circumstances only after trying ALL other
> solutions . . but I have had this problem resolved in the past by reseting
> the value in replinfo column in sysobjects to 0.
> HTH
>
>
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
> message news:8460093D-8C06-43AD-964B-06592F231ADE@.microsoft.com...
> > No, I'm still getting the same error: "table is being used for
> replication"
> >
> > "Mike Wengler" wrote:
> >
> > > Run stored proc sp_removedbreplication and see if that fixes your
> problem.
> > >
> > > Mike
> > >
> > > "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote
> in
> > > message news:37DFDC3E-7435-456E-A137-D94EDFED5915@.microsoft.com...
> > > > I have disabled publisher, distributor and subscriber; no publications
> > > exist
> > > > on publisher. However, when I try to alter a table on former publisher
> I
> > > get
> > > > this error:
> > > >
> > > > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop
> the
> > > > table 'dbo.MAIN' because it is being used for replication.
> > > >
> > > > Please help, thank you in advance for your time!
> > > >
> > > > Leon Shargorodsky
> > >
> > >
> > >
>
>

Monday, March 26, 2012

Replication removed but server says otherwise

Friday, March 23, 2012

Replication question - unable to modify table

Replication question - unable to modify table

Replication question - unable to modify table

Replication in SQL

Blog Archive

About Me