Categories
Database MSSQL MySQL

How to change the default file encoding in Sql Server Management Studio

Recently I was working on SQL code and data migration engagement for a major Financial institiuation. I was leading the team of SQL developers on this proposition. We have been provided thousands of SQL scripts and hundreds of Stored Procedures along with similar amount of user-defined SQL database functions to convert them in new supporting format. Developers were converting the SQL codes, saving it and committing these to the version-control system GIT.  During Database code analysis and review (which follows immediately after development) to reduce the number of “code smells” that creep into your database builds, the Governance team noticed something wrong with the file encoding.

So, What went wrong?

The file that is used to create a new query window has ANSI encoding but when they save the file it was in UTF-16. Unicode characters were broken 🙁

It didn’t took much time to figure out the issue and resolve, but I decided to write the article explaining steps to resolve.

The default encoding for SQL Server Management Studio sql files is UTF-16, more specifically, either Western European (Windows) - Codepage 1252 or Unicode - Codepage 1200. These encodings play havoc with a git diff as these encoding appear as binary files.

Advanced Save Options

The preferred encoding is Unicode (UTF-8 with signature) - Codepage 65001

How to solve the File encoding in SSMS?

We can change the default file encoding in order to be the one we want in the first place. What I have done was change from ANSI encoding to UTF-8.

  • From within SSMS, open the sql template file named SQLFile.sql, by default in one of these locations: –
    • %ProgramFiles%\Microsoft SQL Server\[Sql Version]\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\
    • %ProgramFiles(x86)%\Microsoft SQL Server\[Sql Version]\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\
    • %ProgramFiles%\Microsoft SQL Server\[Sql Version]\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql
    • %ProgramFiles(x86)%\Microsoft SQL Server\[Sql Version]\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql

My path to the file is C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql where the 140 stands for the SSMS v17 (in my case, right know I’m using the v17.5).

Save File with Encoding
  • Resave using correct encoding:
    • File => Save As
    • Click the arrow next to the Save button
    • Choose the relevant encoding: Unicode (UTF-8 with asignature) - Codepage 65001
Advanced Save Options

All new query windows will default to UTF-8 files

'Coz sharing is caring
Categories
App linux PHP

How to debug remote PHP code?

Are you maintaining, developing, debugging PHP application? Been in a situation to debug remote php code when site is in production? Are you taking your site down or using immatured die() or exit() while site is live? Gone are the days, now you can enjoy debugging without any disruption in service. You need to install xdebug on the remote server (only once).

php debugging on local system

The process of Xdebug installation differs from OS to OS. In this article we are covering Amazon Linux. Let’s install it on Amazon Linux.

$ sudo yum install php-pear
$ sudo pecl install xdebug

Forward the local xdebug port to remote server.

$ ssh -R 9669:127.0.0.1:9669 ec2-swatantra@your.remote.host

Run an xdebug client, like macGDBp, or Netbeans debugger.

Make sure to match the port forwarded above and set an IDE key in the debugger you are using.

Local Port: 9669
Remote Port: 9669
IDE Key: xdebug-swatantra

Also you can configure local to remote path mapping on Netbeans or on macGDBp to use code navigation with your local checked out code.

Local Path: /Users/swatantra/workspace/php-code
Remote Path: /home/ec2-swatantra/workspace/php-code

Initiate debugging on your debugger of choice.

Run the php cli command on remote host, remember to use the IDE key you wrote above.

$ alias phpdr="php -d zend_extension=/usr/lib64/php/5.6/modules/xdebug.so -d xdebug.remote_enable=1 -d xdebug.remote_autostart=1 -d xdebug.remote_host=127.0.0.1 -d xdebug.remote_port=9669 -d xdebug.idekey=xdebug-swatantra"
$ phpdr /home/ec2-user/workspace/php-code/src/script.php --option=value
php debugging on remote system

You can save the above alias ‘phpdr’ in your .bashrc or .zshrc based on your choice of shell.

'Coz sharing is caring