SQL Search Indexing Issue

• 

  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 

  Jon_Kirkwood February 16, 2023 06:46

  - 0 +
• 
  @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

  JBrazell February 16, 2023 16:26

  - 0 +
• 

  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 February 23, 2023 06:19

  - 0 +

Add comment

Please sign in to leave a comment.