What are the challenges you face when working across database platforms? Take the survey
Options

SQL Search Indexing Issue

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
Tagged:

Answers

  • Options
    Jon_KirkwoodJon_Kirkwood Posts: 377 Silver 5
    edited February 16, 2023 6:47AM

    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&nbsp;
    FROM sys.syscolumns AS c&nbsp;
    JOIN sys.sysobjects AS o ON c.id = o.id&nbsp;
    JOIN sys.types AS t ON c.xusertype = t.user_type_id&nbsp;
    LEFT JOIN sys.computed_columns AS cc ON cc.object_id = o.id AND cc.column_id = c.colid&nbsp;
    WHERE o.xtype NOT IN ('S', 'IT', 'SQ', 'P', 'PC', 'RF', 'X', 'AF', 'FN', 'FS')&nbsp;
    ORDER BY c.id, c.colid&nbsp;
    GO

     

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

     

    -- #3 - CAST xusertype&nbsp;
    SELECT c.id, c.name, t.name AS 'datatype', c.prec AS 'precision', cc.definition&nbsp;
    FROM sys.syscolumns AS c&nbsp;
    JOIN sys.sysobjects AS o ON c.id = o.id&nbsp;
    JOIN sys.types AS t ON CAST(c.xusertype AS INT) = t.user_type_id&nbsp;
    LEFT JOIN sys.computed_columns AS cc ON cc.object_id = o.id AND cc.column_id = c.colid&nbsp;
    WHERE o.xtype NOT IN ('S', 'IT', 'SQ', 'P', 'PC', 'RF', 'X', 'AF', 'FN', 'FS')&nbsp;
    ORDER BY c.id, c.colid&nbsp;
    GO 
    Jon Kirkwood | Technical Support Engineer | Redgate Software
  • Options
    @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
  • Options

    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

    Jon Kirkwood | Technical Support Engineer | Redgate Software
Sign In or Register to comment.