Have Geek Brain

Sql throws error when executing by transaction

I'm trying to follow the following syntax on a transaction, but it throws an error: -

this is the script I am executing per 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 am getting the following error: -

ALTER DATABASE is not allowed in a multi-op transaction. The procedure "sys.sp_fulltext_table" cannot be executed in a transaction. The procedure "sys.sp_fulltext_table" cannot be executed in a transaction. Procedure 'sys.sp_fulltext_table' cannot be executed in a transaction.

Do you guys have any ideas?

Edit: - I want to know if there is a way to do this. I am trying to change the datatype of columns in a database and they are enabled for full text search, so I want to do this somehow. :(

+1


a source to share


3 answers


What are you asking?



You are given an error message that accurately explains the problem. You cannot execute this SPROC on a transaction.

+3


a source


The problem here is that most data description statements (DDL) like Alter Database cause an implicit commit on execution. TSQL tries to protect you from unexpected results by telling you, "You can't do this."



+2


a source


You can try to roll up your transaction. Basically, at each step, record what was done and if something fails, manually undo the steps until the point of failure. This is dangerous because if there is a catastrophic failure, you may not rollback everything, and there is always a chance that you may miss some combination.

0


a source






More articles:


All Articles