Importing Templates into Sql Server Management Studio

You can add templates to the template explorer by navigating to a directory and putting the files directly into a folder.

On Vista/Windows 7 the folder is:

C:\Users\user\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql

“user” should be replaced with your username and the 100 directory is for Sql Server Management Studio 2008 and you replace it with 90 for 2005!

On XP it is:

C:\Documents and Settings\user\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql

“user” should be replaced with your username and the 100 directory is for Sql Server Management Studio 2008 and you replace it with 90 for 2005!

You will have to restart management studio for the templates to come up. If you have 2005 and 2008 management studio and you use both then you will have to put the templates in the folder for each version also.

EXCEPT operator in Sql Server 2005+

I came across the EXCEPT operator in Sql Server 2005 and went’ and did a little bit of searching.

Pinal Dave has a nice simple example and shows that the execution plan between EXCEPT and NOT IN is the same making it no different except in how you code it obviously.

IT seems the Except operator can work over multiple columns it seems. So it tests all of the columns in both sides of the EXCEPT which makes it somewhat of an easily avoidable keyword it seems…(That is there seems to be other ways that you can do the same things without having to learn this new keyword, even though it is rather simple)

Firewall Rule for Sql Server 2005 on Windows 7

I have Sql Server 2005 installed on Windows 7 and have been trying to get a remote web application to connect to a database on my Windows 7 machine. I have been prevented from doing so while the firewall is on.

It was bit difficult to find the firewall rules and add the correct rule. The following process seems to work for me: (Information was found here under the “To access a program through the Windows Firewall” )

  1. Open the Control Panel
  2. Click “System and Security” if you are in “Category” view mode.
  3. Click “Windows Firewall”. (The green text)
  4. Click “Advanced settings” along the left hand side.
  5. Click “Inbound Rules” along the left hand side.
  6. Click “New Rule…”  to the right in the actions pane.
  7. Ensure “Program” is selected and click “Next >”
  8. Select “This program path:” radio button and click browse.
  9. You need to select the sqlservr.exe file which for my 2005 instance was located “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn”
  10. Click “Open”
  11. Click “Next >”
  12. Click “Next >” again
  13. Click “Next >” again
  14. Give it a name of “Sql Server 2005” or another appropriate name. I would suggest giving the service name maybe. I Called it this “Sql Server: Sql2005“
  15. Click “Finish”

You may need to also add the Sql Browser service so that any named instances can be found.

  1. Click “New Rule…” again in the actions pane.
  2. Select “Port” and click “Next >”
  3. Select “UDP”
  4. Select “Specific local ports” and enter the port number 1434
  5. Click “Next >”
  6. Click “Next >” again
  7. Click “Next >” again
  8. Enter a name like : “Sql Server Browser”

PowerShell Quickstart

Powershell Quickstart which seems rather like a cheetsheet

Get IIS Process ID’s and Application Pool ID’s for IIS 7

In this post I gave a batch file that lists the application pools running on IIS 6. Here is an equivelent for IIS 7.

@Echo off

cd C:\Windows\System32\inetsrv
appcmd list apps

@Echo on

Please note that you will have to run it as administrator on Vista or Windows 7.

Fiddler on Windows 7

Just a quick snippet about running Fiddler on Windows 7. Shows you how to alter Uri so that fiddler can catch traffic.

.NET Cryptography

Found some articles which explains cryptography principles which i’ve found helpeful

Cryptography in Microsoft .NET Part 1: Encryption

Cryptography the .NET Way (good articles)

Cryptography in .NET
See the first comment! :o) Important!

RSA Encryption in .NET — Demystified!