SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sp_replicationdboption throwing error


sp_replicationdboption throwing error

Author
Message
Nabha
Nabha
SSC Eights!
SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)

Group: General Forum Members
Points: 933 Visits: 1814
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.

---------------------------------------------------------------------------------
steve smith-401573
steve smith-401573
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1037 Visits: 744
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.
Nabha
Nabha
SSC Eights!
SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)

Group: General Forum Members
Points: 933 Visits: 1814
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

---------------------------------------------------------------------------------
noeld
noeld
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9934 Visits: 2048
If the subscription was already deleted try
sp_removedbreplication  @dbname =  'dbname' 




* Noel
Nabha
Nabha
SSC Eights!
SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)

Group: General Forum Members
Points: 933 Visits: 1814
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.

---------------------------------------------------------------------------------
irudayams-900345
irudayams-900345
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 17
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
Robert Forgie
Robert Forgie
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 33
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search