Support/Recommendation for Full Text Catalogs/Indexes

[ronnieoverby]

Insight doesn't natively support the full text catalog/index types. So I thought I'd do something like:

-- SCRIPT [FullTextCatalog]
IF NOT EXISTS (
        SELECT *
        FROM sys.fulltext_catalogs
        WHERE NAME = 'FullTextCatalog1'
        )
    CREATE FULLTEXT CATALOG [FullTextCatalog1]

But, SQL server complains

Cannot create SQL object [FullTextCatalog]: CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.

My current workaround is to have this method execute previously ignored scripts after SchemaInstaller.Install:

// SchemaObjectCollection.Load is ignoring names like *.POSTEXEC.*

/// <summary>
/// Runs scripts after and outside of insight's installation process.
/// </summary>
private static void PostExecuteScripts(IDbConnection connection)
{
    var assembly = Assembly.GetExecutingAssembly();

    var scripts = assembly.GetManifestResourceNames()
        .Where(x => x.IndexOf(".POSTEXEC.", StringComparison.OrdinalIgnoreCase) != -1)
        .OrderBy(x => x)
        .Select(name =>
        {
            using (var stream = assembly.GetManifestResourceStream(name))
            using (var reader = new StreamReader(stream))
                return new {Name = name, SQL = reader.ReadToEnd()};
        });

    foreach (var script in scripts)
    {
        Console.WriteLine("Post Exec Script: "+ script.Name);
        connection.ExecuteSql(script.SQL);
    }
}

I'm wondering:

• Do you have a better workaround for now?
• Can Insight support full text search objects natively?
• Must all --SCRIPT's run within a user transaction?

[jonwagner]

Installs are done in a transaction to make sure that the database is always done in a consistent state.

I'd love to support fulltext, but as you found out, it's not supported within a transaction. (I found this out last week.)

Your solution is the best option at this point, but let me think about supporting non-transactional installs with some sort of rollback/compensating mechanism.