sp_replicationdboption throwing error

Question

sp_replicationdboption throwing error

Nabha

SSCrazy Eights

Points: 8534
More actions
September 2, 2008 at 2:30 am

#212044

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

steve smith-401573 SSChampion Points: 10889 More actions · Answer 1

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 SSCrazy Eights Points: 8534 More actions · Answer 2

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 SSC Guru Points: 96590 More actions · Answer 3

If the subscription was already deleted try

sp_removedbreplication @dbname = 'dbname'

* Noel

Nabha SSCrazy Eights Points: 8534 More actions · Answer 4

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 SSC Journeyman Points: 93 More actions · Answer 5

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 SSC Rookie Points: 43 More actions · Answer 6

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