Using Symbolic Links with SSMS templates

For years I have used the template browser in SQL Server Management Studio to store commonly used queries. I have created a sub-folder prefixed with 'aa_ ' so it is listed first then created more sub-folders under that to store my various queries. Then i have those queries available to every server I am connected to. This keeps me from having to find remember if I stored the latest version in Confluence, on my laptop or on a network share. They are always available right from SSMS.

if you haven't used SSMS templates before, to to View→ Template Explorer to bring up the browser. Then you can create a folder structure there.

Now if you are like me, you have more than one edition of SSMS installed on your laptop. Currently I have SSMS 2014, 2016 and 2017. The different versions of SSMS use different folders to store templates, so with each new version I have to copy my aa_Rutherford folder from the old template location to the new. The problem is now they are out of sync, if I add folders or files in one version of SSMS they don't get updated in the others.

I solved this problem using something called symbolic links. If you have used Linux before you are probably familiar with these as they are used quite a bit by the operating system. Basically a symbolic link is a fake file system object that points to a different and real file system object. You can create a folder in one directory that is a link to a folder somewhere else on your machine. Then when you browse to that folder, you see the files and folders that exist in the other directory.

Might be a bit confusing so lets see an example. The command you use is: mklink /D [symlink_name] [C:\Path\To\Actual_folder]

Suppose we have a folder name aa_Rutherford located at C:\JRTemp and we wanted to create a symlink at C:\JR_NewTemp. First you need to launch a command prompt as administrator.

1. Use 'CD' to change to the new directory
2. We can use the 'DIR' command to see that it is empty
3. Run our 'MKLINK' command to create the symlink
4. Run 'DIR' again to see the new symlink and where it is pointing.
5. Then run 'DIR' on the symlink and you can see the files that are inside the destination location.

Here are the locations for the template folders in SSMS

2014 - C:\Users\[username]\AppData\Roaming\Microsoft\SQL Server Management Studio\12.0\Templates\Sql\

2016 - C:\Users\[username]\AppData\Roaming\Microsoft\SQL Server Management Studio\13.0\Templates\Sql\

2017 - C:\Users\[username]\AppData\Roaming\Microsoft\SQL Server Management Studio\14.0\Templates\Sql\