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
source
share