Use of £ Symbol Results in False Differences

howarthcd

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

Chris Auckland

Thanks for your post.

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.

howarthcd

This is what we suspected, however we're keen to stick with ANSI (which we deliberately specified when scripting the database) which, for us, is the default encoding for applications such as SQL Server Management Studio and Notepad. We wish to ensure that all scripts use the same encoding when added to our source control software - enforcing non-standard defaults across a large development team would be tricky.

Applications such as Notepad are able to display these characters - why does SQL Compare handle them differently?

Chris

Chris Auckland

Thanks for your reply.

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.

Michelle T

If you're saving script folders (as opposed to synchronization scripts) you may be able to work around your problem by saving in Windows-1252 instead of ASCII. Most Windows programs like Notepad which are displaying £ symbols and saving in 'ASCII' are actually saving in Windows-1252 anyway - it's just a particular ANSI code page.

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.

howarthcd

Thanks for your responses.

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

Michelle T

When you save a script folder, SQL Compare also saves some information about that script folder (including the encoding) in an XML file - RedGateDatabaseInfo.xml.

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.

howarthcd

Michelle, thank-you very much - the comparison works absolutely fine after making that modification to the XML file.

Thanks
Chris