in the most portable way possible, using only ASCII in my query
Requiring ASCII-only will make it harder to achieve this goal in a truly reliable manner. If you only have one database, or if you have multiple databases that all have the same default Collation, then using
CHAR(asciiValue) as mentioned in @Nelson's answer would work. Just keep in mind that the character you get from using
CHAR(someValue) depends on the default Collation of the "current" or "active" database that the query is running in. The Collation determines the code page, and that can be different per each DB. The following simulates what would happen if the same ASCII value were used in different DBs that had different default Collations that used different code pages:
DECLARE @Test TABLE
[Latin1] VARCHAR(10) COLLATE Latin1_General_100_CI_AS_SC,
[Hebrew] VARCHAR(10) COLLATE Hebrew_100_CI_AS_SC,
[Greek] VARCHAR(10) COLLATE Greek_100_CI_AS_SC
SELECT CONVERT(BINARY(1), 233);
INSERT INTO @Test ([Latin1], [Hebrew], [Greek]) VALUES (0xE9, 0xE9, 0xE9);
SELECT * FROM @Test;
-- é י ι
when I send queries to a counterpart to perform database changes, the text file is opened and edited on different machines, and the special characters are damaged.
ASCII, is inherently difficult to make portable. ASCII text files have no ability to indicate what encoding was used to create them. The problem you are seeing is that the file is being written with one encoding / code page and then being read with another. If you open the file on your computer, does it look exactly as it should? Or has it already been altered? Because
é exists in many code pages, I suspect that it still looks fine on your computer, which means it is being read with the wrong encoding.
One way to avoid this is to ensure that the person opening the file opens it with the correct encoding. You can use the File Open dialog in SSMS to select a file, but don't click the Open button. Instead, click the down arrow on the right side of the Open button and select Open With.... Next, select SQL Query Editor with Encoding and click OK. A new dialog will pop up and will be a drop-down of encodings. The drop-down will default to (Auto-Detect) but that won't work here. Just select the encoding that you are using when saving the file and then click OK.
An easier and more reliable way is to use a Unicode encoding (UTF-8 or UTF-16) with a Byte Order Mark. The Byte Order Mark (BOM) is 2 to 4 bytes at the very beginning of a file that indicates the encoding used to create the file. The BOM is used to auto-detect the encoding, which means that you don't need to tell the person you are sending the file to what encoding to use. And, it doesn't require that you use Unicode data in the script; you current
INSERT statement with the
VARCHAR data should be unaffected.
You can do this by going to File | Save As... Then, just like with the Open dialog, click on the down arrow on the Save button and select Save with Encoding. Then select either Unicode (UTF-8 with signature) - Codepage 65001 or Unicode - Codepage 1200 and then click OK.