SQL History Search Not working

When I try to search SQL History it doesn't return anything. I see the below error in the logs. I'm able to query the database directly using db browser, but i'd prefer to search SQL history from the SQL prompt interface.

2024-11-13 09:44:56.462 -05:00 [Information] Trimming history...
2024-11-13 09:44:56.581 -05:00 [Information] History trimming complete (time taken = 00:00:00.1200647)
2024-11-13 09:44:57.903 -05:00 [Information] [Startup status] Size on disk (bytes) = 81915904, Number of queries = 4460, Number of revisions = 11218, Highest revisions per query = 209, Longest contents (characters) = 907601
2024-11-13 09:44:57.996 -05:00 [Information] Indexing SQL History to C:\Users\zgelnett\OneDrive\Documents\WorkStuff\Settings\SSMS\SQL Prompt\SqlHistoryIndex...
2024-11-13 09:44:58.086 -05:00 [Error] Error indexing database
System.FormatException: Additional non-parsable characters are at the end of the string.
   at J2N.Numerics.ParseNumbers.StringToLong(String s, Int32 radix, Int32 flags, Int32 sign, Int32& currPos, Int32 length)
   at J2N.Numerics.Int64.Parse(String s, Int32 startIndex, Int32 length, Int32 radix)
   at Lucene.Net.Index.SegmentInfos.GetLastCommitGeneration(String[] files)
   at Lucene.Net.Index.SegmentInfos.FindSegmentsFile.Run(IndexCommit commit)
   at Lucene.Net.Index.SegmentInfos.Read(Directory directory)
   at Lucene.Net.Index.IndexWriter..ctor(Directory d, IndexWriterConfig conf)
   at RedGate.SqlPrompt.SqlHistory.Storage.Search.Indexing.IndexWriterWrapper..ctor(Directory directory)
   at RedGate.SqlPrompt.SqlHistory.Storage.Search.Indexing.Indexer.ReIndex(IDbCommand command)
   at RedGate.SqlPrompt.SqlHistory.Storage.Search.Indexing.Indexer.Index(IDbCommand command)
   at RedGate.SqlPrompt.SqlHistory.Storage.Search.CacheInitializer.Initialize(String sqlHistoryPath)

Answers

  • Good Afternoon @prozach99,

    Thanks for reaching out to Redgate Support and sorry to hear you are having some trouble with SQL Search. I have seen this issue in the past sometimes be caused by the user having an out-of-date version of SQL Search installed. Could I kindly have you confirm you have SQLSearch_3.7.0.4929.exe installed in this environment, and if not, install it and confirm if you are still seeing this issue?
  • Thank you for the reply. I installed that but it didn't fix the issue. The problem is SQL History not SQL Search. 

    For example there are recent queries that reference the table fw_error_log, but when I type it in the filter box nothing returns and I can see the error referenced above in the error log.

  • My Apologies @prozach99,

    I saw the words Search and SQL together and the rest is history. In that case the latest version of SQL Prompt is 10.15.0.12121.exe and the link is below:

    https://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_10.15.0.12121.exe

    Try upgrading that and then see if you are still seeing that same error. If that does not work, could I trouble you to attach your logs here for further review (SQL Prompt > Help > Locate Log Files)? Specifically, we would be looking for the below log message which may confirm a known issue.

    [Error] Error processing command: StoreOpenState
    System.FormatException: Additional non-parsable characters are at the end of the string.
    
  • prozach99prozach99 Posts: 14 Bronze 1
    That version did not fix the issue. I'm attaching the log file from today that includes that error here.
  • @prozach99 Roger that,

    Thanks for following up, I am discussing this internally. Will pop in here with more details shortly, this may indeed be a known issue I have seen previously.
  • prozach99prozach99 Posts: 14 Bronze 1
    Sure thing, thank you for looking into it.  I've had it happen before but then it resolved itself, I'm not sure what caused it or fixed it. It would be nice to have a fix.
  • @prozach99

    Good Morning, could I have you check your Registry Editor for  Computer\HKEY_CURRENT_USER\Software\Red Gate\SQL Prompt 10. If you have a key with the name   Options Folder then you may have set it to go to a new location. If this is the case I would check if you set this back to the default location if you are still getting this issue of it loading. Looks like its about 80mb so not sure if thats big enough to cause loading problems. 

    But check if they try in the default location. Would recommend you backup the file before you do anything too
Sign In or Register to comment.