How can we help you today? How can we help you today?

SQL History Search not working

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.

prozach99
0

Comments

10 comments

Sort by
  • chrisqtran
    Hi there,

    Thank you for reaching out.

    When opening up SQL Prompt, are you getting any error messages?
    chrisqtran
    0
  • prozach99
    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
    prozach99
    0
  • escallin
    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?






    escallin
    0
  • prozach99
    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>

    prozach99
    0
  • prozach99
    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.

    prozach99
    0
  • prozach99
    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)
    prozach99
    0
  • chrisqtran
    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?
    chrisqtran
    0
  • escallin
    @chrisqtran, yes I opened a support ticket for this.
    escallin
    0
  • prozach99
    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.
    prozach99
    0
  • prozach99
    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.
    prozach99
    0

Add comment

Please sign in to leave a comment.