Archive for May, 2014

How to Display Line Numbers in the SQL Server Management Studio Query Window?

If it hasn’t already happened to you as a SQL Server DBA, you can bet on it that it will at some point in the near future. You are writing a script that has more than two pages, after you pat yourself on the back for a job well-done, you hit the execute button. To your horror, the script has an error. The error message references to a particular line number within your script. It is 3 am in the morning, you need to have this script ready and then head to bed for 3 hours of sleep before you have to get up to race to catch the railroad or the bus into the city. You are frustrated. You need to find the line number where the syntax error has occurred so that you can fix it or have a good starting point to dig a little deeper into what is the possible problem. Let’s “Keep Calm and Carry On.” SQL Server Management Studio (SSMS) has the option for you to enable the line numbers and here’s how it can be done:
1. Open SSMS. The on the menu bar, click on Tools > Options…
Tools and Options
2. On the “Options” dialog box, expand the “Text Editor” node then expand the “Transact-SQL” node. Click on “General” on the subtree and then on the “Display” section click on the “Line numbers” checkbox to enable or disable line numbering.
Line Number
3. Open a new query window and key in some T-SQL code. You will see the line number on the left hand side:
Query Window With Line Numbering

How to Change the Color in the SQL Server Management Studio Status Bar?

Have you ever executed a script that’s intended for testing purposes in the Production SQL Server environment rather than in the Development environment? If you have had this regrettable experience, don’t you wish SQL Server can give you color code warnings of the different types of environment? Well, actually, SQL Server does have this option and it is relatively easy to configure it. Similar to the traffic lights, you can configure red for Production, yellow for Staging, and green for Development. Just like traffic light, it can avert disasters or save you many hours to undo or attempt to undo the avoidable mistake.
Here’s how it is done:
1. Open SSMS.
2. On the “Connect to Server” dialog box, click the “Options >>” button.
Connect to Server
3. Click the “Connection Properties” tab, click on the “Select…” button to pick a color, and then click on the “Connect” button to connect to the designated SQL Server instance.
Connection Properties
Color Picker
4. Open a new query window. You will then see the color that you had picked as the background color on the status bar.
SSMS Red Status Bar