Completely unexpectedly I stumbled across Platform Invocation Services today when I was introduced to the IBM Tivoli Storage Manager API. It should have been a simple case of getting to know the API and writing a sample application with it, but it turned out that there’s no managed wrapper available for it (in spite of first being told otherwise). Combine that fact with an abundance of low level calls and large structs as their parameters and you can imagine that after one day there’s still a long way to that sample application, not to mention the final solution.

For anybody else like me out there who hasn’t done more than an occasional DllImport call or two the following resources should help getting to grips with the PInvoke basics:

• Platform Invoke Tutorial – a must read for anyone starting with PInvoke. As usual, the follow-up links provide lots of additional useful information.
• PINVOKE.NET – an indispensable source of information on WinAPI PInvoke calls. Not exactly what I needed but still the abundance of example calls turned out helpful.
• The P/Invoke Wizard – a simple tool for converting C/C++ include files to PInvoke declarations. Not perfect and a bit pricy for what it does but still worth it due to the time it can save you when dealing with many large include files.

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.