SQL History Search Not working
prozach99
Posts: 14 Bronze 1
in SQL Prompt
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)
Tagged:
Answers
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?
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.
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.
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.
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