SQL History Search not working

prozach99prozach99 Posts: 12 Bronze 1
in SQL Prompt
I'm not sure when it happened but the SQL History search feature stopped working for me. I see the sql history going back years when i bring up the history tool, but when I go to search the history for a known term, any term, I just get a message that there are "No items to display."

I don't use the history tool all the time so I'm not 100% sure when this stopped working. I am using SSMS v20 and SQL Prompt v 10.14.12.7945.

Tagged:
· Share on Twitter

Answers

  • chrisqtranchrisqtran Posts: 121 Silver 3
    Hi there,

    Thank you for reaching out.

    When opening up SQL Prompt, are you getting any error messages?
    · Share on Twitter
  • prozach99prozach99 Posts: 12 Bronze 1
    Hello, I am not seeing any error messages when i sign in. I just tried updating to the new version of sql prompt but that didn't fix the issue
    · Share on Twitter
  • escallinescallin Posts: 2 New member
    Hello, I am experiencing the same issue.  I am using SSMS 19.3.4.0 and SQL Prompt 10.14.10.

    Please see attached screenshots for reference.  I have a query in the history, but when I search for a word that I can clearly see in the query, it is not returned as one of the results.

    Why is the search not finding this query?






    · Share on Twitter
  • prozach99prozach99 Posts: 12 Bronze 1
    I found the error logs and see this in the error logs for sql history:

    2024-04-17 13:56:24.674 -04:00 [Error] Error processing command: StoreContentsRevision
    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.IndexUpdater.GetCurrentState(Guid queryIdentity)
       at RedGate.SqlPrompt.SqlHistory.Storage.Search.Indexing.IndexUpdater.AddContentsRevision(Guid queryIdentity, String name, String path, String contents, SqlHistoryConnection connection, DateTime dateSaved, Guid queryVersionIdentity, String originalName, String originalPath, String originalContents, SqlHistoryConnection originalConnection, DateTime originalDateSaved)
       at RedGate.SqlPrompt.SqlHistory.Storage.Commands.CommandStore.AddContentsRevision(Guid queryIdentity, String name, String path, String contents, SqlHistoryConnection connection, SaveReason saveReason)
       at RedGate.SqlPrompt.SqlHistory.Server.Commands.CommandProcessor.StoreContentsRevision(RevisionMessage message)
       at RedGate.SqlPrompt.SqlHistory.Server.Commands.CommandProcessor.Handle(CommandOperation operation)
    2024-04-17 14:01:29.903 -04:00 [Error] Error while searching for 'bank' (page: 1, object type: Everything, start date: <not set>, end date: <not set>, server: <not set>, database: <not set>, starred state: NotSet, open state: NotSet, sort: Relevance): Additional non-parsable characters are at the end of the string.

    <div></div>

    · Share on Twitter
  • prozach99prozach99 Posts: 12 Bronze 1
    I found this in the sql history error log:

    2024-04-17 13:56:24.674 -04:00 [Error] Error processing command: StoreContentsRevision
    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.IndexUpdater.GetCurrentState(Guid queryIdentity)
       at RedGate.SqlPrompt.SqlHistory.Storage.Search.Indexing.IndexUpdater.AddContentsRevision(Guid queryIdentity, String name, String path, String contents, SqlHistoryConnection connection, DateTime dateSaved, Guid queryVersionIdentity, String originalName, String originalPath, String originalContents, SqlHistoryConnection originalConnection, DateTime originalDateSaved)
       at RedGate.SqlPrompt.SqlHistory.Storage.Commands.CommandStore.AddContentsRevision(Guid queryIdentity, String name, String path, String contents, SqlHistoryConnection connection, SaveReason saveReason)
       at RedGate.SqlPrompt.SqlHistory.Server.Commands.CommandProcessor.StoreContentsRevision(RevisionMessage message)
       at RedGate.SqlPrompt.SqlHistory.Server.Commands.CommandProcessor.Handle(CommandOperation operation)
    2024-04-17 14:01:29.903 -04:00 [Error] Error while searching for 'bank' (page: 1, object type: Everything, start date: <not set>, end date: <not set>, server: <not set>, database: <not set>, starred state: NotSet, open state: NotSet, sort: Relevance): Additional non-parsable characters are at the end of the string.

    · Share on Twitter
  • prozach99prozach99 Posts: 12 Bronze 1
    I see this at the beginning of the log for today. Is there a way to repair/clean the database?

    2024-04-17 07:31:52.961 -04:00 [Information] Trimming history...
    2024-04-17 07:31:53.115 -04:00 [Information] History trimming complete (time taken = 00:00:00.1536573)
    2024-04-17 07:31:56.531 -04:00 [Information] [Startup status] Size on disk (bytes) = 75513856, Number of queries = 4212, Number of revisions = 9698, Highest revisions per query = 209, Longest contents (characters) = 907601
    2024-04-17 07:31:56.558 -04:00 [Information] Indexing SQL History to C:\Users\zgelnett\OneDrive\Documents\WorkStuff\Settings\SSMS\SQL Prompt\SqlHistoryIndex...
    2024-04-17 07:31:57.105 -04: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)
    · Share on Twitter
  • chrisqtranchrisqtran Posts: 121 Silver 3
    Hi @prozach99

    I'm going to create a support ticket for you so we can take a look at the logs. You will see it in your email shortly.

    @escallin
    I believe you already have a support ticket open, correct?
    · Share on Twitter
  • escallinescallin Posts: 2 New member
    @chrisqtran, yes I opened a support ticket for this.
    · Share on Twitter
  • prozach99prozach99 Posts: 12 Bronze 1
    Hello,

    This issue is happening again, I'm not sure what fixed it previously but right now I'm unable to search with the same error:

    2024-10-01 13:01:01.816 -04:00 [Error] Error while searching for 'mentor' (page: 1, object type: Everything, start date: <not set>, end date: <not set>, server: <not set>, database: <not set>, starred state: NotSet, open state: NotSet, sort: Relevance): Additional non-parsable characters are at the end of the string.


    I see this error earlier in the log:

    2024-10-01 10:26:40.640 -04:00 [Error] Error processing command: StoreContentsRevision
    System.FormatException: Additional non-parsable characters are at the end of the string.
    · Share on Twitter
  • prozach99prozach99 Posts: 12 Bronze 1
    My current workaround is to use DB Browser for SQLite and run the query there.

    select *
    from QueryVersions
    where Contents like '%mentor%'

    Gets me what I need but not as easy as the integrated tool.
    · Share on Twitter
Sign In or Register to comment.