Ability to add custom commands to ..\internal\sql\clear-azurebacpacdatabase.sql about d365fo.tools HOT 17 CLOSED

Is implemented with 0.3.41 release. Please take it for a spin.

from d365fo.tools.

Hi Paul,

Could you share the script that you use ?

Would you say that everyone needs to execute this part / step also, and should it be executed always?

Have you found this yourself or did you find it on a blog or got it from microsoft?

from d365fo.tools.

One part of our vertical is an extended search for items, customers, vendors ... where we use full text indexes on several table. On this table we add so called stop lists for the full text indexes. The problem with this stop list is that bacpac will throw an error because of an unsupported element. Because of that we have to delete the stop list before export. So every environment that has stop lists setup have to do this. My script looks like this:
ALTER FULLTEXT INDEX ON [FULLTEXTINDEXTABLE1INDEX] SET STOPLIST = SYSTEM
ALTER FULLTEXT INDEX ON [FULLTEXTINDEXTABLE2INDEX] SET STOPLIST = SYSTEM
ALTER FULLTEXT INDEX ON [FULLTEXTINDEXTABLE3INDEX] SET STOPLIST = SYSTEM
DROP FULLTEXT STOPLIST [MyEmptyStopList];

from d365fo.tools.

Maybe we could scan the database for all FullText indexes and if any, just remove them? Then the solution don't need any hook points, it will just run the same code every time.

But - what about creating them on the receiving environment? Is that done with a classic DB sync?

Just want to make sure we don't break anything for you guys.

from d365fo.tools.

Yes maybe there is a SQL command to do this in a generic manner.
After restore to the database in a TIER 1 environment we recreated the stop list with a runnable class.

from d365fo.tools.

That you can execute with
Invoke-D365SysRunnerClass -ClassName CustomClass

If you didn't know :)

from d365fo.tools.

Ok, we can find all full text indexes using stop list with following command
select * from sys.fulltext_indexes where stoplist_id != 0
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-fulltext-indexes-transact-sql?view=sql-server-2017

And all stop lists with the following command
select * from sys.fulltext_stoplists
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-fulltext-stoplists-transact-sql?view=sql-server-2017

So there is the possibility for a generic approach. Let me try to find some time to build a generic SQL for this...

from d365fo.tools.

@PaulHeisterkamp
Could you run this on one of your environments?
(https://dba.stackexchange.com/questions/31646/find-fulltext-indexes-in-sql-server-2008/31651#31651)

SELECT
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName, 
    c.name AS FTCatalogName ,
    f.name AS FileGroupName,
    i.name AS UniqueIdxName,
    cl.name AS ColumnName
FROM 
    sys.tables t 
INNER JOIN 
    sys.fulltext_indexes fi 
ON 
    t.[object_id] = fi.[object_id] 
INNER JOIN 
    sys.fulltext_index_columns ic
ON 
    ic.[object_id] = t.[object_id]
INNER JOIN
    sys.columns cl
ON 
    ic.column_id = cl.column_id
    AND ic.[object_id] = cl.[object_id]
INNER JOIN 
    sys.fulltext_catalogs c 
ON 
    fi.fulltext_catalog_id = c.fulltext_catalog_id
INNER JOIN 
    sys.filegroups f
ON
    fi.data_space_id = f.data_space_id
INNER JOIN 
    sys.indexes i
ON 
    fi.unique_index_id = i.index_id
    AND fi.[object_id] = i.[object_id];

and see if that finds all your expected full text indexes?

from d365fo.tools.

@PaulHeisterkamp
These link might have some of the basics we are after to actually create the needed drop statement.

https://www.sqlshack.com/automatically-maintain-full-text-indexes-catalogs/

https://www.mssqltips.com/sqlservertip/5496/script-to-distribute-existing-sql-server-full-text-indexes-across-multiple-catalogs/

Let us know when you have something, then we can do some testing and have it built into the module.

from d365fo.tools.

Here you go:

DECLARE @_SQL NVARCHAR(4000)

-- ALTER FULLTEXT INDEX ON [TableName] SET STOPLIST = SYSTEM'
IF object_id('tempdb..#TMPSETSTOPLIST') IS NOT NULL
DROP TABLE #TMPSETSTOPLIST;
CREATE TABLE #TMPSETSTOPLIST (
TableName [nvarchar] (250)
);

DECLARE cur CURSOR
FOR
select object_NAME(sys.fulltext_indexes.object_id) as TableName from sys.fulltext_indexes where stoplist_id != 0

OPEN cur;

DECLARE @TableName nvarchar;

FETCH NEXT
FROM cur
INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #TMPSETSTOPLIST (TableName)
VALUES (@TableName);

FETCH NEXT
	FROM cur
	INTO @TableName;

END;

CLOSE cur;

DEALLOCATE cur;

DECLARE cur CURSOR
FOR
SELECT TableName
FROM #TMPSETSTOPLIST;

OPEN cur;

FETCH NEXT
FROM cur
INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @_SQL = N'ALTER FULLTEXT INDEX ON ' + QUOTENAME(@TableName) + ' SET STOPLIST = SYSTEM'
PRINT (@_SQL)
EXEC SP_EXECUTESQL @_SQL

FETCH NEXT
	FROM cur
	INTO @TableName;

END;

CLOSE cur;

DEALLOCATE cur;

-- DROP FULLTEXT STOPLIST [FullTextStopListName];
IF object_id('tempdb..#DROPFULLTEXTSTOPLIST') IS NOT NULL
DROP TABLE #DROPFULLTEXTSTOPLIST;
CREATE TABLE #DROPFULLTEXTSTOPLIST (
StopListName [nvarchar] (250)
);

DECLARE cur CURSOR
FOR
select name from sys.fulltext_stoplists

OPEN cur;

DECLARE @StopListName nvarchar;

FETCH NEXT
FROM cur
INTO @StopListName;

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #DROPFULLTEXTSTOPLIST (StopListName)
VALUES (@StopListName);

FETCH NEXT
	FROM cur
	INTO @StopListName;

END;

CLOSE cur;

DEALLOCATE cur;

DECLARE cur CURSOR
FOR
SELECT StopListName
FROM #DROPFULLTEXTSTOPLIST;

OPEN cur;

FETCH NEXT
FROM cur
INTO @StopListName;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @_SQL = N'DROP FULLTEXT STOPLIST ' + QUOTENAME(@StopListName) + ';'
PRINT (@_SQL)
EXEC SP_EXECUTESQL @_SQL

FETCH NEXT
	FROM cur
	INTO @StopListName;

END;

CLOSE cur;

DEALLOCATE cur;

from d365fo.tools.

@PaulHeisterkamp
Could you share the code on pastebin.com - then we don't loose anything in the formatting here on github :)

from d365fo.tools.

Sure: https://pastebin.com/S6NXNe9i

from d365fo.tools.

@PaulHeisterkamp
Do you know if we need to run the ALTER statement at all, when we are dropping the index just after? Is that the proces defined by MS or just how you have done it until now? Just making sure we do what is necessary :)

from d365fo.tools.

You cannot DROP the stop list if it´s used on a table, so we need to run both statements.

from d365fo.tools.

Cool - didn't know that :)

I guess that we only need to run this against a Tier 2 (or higher). Right?

from d365fo.tools.

Yes, we only need this when we want to create a BacPac form AzuerSQL so Tier 2 (or higher)

from d365fo.tools.

Works just fine!

from d365fo.tools.