java.SQL.Exception - not full-text indexed

[isodius]

Hi,

I've successfully installed Integrity Server 10.3 with SQL Server 2008 R2. The server runs but in the server.log file, there is the following exception:

ERROR [mksis.IntegrityServer] * * * * ERROR * * * * (0): java.sql.SQLException -- java.sql.SQLException: Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Text3' because it is not full-text indexed.

I searched a by the Integrity documentation avec DB requirements and settings, but found nothing related to full-text indexing. This is not also a feature or setting of SQL Server.

Could someone tell me a bit more about this exception? How to fix it?

Thanks.

[jgorsline]

You are seeing this error because you don't have the correct database priviledges granted to the Integrity database user. The database user needs the db_owner role. This is outlined in the Installation guide.

To remedy this, give the Integrity database user the correct permissions, make sure you have case-sensitive collation installed on the database and enable Full Text Search.

[isodius]

Thanks Jeremy,

I won't be able to test your advice until this next monday. But I'll do asap.

Thanks again for the help.

[isodius]

Hi Jeremy,

I was able to analysis a deep further. I'm far from being a SQL Server expert, and we don't use Integrity for any production or "real stuff". We develop software solutions around Integrity.

As specified in the Integrity installation guide, we specify a mixed authentication mode for SQL Server. The local "sa" account is the one we used to create the Integrity DB.

You'll see here under that "sa" is the owner of the DB and the "Use full-text indexing" property is well checked.

The following screeshot shows the DB collation is the one specified in the Integrity document SQL_Latin1_General_CP1_CS_AS.

This was setup from the beginning, before the Integrity Server installation, I didn't change anything. And the issue remains. Does this issue prevent some features to run? What's its severity exactly?

Thanks.

[KaelLizak]

Hello IT,

I suspect based on the information provided that sa is not the user that Integrity is using to connect to the database.

Could you go to the filesystem the Integrity server is installed on, and do the following:

1. Navigate to <IntegrityServerInstallDirectory>/config/properties
2. Make a copy of is.properties (this can be on made on your own desktop, or any safe working directory)
3. Open up your copy of is.properties in a text editor (e.g. notepad.exe)
4. Search for the string mks.dbUser
   • The value of the line should not start with a hash mark (#), and should look like: mks.dbUser=IntegrityDBUser
     Where IntegrityDBUser is some valid MS SQL Server user
   • If mks.dbUser=sa, you should change it in the original file, since anyone who can navigate to that filesystem can get your database sa password, unless the passwords have been encrypted
5. In a maintenance period (when the Integrity server can be safely taken offline):
   1. Go back to SQL Studio and make sure that the user assigned to mks.dbUser has the required permissions on page 16, step 2 of the Integrity 10.3 Installation and Configuration Guide
   2. Restart the Integrity server for the changes to take effect

Regards,
Kael

[isodius]

Hi Michael,

Thanks to help. I do confirm: mks.dbUser=sa. And as the screenshot shows, sa is well the owner of the integrity database.

romuald

[isodius]

Hi,

I finally fixed the issue. The version of SQL Server we used didn't contain the Full-Text Search service. Pretty strange but even if the service is missing, you can have some "Full text search" features checked on the GUI.

Jeremy and Michael, thanks for all.

romuald