Today I’ve been evaluating database designs made by this year’s candidates for work at our company. One of the issues I’ve been encountering all the time was the handling of date and time values. The SQL Server’s common data type datetime for both values has been the source of quite some confusion, especially since the values required to be separate at some occasions and joined at others.

I suppose the decision on whether to store them separate or joined depends on how the values are going to be used. In most cases the extra storage space required by using separate columns for both values isn’t worth it but in some usage scenarios the resulting reduced query complexity and improved indexing usage can justify it.

Either way sooner or later the need for separating or joining the date and time parts will arise. The following query demonstrates both operations in what I believe is the most efficient way:

SELECT
   dateOnly = CONVERT(datetime, FLOOR(CONVERT(float, dateTimeColumn))),
   timeOnly = dateTimeColumn - CONVERT(datetime, FLOOR(CONVERT(float, dateTimeColumn))),
   dateAndTime = dateOnlyColumn + timeOnlyColumn 
FROM DateTable

The following query is a good starting point if you want to export the SQL Server Agent job history to a file and you’re still using Enterprise Manager from SQL Server 2000. In SQL Server Management Studio from SQL Server 2005 there’s already a command available to do this in the Log File Viewer window accessible from the View History command on the selected job.

USE msdb
SELECT
   J.name, 
   S.step_id,
   S.step_name, 
   H.message, 
   run_status = CASE H.run_status
      WHEN 0 THEN 'Failed'
      WHEN 1 THEN 'Succeeded'
      WHEN 2 THEN 'Retry'
      WHEN 3 THEN 'Canceled'
      WHEN 4 THEN 'In progress'
   END, 
   H.run_date, 
   H.run_time, 
   H.run_duration
FROM sysjobhistory H
   INNER JOIN sysjobsteps S ON H.step_id = S.step_id AND H.job_id = S.job_id
   INNER JOIN sysjobs J ON J.job_id = H.job_id
ORDER BY H.job_id, H.run_date, H.run_time, S.step_id

Ever since I attended a lecture on test driven development I’ve been trying to find a way to use its essential part – unit testing – in real development environment. I realized this isn’t completely trivial after assigning a developer to write ad hoc unit tests for the class library of a recent project and failing at it completely because the tests turned out to make sure the method functionality wouldn’t change instead of making sure the functionality is actually correct.

Since a new project is coming up and we really need more security when changing the code in its later phases I decided to dedicate some more time to exploring and deriving the concepts of using unit testing in a not really development driven project. Well, I happened to stumble upon Pragmatic Unit Testing in C# with NUnit, a book which turned out to be a perfect answer to my questions. All that’s left now is to make the developers read the book and grasp its concepts before starting their work on the project.

In a few months you’ll probably be able to read here how it turned out. In the meantime I recommend the book to everyone who wants to start with unit testing but just doesn’t know how to do it.

Never forget to disable simple file sharing (Explorer menu, Tools > Folder Options..., View tab, Use simple file sharing (Recommended)) when attempting to set permissions for certificate private key file on a machine with a fresh Windows XP install, not joined to a domain. In this case the option is enabled by default which hides the Security tab in all properties dialogs and consequently disallows any explicit setting of permissions.

It is a good practice to disable the option anyway but it is easily forgotten when setting up the machine for the first time. This reminder should prevent me from losing another half an hour wondering why I can't set the desired permissions using the WSE X.509 Certificate Tool the usual way in case I am once again given a task of preparing a demo machine for an application using certificates with service accounts.

Yesterday I tried committing some changes to one of the websites I am maintaining into my CVS repository. To my surprise the operation failed or to be more exact, the TortoiseCVS client just kept performing the command for several minutes without failing until I cancelled it.

After some investigation I realized that it’s been almost two weeks since my last CVS usage and that in the meantime the only noticeable change on my server machine where I have the CVSNT service running was the change of the antivirus software. I started trying out NOD32. It became the primary suspect for the problem.

To make my further investigation easier I decided to try if the CVS works when used locally from the server:

cvs -d :pserver:damir@localhost:/Root login
Logging in to :pserver:damir@localhost:2401:/Root
CVS Password: ******
cvs [login aborted]: Error reading from server localhost: -1

Obviously it didn’t work even locally so it wasn’t a simple problem of firewall blocking the port (although this would be strange without having any firewall enabled at all). Some googling quickly uncovered the program responsible: NOD32 IMON service. Disabling it and restarting the server solved the problem.

But I wasn’t satisfied with that. I didn’t want to disable the internet monitor completely to make the CVSNT work. This would mean that I’d have to go on looking for another antivirus solution while I was completely satisfied with this one up till now. After some more investigation I found a hint that brought me to my final solution: re-enabling the IMON service and excluding the CVSNT executable from the monitoring. The exclusions list can be reached by opening up the NOD32 Control Center under Resident modules and filters > IMON. Setup button in the following window opens up another window with Miscellaneous tab, inside the Exclusion group there's an Edit... button which displays the list. The file that has to be added is cvsservice.exe from the C:\Program Files\CVSNT directory in the case of default installation.