Non-transaction SQL migration through span

im trying to create full-text directories in SQL and transfer all scripts via Flyway, but im getting an error message when trying to transfer full-text directories.

Migration V1_5__FullTextSearch.sql failed
-------------------------------
SQL State  : S1000
Error Code : 574
Message    : CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.
Location   : C:/Users/User1/Desktop/flyway-3.2.1/sql/V1_5__FullTextSearch.sql (C:\Users\User1\Desktop\flyway-3.2.1\sql\V1_5__FullTextSearch.sql)
Line       : 152
Statement  : EXEC sp_CreateFullTextSearch

Is there a way to avoid forcing the use of a gateway for user transactions or any workaround for creating FullText directories.

PD:

Im creates the FullText directories in the stored procedure, but im has the same luck:

CREATE PROCEDURE sp_CreateFullTextSearch
AS
BEGIN
    DECLARE @FullTextQuery AS nvarchar(MAX)= N'
    IF not EXISTS (SELECT 1 FROM sys.fulltext_catalogs
       WHERE name = ''MerchantSearchCat'') 
    begin
        CREATE FULLTEXT CATALOG MerchantSearchCat
        AUTHORIZATION [dbo]
    end

    IF not EXISTS (SELECT 1 FROM sys.fulltext_catalogs
       WHERE name = ''IndividualSearchCat'') 
    begin
        CREATE FULLTEXT CATALOG IndividualSearchCat
        AUTHORIZATION [dbo]
    end

    IF not EXISTS (SELECT 1 FROM sys.fulltext_catalogs
       WHERE name = ''CustomerSearchCat'') 
    begin
        CREATE FULLTEXT CATALOG CustomerSearchCat
        AUTHORIZATION [dbo]
    END

    IF not EXISTS (SELECT 1 FROM sys.fulltext_catalogs
       WHERE name = ''ItemSearchCat'') 
    begin
        CREATE FULLTEXT CATALOG ItemSearchCat
        AUTHORIZATION [dbo]
    END'

    EXEC sp_executesql @FullTextQuery
END
+4
source share

Source: https://habr.com/ru/post/1598420/


All Articles