Use of £ Symbol Results in False Differences
howarthcd
Posts: 70 Bronze 3
When a database stored procedure, function or view containing a £ symbol as part of its definition is compared with an identical ANSI script file then, in the comparison window, the £ symbol in the script file is displayed as either ? or a box symbol, which results in a difference being flagged by SQL Compare. The £ symbol in the object definition is displayed correctly.
For a database containing hundreds of such objects this produces a significant overhead as each object must be manually reviewed to check for true differences.
If the script files are generated as unicode then the problem does not occur.
Is there a workaround to prevent this behaviour when using ANSI files? Scripting the database into unicode files is not an option for us, and we're reluctant to update our code to replace every occurance of the £ symbol with CHAR(163).
Thanks
Chris
For a database containing hundreds of such objects this produces a significant overhead as each object must be manually reviewed to check for true differences.
If the script files are generated as unicode then the problem does not occur.
Is there a workaround to prevent this behaviour when using ANSI files? Scripting the database into unicode files is not an option for us, and we're reluctant to update our code to replace every occurance of the £ symbol with CHAR(163).
Thanks
Chris
Comments
If a character (£ for example) is not in the character set for the encoding standard, then it will be represented with something like a ? or a square.
When you created the script folder using SQL Compare, what 'Script Encoding Type' did you set in 'Script creation options'? The £ symbol will be shown as a '?' if you use ASCII, but other types like unicode or UTF8 should represent the character correctly. As far as I'm aware UTF8 is backwards compatiable with ASCII and can represent all unicode characters.
I hope this is helpful.
Applications such as Notepad are able to display these characters - why does SQL Compare handle them differently?
Chris
When you say you deliberately set ANSI when scripting the database, which actual 'type' did you select in SQL Compare?
If you are referring to setting ASCII as the type, then any characters that are not part of the ASCII character set will be shown as '?' in the scripts.
If you open the ASCII SQL Compare script in notepad, you will see that the actual file contains the '?'. So SQL compare is comparing a ‘£â€™ to a ‘?’ as an ASCII file cannot contain a £.
Notepad and SSMS can show the characters because they can display Unicode characters, but if you write the file as ASCII it can only write ASCII characters.
Unfortunately we haven't got the option to save synchronization scripts in Windows-1252 yet - if you'd find this useful, we could add a feature request to the list to get this added to the options.
Redgate Software
We re-scripted the database to script folders using SQL Compare's ASCII option and, yes, the script files did contain '?' characters, which I duly replaced with '£' using Notepad before saving the files without changing the encoding.
The files are currently encoded using Windows-1252 (according to the file properties in Team Foundation Server) and, when opened in Notepad or SSMS, contain the '£' character.
When using SQL Compare to compare the database objects containing '£' to the Windows-1252 files containing '£', SQL Compare displays the script files' '£' as '?' even though other applications display '£'. SQL Compare then indicates that a difference is present, even when other applications and text editors display '£', as intended.
Presumably SQL Compare is treating the script files as being ASCII rather than Windows-1252 and, hence, the extended characters are being converted to '?' before comparison.
I hope this is a little clearer, but I don't see a way around this without either re-scripting to UNICODE (which is not workable as this will introduce inconsistencies due to the default encoding used by SSMS, which is where the majority of our scripts are created), or by replacing each occurance of '£' in our code with 'CHAR(163)'. :?
Thanks
Chris
Generally we recommend that you save another script folder with the right settings instead of manually modifying the XML file, but if that's not possible then you can try changing the value of the 'ScriptFileEncoding' element to 'Windows1252' with a text editor.
Then SQL Compare may stop trying to read the files as pure ASCII and start trying to read them as Windows-1252, which will probably work if that's what they are actually currently in.
Obviously we don't usually recommend this and can't be certain that it won't cause other problems, but it might help in your situation.
Redgate Software
Thanks
Chris