osql.exe and unicode files – how to save your sql scripts with encoding
osql.exe is a great application for running sql scripts in a batch. I use a batch file to execute multiple sql scripts that I use to rebuild my current application database from scratch. When developing a brand new application, deploying a database in this way makes it really easy to recreate the database just like it will be created on Day 1 when you build out the Production environment. This requires scripting out all of your sql objects and then also having a way to execute all of those sql scripts easily. That is where osql.exe comes in handy.
But osql.exe does have one issue that I ran into this week where it does not like UTF-8 (codepage 65001) or UTF-7 (codepage 65000) encoded files. And sometimes you need to include unicode characters in your sql scripts. At first I thought osql just did not support unicode but that is not the case… it just does not like the UTF-8 or UTF-7 encoding.
Trying to run a UTF-8 (codepage 65001) or UTF-7 (codepage 65000) encoded file with osql.exe will give you errors such as:
Incorrect syntax near '+'.
Incorrect syntax near ' '.
Incorrect syntax near 'ï'.
Saving the same file with Unicode Encoding (codepage 1200) will work just fine. Here is how to save sql scripts in Microsoft SQL Server Management Studio with a particular encoding (you can also use this method to see what type of encoding the file is saved in in the first place). One other thing to note is that Visual Studio has this same type of Save As… functionality.
From the Microsoft SQL Server Management Studio (or Visual Studio) File menu choose Save [FILENAME] As…
Then when the Save File As dialog appears you will see a little black arrow (inverted triangle) as part of the Save button.
Clicking the just the inverted triangle portion of the button will give you a menu.
Choosing the Save with Encoding… option will then present you with an Advanced Save Options dialog.
Here is where you can specify the encoding to use for the file. For osql.exe make sure you specify either Western European (Windows) – Codepage 1252 or Unicode – Codepage 1200. Do not select UTF-8 (codepage 65001) or UTF-7 (codepage 65000) or osql.exe will give errors when trying to parse the file.