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

Editing the RedGate_SqlPrompt_Engine_EngineOptions.xml file causes error

I am using SQL Prompt 10.14.12.7945 on an Azure SQL Server DB in the cloud. There are 126 tables and 52 views to which I want to assign unique aliases. I know I can add them manually in the Options form, but instead I would like to generate the XML for them in VBA, and add that to the XML file : RedGate_SqlPrompt_Engine_EngineOptions.xml.

However, when I save the updated file using Notepad++ or VS2022 and then restart SSMS it comes up with this error message “Data at the root level is invalid. Line 1, position 1”.

Can anyone suggest why this is, and how to get around it, as the XML is validated.

TIA for any help

Bodders
0

Comments

3 comments

Sort by
  • ATurner
    Hi Bodders

    For this example below


    it is stored as 
      <UserDefinedAliases>&lt;UserDefinedAliases&gt;
      &lt;Count&gt;1&lt;/Count&gt;
      &lt;UserDefinedAlias&gt;
        &lt;ObjectName&gt;Facility&lt;/ObjectName&gt;
        &lt;Alias&gt;Fac&lt;/Alias&gt;
      &lt;/UserDefinedAlias&gt;
    &lt;/UserDefinedAliases&gt;</UserDefinedAliases>
    You can close SSMS, manually edited the XML directly to add another entry and then restart SSMS (just be sure to increase the count as well when you drop in another encoded XML alias chunk):
      <UserDefinedAliases>&lt;UserDefinedAliases&gt;  &lt;Count&gt;2&lt;/Count&gt;
      &lt;UserDefinedAlias&gt;
        &lt;ObjectName&gt;Facility&lt;/ObjectName&gt;
        &lt;Alias&gt;Fac&lt;/Alias&gt;
      &lt;/UserDefinedAlias&gt;
      &lt;UserDefinedAlias&gt;
        &lt;ObjectName&gt;abtest&lt;/ObjectName&gt;
        &lt;Alias&gt;abt&lt;/Alias&gt;
      &lt;/UserDefinedAlias&gt;
    &lt;/UserDefinedAliases&gt;</UserDefinedAliases>

    ATurner
    0
  • Bodders
    Thanks for that post - it was useful, but I am still having the same problem. I think it may be to do with the line breaks - it seems to need them but when I add them in VBA that seems to cause the problem
    Bodders
    Bodders
    0
  • ATurner
    Hi Bodders

    It will need to be in that exact format, or it will not work. 
    However, I cannot speak for if VBA would add these.
    ATurner
    0

Add comment

Please sign in to leave a comment.