Sql threw error when executed under transaction

Question

Hi All, I am trying to execute following syntax under transaction but it throws error:-

this is the script that I am executing under transaction:-

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
-- full text search is installed. Run the necessary procedures
BEGIN
    declare @dbName nvarchar(128)
    select @dbName = DB_Name()
    exec('ALTER DATABASE [' + @dbName + '] SET RECOVERY SIMPLE')

    if(0 = DATABASEPROPERTY(DB_Name(),'IsFulltextEnabled'))
    BEGIN
     -- Full text is installed but not enabled on the Database. Enable that
     EXEC sp_fulltext_database 'enable' 
    END

    -- Check if there are current tables in full text search. If yes, remove them
    if(1 = INDEXPROPERTY(Object_id('Blog'),'PK_Blog','IsFulltextKey'))
    BEGIN
     -- Drop the full text index
     EXEC sp_fulltext_table 'Blog','drop'
    END
END

I get following error:-

ALTER DATABASE statement not allowed within multi-statement transaction. The procedure 'sys.sp_fulltext_table' cannot be executed within a transaction. The procedure 'sys.sp_fulltext_table' cannot be executed within a transaction. The procedure 'sys.sp_fulltext_table' cannot be executed within a transaction.

Guys do you have any idea??

Edit:- I want to know is there any way out to do this. I am trying to change the datatype of columns in the database and they are enabled for fulltextsearch hence I want above to be done somehow. :(

Answer 1

What are you asking ?

You've been given an error message that explains precisely whta the problem is. You can't execute that SPROC under a transaction.

Answer 2

The issue here it that most Data Definition Language statements (DDL) like Alter Database issue an implicit commit when executed. The TSQL code tries to protect you from some unexpected results by telling you "You can't do that".

Answer 3

You could try to roll your own transaction. Basically, at each step capture what was done and if anything fails, manually undo the steps up to the point of failure. It's dangerous though, because if there is a catastrophic failure then you may not rollback everything and there's always the chance that you might miss some combination.