SQL Search Indexing Issue

JBrazell

I am running into issues with the Indexing of a couple of our databases so that it returns no results. Here is the error that I am running into 

2023-02-14 13:14:20.466 -06:00 [Information]  [DatabaseObjectStorage]: Inserting objects into index

2023-02-14 13:14:22.685 -06:00 [Information]  [DatabaseObjectStorage]: Done objects

2023-02-14 13:14:22.685 -06:00 [Information]  [DatabaseObjectStorage]: Inserting columns into index

2023-02-14 13:14:22.755 -06:00 [Error]  [DatabaseObjectIndexer]: Exception while indexing database MyDatabase: Arithmetic overflow error for data type smallint, value = 33304.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

   at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)

   at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)

   at System.Data.SqlClient.SqlDataReader.Read()

   at RedGate.SQLSearch.Indexing.SQLServer.SqlServerConnection.<ExecuteQuery>d__3.MoveNext()

   at RedGate.SQLSearch.Indexing.SQLServer.SqlDatabaseReader.<GetColumnsFromSyscolumnsQuery>d__10.MoveNext()

   at RedGate.SQLSearch.Indexing.SQLite.DatabaseObjectStorage.InsertColumns(IEnumerable`1 columns)

   at RedGate.SQLSearch.Indexing.DatabaseObjectIndexer.Index(ISqlDatabaseReader sqlServerConnection, IDatabaseObjectStorage indexStorage)

   at RedGate.SQLSearch.Indexing.DatabaseObjectIndexer.Index(CancellationToken token)

Object counts are as follows

D       DEFAULT (constraint or stand-alone)    226

F        FOREIGN KEY Constraint                     194

FN    SQL Scalar functions                                  6

IT      Internal table                                             22

P       SQL Stored Procedure                              23

PK    Primary Key                                            265

S       System base table                                    72

SN    Synonym                                                    1

SQ    Service Queue                                           3

TF     SQL table-valued-function                         1

TT     Table type                                                  3

U       User Table                                              317

UQ   UNIQUE Constraint                                    5

V      View                                                       469

Jon_Kirkwood

Hi @JBrazell

 

Thank you for reaching out on the Redgate forums regarding your SQL Search indexing issue.

 It looks like this may be a problem inserting into the sqlite database that stores the indexing. Clearing out the index for that database may clear the block.

 The indexes can be found in the following file path: C:\Users\<User Name>\AppData\Local\Red Gate\RedGate.SQLSearch.Addin 3\Indexes

If this doesn't help, can you please run the following queries in SSMS and confirm your results:

 

-- #1
SELECT c.id, c.name, t.name AS 'datatype', c.prec AS 'precision', cc.definition 
FROM sys.syscolumns AS c 
JOIN sys.sysobjects AS o ON c.id = o.id 
JOIN sys.types AS t ON c.xusertype = t.user_type_id 
LEFT JOIN sys.computed_columns AS cc ON cc.object_id = o.id AND cc.column_id = c.colid 
WHERE o.xtype NOT IN ('S', 'IT', 'SQ', 'P', 'PC', 'RF', 'X', 'AF', 'FN', 'FS') 
ORDER BY c.id, c.colid 
GO

 

-- #2 - CAST colid 
SELECT c.id, c.name, t.name AS 'datatype', c.prec AS 'precision', cc.definition 
FROM sys.syscolumns AS c 
JOIN sys.sysobjects AS o ON c.id = o.id 
JOIN sys.types AS t ON c.xusertype = t.user_type_id 
LEFT JOIN sys.computed_columns AS cc ON cc.object_id = o.id AND cc.column_id = CAST(c.colid AS INT) 
WHERE o.xtype NOT IN ('S', 'IT', 'SQ', 'P', 'PC', 'RF', 'X', 'AF', 'FN', 'FS') 
ORDER BY c.id, c.colid 
GO

 

-- #3 - CAST xusertype 
SELECT c.id, c.name, t.name AS 'datatype', c.prec AS 'precision', cc.definition 
FROM sys.syscolumns AS c 
JOIN sys.sysobjects AS o ON c.id = o.id 
JOIN sys.types AS t ON CAST(c.xusertype AS INT) = t.user_type_id 
LEFT JOIN sys.computed_columns AS cc ON cc.object_id = o.id AND cc.column_id = c.colid 
WHERE o.xtype NOT IN ('S', 'IT', 'SQ', 'P', 'PC', 'RF', 'X', 'AF', 'FN', 'FS') 
ORDER BY c.id, c.colid 
GO 

JBrazell

@Jon_Kirkwood

Thanks for your response. When I run any of these queries, I receive the error.
 Msg 220, Level 16, State 1, Line 2

Arithmetic overflow error for data type smallint, value = 33304.

Having the queries helped direct me in the correct direction. The issue is with the system views that Microsoft has provided. By querying the sys.syscolumns I receive the error because some of the columns have a colid (as well as another field or two) that is larger than the smallint. If I query the sys.columns the column_id field it is fine.  In their views sys.columns.column_id is defined as int and in the sys.syscolumns.colid it is defined as smalint. Thus the issue.

 This has allowed me to trace down reported issue on MS SQL Server related to this error. I need to look at why preferences of using sys.syscolumns rather than sys.columns table

Jon_Kirkwood

Hi @JBrazell

 

I have received a notification from our development team that a new release of SQL Search should resolve this error.

 If you are still having the issue can I suggest patching to v3.6.1 at your earliest convenience and verifying that this resolves it for you.

 Download link here: https://download.red-gate.com/checkforupdates/SQLSearch/SQLSearch_3.6.1.3286.exe