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 Shargorodsky
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
|||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...
> exist
> get
>
>
|||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"[vbcol=seagreen]
> "Mike Wengler" wrote:
problem.[vbcol=seagreen]
in[vbcol=seagreen]
I[vbcol=seagreen]
the[vbcol=seagreen]
|||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...
> replication"
> problem.
> in
> I
> the
>
>

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

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]Canno
t 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]Can
not 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...
> exist
> get
>
>|||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"[vbcol=seagreen]
> "Mike Wengler" wrote:
>
problem.[vbcol=seagreen]
in[vbcol=seagreen]
I[vbcol=seagreen]
the[vbcol=seagreen]|||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...
> replication"
> problem.
> in
> I
> the
>
>

Replication of check constraints

I have tables that are replicated using transactional and merge replication.As a result I am unable to use automatic identity management as transactional replication doesn’t seem to understand it.

Therefore I have implemented a version of the automatic mechanisms that seems to work in a hybrid environment.It is based on a central table that holds the maximum identity for each table that has been issued to date.Valid identity ranges are issued to each publisher and subscriber as needed in a similar way to the automatic mechanisms and tables are reseeded as needed.

I want to enforce the ranges in a similar way to the automatic mechanism using a check constraint similar to this:

alter table [dbo].[test1] with NOCHECK add CONSTRAINT repl_identity_range_48DF13ED_D503_4F5C_AED9_4E504D03E752 check NOT FOR REPLICATION (([id] > 10001 and [id] <= 20001) or ([id] > 50001 and [id] <= 70001))

This works OK on a client subscriber, but if the change is made on the publisher, then the alter statement itself is replicated out to all clients – which is not what is wanted.I have traced the automatic mechanisms using profiler and they issue an alter statement as above – following dropping of the constraint – but the check constraint isn’t replicated. I can't see how this is achieved.

How do I stop the check constraint being replicated?

The article property schema option can be set to stop replicating check constraints, but this seems to have no effect.If the publication property replicate_ddl is set to 0 then I do see the behaviour that I want.However, I do need to be able to replicate most schema changes due to upgrades etc – so this doesn’t look like a viable option – except possibly for the transactional publication.

Any help would be much appreciated

Thanks

aero1

I think I have found a way forward. I had been considering the publication setup as pretty static, but on subsequent thoughts I realised that there is no reason why I can't change the replicate_ddl property as and when I need to

I have tested out the following and it seems to work (Other unrelated schema changes get synched correctly)

begin tran

exec sp_changemergepublication to set replicate_ddl to 0

make the check constraint change

exec sp_changemergepublication to set replicate_ddl back to 1

commit

Is there any problem with this approach?

|||This is the supported way of not wanting to replicate DDL-type statements (enable/disable publication property replicate_ddl). Please let us know if you run into problems.|||

Thanks for the info

aero1

replication monitor group

Hi,
I am part of the replmonitor role of a distribution
database. I am able to view the agents of the distribution
database but i am unable to start or stop any agent. the
books online says that i should be able to. Reference
Replication/administering and monitoring replication/Tools
for administering and monitoring replication/Replication
Monitor: "You can use Replication Monitor
to: ....Administer agents and subscriptions including
starting and stopping agents and reinitializing
subscriptions".
what did i miss?
Thankx for any help
do a search in BOL for replmonitor and look in Role Requirements. It states that this role can only view. It further states that you can't change anything unless you are also a member of the sysadmin role.
Likewise you can't start or stop agents unless you are a member of the sysadmin role, although this is left unstated in BOL.
In the quote you gave, it does say you can stop and start replication agents using replication monitor, but what is left unsaid is that you need the correct permissions to do so. If you do search on BOL for replmonitor and look in the Replication Monitor
section it does say however "however, the user will not ba ale to administer replication." Starting and stopping agents falls under the umbrella of administering replication.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"mona" wrote:

> Hi,
> I am part of the replmonitor role of a distribution
> database. I am able to view the agents of the distribution
> database but i am unable to start or stop any agent. the
> books online says that i should be able to. Reference
> Replication/administering and monitoring replication/Tools
> for administering and monitoring replication/Replication
> Monitor: "You can use Replication Monitor
> to: ....Administer agents and subscriptions including
> starting and stopping agents and reinitializing
> subscriptions".
> what did i miss?
> Thankx for any help
>

Replication Job Failed. Unable to determine the owner

A former employee at my organization created a replication job to back up our database. The developer has since left and his windows account was deleted. I'm now receiving this error:

SQL Server Scheduled Job 'Replication agents checkup' (0xFC17DD4EF8E5694E8B786EE54CF8AD0D) - Status: Failed - Invoked on: 2007-06-05 15:40:00 - Message: The job failed. Unable to determine if the owner (username) of job Replication agents checkup has server access (reason: Could not obtain information about Windows NT group/user 'username', error code 0x534. [SQLSTATE 42000] (Error 15404) The statement has been terminated. [SQLSTATE 01000] (Error 3621)).

How can I fix this? I looked under the Replication folder but didn't see anything.

In Object Explorer, look at the job in SQL Server Agent to see who owns it, and verify the account is valid.

|||That's for the help. I changed the owner but am now getting this error:

SQL Server Scheduled Job 'Replication agents checkup' (0xFC17DD4EF8E5694E8B786EE54CF8AD0D) - Status: Failed - Invoked on: 2007-06-07 01:00:00 - Message: The job failed. The Job was invoked by Schedule 175 (Replication agent schedule.). The last step to run was step 1 (Run agent.).

When I executed the maintenance plan manually it worked, but it doesn't seem to work on the schedule. I've never created a maintenance plans so I'm going to look up some more information to see if I can figure out what is going to wrong.

Replication Job Failed. Unable to determine the owner

A former employee at my organization created a replication job to back up our database. The developer has since left and his windows account was deleted. I'm now receiving this error:

SQL Server Scheduled Job 'Replication agents checkup' (0xFC17DD4EF8E5694E8B786EE54CF8AD0D) - Status: Failed - Invoked on: 2007-06-05 15:40:00 - Message: The job failed. Unable to determine if the owner (username) of job Replication agents checkup has server access (reason: Could not obtain information about Windows NT group/user 'username', error code 0x534. [SQLSTATE 42000] (Error 15404) The statement has been terminated. [SQLSTATE 01000] (Error 3621)).

How can I fix this? I looked under the Replication folder but didn't see anything.

In Object Explorer, look at the job in SQL Server Agent to see who owns it, and verify the account is valid.

|||That's for the help. I changed the owner but am now getting this error:

SQL Server Scheduled Job 'Replication agents checkup' (0xFC17DD4EF8E5694E8B786EE54CF8AD0D) - Status: Failed - Invoked on: 2007-06-07 01:00:00 - Message: The job failed. The Job was invoked by Schedule 175 (Replication agent schedule.). The last step to run was step 1 (Run agent.).

When I executed the maintenance plan manually it worked, but it doesn't seem to work on the schedule. I've never created a maintenance plans so I'm going to look up some more information to see if I can figure out what is going to wrong.

Replication issues after a Database Restore - Unable to drop or create Transactional Repli

Hi,

I have transactional replication set up on on of our MS SQL 2000 (SP4)
Std Edition database server

Because of an unfortunate scenario, I had to restore one of the
publication databases. I scripted the replication module and dropped
the publication first. Then did a full restore.

When I try to set up the replication thru the script, it created the
publication with the following error message

Server: Msg 2714, Level 16, State 5, Procedure SYNC_FCR To
GPRPTS_GL00100, Line 1
There is already an object named 'SYNC_FCR To GPRPTS_GL00100' in the
database.

It seems the previous replication has set up these system views
SYNC_FCR To GPRPTS_GL00100. And I have tried dropping the replication
module again to see if it drops the views but it didn't.

The replication fails with some wired error & complains about this
views when I try to run the synch..

I even tried running the sp_removedbreplication to drop the
replication module, but the views do not seem to disappear.

My question is how do I remove these system views or how do I make the
replication work without using these views or create new views.. Why
is this creating those system views in the first place?

I would appreciate if anyone can help me fix this issue. Please feel
free to let me know if any additional information or scripts needed.

Thanks in advance..

Regards,
Aravin Rajendra.you should be able to drop them using query analyzer.

--
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
"Query Builder" <querybuilder@.gmail.comwrote in message
news:1189702889.303360.189580@.o80g2000hse.googlegr oups.com...

Quote:

Originally Posted by

Hi,
>
I have transactional replication set up on on of our MS SQL 2000 (SP4)
Std Edition database server
>
Because of an unfortunate scenario, I had to restore one of the
publication databases. I scripted the replication module and dropped
the publication first. Then did a full restore.
>
When I try to set up the replication thru the script, it created the
publication with the following error message
>
Server: Msg 2714, Level 16, State 5, Procedure SYNC_FCR To
GPRPTS_GL00100, Line 1
There is already an object named 'SYNC_FCR To GPRPTS_GL00100' in the
database.
>
It seems the previous replication has set up these system views
SYNC_FCR To GPRPTS_GL00100. And I have tried dropping the replication
module again to see if it drops the views but it didn't.
>
The replication fails with some wired error & complains about this
views when I try to run the synch..
>
I even tried running the sp_removedbreplication to drop the
replication module, but the views do not seem to disappear.
>
My question is how do I remove these system views or how do I make the
replication work without using these views or create new views.. Why
is this creating those system views in the first place?
>
I would appreciate if anyone can help me fix this issue. Please feel
free to let me know if any additional information or scripts needed.
>
Thanks in advance..
>
Regards,
Aravin Rajendra.
>

|||Thanks for your response.. I tried dropping it thru QA.. Now the
replication doesn't show up on the publication. But the replication
monitor still has this replication with a failed status....

Can you please point me to the direction on safely removing all
components of a particular replication module (I have other publishers
in this server)..

Thanks again..

Aravin Rajendar.

On Sep 13, 2:37 pm, "Hilary Cotter" <hilary.cot...@.gmail.comwrote:

Quote:

Originally Posted by

you should be able to drop them using query analyzer.
>
--
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 FTShttp://www.indexserverfaq.com"Query Builder" <querybuil...@.gmail.comwrote in message
>
news:1189702889.303360.189580@.o80g2000hse.googlegr oups.com...
>

Quote:

Originally Posted by

Hi,

>

Quote:

Originally Posted by

I have transactional replication set up on on of our MS SQL 2000 (SP4)
Std Edition database server

>

Quote:

Originally Posted by

Because of an unfortunate scenario, I had to restore one of the
publication databases. I scripted the replication module and dropped
the publication first. Then did a full restore.

>

Quote:

Originally Posted by

When I try to set up the replication thru the script, it created the
publication with the following error message

>

Quote:

Originally Posted by

Server: Msg 2714, Level 16, State 5, Procedure SYNC_FCR To
GPRPTS_GL00100, Line 1
There is already an object named 'SYNC_FCR To GPRPTS_GL00100' in the
database.

>

Quote:

Originally Posted by

It seems the previous replication has set up these system views
SYNC_FCR To GPRPTS_GL00100. And I have tried dropping the replication
module again to see if it drops the views but it didn't.

>

Quote:

Originally Posted by

The replication fails with some wired error & complains about this
views when I try to run the synch..

>

Quote:

Originally Posted by

I even tried running the sp_removedbreplication to drop the
replication module, but the views do not seem to disappear.

>

Quote:

Originally Posted by

My question is how do I remove these system views or how do I make the
replication work without using these views or create new views.. Why
is this creating those system views in the first place?

>

Quote:

Originally Posted by

I would appreciate if anyone can help me fix this issue. Please feel
free to let me know if any additional information or scripts needed.

>

Quote:

Originally Posted by

Thanks in advance..

>

Quote:

Originally Posted by

Regards,
Aravin Rajendra.

Replication initializing

Hi All,
Log reader agent shows as initializing. I tried changing read batchsize from
1- 100 with noluck. If I start, it sometimes unable to do sp_repl
commands.....
Can you help?
Regards,
Prashanth
May be the logreader agent has a lot to catch up on - that is, lot of
replicated transactions in the log. I suggest you let it continue, and catch
the exact error message, if it fails. You can look at the error message by
right clicking on the agent and selecting 'Error Details'.
Optionally, you can increase the timeout values for the agent. See
"Replication Log Reader Agent Utility" in SQL Server 2000 Books Online.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Prashanth" <prashanthlv@.talisma.com> wrote in message
news:%23JuZor1DFHA.3376@.TK2MSFTNGP12.phx.gbl...
Hi All,
Log reader agent shows as initializing. I tried changing read batchsize from
1- 100 with noluck. If I start, it sometimes unable to do sp_repl
commands.....
Can you help?
Regards,
Prashanth