Find duplicates in a table - SSMS Tools to the rescue

The other day one of my co-workers was asking if there is a quick way to insert a SQL query to find duplicates in a given column in Microsoft SQL Server Management Studio (SSMS) similar to how you can insert SQL scripts, i.e. “Select Top 1000 Rows”, “Edit top 200 Rows” etc., when you right-click on a table name.

You could sort of do this using Template Explorer feature in SSMS, but it is not very user-friendly. A while back I have installed an add-in for SSMS called SSMS Tools Pack to help me organize length SQL Scripts into regions and sub-regions. This tool is very useful and I have yet to capitalize on all of the features that it offers.

This tool has a feature called Custom Scripts and it is very powerful.
Using the Custom Scripts feature, I have created a template that looks like this to quickly find duplicate values under a given column:

SELECT |ObjectName|, COUNT(*)
FROM |SchemaName|.|ParentTableOrViewName|
GROUP BY |ObjectName|
HAVING COUNT(*) > 1

The tokens between the pipes are automatically inserted by this tool when you instantiate the template from a context-menu off of the selected object. For example, I would right-click on column Title in a table called Courses and select the template "Find duplicates" from the SSMS Tools context menu. It will open a new Query window with the following script embedded:

SELECT Title, COUNT(*)
FROM dbo.Courses
GROUP BY Title
HAVING COUNT(*) > 1

You simply run the script to find the duplicates. As you can see this is a very useful feature and the next step is to come up with custom scripts that we find ourselves repeating them often and turn them into templates using SSMS Tools Pack.

Hope you find this tool useful and come up with some creative custom scripts that you could share them with your blog readers online.

Happy Coding,
Sonny

Fully Qualified URL to refer to JIRA instance

We have our JIRA instance running on a server, for example, JIRA_SERVER01. Users can get to that server by using URL http://JIRA_SERVER01/.  We also created a CNAME to that server called JIRA so users can use a friendly name such as http://JIRA/ or fully qualified name, for example, http://JIRA.OurCompany.com/.

All of these URLs work fine, however, we want all of those URLs to automatically converged to the fully qualified name so regardless how users get to the JIRA application, the browser will always use the same URL and hence can take advantage of local cache and user’s cookies to restore the User’s last state.

Luckily, JIRA already uses a utility called UrlRewriteFilter (it is a Java Web Filter to be used on any J2EE compliant web application servers such as Tomcat, Resin or Orion) provided by http://www.tuckey.org/urlrewrite/.

Added the following rule elements to be the first node under the root node (urlrewrite) in the file urlrewrite.xml on the JIRA server:

<?xml version="1.0" encoding="utf-8" ?>
<!-- Make sure users are always redirected to fully qualified name when a short name is entered for JIRA application -->
<rule>
<name>Canonical Hostname for jira.OurCompany.com</name>
<note>The goal of this rule is to force the use of jira.OurCompany.com as the hostname to reach JIRA prod instance.</note>
<condition operator="notequal" name="host">^jira\.OurCompany\.com</condition>
<condition operator="notequal" name="host">^$</condition>
<from>^/(.*)</from>
<to last="true" type="permanent-redirect">http://jira.OurCompany.com/$1</to>
</rule>

The file urlrewrite.xml should be in folder [JIRA_INSTALLATION_DIRECTORY]\atlassian-jira\WEB-INF

Note of Caution:  This change is made in JIRA v4.2.1 running in a stand-along service mode.  This is NOT officially supported by JIRA and use your discretion while making these kind of changes to JIRA installation files. As with any such unsupported changes, make them at your own risk and make sure you backup the files prior to your changes so you can revert to the original configuration if needed.

There may be other ways to achieve this same behavior, but this is what I did and it works fine for us.

Cheers,

Sonny

Combining Multiple C# Using Statements

I sometimes find myself having to write the following code where I need to use multiple Using statements and end up nesting them.

using (StreamReader reader = new StreamReader("FileToProcess.txt";))
{
    using (StreamWriter results = new StreamWriter("Results.txt"))
    {
        using (StreamWriter errors = new StreamWriter("Errors.txt"))
        {
            // Read/Write from/to the files as needed
        }
    }
}

There is nothing wrong with this, but I do not like having too much indentation in my code.  When I searched on the web to see if there are other ways to avoid nesting multiple Using statements, I found the post on StackOverflow:
http://stackoverflow.com/questions/966086/using-various-types-in-a-using-statement-c

Basically, the statement inside the using is like a variable declaration, so if you happen to be declaring multiple variables of same type that all implement IDisposable, then you could combine them inside a single using declaration.  For example, I could combine results and errors variables in the above code as follows:

using (StreamReader reader = new StreamReader("FileToProcess.txt"))
{
    using (StreamWriter results = new StreamWriter("Results.txt"), 
            errors = new StreamWriter("Errors.txt"))
    {
        // Read/Write from/to the files as needed
    }
}

It is a little better, but it would be nice if I can get rid of the second using statement or nesting another level, so I have modified the original code to as follows:

StreamReader reader;
StreamWriter results, errors;
using (IDisposable _reader = new StreamReader("FileToProcess.txt"),
    _results = new StreamWriter("Results.txt"),
    _errors = new StreamWriter("Errors.txt"))
{
    reader = (StreamReader)_reader;
    results = (StreamWriter)_results;
    errors = (StreamWriter)_errors;

    // Read/Write from/to the files as needed
}

Granted I have more lines of code that does the same thing as the original code plus extra casting for the sake of a single using statement and eliminating multiple levels of nesting, I however prefer this later version of code.
While getting ready to write this report, I was searching StackOverflow(SO) for the above link as I found that post a while back. While searching SO, I found this other post where it shows how you could write separate usings one below another without having to nest them.
http://stackoverflow.com/questions/1329739/nested-using-statements-in-c

Taking a cue from this post, I have changed the code as follows:

using (StreamReader reader = new StreamReader("FileToProcess.txt"))
using (StreamWriter results = new StreamWriter("Results.txt"))
using (StreamWriter errors = new StreamWriter("Errors.txt"))
{
    // Read/Write from/to the files as needed
}

This is the ideal solution in my opinion. You could list all of usings one by one explicitly and do not have to nest them. I understand, I originally said, I wanted to put all of them in a single using statement, but my main objective is to avoid nesting and this syntax that I was not aware of until now suits me the best.

Thanks for reading.

Sonny

Collapse/Expand All Comments in JIRA Issue Details Screen

In JIRA, when you are looking at an issue, it often shows all the comments expanded. This would be OK if there are only a couple of comments and they are compact, but often comments are added to JIRA via emails and each email contains the whole thread and thus it makes very hard to read/browse through the comments as they all seem to run together with same text repeated in successive comments. For example, you would often see comments displayed like this (http://jira.atlassian.com/browse/JRA-12569):You could collapse all the comments and JIRA will automatically show the first line of the comment which makes it very easy to look at all the comments as whole and improves the readability of the issue. For example, the above page is displayed like below when all the comments are collapsed:Problem:

You would have to collapse each comment one by one as there is no single button that can collapse all the comments in one click. This could be quite tedious if there are many comments in an issue. I believe if the JIRA instance is integrated with Confluence, it provides this feature but our instance of JIRA is stand-alone and that is why I came up with this technique.

Solution:

Use the following steps to install two macros that allow you to collapse/expand all the comments in JIRA window with a single click:

1. Launch Internet Explorer (IE)
2. While in the IE window, type “about:blank” in the address bar.
3. Press Ctrl+D key combination to bring up the Add a Favorite dialog box.

4. In this dialog box, select the “Favorites Bar” from the “Create in” drop-down and type “Collapse JIRA Comments” in the “Name” box and click on the “Add” button.

   

5. Turn on the “Favorites Bar” if it is not already on. Right-click on the toolbar area and select “Favorites Bar”.

6. In the “Favorites Bar”, you should now see a new icon by name “Collapse JIRA Comments” as shown below:

   

7. Right-click on this icon and click on Properties command to bring up the link properties dialog box as shown below:

   

8. In this dialog box, while in the “Web Document” tab, enter the following javascript code in the box titled “URL”:

javascript:if(!window._ctwixi){function _ctwixi(){jQuery(“.verbose”).each(function(){if (jQuery(this).css(“display”) == “block”) jQuery(this).find(“.twixi”).click();});return;}}_ctwixi();

1. Click on the “Change Icon” button to bring up the “Change Icon” dialog box as shown below:

   

2. In this dialog box, click on the “Browse” button and select the shell32.dll and click “Open” as shown below:

   

3. Select the collapsed folder icon in the icons shown in the “Change Icon” dialog box and click OK as shown below:

   

4. Click on “OK” in the “Collapse JIRA Comments Properties” dialog box.

5. Click on “Yes” in the following warning dialog box:

   

6. Navigate to any JIRA issue page and click on this button in the favorites bar to collapse all the comments.
7. Repeat the above steps to add a button to “Expand JIRA Comments” using the following javascript code:

javascript:if(!window._etwixi){function _etwixi(){jQuery(“.concise”).each(function(){if (jQuery(this).css(“display”) == “block”) jQuery(this).find(“.twixi”).click();});return;}}_etwixi();

Conclusion:

You should now have two buttons in your IE Favorites Bar that you could use to collapse/expand JIRA comments while looking at a JIRA issue.

As you can see, I am using the jQuery in my javascript snippets, this works because JIRA already uses jQuery and hence we do not have to worry about making sure jQuery is already loaded.

Thanks for reading and look forward to your comments.

Check MD5 or SHA1 hashes in Windows

If you ever download stuff off of web, you should use some sort of checksum tool.  I believe in most Unix/Linux flavors of Operating Systems, come with a checksum tool, but no such luck in Windows.

Have you every wished every edition of Windows came with checksum tool?  Well, almost.  As far as know, most of the Windows versions do not come with this tool, however, Microsoft does provide one.  In the past, I have used to use various other third party tools whichever I could find online at that instance, but I would rather use one that is provided by Microsoft itself.

So, just go to this page and install it:
http://www.microsoft.com/download/en/details.aspx?id=11533

As the page states, this small command line tool works on Windows 2000 or higher.

Hope you find this useful.

Thanks.

Sonny