1

I'm pretty sure this is not a SQL Server problem.

I already asked a question HERE with an awesome explanation, BUT I still can't explain to the guys where I work that it has nothing to do with SQL Server collation or etc.

the situation:

We create scripts ( INSERTS/DELETS ) using ANSI. We send these scripts to our clients. Out clients run these scripts via SQLPLUS, SSMS, or any other method.

When they use something that is using ANSI, there's no problem.

BUT we would like to FORCE soomething, to tell them something like "hey, you are running this script with a different encode, and some characters will be worngly formated, like an ã would be xE3.

is there a way to force a script to run on it's native encode ( the encode that I created )?

I really have no idea how to dead with this problem. We want to sent a script, and we want to be sure that this script will run with A being A, Ã being Ã.

  • Cant you tell them what tool to use to run your scripts and provide an example of how to run ? This is a problem that is NOT sql server related as you rightly said. – Kin Shah Jun 21 at 17:59
  • Hey Kin, they're using for both oracle and sql server, for example, SSMS, SQPLUS ( for oracle ), and some other 3rd party tools. I really know this is not a database problem, but I'm tired of people here throwing this problem on my shoulders. and well, it would be a great oportunity to learn. – Racer SQL Jun 21 at 18:44
  • Is the problem concerning DB object names, string data, or both? – Solomon Rutzky Jun 21 at 20:03
  • Since our tables are already there for some time, the problem arises when they're inserting new data. And it's not every time, it's only when they open the Script in a incorrect way, with a different encode we sent them, – Racer SQL Jun 21 at 20:32
2

One option might be to have them run the script via sqlcmd with the codepage options:

sqlcmd -f <codepage> | i:<codepage>,o:<codepage>

Alternately, save the source files with UTF-8 encoding (code page 65001), with a byte-order mark.

  • That's the problem mark. When we send the scripts using ANSI, everything is OK. when we sent them the script using UTF-8, they report to us saying that there are strange characters. I will just be sure we were using BOM. Thanks for the SQLCMD tip, will test it. – Racer SQL Jun 21 at 18:42
1

As I suggested in that previous answer that you linked to in the question, the only way for a script to identify itself as a particular encoding is by using a Byte Order Mark (BOM). This is only available for Unicode encodings.

  • If you are using SSMS, you open the "Save As..." dialog, pull down the arrow on the right side of the Save ▼ button, select "Save with Encoding...", and in the "Encoding" drop-down, select "Unicode (UTF-8 with signature) - Codepage 65001" (towards the top of the list).
  • If you are creating the script in Notepad++ (noted in the title of this question), go to the "Encoding" menu and select "Encode in UTF-8-BOM".

Setting a Byte Order Mark at the very least allows for a program that reads the file to detect the encoding. This does not guarantee that the script will be opened in, or read by, a program that will read the BOM, but if the clients are using SSMS or Notepad++, not sure about SQLPLUS (haven't used it in many years), then those programs do properly detect the BOM.

Of course, if a program that reads a file that has a BOM does not detect BOMs, then the script will probably have 2 or 3 "strange" characters at the very beginning. Those are the Byte Order Mark characters that are removed by programs that detect the BOM.

Beyond that, here are some other ideas to consider:

  1. I think you might be able to have the script itself detect if it has been opened with the correct encoding by comparing a supplementary character saved as itself with the same character generated by SQL Server. When the script is saved as UTF-8, the byte sequence of this character is 0xF09F9983. If the script is opened as UTF-8, you will see: "🙃". BUT, if the script is opened as "ANSI", you will see: "🙃". If the character is not correct, you can print an error and turn execution off:

    -- top of script
    IF (N'🙃' <> NCHAR(0xD83D) + NCHAR(0xDE43))
    BEGIN
      RAISERROR('
        This script is encoded in UTF-8 but has been opened using a non-UTF-8 encoding.
        Please re-open this script either as UTF-8, or try switching the encoding
        to UTF-8 if that is an option.', 16, 1);
      SET NOEXEC ON;
    END;
    
    -- the next line is for testing; just to prove that execution is OFF
    PRINT 5;
    -- stuffs
    
    
    
    -- at the very end of the script
    GO
    SET NOEXEC OFF;
    -- the next line is for testing; just to prove that execution is back ON
    PRINT 6;
    GO
    

    To see where I got the byte sequences from, just look at https://unicode-table.com/en/1F643/ and there is a chart at the bottom. The UTF-16BE hex listing is what you want for the NCHAR(0x....) + NCHAR(0x....)

  2. Since this is string data, it can be encoded in a "safe" format that does not require any specific encoding. I do either of the following tricks, depending on the situation:

    1. For only a few non-ANSI characters here and there, you can do them individually:

      SELECT UNICODE(N'Ã'); -- 195
      
      SELECT N'start of txt' + NCHAR(195) + N'more text';
      
    2. For many characters, just convert the string to its binary representation:

      SELECT CONVERT(VARBINARY(MAX), N'start of txt' + NCHAR(195) + N'more');
      --  0x7300740061007200740020006F0066002000740078007400C3006D006F0072006500
      
      INSERT INTO dbo.SomeTable (columnName)
      VALUES (CONVERT(NVARCHAR(100), 
          0x7300740061007200740020006F0066002000740078007400C3006D006F0072006500);
      

      This hex representation can be concatenated into regular string values:

      VALUES (N'some text' + CONVERT(NVARCHAR(50), 0x73007400610072007400...)
             + N'more text')
      

I'm not sure how to implement those ideas for Oracle, but the concept should be the same.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.