SQL Prompt Error '.', hexadecimal value 0x00, is an invalid character. Line 1, position 1.

surgen

I am dealing with this very annoying issue that is currently not allowing me to use SQL Prompt at all.

SSMS - v18.6
SQL Prompt - v10.3.4.15571

This error occurs whenever I open my SSMS, it shows up immediately. When I close it down and try to use any of the options in SQL Prompt drop-down (in SSMS top navbar) nothing happens. When I try to use auto-complete it seems like SQL Prompt is disabled. I suppose all of these issues are caused by the initial error that occurs at the SSMS loading.

I have tried the following:

1) Re-installing all Red Gate products on my machine
2) Re-installing SSMS
3) Uninstalling SSMS and Red Gate products, and then installing them back
4) Installing older version of SQL Prompt

None of these seem to have worked. Someone please help.

P.S. Visual Studio is also showing a popup message with the same error.

surgen

I was able to find figure out the solution to this issue.

1) Uninstalled SQL Prompt
2) I went into C:\Users\myuser\AppData\Local\RedGate folder and deleted SQL Prompt 10 folder
3) Installed SQL Prompt

Things are back to normal now.

Steve_Paxton

Hi all, i had same problem after i accidentally hit the power switch.

To fix the issue without re-installing SQL Prompt what I did was 
1) Exit SQL Management studio
2) Go to folder C:\Users\myuser\AppData\Local\Red Gate\SQL Prompt 10\
3) Rename the file (to test it first) RedGate_SQLPrompt_CommonUI_Options_UIOptions.xml to something else like RedGate_SQLPrompt_CommonUI_Options_UIOptions_old.xml
4) Run SQL Management studio

This will then re-generate this file. It seems this file got corrupted (i.e. its empty) once i run it up and worked I removed renamed file

Cassaundra

Steve_Paxton said:

Hi all, i had same problem after i accidentally hit the power switch.

To fix the issue without re-installing SQL Prompt what I did was 
1) Exit SQL Management studio
2) Go to folder C:\Users\myuser\AppData\Local\Red Gate\SQL Prompt 10\
3) Rename the file (to test it first) RedGate_SQLPrompt_CommonUI_Options_UIOptions.xml to something else like RedGate_SQLPrompt_CommonUI_Options_UIOptions_old.xml
4) Run SQL Management studio

This will then re-generate this file. It seems this file got corrupted (i.e. its empty) once i run it up and worked I removed renamed file

This worked for me, thank goodness. I didn't want to lose my entire setup. Thanks!

lvbarnes_d

Thanks for your post Steve_Paxton.  This started happening to me around 2021-01-15 and your steps resolved it without losing my snippets.

KathyPDX

I tried all of the above, but nothing has yet resolved that I get this same error message (and no part of SQL Prompt works).  I uninstalled, re-downloaded and re-installed, but get the same error.  One difference for me, from the instructions above, is that I found no Red Gate (nor RedGate) folder in my C:\Users\"myuser"\AppData\Local\ , nor a folder like 'SQLPrompt', or 'SQL Prompt', nor a file like 'UIOptions'.  The only thing in my AppData\Local folder is the Microsoft folder.  
 In my Program Files (x86) folder, there is a Red Gate/SQL Prompt 10 folder, but it has no xml files in it.  

I'd really like to continue to use this tool that my company bought for me, but I need more suggestions about how to resolve this problem.

lvbarnes_d

KathyPDX The AppData folder was a hidden folder.  I had to go to the Windows Explorer options, click on View and then enable it to show hidden folders.  HTH

KathyPDX

lvbarnes_d, Thank you!  I knew that AppData and subfolder Local are hidden, but I have often found folders and files within their structure (like the Microsoft one) from search results.  Interesting that some aren't found even by search.  I re-named that xml file, re-tried the process described above, and finally, SQL Prompt is working again! 

KathyPDX

I gave a positive report too quickly...Yes, SSMS launched, but I could not connect to any server ("Could not load file or assembly") which made working impossible.  I uninstalled SQL Prompt, and now I can connect to the servers again.  Just can't use SQL Prompt. 

Update (an hour later): I upgraded from SSMS v18.2 to v18.8, then re-installed SQL Prompt, and now it all seems to be working: connection to servers AND sql prompt.

KathyPDX

lvbarnes_d, Thank you!  I knew that AppData and subfolder Local are hidden, but I have often found folders and files within their structure (like the Microsoft one) from search results.  Interesting that some aren't found even by search.  Now that I have re-named that xml file, I'll re-try the process described above. 

KathyPDX

I gave a positive report too quickly    Yes, SSMS launched, but I cannot connect to any server, therefore I cannot work.  Now I get this problem:  https://forum.red-gate.com/discussion/87482/update-broke-ssms-and-now-i-cant-use-it

slow

3) Rename the file (to test it first) RedGate_SQLPrompt_CommonUI_Options_UIOptions.xml to something else like RedGate_SQLPrompt_CommonUI_Options_UIOptions_old.xml

4) Run SQL Management studio

This will then re-generate this file. It seems this file got corrupted (i.e. its empty) once i run it up and worked I removed renamed file

This worked for me!  Thank you!

The most irritating thing about this issue:  if you go to the Red Gate support page, and attempt to open a case, it asks for your serial number.  But you can't retrieve your serial number from SQL Prompt because SQL Prompt has died.  Explaining this in the field for serial number gets you a "that's not a valid serial number" message, after you've spent ten minutes documenting, and making annotated screen shots. 

And you can't call the support number.  I mean, you can, but nobody answers.  Not even a voicemail.

So:  thanks!

q_pac

Ahhh this is a big win, thanks @Steve_Paxton!