Wednesday, October 08, 2014

Ever need to pull up all columns for all rows with a duplicate field?  Try this query .
Select * from dbo.Table where ID in
        (SELECT ID,COUNT(*) AS ItemCount
         FROM dbo.Table
         GROUP BY ID
         HAVING COUNT (*) >1
         )AS a
Basically we have three nested queries.  The inner-most pulls up a count grouped by the ID field which have more than one occurrence.  Note the "AS a" tag.  This alias is required on MS SQL for this to work.

The next query (next as the next inner-most) selects only the ID field from our inner query.  This is so we can use this in the last query, which selects all rows that have ids which match the returned set of ID's.

Monday, December 16, 2013

Have you seen my cookie?

I received a call from one of our partners telling me they can't log into the website.

Since today is Monday, my first inclination is "forgot password."  I checked my log, and I see the user has validated.

I've been having  problems with Internet Explorer for a few months along these lines.  The use logs in, yet forms authentication doesn't recognize them.  Using any other browser avoids the problem.

I've looked and looked, but haven't been able to find a solution, until today.

The problem is that when the user logs in, the system creates a session cookie, which holds the authentication ticket.  Without said cookie, the user can't look at protected content.

I found the solution.  By adding a single line to the web.config file,  the problem goes away.


Monday, August 12, 2013

What's the point?

Got asked an interesting question.  In a GPS longitude/latitude  measurement, how much resolution does each decimal give us?

Since each line of longitude is 68.71 Miles then
  • each tenth represents 6.9 miles (or 36,279 feet)
  • each hundredth represents .69 miles (or 36279 feet)
  • each thousandths represents 362 feet (or .07 miles)
  • each then thousandth represents 36.3 feet (or .0069 miles)

Friday, January 18, 2013

Pretty URLs

At work, we are moving to SQL 2008 from SQL 2000.  As part of the move I had to test our Umbraco website against the new server.

So, I copied the contents of our website to the test server, changed the connection strings to point to the new server, and loaded the page.

When clicking on any links, I get a 404 error.

After some investigation, I learned that the issue was with IIS.  Our website uses PrettyURLs.  So if you want the trauma section you would go to www.wvstecs/trauma

The problem is that going to that location was generating a 404, not found error.

The reason is that you have to configure IIS to handle this type of request.  There are two steps.

  1. Make sure that you have configured Umbraco to do the pretty URLs by modifying the umbracoUseDirectoryUrls to true in web.cofig
  2. Make sure you have added the aspnet_isapi.dll handler to your website (Inetmgr - Home Directory tab, Configuration)
More informaton can be found here.

Monday, December 17, 2012

We use Umbraco as our Content Management System.  Today, I had an error pop up, and I needed to debug it.  I found this useful tip.  Call your umbraco page and pass it this query string
It enables the tracing information that may, or may not help identify the problem.  In my case, not so much, but it's handy non-the-less.

Tuesday, November 13, 2012

How to Write to an Identity Field

We have an online application system for folks who are registering as an EMT or Paramedic.  We received a request from one of our clerks, to delete an application from our database.

Our database is Microsoft SQL Server 2000.

Turns out the record wasn't to be deleted anyway.  So I get yesterday's backup of the database, and go to insert the deleted rows.  I got an error saying that I can't insert into an identity column.

In order to properly restore the database, I needed to restore the ID.   After some searching, I found the solution.

Assuming that you are restoring a record to the foo table, the following fixes the problem

(ID, Occurred, [Status])
 Values (1234,'2011-08-09 09:27:51.383','new');


Monday, September 17, 2012

Our Office's HR  person wanted us to submit a vacation-use form to account for taking off early after working late earlier in the week.  For example, if I worked over two hours on Monday, and knocked-off two hours early on Tuesday, she wanted a vacation form to show why I wasn't at work for the two hours on Tuesday.

The problem is that our vacation-use form wasn't designed for such a thing, and I needed to modify it to fit this need.

Alas, some well-meaning soul had placed a password on the file that  prevented me from adding the extra category.

Since I didn't know who that well-meaning soul was, I went to the all-knowing Google.  I found this tip so helpful that I wanted to share it.

Thanks to James Welch at, I have the answer.

  • Save the password-protected document in RTF.  
  • Open the file using your favorite text editor.  
  • Find the string {\*\passwordhash ########}, where the ######## represents the password hash.  (hint look for the passwordhash portion.)
  • Remove the hash
  • save the file
Now, when you open the file, you can remove the security without being prompted for a password.

More details can be located here