SQL History not showing anything

trek_madone

Hello,

SQL History has not been working for me for few months now. Every time I click SQL History, nothing is showing in Recent queries.
I ignored it because I thought it was just a bug that will be fixed in the next version, but I already received about 5 updates since and still not working. I have the version 10.14.4.4865.

I already tried the following:
1. Re-installed SQL Prompt
2. Deleted the folder AppData\Local\Red Gate and re-installed SQL Prompt
3. Re-installed SSMS and SQL Prompt
4. Deleted the file SqlHistory.db and I can see that it recreates the file when opening SSMS. However, queries are still not being saved in history.

Is anyone experiencing the same? 

Dan_J

Hi @trek_madone

Thanks for reaching out to us, I'm sorry you're seeing this issue with SQL Prompt!

That is very strange that you are seeing absolutely nothing in SQL History, especially after the steps you have followed to try to resolve the issue.

Would you mind confirming what authentication method do you use to connect to your database(s)?
 
Also, does performing an advanced search in SQL History (with Object type and Search period set to everything) reveal any queries your not seeing in the main view?

trek_madone

Hi Dan,

Thanks for your response.
I am using Windows Authentication.
Advanced Search also shows nothing.



If I uncheck the state Open, I get few results, but I can't open them either, throws an error.

Riz

Hi - i have been struggling with this same issue for a few months - and already tried the following:
1. Re-installed SQL Prompt
2. Deleted the folder AppData\Local\Red Gate and re-installed SQL Prompt
3. Re-installed SSMS and SQL Prompt
4. Deleted the file SqlHistory.db and I can see that it recreates the file when opening SSMS. However, queries are still not being saved in history.

Dan_J

Hi @trek_madone

Thank you for your reply on this. My apology for the delay in coming back to you!

We have had a couple of other customers (in addition to @Riz also) that are reporting some issues with SQL History. Would you mind confirming what version of SSMS you have installed? 

Also, regarding the error you are also seeing, could you enable Verbose Logging (https://documentation.red-gate.com/sp10/troubleshooting/finding-the-sql-prompt-log-files), recreating the error and providing the resulting log file? If you'd rather provide this file privately I'd be happy to reach out to you via the ticket we have logged for this issue.

trek_madone

Hi @Dan_J,

I have both SSMS 18.12.1 and 19.2. I recently installed 19.2 to try if it's working on this version but it's same behavior.

Sorry but I cannot send you log files because it's against our company policy. However, I have this error and it's consistent with all the logs:

~~~~~~~~~~~~~~~~~~~~~

2024-01-05 15:23:50.525 -06:00 [Information] Logging level set to 'Information'

2024-01-05 15:23:50.646 -06:00 [Information] Started successfully with identifier '***removed***'

2024-01-05 15:23:50.792 -06:00 [Error] Error converting message to object: {

  "clientIdentifier": "***removed***",

  "queryIdentity": "***removed***",

  "name": "SQLQuery3.sql",

  "path": "C:\\Users\\***removed***\\AppData\\Local\\Temp\\~vsE565.sql",

  "contents": "***removed***",

  "connection": {

    "server": "***removed***",

    "serverType": "***removed***",

    "authenticationType": "WindowsAuthentication",

    "database": "***removed***",

    "userName": "***removed***",

    "advancedOptions": {

      "connectioN_TIMEOUT": "30",

      "exeC_TIMEOUT": "0",

      "packeT_SIZE": "4096",

      "encrypT_CONNECTION": "False",

      "trusT_SERVER_CERTIFICATE": "False",

      "usE_CUSTOM_CONNECTION_COLOR": "False",

      "custoM_CONNECTION_COLOR": "-986896",

      "usE_CUSTOM_TENANT_ID": "False",

      "custoM_TENANT_ID": "",

      "database": "***removed***"

    }

  },

  "saveReason": "QueryEdited"

}

Newtonsoft.Json.JsonSerializationException: Cannot deserialize the current JSON object (e.g. {"name":"value"}) into type 'System.Collections.Generic.IReadOnlyDictionary`2[System.String,System.String]' because the type requires a JSON array (e.g. [1,2,3]) to deserialize correctly.

To fix this error either change the JSON to a JSON array (e.g. [1,2,3]) or change the deserialized type so that it is a normal .NET type (e.g. not a primitive type like integer, not a collection type like an array or List<T>) that can be deserialized from a JSON object. JsonObjectAttribute can also be added to the type to force it to deserialize from a JSON object.

Path 'operation.message.connection.advancedOptions.connectioN_TIMEOUT', line 1, position 635.

   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateObject(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)

   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateValueInternal(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)

   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.ResolvePropertyAndCreatorValues(JsonObjectContract contract, JsonProperty containerProperty, JsonReader reader, Type objectType)

   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateObjectUsingCreatorWithParameters(JsonReader reader, JsonObjectContract contract, JsonProperty containerProperty, ObjectConstructor`1 creator, String id)

   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateObject(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)

   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateValueInternal(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)

   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.ResolvePropertyAndCreatorValues(JsonObjectContract contract, JsonProperty containerProperty, JsonReader reader, Type objectType)

   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateObjectUsingCreatorWithParameters(JsonReader reader, JsonObjectContract contract, JsonProperty containerProperty, ObjectConstructor`1 creator, String id)

   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateObject(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)

   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateValueInternal(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)

   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.Deserialize(JsonReader reader, Type objectType, Boolean checkAdditionalContent)

   at Newtonsoft.Json.JsonSerializer.DeserializeInternal(JsonReader reader, Type objectType)

   at Newtonsoft.Json.Linq.JToken.ToObject(Type objectType, JsonSerializer jsonSerializer)

   at Newtonsoft.Json.Linq.JToken.ToObject(Type objectType)

   at Newtonsoft.Json.Linq.JToken.ToObject[T]()

   at RedGate.SqlPrompt.SqlHistory.Server.OperationConverter.<CreateCommandOperation>g__ToCommandMessage|3_0[T](JObject jObject)

2024-01-05 15:23:50.825 -06:00 [Error] Error deserializing operation json: Newtonsoft.Json.JsonSerializationException: Cannot deserialize the current JSON object (e.g. {"name":"value"}) into type 'System.Collections.Generic.IReadOnlyDictionary`2[System.String,System.String]' because the type requires a JSON array (e.g. [1,2,3]) to deserialize correctly.

To fix this error either change the JSON to a JSON array (e.g. [1,2,3]) or change the deserialized type so that it is a normal .NET type (e.g. not a primitive type like integer, not a collection type like an array or List<T>) that can be deserialized from a JSON object. JsonObjectAttribute can also be added to the type to force it to deserialize from a JSON object.

Path 'operation.message.connection.advancedOptions.connectioN_TIMEOUT', line 1, position 635.

   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateObject(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)

   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateValueInternal(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)

   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.ResolvePropertyAndCreatorValues(JsonObjectContract contract, JsonProperty containerProperty, JsonReader reader, Type objectType)

   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateObjectUsingCreatorWithParameters(JsonReader reader, JsonObjectContract contract, JsonProperty containerProperty, ObjectConstructor`1 creator, String id)

   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateObject(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)

   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateValueInternal(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)

   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.ResolvePropertyAndCreatorValues(JsonObjectContract contract, JsonProperty containerProperty, JsonReader reader, Type objectType)

   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateObjectUsingCreatorWithParameters(JsonReader reader, JsonObjectContract contract, JsonProperty containerProperty, ObjectConstructor`1 creator, String id)

   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateObject(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)

   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateValueInternal(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)

   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.Deserialize(JsonReader reader, Type objectType, Boolean checkAdditionalContent)

   at Newtonsoft.Json.JsonSerializer.DeserializeInternal(JsonReader reader, Type objectType)

   at Newtonsoft.Json.Linq.JToken.ToObject(Type objectType, JsonSerializer jsonSerializer)

   at Newtonsoft.Json.Linq.JToken.ToObject(Type objectType)

   at Newtonsoft.Json.Linq.JToken.ToObject[T]()

   at RedGate.SqlPrompt.SqlHistory.Server.OperationConverter.<CreateCommandOperation>g__ToCommandMessage|3_0[T](JObject jObject)

   at RedGate.SqlPrompt.SqlHistory.Server.OperationConverter.CreateCommandOperation(JObject jObject)

   at RedGate.SqlPrompt.SqlHistory.Server.OperationConverter.ReadJson(JsonReader reader, Type objectType, IOperation existingValue, Boolean hasExistingValue, JsonSerializer serializer)

   at Newtonsoft.Json.JsonConverter`1.ReadJson(JsonReader reader, Type objectType, Object existingValue, JsonSerializer serializer)

   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.DeserializeConvertable(JsonConverter converter, JsonReader reader, Type objectType, Object existingValue)

   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.Deserialize(JsonReader reader, Type objectType, Boolean checkAdditionalContent)

   at Newtonsoft.Json.JsonSerializer.DeserializeInternal(JsonReader reader, Type objectType)

   at Newtonsoft.Json.JsonConvert.DeserializeObject(String value, Type type, JsonSerializerSettings settings)

   at Newtonsoft.Json.JsonConvert.DeserializeObject[T](String value, JsonConverter[] converters)

   at RedGate.SqlPrompt.SqlHistory.Server.NamedPipeListener.ProcessOperationJson(String input, INamedPipeStream stream)

Dan_J

Hi @trek_madone

Many thanks for your update on this.

Please can you confirm the Newston.Json.dll version in the following file patch C:\Program Files (x86)\Red Gate\SQL Prompt 10.



The reason I ask is that we have seen it on a few occasions before that this issue is caused by the version of Newston.Json.dll file contained in C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\Newtonsoft.Json . Essentially,  the version contained in this file path is typically older than 13.0.1 and is overriding the newer version that is contained in the Redgate folder (C:\Program Files (x86)\Red Gate\SQL Prompt 10), which was updated in a recent release of SQL Prompt. 

If this is the case for you, the only way to get around this is to manually replace the Newtonsoft.Json file in the above file path (as the SQL Prompt installation does not modify this folder).

trek_madone

Same version:

trek_madone

Hi @Dan_J

Any update on this?

Dan_J

Hi @trek_madone

My sincere apology for the delay in replying to you on this! 

Thank you for confirming on the file version, clearly this isn't the issue.

Would it be at all possible for you to provide a copy of your SQLHistory.db and sqlhistory-search.json files? If you'd rather not provide these via this forum thread I can reach out to you directly from the support ticket we have logged for this issue.

trek_madone

Hi @Dan_J

Unfortunately, I can't provide you the SQLHistory.db. It's against our company policy. I think the log I provided above should be enough for your developers to start looking into this issue. 

kashyapmnvl

I have the exact same problem with SQL Tab History. I have tried downgrading to a different version of SSMS and SQL Prompt too. But the SQL Tab History is all blank.

sqlbug

I'm having this issue as well. So is a colleague of mine. Is there any update? My files and steps are all the same as OP.

DanielY

I am having the same issue ever since I upgraded to a newer version of SSMS SQL Server Management Studio 20.1.10.0