SQL History Search Not working

prozach99

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)

cperez

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?

prozach99

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.

cperez

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.

prozach99

That version did not fix the issue. I'm attaching the log file from today that includes that error here.

cperez

@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.

prozach99

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.

cperez

@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

prozach99

Ahh, yes I have that registry setting. I used this guide to save my history in Onedrive, I have history going back to 2013. 

Removing that entry resolved the issue; however, I lost all the historical SQL. When I change it back the issue returns.

Is there a way to fix the database or something so I don't have to lose all that?  

On a side note, restoring open queries has been going a bit crazy lately, opening up a ton of duplicate tabs, I'm guessing these two issues are related.

prozach99

I've found I can use this query in DB Browser for SQLite to get similar behavior, however it would be great if this worked in the SQL History tool:

select qv.DateSaved, qv.Contents, qvp.Server, qvp.Database, qvp.Name

from Queries as q

inner join QueryVersions qv on q.Identity = qv.QueryIdentity

inner join QueryVersionProperties as qvp on qvp.Identity = qv.QueryPropertiesIdentity

inner join (select QueryIdentity, max(datesaved) as maxDateSaved

from QueryVersions

group by QueryIdentity) as qv2 on qv2.QueryIdentity = qv.QueryIdentity

and qv2.maxdatesaved = qv.DateSaved

where Server is not null

and Contents like '%<some string>%'

order by DateSaved DESC