Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sp_replicationdboption throwing error Expand / Collapse
Author
Message
Posted Tuesday, September 2, 2008 2:30 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 579, Visits: 1,807
Dear All,

I am very new to replication and just studying now. I am in the process of deleting the replication settings and I have deleted the publication(from SSMS) that was created from SSMS. As a next step deleted the subscriber database that was created for the above said publication. Now I am executing this query which disables the repltesting database for publishing and should give this message , 'The replication option 'publish' of database 'ReplTesting' has been set to false.'

exec sp_replicationdboption @dbname = N'ReplTesting'
, @optname = N'publish'
, @value = N'false'

but its giving me an error,

TABLES cannot be dropped
Msg 3609, Level 16, State 2, Procedure sp_MSdrop_pub_tables, Line 17
The transaction ended in the trigger. The batch has been aborted.

Might be a very simple and straightforward one but I could not figure out what the problem may be.

Thanks and appreciate any help.

Prakash.


---------------------------------------------------------------------------------
Post #562244
Posted Thursday, September 4, 2008 9:11 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 21, 2012 1:52 PM
Points: 696, Visits: 743
I don't mean to sound offensive, and apologize if this seems too easy: Do you have the name correct? Is it possible that the name you are using is either mistyped (watch capitalization) or incomplete? If you restore the subscriber database, then execute the procedure, do you get the same results? I would think you want to break the connection before you try to remove an end of a connection.
Post #563901
Posted Thursday, September 4, 2008 1:00 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 579, Visits: 1,807
Thanks Steve for your reply,

I checked the name again and its correct and tried the other proposed way of keeping subscriber and running this procedure but I got the same error. Interestingly enough I see this SP sp_replicationdboption present as a system stored procedure in the database but sp_MSdrop_pub_tables is not there. Does it ring any bell to you.


Thanks,
Prakash


---------------------------------------------------------------------------------
Post #564105
Posted Thursday, September 4, 2008 2:09 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:51 AM
Points: 6,266, Visits: 2,028
If the subscription was already deleted try
sp_removedbreplication  @dbname =  'dbname' 




* Noel
Post #564150
Posted Thursday, September 4, 2008 10:02 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 579, Visits: 1,807
Its throwing the same error,

TABLES cannot be dropped
Msg 3609, Level 16, State 2, Procedure sp_MSdrop_pub_tables, Line 17
The transaction ended in the trigger. The batch has been aborted.

The only configuration change that I made apart from what mentioned in my study guide was that the SQL Server agent service was not running and I started it from service console. I tried stopping it and then disabling the publilsh but to no use.

Thanks,
Prakash.


---------------------------------------------------------------------------------
Post #564315
Posted Friday, June 12, 2009 6:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 14, 2010 1:14 AM
Points: 1, Visits: 16
Its really useful information, its worked fine for me.
1. I deleted subscription as well as publication.
2. when i delete or drop the publication database.
3. i got an error this db is marked for replication so i can't delete this db.
4. after running this sp i.e sp_removedbreplication 'xxxdb'
5. successfuly dropted the xxxdb.

Thanks Noel

-Ratnam
Post #733740
Posted Saturday, August 28, 2010 2:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 5, 2014 9:19 AM
Points: 1, Visits: 27
I ran into the same problem. There is likely a DDL trigger in the database that was created from an earlier lesson in the Training kit. I dropped the DDL trigger and all was well.

DROP TRIGGER [tddl_tabledropprevent] ON DATABASE

Robert
Post #977026
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse