Failed to import snippets - JSON value could not be converted

borateenq

I get this error message every time I start ADS. The snippet works fine so I think this message is erroneous and mostly just annoying. Also, I think it incorrectly references "SSMS" even though this snippet is not stored there.

Beginning of error message:

Failed to import snippets from SSMS: Error: Unhandled exception: 
System.Reflection.TargetInvocationException: Exception has been thrown 
by the target of an invocation.<br>&nbsp;---> 
System.Text.Json.JsonException: The JSON value could not be converted to
 System.String. Path: $['Clean phone number'].body | LineNumber: 5 | 
BytePositionInLine: 128.

The body of my snippet is:

"body": " REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(${1:PhoneField}, ')', ''), '(', ''), ' ', ''), '-', ''), '+', '') "

which, like I said, works fine as a snippet. To troubleshoot, I change the body to:

"body": ""

but I still get the error when opening ADS. When I comment out the snippet the error still appears referencing my next snippet. When I comment out all my snippets the error does go away.

• ADS version:

Version: 1.34.0 (user setup)
Commit: 83a4316cf89cbf961a0f09a72c861379cfd01fa9
Date: 2021-12-15T20:47:33.863Z
VS Code: 1.58.0
Electron: 12.0.9
Chrome: 89.0.4389.128
Node.js: 14.16.0
V8: 8.9.255.25-electron.0
OS: Windows_NT x64 10.0.19044

• Redgate SQL Prompt v0.2.9

I do not have Redgate SQL Prompt in SSMS, nor do I have any snippets in the SSMS AppData folder.

The snippets the error is referencing is in: '~\AppData\Roaming\azuredatastudio\User\snippets\sql.json'

Similar to https://forum.red-gate.com/discussion/88445/importing-snippets-into-ads-failing

Full error:

Failed to import snippets from SSMS: Error: Unhandled exception: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.<br> ---> System.Text.Json.JsonException: The JSON value could not be converted to System.String. Path: $['Clean phone number'].body | LineNumber: 5 | BytePositionInLine: 128.<br>   at System.Text.Json.ThrowHelper.ThrowJsonException_DeserializeUnableToConvertValue(Type propertyType)<br>   at System.Text.Json.JsonPropertyInfo.Read(JsonTokenType tokenType, ReadStack& state, Utf8JsonReader& reader)<br>   at System.Text.Json.JsonSerializer.ReadCore(JsonSerializerOptions options, Utf8JsonReader& reader, ReadStack& readStack)<br>   at System.Text.Json.JsonSerializer.ReadCore(Type returnType, JsonSerializerOptions options, Utf8JsonReader& reader)<br>   at System.Text.Json.JsonSerializer.Deserialize(String json, Type returnType, JsonSerializerOptions options)<br>   at System.Text.Json.JsonSerializer.Deserialize[TValue](String json, JsonSerializerOptions options)<br>   at RedGate.SqlPrompt.SnippetConverter.Ads.AdsSnippetSerializer.Deserialize(Stream stream) in D:\BuildAgentA\work\dc5fefc0dc183d00\SnippetConverter\Ads\AdsSnippetSerializer.cs:line 47<br>   at RedGate.SqlPrompt.SnippetConverter.Ads.AdsSnippets.Load(String snippetsFile) in D:\BuildAgentA\work\dc5fefc0dc183d00\SnippetConverter\Ads\AdsSnippets.cs:line 17<br>   at RedGate.SqlPrompt.SnippetConverter.SsmsToAdsSnippetConverter.Convert(String ssmsSnippetsFolder, String adsSnippetsFile) in D:\BuildAgentA\work\dc5fefc0dc183d00\SnippetConverter\SsmsToAdsSnippetConverter.cs:line 25<br>   at RedGate.SqlPrompt.SnippetConverter.Program.Run(Logger logger, Options options) in D:\BuildAgentA\work\dc5fefc0dc183d00\SnippetConverter\Program.cs:line 79<br>   at RedGate.SqlPrompt.SnippetConverter.Program.<>c__DisplayClass1_0.<Main>b__0(Options options) in D:\BuildAgentA\work\dc5fefc0dc183d00\SnippetConverter\Program.cs:line 34<br>   --- End of inner exception stack trace ---<br>   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor, Boolean wrapExceptions)<br>   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)<br>   at System.Delegate.DynamicInvokeImpl(Object[] args)<br>   at System.Delegate.DynamicInvoke(Object[] args)<br>   at System.CommandLine.Invocation.ModelBindingCommandHandler.InvokeAsync(InvocationContext context)<br>   at System.CommandLine.Invocation.InvocationPipeline.<>c__DisplayClass4_0.<<BuildInvocationChain>b__0>d.MoveNext()<br>--- End of stack trace from previous location where exception was thrown ---<br>   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass23_0.<<UseParseErrorReporting>b__0>d.MoveNext()<br>--- End of stack trace from previous location where exception was thrown ---<br>   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass16_0.<<UseHelp>b__0>d.MoveNext()<br>--- End of stack trace from previous location where exception was thrown ---<br>   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass27_0.<<UseVersionOption>b__1>d.MoveNext()<br>--- End of stack trace from previous location where exception was thrown ---<br>   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass25_0.<<UseTypoCorrections>b__0>d.MoveNext()<br>--- End of stack trace from previous location where exception was thrown ---<br>   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c.<<UseSuggestDirective>b__24_0>d.MoveNext()<br>--- End of stack trace from previous location where exception was thrown ---<br>   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass22_0.<<UseParseDirective>b__0>d.MoveNext()<br>--- End of stack trace from previous location where exception was thrown ---<br>   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass11_0.<<UseDebugDirective>b__0>d.MoveNext()<br>--- End of stack trace from previous location where exception was thrown ---<br>   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c.<<RegisterWithDotnetSuggest>b__10_0>d.MoveNext()<br>--- End of stack trace from previous location where exception was thrown ---<br>   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass14_0.<<UseExceptionHandler>b__0>d.MoveNext()<br><br>

squigley

Good Afternoon,

Thanks for your inquiry and sorry to hear about your issue with SQL Prompt in ADS.

If you try installing regular SQL Prompt alongside this does that help to workaround the issue?
https://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_10.10.4.26165.exe

borateenq

Hi squigley. I installed SQL Prompt 10.10.4 from the link, opened SSMS (did not log in to Red Gate), and restarted my PC. I still get the error when opening ADS.

VBJV

I have gotten this error as well.  When I finally found the problem, the error wasn't in the snippets file for ADS at all.  It was actually in the SSMS snippets file that it was trying to import from. In my case there was a a character sequence that in the XML file that, when copied over to JSON, messed up the JSON parsing.  I cannot remember exactly what the sequence was, but I think it was because I had a double-quote (") in one of the XML nodes of the snippet file.  You might check that to see if that solves your issue(s).

fyk_cz

Hi, I have the same problem. Also it crashes on SsmsToAdsSnippetConverter.cs on line 25. I don't use SSMS snippets at all, they are just installed with SSMS 20.2.30.0.

I am only using my snippets stored in the sql.json file in the default location.

How can I cancel this annoying error message?