Default encoding in SQL Server Management Studio

Question

I have recently had SSMS 2008 re-installed on my laptop

When I click "New Query..", I am now prompted to select the encoding for the file, something I was never asked to do before

I have associated sql againt "SQL Query Editor with Encoding" in the Text Editor section of the Option and restarted SSMS

What can I do to associate with an encoding as I don't want to select everytime I do a new query or open an existing sql file

Answer 1

In SQL Server Management Studio, select Tools/Options then expand "Text Editor" and add the extension "sql" and map it to "SQL Query Editor".

The solution works if you are working with ASCII characters. If the sql contains characters that require explicit encoding (as determined by the SQL editor) then you will must select an encoding. The issue comes into play when creating files in both the SQL editor and other editor (NotePad++), then editing those files in the other editor. NotePad++ saves files without the header and can guess the encoding. The SQL editor on the other hand always requires the encoding header once certain characters are used.

Answer 2

As per the workaround posted by Fakher Halim on Microsoft Azure Feedback Forums.

I had the same issue. Git wouldn't show any differences in history (besides "Binary files a/x.sql and b/x.sql differ). So I clicked Tools=>Options=>Environments=>International Settings. Changed the Language from "English" to "Same as Microsoft Windows". Now GIT DIFF works perfectly well -- reports version difference normally!

Answer 3

Based on the advice given elsewhere (https://feedback.azure.com/forums/908035-sql-server/suggestions/32892454-need-a-way-to-set-the-default-encoding-for-query-f) I've hacked up a "command script" to attempt to "change the default encoding" to UTF-8 with a BOM. The script works by writing a UTF-8 w/ BOM template to a SQLFile.sql file within the Program Files tree of SQL Server. It has worked once on my machine, but that doesn't mean that it's bugfree or 100% safe. USE AT OWN RISK!

According to "Microsoft's" response in that thread:

The default for how files are saved is determined by the way the main template file SQLFile.sql located at C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\sqlworkbenchprojectitems\Sql. If you save this file using ANSI encoding, then subsequent new query sessions will use this encoding.

Note that on my machine I found the template at a different path so I wrote the script to find any file matching that name under either Program Files. It seems like a silly enough name that false collisions should be rare.

If passed no arguments (or any arguments other than what it expects) then it should do a dry-run. Make sure to run it without administrative privileges first. It will at least find any applicable files and you can decide then if you want to proceed with the script or modify the files yourself.

To run the script for real then pass a first argument of "force". This is the dangerous part. Heed caution. To run the script to completion without pausing for user input then pass a second argument of "dontpause" (this can be passed with an empty first argument to skip pausing during a dry-run).

I choose UTF-8 because it's 7-bit ASCII compatible, it supports the vast majority of languages and characters you'll ever care to script, it stores Western text and source code very efficiently, and it's source control/text patch friendly. The template at the bottom of the script contains a byte-order mark (BOM) to signal editors that it is not ISO-8859-1 or some other incompatible default 8-bit encoding. Be sure it isn't lost when you copy/paste. Save the actual batch script as ASCII (or compatible; without a BOM!). The command processor chokes on a UTF-8 BOM.

@echo off
setlocal ENABLEEXTENSIONS ENABLEDELAYEDEXPANSION || exit /b 1

if "x%1" == "xforce" (
    echo Force enable. Danger Will Robinson! This means that I'm going to
    echo attempt to move files and write a new file to replace them. This
    echo is potentially destructive if there are any bugs in this script.
    echo In particular, if there are any unrelated files with the name
    echo sqlfile.sql under your Program Files directories then they'll be
    echo corrupted.
    echo.
    echo This script is going to require elevated privileges to write to
    echo Program Files. Execute the Command Prompt application as an
    echo administrator to proceed ^(should be harmless to try without
    echo elevation first^).
    echo.
    echo I RECOMMEND YOU _DO NOT_ RUN THIS SCRIPT FROM WINDOWS EXPLORER
    echo BECAUSE YOU MAY HAVE A HARDER TIME READING THE OUTPUT. Start a
    echo Command Prompt and run it there.
    echo.

    if not "x%2" == "xdontpause" (
        echo Now is a good time to Ctrl+C ^(and Y^).
        pause
    )
) else (
    echo Dry-run enabled. Pass a lone argument of "force" to go for real. 1>&2
    echo Be careful running the script several times. Your template backup
    echo will be overwritten and lost.
)

set paths="C:\Program Files (x86)\SQLFile.sql" "C:\Program Files\SQLFile.sql"

for /f "tokens=*" %%f in ('dir /a /b /s %paths% 2^>NUL') do @(
    echo.
    echo Found: %%f

    if "x%1" == "xforce" (
        echo Moving to: %%f.orig
        echo.
        echo If you ^(or anything else^) has made any changes to your template
        echo then you should be able to recover your template from the backup
        echo file above ^(not yet, once you continue^) ^(alternatively,
        echo recover the template from the source file now^).

        if not "x%2" == "xdontpause" (
            pause
        )

        move "%%f" "%%f.orig" || exit /b 1
    )

    echo.
    echo Writing a standard UTF-8 template with byte-order mark ^(BOM^).
    echo Feel free to open this file up afterward and manually set the
    echo encoding preferred. You can also replace it with your own
    echo template text.

    if not "x%2" == "xdontpause" (
        pause
    )

    set ok=0

    rem Read in myself, look for the __BEGIN__ marker and write
    rem subsequent lines to the file.
    for /f "tokens=*" %%g in (%~dpf0) do @(
        if !ok! == 1 (
            if "x%1" == "xforce" (
                if "x%%g" == "x." (
                    echo.
                    echo.>>"%%f"|| exit /b 1
                ) else (
                    echo %%g
                    echo %%g>>"%%f"|| exit /b 1
                )
            )
        ) else (
            if "%%g" == "__BEGIN__" (
                echo.
                echo Found marker. Starting write on next ^(non-empty^) line... 1>&2
                echo.
                set ok=1
            )
        )
    )
)

exit /b 0

Below is the SQL template. Lines containing only a dot (.) represent
blank lines. Actual blank lines will be lost.

__BEGIN__
BEGIN TRANSACTION;
.
SET XACT_ABORT ON;
.
.
.
ROLLBACK;
--COMMIT;

Answer 4

Previous answers have suggested using Tools→Options→Environments→International Settings, but this option is not present in SSMS 17.2.

Instead, you can go to File→Save As..., click the arrow on the "Save" button, and select "Save with Encoding".