How to create an index or full-text search directory from a .NET application?

Question

I am trying to deploy a full-text search solution with SQL Server 2008 R2 and Orchard CMS with a multi-tenant configuration. Each tenant has its own database. We use automatic data migration to manage database schema changes. I would like to create a full text directory and index using migration. However, I get the following error:

CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.

I know the cause of the error, but I can’t determine the workaround. I tried to open a new database connection using the System.Data.SqlClient.SqlConnection class inside the application and somehow configured it to not create transactions, however this did not achieve anything.

Is it possible to create a full text index / directory through a .NET provider instead of directly connecting to the database via SSMS?

EDIT:

I tried with this code with SMO:

 var server = new Server(new ServerConnection(new SqlConnection(connectionString))); var database = server.Databases[databaseName]; var fulltextCatalog = new FullTextCatalog(database, fullTextCatalogName); fulltextCatalog.Create(); 

This leads to the same exception.

Answer 1

The user must have CREATE FULLTEXT CATALOG permission in the database or be a member of the db_owner or db_ddladmin fixed database roles.

Answer 2

You can create and manage full-text indexes - and almost any other SQL Server object - using SMO .