SQL Error: "Cannot use full-text search in user instance."

Question

I'm using SSEUtil to automatically create a functional test database from the schema derived from my development database (SQL Server 2008 R2) as part of my build process. This allows me to keep the two databases in-sync (schema-wise) without running SQL repository tests against my development database.

I recently added a full text index to a table for the first time for this project in my development schema and now when I attempt to apply the schema to a newly created unit test database I get the following error when it executes the SQL to create the full text catalog:

CREATE FULLTEXT CATALOG [FullTextCatalog] AUTHORIZATION [dbo]
GO

Cannot use full-text search in user instance.
[SqlException Number 9982, Class 16, State 100, Line 1]

I've searched high and low. The only help I found was on another site where someone suggested "attaching to the parent database." I have no idea what that means. And I have no idea what this error means. What is a "user instance"?

Can anyone suggest a way around this? Is there a better way to keep two database schemas in-sync for continuous integration?

Answer 1

Either

User must have CREATE FULLTEXT CATALOG permission on the database, or be a member of the db_owner, or db_ddladmin fixed database roles.

or

you won’t be able to use full text catalogues if the SQL instance does not have it installed. Make sure the SQL server instance has the full text service running.

Here are two ways of testing whether this feature installed or not. This feature is available for all editions of SQL Server 2005 and 2008 (including SQL Express)

1. Check the services applet (run -> type 'services.msc') for this entry : SQL Server FullText Search Or SQL Server FullText Search (SQLEXPRESS) (in case of SQL Express). Start this service of not running

2. Run the query "select fulltextserviceproperty('isfulltextinstalled')" in the Query analyzer. if the result is '1' then it is installed else not.

In the case of unavailability of this feature, you need to install by downloading the "SQLEXPR_ADV.EXE" from http://www.microsoft.com/express/sql/download/default.aspx

Reference taken from here

Answer 2

This is not really an answer to the question, but I came upon a similar issue with visual studio package manager when updating an entity framework code-first database (command update-database).

The problem was that I selected the wrong start-up project, and therefore visual studio was not using my connection string at all. It was connecting to a localdb instance, hence the cryptic error message "cannot use full text search in user instance". Once I made it connect to the actual database, it worked as expected.