One of my SQL Server databases was returning an error 102 while creating a full-text stoplist. We were trying to create a stoplist based on the system stoplist and later also tried to create a blank stoplist. The error happened both via SSMS, and equivalent TSQL commands.

The Error, 102

The following TSQL gave the error –

USE [DEMO_Database]
GO
CREATE FULLTEXT STOPLIST [DemoStopList]
AUTHORIZATION [dbo];
GO

CREATE FULLTEXT STOPLIST [DemoStopList]
FROM SYSTEM STOPLIST
AUTHORIZATION [dbo];
GO

The error dialog box –

The text in the error message –

TITLE: Microsoft SQL Server Management Studio
------------------------------

Cannot execute changes.

------------------------------
ADDITIONAL INFORMATION:

Create failed for FullTextStopList 'Demo'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.5058.0+((SQL11_PCU_Main).140514-1820+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+FullTextStopList&LinkId=20476

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Incorrect syntax near 'STOPLIST'. (Microsoft SQL Server, Error: 102)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=102&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

The Cause

I looked at the MSDN page related to the TSQL command to check if I was using the right syntax.

REFERENCE:

• CREATE FULLTEXT STOPLIST (Transact-SQL)
  https://msdn.microsoft.com/en-us/library/Cc280405(v=sql.105).aspx

My syntax was correct but there was something else on the page that looked relevant. Right at the top of the documentation page is the following message –

Important
CREATE FULLTEXT STOPLIST, ALTER FULLTEXT STOPLIST, and DROP FULLTEXT STOPLIST are supported only under compatibility level 100. Under compatibility levels 80 and 90, these statements are not supported. However, under all compatibility levels the system stoplist is automatically associated with new full-text indexes.

To verify if the compatibility level of my database could indeed be an issue, I checked the properties of the database by –

SELECT
is_fulltext_enabled,
compatibility_level
FROM
sys.databases

is_fulltext_enabled	compatibility_level
0	90

There you have it! My database was originally on a SQL Server 2005 installation so its compatibility level was 90, and that was the reason the CREATE/ALTER/DROP STOPLIST commands were unavailable. The current server that I was working on was SQL Server 2008 R2, which could be checked by –

SELECT @@VERSION
GO

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1

So the resolution to the error lies in changing the compatibility level. As per the documentation, the highest compatibility level I could go on a SQL Server 2008 R2 installation was 100.

REFERENCE:

• View or Change the Compatibility Level of a Database
  https://msdn.microsoft.com/en-us/subscriptions/index/bb933794
• ALTER DATABASE Compatibility Level (Transact-SQL)
  https://msdn.microsoft.com/en-us/subscriptions/index/bb510680

Changing the Compatibility Level

I checked that no other users were connected to the database and then issued this command to change the compatibility level.

USE [master]
GO
ALTER DATABASE [DEMO_database]
SET COMPATIBILITY_LEVEL = 100;
GO

It ran successfully and I could verify in the sys.databases catalog view that the compatibility level has changed to 100.

Now I was able to create a Stop List, Full-text Catalog and a Full-text Index on my table, and was able to run queries using the CONTAINS and CONTAINSTABLE keywords.

Fixed? Not so fast!

Interestingly, even though I could use the Full-text features now, the is_fulltext_enabled property still showed up as 0 (i.e. Disabled).

That was fixed by running the following –

EXEC [DEMO_Database].[dbo].[sp_fulltext_database]
@action = 'enable'
GO

REFERENCE:

• sp_fulltext_database (Transact-SQL)
  https://msdn.microsoft.com/en-us/library/ms190321(v=sql.105).aspx