The Terrible Towel at Obama's Inauguration
The Terrible Towel turns up everywhere. It's to the point when I look for it like a "Where's Waldo?" game at every crowd event. Here's a video of the towel at Obama's speech:
Get pumped up with this Steelers highlight video...
This is a very well done highlight video. The creator did a create job of syncing up the video to the music. This is the kind of stuff to watch to get you even more pumped up for the Super Bowl!
Getting Microsoft SQL Server Management Studio to automatically log in to a server
I finally got tired enough of having to click the "Connect" button at SQL Server Management Studio startup, that I decided to look for a way to bypass this prompt. Since in development, I typically am only ever dealing with the same server, the connection dialog always added an extra step to the process of getting up and running.
So, a quick search on Google turned up a list of the following command prompt switches you can use to specify a server (and database) to use when starting up:
Usage:
sqlwb.exe [-S server_name[\instance_name]] [-d database] [-U user] [-P
password] [-E] [file_name[, file_name]] [/?][-S The name of the SQL Server instance to which to connect]
[-d The name of the SQL Server database to which to connect]
[-E] Use Windows Authentication to login to SQL Server
[-U The name of the SQL Server login with which to connect]
[-P The password associated with the login]
[file_name[, file_name]] names of files to load
[-nosplash] Supress splash screen
[/?] Displays this usage information
So, all you need to do to get SQL Server Management Studio to automatically log in to a specific server is to right-click on your SQL Server Management Studio icon, and change the "Target" to something like:
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe" -S SERVERNAME -d DATABASENAME
Useful tips and tricks for dealing with date/time in SQL
I've come across several clever and convenient tricks for dealing with dates recently in SQL Server. I previously blogged about how to remove time from date/time stamp in SQL Server, but I've come across a group of other really useful date/time manipulations that can be very useful when dealing with SQL Server.
The basic idea behind most of these tricks is that by using a known base datetime stamp, you can then use simple addition and subtraction to calculate the new dates. For example, if I calculate the difference in days between Jan 9, 2008 at 08:00 Jan 1, 2000 at 00:00, I'll end up with a whole number of days which I can then and add back to Jan 1, 2000 at 00:00 and I'll end up with Jan 9, 2008 at 00:00.
Take the tricks below and play around to see what other useful combinations you can develop. If you have any other useful date/time tricks, let me know!
Dynamically generating a table of dates in Microsoft SQL (Part Deux!)
Yesterday I wrote a blog entry talking about dynamically generating a table of dates in MSSQL 2005. My original solution involved common table expressions (CTE) and involved recursion. While there's nothing wrong with using recursion this way, I mainly was using recursion to simplify the UDF code into a single SQL expression.
However, the more I started thinking about the problem last night, I decided I really needed to revise things a bit further. I decided I really needed to have the query return both a start and end date for each row in the recordset—as this would make it much easier to things such as find all the orders placed during each hour of the day.
So, this morning I came in and re-wrote my query and optimized a few things. The first thing I did was remove the recursion, so this UDF should now work in SQL Server 2000 as well as SQL Server 2005. It's important to note that you could do the same thing with a physical table, but for my uses my date range table generally would only include at most a few dozen rows and there's not enough of a performance difference to warrant the pain of maintaining a table of every date and possible combination you may want to return aggregate results over.
The new code looks like this:
Dynamically generating a table of dates in MSSQL 2005
[UPDATED: Thursday, January 08, 2009 at 11:04:25 AM]
UPDATE:I've revised this concept, so please see the new version: Dynamically generating a table of dates in Microsoft SQL (Part Deux!)
I'm working on some code where I need to show some trend date over various periods of time. One of the issues with working with SQL and aggregate values is that it'll only show you values where there was some data, so if you want to print the number of orders on each day and some days had no orders, they wouldn't show up in the results.
For what I was working on, I needed to ensure that even missing days would show up in my query results. This got me thinking about the best way to accomplish this task. After thinking about the problem for a while, I decided the ideal method would be if I could dynamically generate a table of dates that I could then use in a LEFT OUTER JOIN.
Organizing the icons in your Firefox status bar...
One thing that's always bothered me about Firefox, is the lack of the ability to customize the icons in the status bar. As you add various plug-ins, the order of icons in the status bar can change and isn't always ideal. Plus, when you have multiple installs of Firefox on multiple boxes, I find it disrupts my workflow when icons are in a different spot for each browser (even with the exact same plug-ins installed.) So, I decided I finally wanted to re-arrange the order of my icons and get things working in a fashion that suited me better.
There are two main ways to change the order: 1) via a manual editing or 2) via another add-on.
You can manually re-arrange the order of the icons by editing the extensions.ini file located in your personal profile directory in the folder C:\Documents and Settings\{username}\Application Data\Mozilla\Firefox\Profiles\{profile}. What you need to do is change the order and position of the entries in the [ExtensionDirs] section of the INI file. While this works, it's a bit of a pain since the entries aren't easy to decipher (since values don't match up to a user friendly label.)
This led me to a find an add-on to simplify the task. The add-on I found was the Organize Status Bar v0.6.2 add-on. Using this plug-in gives you a very visual way of organizing your status bar. You can completely order things in any manor you want. Here's a screenshot of the add-on in action:
What I like about the add-on is that it highlights in yellow the currently selected icon and also has options to hide or show an icon if you don't want it to show up in the status bar. This add-on does exactly what I needed—I just wish the functionality was native to Firefox like the organizing toolbar feature.
Find the longest running queries on your MSSQL 2005 Server
[UPDATED: Wednesday, January 21, 2009 at 8:19:25 AM]
I was reading the SQL SERVER - 2008 - 2005 - Find Longest Running Query - TSQL on Pinal Dave's blog and thought I'd run the query on my development machine to see what interesting things I'd fine.
However, right off the bat I was seeing something I thought was odd—my execution times looked way too high. After some quick searching, I confirmed my initial thought that MSSQL reports time in microseconds (which is one millionth of a second or one thousandth of a millisecond.)
For my needs reporting in milliseconds is more than enough, so I revised the query to report the time in milliseconds instead of microseconds. I also made some other modifications so that the query breaks the actual SQL statement and extra parameters into 2 columns (I derived this code from the an article on MSSQLTips.com.) I also added last execution time and minimum elapsed times for the query. I also changed the query to show the results by the average elapsed time—since I occasionally you'll see a single query spike with a high maximum elapsed time that might be due to external circumstances (although this is good to know too.)
Here's my revised version:
substring(t.text, (s.statement_start_offset/2)+1, (((case s.statement_end_offset when -1 then datalength(t.text) else s.statement_end_offset end) - s.statement_start_offset)/2) + 1) as SqlText
, (case when s.statement_start_offset > 0 then substring(t.text, 0, (s.statement_start_offset/2)+1) else '' end) as SqlParameters
, s.execution_count as ExecutionCount
, (s.max_elapsed_time/1000) as MaxElapsedTimeMs
, (s.min_elapsed_time/1000) as MinElapsedTimeMs
, isnull((s.total_elapsed_time/1000) / nullif(s.execution_count, 0), 0) as AvgElapsedTimeMs
, s.creation_time as LogCreatedOn
, s.last_execution_time as LastExecutionTime
, isnull(s.execution_count / nullif(datediff(s, s.creation_time, getdate()), 0), 0) as FrequencyPerSec
from
sys.dm_exec_query_stats as s
cross apply
sys.dm_exec_sql_text(s.sql_handle) as t
order by
isnull((s.total_elapsed_time/1000) / nullif(s.execution_count, 0), 0) desc
UPDATE:I added another nullif() statement to further prevent divide by zero errors.
One thing to keep in mind is that the DBCC FREEPROCCACHE will clear the cache and reset the log (obviously a reboot of the server or restart of the services would do the same thing.) You may have some maintenance scripts that might issue the DBCC FREEPROCCACHE command, so just keep this in mind when looking at the results.
You can use the query above for lots of purposes. With a little refactoring, you could change the ORDER BY clause to return queries that had executed recently—which can be useful if you're trying to debug a slow running template.