SQL Search not returning results for one database only

delapohl

After upgrading SSMS to 18.5 and then upgrading SQL Search to 3.5.0.2590, SQL Search is failing to return results for one of my databases. From what I can tell, searches on any other database on the same database server return expected results.

Attempting to re-index the problem database (name obscured in snippet below) yields the following error in the logs:

13 Apr 2020 17:24:18.299 [74] ERROR DatabaseObjectIndexer - Exception while indexing database XXXXXXX_DEV: <br>code = NotADb (26), message = System.Data.SQLite.SQLiteException (0x800007FF): file is not a database<br>file is not a database<br>&nbsp;&nbsp; at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)<br>&nbsp;&nbsp; at System.Data.SQLite.SQLiteCommand.BuildNextCommand()<br>&nbsp;&nbsp; at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)<br>&nbsp;&nbsp; at System.Data.SQLite.SQLiteDataReader.NextResult()<br>&nbsp;&nbsp; at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)<br>&nbsp;&nbsp; at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)<br>&nbsp;&nbsp; at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior)<br>&nbsp;&nbsp; at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()<br>&nbsp;&nbsp; at System.Data.SQLite.SQLiteConnection.Open()<br>&nbsp;&nbsp; at RedGate.SQLSearch.Indexing.SQLite.SqliteDatabaseConnection.EnsureConnectionOpen()<br>&nbsp;&nbsp; at RedGate.SQLSearch.Indexing.SQLite.SqliteDatabaseConnection.CreateCommand()<br>&nbsp;&nbsp; at RedGate.SQLSearch.Indexing.SQLite.DatabaseObjectStorage.Initialise()<br>&nbsp;&nbsp; at RedGate.SQLSearch.Indexing.DatabaseObjectIndexer.Index(CancellationToken )

A co-worker who has not upgraded SSMS or SQL Search is still able to use SQL Search to search this database.

delapohl

It looks like the log snippet got truncated in my post. Trying again:

13 Apr 2020 17:24:18.299 [74] ERROR DatabaseObjectIndexer - Exception while indexing database XXXXXXX_DEV:
code = NotADb (26), message = System.Data.SQLite.SQLiteException (0x800007FF): file is not a database
file is not a database
   at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
   at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
   at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
   at System.Data.SQLite.SQLiteDataReader.NextResult()
   at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
   at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior)
   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
   at System.Data.SQLite.SQLiteConnection.Open()
   at RedGate.SQLSearch.Indexing.SQLite.SqliteDatabaseConnection.EnsureConnectionOpen()
   at RedGate.SQLSearch.Indexing.SQLite.SqliteDatabaseConnection.CreateCommand()
   at RedGate.SQLSearch.Indexing.SQLite.DatabaseObjectStorage.Initialise()
   at RedGate.SQLSearch.Indexing.DatabaseObjectIndexer.Index(CancellationToken )

delapohl

I was able to fix this issue by deleting the Indexes directory from C:\Users\[username]\AppData\Local\Red Gate\SQLSearch 3.