Recently we were working on adjusting the configuration on a database which had just been moved to another server during a routine technology upgrade of our development servers (Reviewing and configuring your SQL Server instance). No users were yet allowed in the system and hence, one of the team members was surprised to note a “Number of Users” count higher than 1 in the database properties window of the SQL Server Management Studio (Getting Started with SSMS).

The team member obviously thought that the value of “Number of Users” indicated the number of distinct, currently open connections to the database. The confusion that the team member went through is understandable, and that’s what inspired me to write this quick post today.

The “Number of Users” value is actually the number of database users associated with the database – it is not, I repeat: not, the number of concurrent users connected to the database at a given point in time.

The number of users associated with a database for AdventureWorks2008R2 sample database is 4:

I trust the above quick post helps clear out the confusion around the “Number of Users” value in the database properties window of SSMS.

Until we meet next time,

Be courteous. Drive responsibly.

There are many productivity improving and usability tips and tricks related to the SQL Server Management Studio (SSMS). Ever since I started my blog (about 2 years ago), I have been writing mostly about these tips, and the list never ends! In today’s post, I will show how to configure the SSMS to recognize URLs within code comments.

One may place URLs in code comments for many reasons. Those that are applicable for me are:

• Referencing a specification
  
• Referencing a code defect/enhancement: The URL would point to the related TFS defect
  
• A link to Sharepoint or any collaboration tool that development teams may be using
  
• A link/citation to a website/blog that has been used as reference

For most languages & file extensions that the SSMS can handle, using Ctrl+click on any hyperlink opens the built-in web-browser and launches the associated page within SSMS. [One difference between SSMS for SQL 2012 and SQL 2008 is that the SSMS for SQL 2008 does not show the hyperlink in blue/underline].

The SSMS for SQL Server 2012 and below (tested for SQL Server 2008 and SQL 2008 R2), provide a text editor option – “Enable single-click URL navigation” that allows the user to control this behaviour. To access this option, go to Tools –> Options –> Text Editor –> All languages –> General (one can do this for specific languages too).

Options->Text Editor)” src=”http://media.beyondrelational.com/images.ashx?id=2e1b88231cf24e9c91c41ca0192eb778&w=-1&h=-1″ width=468 height=275>

Un-checking this option will cause SSMS to stop recognizing hyperlinks within code comments:

Options causes SSMS to stop recognizing hyperlinks.’ src=”http://media.beyondrelational.com/images.ashx?id=600b25791c0948408aec09821a2834b9&w=-1&h=-1″ width=510 height=148>

The benefit of keeping “Enable single-click URL navigation” checked is that when developing and reviewing T-SQL code, it becomes very easy to reference and reach out to related documentation. I trust you found this feature useful.

Until we meet next time,

Be courteous. Drive responsibly.

Recently, I deployed Microsoft SQL Server 2012 on to one of my test workstations which already had SQL Server 2008 R2 installed on it. Later, I uninstalled SQL Server 2008 R2 from the workstation and suddenly, I noticed something strange – the SQL Server related script and solution files which were supposed to open up in SSMS failed to do so – the icon was set to a default “unknown program” too.

To re-associate these files with the SSMS, there are two possible options:

Option 1: Use the Control Panel to associate a program to a given file type

1. Launch the Control Panel
   
2. Go to Programs –> Default Programs
   
3. Select “Associate a file type or protocol with a specific program”
   
4. Scroll through the window and select the required file extension
   
5. Choose “Change Program” and select the required program (in this case, SSMS) to associate it with the file type at hand

(*This method is valid for SQL Server 2012 and below).

Option 2: Use the SSMS to restore broken file associations related to Visual Studio shell

The SSMS is nothing but a Visual Studio shell. Hence, the SSMS for SQL Server 2012 extends the core benefits of Visual Studio 2010 shell. One such feature/benefit is the ability to Restore File Associations from the Tools menu.

1. Within the SSMS for SQL Server 2012, go to Tools –> Options
   
2. Under the General page in the Environment node, click on “Restore File Associations”
   
3. 
   
4. The following confirmatory message will be received, indicating that the file associations have been restored as expected
   
5. 

(*This method is applicable from SQL 2012 only. SSMS for SQL Server 2008 R2 and below does not have this feature.)

(Please NOTE: For me, this did not work as expected when using the SSMS for SQL Server 2012 RTM. However, when I deployed SQL Server 2012 SP1, things worked as expected. Do share your findings in the comments below as applicable.)

Until we meet next time,

Be courteous. Drive responsibly.

Recently, I deployed Microsoft SQL Server 2012 on to one of my test workstations which already had SQL Server 2008 R2 installed on it. Later, I uninstalled SQL Server 2008 R2 from the workstation and suddenly, I noticed something strange – the SQL Server related script and solution files which were supposed to open up in SSMS failed to do so – the icon was set to a default “unknown program” too.

To re-associate these files with the SSMS, there are two possible options:

Option 1: Use the Control Panel to associate a program to a given file type

1. Launch the Control Panel
   
2. Go to Programs –> Default Programs
   
3. Select “Associate a file type or protocol with a specific program”
   
4. Scroll through the window and select the required file extension
   
5. Choose “Change Program” and select the required program (in this case, SSMS) to associate it with the file type at hand

(*This method is valid for SQL Server 2012 and below).

Option 2: Use the SSMS to restore broken file associations related to Visual Studio shell

The SSMS is nothing but a Visual Studio shell. Hence, the SSMS for SQL Server 2012 extends the core benefits of Visual Studio 2010 shell. One such feature/benefit is the ability to Restore File Associations from the Tools menu.

1. Within the SSMS for SQL Server 2012, go to Tools –> Options
   
2. Under the General page in the Environment node, click on “Restore File Associations”
   
3. 
   
4. The following confirmatory message will be received, indicating that the file associations have been restored as expected
   
5. 

(*This method is applicable from SQL 2012 only. SSMS for SQL Server 2008 R2 and below does not have this feature.)

(Please NOTE: For me, this did not work as expected when using the SSMS for SQL Server 2012 RTM. However, when I deployed SQL Server 2012 SP1, things worked as expected. Do share your findings in the comments below as applicable.)

Until we meet next time,

Be courteous. Drive responsibly.

Database Consistency Check, abbreviated as DBCC commands are perhaps the best friends of any DBA. Following are some of the most common DBCC commands that can be found in almost all the script banks:

1. CHECKDB
   
2. CHECKIDENT
   
3. CHECKALLOC
   
4. CHECKTABLE
   
5. TRACEON
   
6. TRACEOFF
   
7. SHOWCONTIG

I am poor at remembering the syntax of these commands and therefore take occasional help of the SQL Server Books On Line (BOL) to get a hint on the parameters that a particular DBCC command uses. However, there are cases when one is working directly off the development or quality assurance servers, which generally do not have BOL installed. In such cases, the easiest way to obtain help for the DBCC commands is to use another, obvious DBCC command – DBCC HELP!

Here’s a quick example:

DBCC HELP (‘CHECKIDENT’)

GO

Running the above returns the following text, which is essentially the syntax help for DBCC CHECKIDENT:

dbcc CHECKIDENT
(
    ‘table_name’
    [ , { NORESEED
        | { RESEED [ , new_reseed_value ] }
    } ]
)
    [ WITH NO_INFOMSGS ]

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

So the next time you are stuck with the syntax for a DBCC command, ask DBCC for HELP before reaching out for the BOL.

(The catch: To the best of my knowledge DBCC HELP only works for the documented DBCC commands, similar to the BOL.)

Until we meet next time,

Be courteous. Drive responsibly.