iPod Recovery

My wife's computer died recently. Fortunately most of her music fits onto her iPod, the problem we had then was how to transfer this to her new PC. The Apple site wasn't particularly forthcoming for anything other than music bought from iTunes, however googling around uncovered this site which appears to offer comprehensive and well thought out advice on how to get out of this mess of this mess.

Databases and .NET

Here is a great little site that has a list of connection strings and a list of datatype matches. One of those sites that is incredibly useful

Printing Posters

I've always had a problem when I wanted to print a large image, particularly from programs like photoshop which seemed to assume that you would do this sort of thing from a different package. I don't use Photoshop any more so I dont know if its been resolved but I do use Photoshop Elements and that suffers from the same issue.

My preferred way of solving this would be to use a DTP package but I don't have one and Word is particularly inadequate at this sort of thing. The only reasonable alternative I can think of is to scale to fit onto a single sheet. The problem with this of course is that if the image is particularly large it can be rendered too small to be useful.

A quick Google and I came across this page where if you look hard enough you will see the suggestion that you can put the image in Excel and then print it. Clearly this is not the perfect solution but it certainly was adequate for my A2 equivalent poster

TrafficMap

If you live in the UK and are planning to travel on a motorway you may find the TrafficMap site useful. It tells you the status of all the signs on the motorways. During the recent flooding I used it to decide which way to go home

Models

If you are learning to design databases you need to know about data models. The Library of Free Data Models can be a good place to start. It has many models covering all areas from Accidents at Work to Olympic Sports

Web 2 once more

I've been looking at web 2 applications again and having seen quite a few I was most impressed with goowy it looks like the sort of thing that Google might snap up. With Office applications added to it I think it could become invaluable

Web Developer

I use Firefox as my browser, particularly at work, one of the most useful add ons for developing web pages is Web Developer which has a huge range of features and I highly recommend it

Hot Dates

Dates (or rather Datetimes) are cause of much concern to new SQL server users and even sometimes to experienced users. While haunting some SQL server forums I came across a couple of ideas that I felt could be incorporated into one.

Firstly there is a function to create a date table in SQL Server 2000 here combined with a procedure to create a list of UK holiday dates as demonstrated here we clearly have a powerful tool.

In my case I wanted dates based on the financial year so I added some columns to handle that. I also wanted the number of working days between 2 dates and so I have amalgamated the data above to facilitate that.

Here is the complete code


-- Code taken from
-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&whichpage=1
-- for date table





if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[F_TABLE_DATE]')
and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[F_TABLE_DATE]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
create function dbo.F_TABLE_DATE
(
@FIRST_DATE datetime,
@LAST_DATE datetime
)
/*
Function: dbo.F_TABLE_DATE

This function returns a date table containing all dates
from @FIRST_DATE through @LAST_DATE inclusive.
@FIRST_DATE must be less than or equal to @LAST_DATE.
The valid date range is 1754-01-01 through 9997-12-31.
If any input parameters are invalid, the fuction will produce
an error.

The table returned by F_TABLE_DATE contains a date and
columns with many calculated attributes of that date.
It is designed to make it convenient to get various commonly
needed date attributes without having to program and test
the same logic in many applications.

F_TABLE_DATE is primarily intended to load a permanant
date table, but it can be used directly by an application
when the date range needed falls outside the range loaded
in a permanant table.

If F_TABLE_DATE is used to load a permanant table, the create
table code can be copied from this function. For a permanent
date table, most columns should be indexed to produce the
best application performance.


Column Descriptions
------------------------------------------------------------------


DATE_ID
Unique ID = Days since 1753-01-01

DATE
Date at Midnight(00:00:00.000)

NEXT_DAY_DATE
Next day after DATE at Midnight(00:00:00.000)
Intended to be used in queries against columns
containing datetime values (1998-12-13 14:35:16)
that need to join to a DATE.
Example:

from
MyTable a
join
DATE b
on a.DateTimeCol >= b. DATE and
a.DateTimeCol < example =" 2005" example =" 20052" example =" 200511" example =" 2005364" example =" 4" example =" 11" example =" 362" example =" 31" sun="1," mon="2," tue="3," wed="4," thu="5," fri="6," sat="7" example =" 2005" example =" 2005" example =" 2005" example =" 2005" example =" Q1" example =" Mar" example =" September" example =" Tue" example =" Wednesday" example =" 2004-02-29" example =" 200403" example =" 52" mon="1," tue="2," wed="3," thu="4," fri="5," sat="6," sun="7" example =" 2004-W52" example =" 2004-W52-2" example =" 2004/02/29" example =" 2004/2/9" example =" 06/05/2004" example =" 6/5/2004" example =" Jan" example =" September" example =" 06/05/97" example =" 6/5/97" cr =" char(13)+Char(10)" errormessage =" '@FIRST_DATE" errormessage =" '@LAST_DATE" errormessage =" '@FIRST_DATE" first_date =" '+"> '99971231' begin
select @ErrorMessage =
'@LAST_DATE cannot be after 9997-12-31'+
', @LAST_DATE = '+
isnull(convert(varchar(40),@LAST_DATE,121),'NULL')
goto Error_Exit
end

-- Verify @FIRST_DATE is not after @LAST_DATE
if @FIRST_DATE > @LAST_DATE
begin
select @ErrorMessage =
'@FIRST_DATE cannot be after @LAST_DATE'+
', @FIRST_DATE = '+
isnull(convert(varchar(40),@FIRST_DATE,121),'NULL')+
', @LAST_DATE = '+
isnull(convert(varchar(40),@LAST_DATE,121),'NULL')
goto Error_Exit
end

-- Set @START_DATE = @FIRST_DATE at midnight
select @START_DATE = dateadd(dd,datediff(dd,0,@FIRST_DATE),0)
-- Set @END_DATE = @LAST_DATE at midnight
select @END_DATE = dateadd(dd,datediff(dd,0,@LAST_DATE),0)
-- Set @LOW_DATE = earliest possible SQL Server datetime
select @LOW_DATE = convert(datetime,'17530101')

-- Find the number of day from 1753-01-01 to @START_DATE and @END_DATE
select @start_no = datediff(dd,@LOW_DATE,@START_DATE) ,
@end_no = datediff(dd,@LOW_DATE,@END_DATE)

-- Declare number tables
declare @num1 table (NUMBER int not null primary key clustered)
declare @num2 table (NUMBER int not null primary key clustered)
declare @num3 table (NUMBER int not null primary key clustered)

-- Declare table of ISO Week ranges
declare @ISO_WEEK table
(
[ISO_WEEK_YEAR] int not null
primary key clustered,
[ISO_WEEK_YEAR_START_DATE] datetime not null,
[ISO_WEEK_YEAR_END_DATE] Datetime not null
)

-- Find rows needed in number tables
declare @rows_needed int
declare @rows_needed_root int
select @rows_needed = @end_no - @start_no + 1
select @rows_needed =
case
when @rows_needed < rows_needed_root =" convert(int,ceiling(sqrt(@rows_needed)))" number =" 0" number =" a.NUMBER+(16*b.NUMBER)+(256*c.NUMBER)" number =" a.NUMBER+(@rows_needed_root*b.NUMBER)" iso_start_year =" datepart(year,dateadd(year,-1,@start_date))" iso_end_year =" datepart(year,dateadd(year,1,@end_date))" number =" NUMBER+@iso_start_year" sunday =" 1," monday =" 2," saturday =" 7" sunday =" 1," monday =" 2," saturday =" 7" number =" NUMBER+@start_no" error =" convert(int,@cr+@cr+" name =" N'DATEINFO'" type =" 'U')" date =" dateadd(dd,datediff(dd,0,@DATE),0)" financial_year_start_date =" dateadd(dd,datediff(dd,0,@FINANCIAL_YEAR_START_DATE),0)" financial_year_start_date =" DATEADD(yy,"> 0
BEGIN
SET @YEAR = YEAR(@FINANCIAL_YEAR_START_DATE) -1
END
ELSE
BEGIN
SET @YEAR = YEAR(@FINANCIAL_YEAR_START_DATE)
END

RETURN (@YEAR)
END


-- populates Financial year start year
UPDATE DATEINFO
SET FINANCIAL_YEAR_START_YEAR = dbo.FINANCIAL_YEAR([DATE],'20000401')

-- note that could use YEAR(FINANCIAL_YEAR_START_DATE) if that has been populated

-- populates financial year start date
UPDATE DATEINFO
SET FINANCIAL_YEAR_START_DATE =
CASE
WHEN DATEDIFF(dd,DATEADD(yy, year([DATE]) - 1960, '19600401'),[DATE]) < financial_year_name =" RIGHT(CONVERT(VARCHAR(4)," weekend =" CASE" day_of_week =" 7)" day_of_week =" 1)" topic_id="49711" topic_id="45689)" a =" @YEAR%19" b =" @YEAR" c =" @YEAR%100" d =" @B" e =" @B%4" f =" (@B" g =" (@B" h =" (" i =" @C" k =" @C%4" l =" (32" m =" (@A" o =" 22"> 31
BEGIN
SET @R = @O - 31 + 400 + @YEAR * 10000
END
ELSE
BEGIN
SET @R = @O + 300 + @YEAR * 10000
END

RETURN @R
END
GO
--END fnDLA_GetEasterdate

-- SET EASTER DATES

UPDATE DATEINFO
SET UK_HOLIDAY = 'B'
--------------------------------NEW YEAR-------------------------------------------
WHERE DATE IN (SELECT MIN(DATE) FROM DATEINFO
WHERE MONTH = 1 AND DAY_OF_WEEK NOT IN (1,7)-- using American DATEFIRST
GROUP BY YEAR)
--------------------EASTER Good Friday and Easter Monday ------------------------------
OR DATE = DATEADD(dd,-2,CONVERT(datetime,dbo.fnDLA_GetEasterdate([YEAR]))) --Good Friday
OR DATE = DATEADD(dd,+1,CONVERT(datetime,dbo.fnDLA_GetEasterdate([YEAR]))) --Easter Monday
---------------------MAY BANK HOLIDAYS(Always Monday)------------------------------
OR DATE IN (SELECT MIN(DATE) FROM DATEINFO
WHERE MONTH = 5 AND DAY_OF_WEEK = 2 -- using American DATEFIRST
GROUP BY YEAR)
OR DATE IN (SELECT MAX(DATE) FROM DATEINFO
WHERE MONTH = 5 AND DAY_OF_WEEK = 2 -- using American DATEFIRST
GROUP BY YEAR)
--------------------AUGUST BANK HOLIDAY(Always Monday)------------------------------
OR DATE IN (SELECT MAX(DATE) FROM DATEINFO
WHERE MONTH = 8 AND DAY_OF_WEEK = 2 -- using American DATEFIRST
GROUP BY YEAR)
--------------------XMAS(Move to next working day if on Sat/Sun)--------------------
OR DATE IN (SELECT CASE WHEN DAY_OF_WEEK IN (1,7) THEN -- using American DATEFIRST
DATEADD(dd,+2,DATE) ELSE DATE END
FROM DATEINFO
WHERE MONTH = 12 AND DAY_OF_MONTH IN (25,26))


-- Add if a UK working day
UPDATE DATEINFO
SET UK_WORKING_DAY = CASE
WHEN (WEEKEND = 'Y') OR (UK_HOLIDAY = 'B') THEN 'N'
ELSE 'Y'
END


-- Now work out the next working day
-- NOTE THAT this fails for the last day
--

-- CREATE TEMPORARY TABLE for NEXT WORKING DATE

-- DROP TABLE #NextWorkingDate

CREATE TABLE #NextWorkingDate
(
DATE DATETIME NOT NULL,
NEXT_WORKING_DATE DATETIME NULL
)

INSERT #NextWorkingDate(DATE,NEXT_WORKING_DATE)
SELECT d1.DATE,
NEXT_WORKING_DATE = (SELECT MIN(d2.DATE) FROM DATEINFO d2 WHERE d2.UK_WORKING_DAY = 'Y' AND D2.DATE > d1.DATE)
FROM DATEINFO d1
GROUP BY d1.DATE

-- Do update
UPDATE DATEINFO
SET NEXT_UK_WORKING_DATE = n.NEXT_WORKING_DATE
FROM dateinfo t inner join #NextWorkingDate n
On t.date = n.DATE

GO

So we can now trivially calculate the number of working days between 2 dates using

DECLARE @START DATETIME
DECLARE @END DATETIME

SET @START = '20000109'
SET @END = '20000116'

SELECT COUNT(1) from DATEINFO
WHERE UK_WORKING_DAY = 'Y'
AND DATE > @START and DATE <= @END

This can be used to calculate all sorts of things. Essentially if we think of it in more abstract terms it is calculating the number of events between 2 dates. We don't have to use Count as I needed to - we can use pretty much any mathematical operator. All we need to do is populate the events (UK_WORKING_DAY here) and then adapt the code above accordingly
.

Channel Operations

Some time ago I worked with Graphic Designers and Journalists. It was there that I learn how to use Photoshop which to my mind is one of the most impressive tools that there is in so many ways.

My problem was that I am not particularly artistic. It was then that I discovered Kai Krause's Channel Operations. To my mind this took Photoshop to another level, yes you still need some artistic skill, but these techniques are reproducible with mathematical precision, that is what makes it so powerful.

I'm not sure this link is the original pages but they were the only ones I found. It would be tragic if this got relegated to the Google Archive.

Vista Killed my PC

My extra 2Gb Ram arrived and I immediately noticed two things after it was installed

Firstly, Vista didn't seem to recognise it claiming that it only had 3.25Gb. At first I thought the RAM may be faulty, particularly as the computer slowed down quite significantly which was a nasty surpise. WoW in particular became unusable as it was down to less than 10 frames per second even with all the graphic settings turned down.

Using my wifes machine with its Geforce 6600GT showed there was something seriously wrong as her machine was faster even though it was a lesser spec, and she had some of the graphic settings on WoW turned up. Though she was still complaining that her frame rate was slow when my machine was turned on.

After a lot of poking around I discovered this which at least explained the RAM issue frustrating though it is. I was almost tempted to try Linux on it instead at this point, as this didn't explain why the machine was so slow

At first I wondered if it was Symantec Antivirus that was slowing it down so I looked through it's logs to see what it was doing. It was then I noticed that Symantec was checking some strange looking IP addresses. A quick Google and I realised that I had somehow installed IPv6 on the machine. At the moment no one uses this (at least no one I know of) and so I disabled it. Miraculously the machine returned to full speed.

So the moral of the tale is don't install IPv6 unless you know you need to

Liftoff

The machine worked!

Only a few minor glitches, the fan on the back of the case wasn't plugged in properly, that was soon resolved. After that the Graphics card wasn't properly recognised by Vista which is ironic as it was more or less built for Vista. A quick scout around the Nvidia site identified what I needed and than I was away.

The Vista install was pretty nice, as I recall it has been streamlined over the XP one. Then everything was looking good - in fact large on the 22" widescreen.

I tried to play some music using media player and it started to refuse point blank or it would work intermittently. At first I wondered if it was the LaCie but it was playing fine to the other computers. A bit of messing about with driver downloads from the Asus site (the sound is on a daughterboard) and it started working as expected.

Overall it's looking good, there are a few annoying things in Vista but I will get used to them or work around them. The machine is blazingly fast, in fact the Microsoft analysis came up with 5 as the lowest score and that was only because it has 2Gb RAM, I'm about to buy another 2Gb so that should improve.

The only issue I have is, as to be expected for an "early adopter", that installing drivers that come with hardware is a bit hit and miss. Some of the software with the DVD writer complains and takes you to the software manufacturers website. I'm still not clear if the download it points you at is trial software or not, It seems a bit unfair if an XP install would give you the full application even if it was a lite edition

New PC - Graphics

My graphics card came for my new PC yesterday. I got an email from the supplier to say it had been despatched. By the time I had read that, looked on the couriers website and texted my wife, it has been there nearly an hour.

The card is enormous. It takes 2 slots in the back of the case which I realise these days is not too unusual. The whole thing looks like a slab of metal about an inch thick with a motherboard stuck to one side. My motherboard comes with an audio daughter board and I was concerned that some of the capacitors on that would press against the graphics card. Fortunately there is a small gap of a few millimetres. My plan was always that I may be able to buy a second graphics card in the future and put them in an SLI configuration. What I have realised though is that this would put the second card very close to the Power Supply fan, with the two fans facing each other. The case is very well ventilated so this may not be an issue but I may never know until I try it.

Having installed the card, I realised there was one thing I hadn't purchased. The Graphics card is DVI and the monitor is VGA. I was appalled to find that these devices are £15 in PC World. Even worse, a cable to replace the monitor one would have cost me £25 even though it would be a better solution

All I needed then was to install the OS. Eventually after hunting high and low (I had put it in a safe place) I found it at a quarter to midnight and so had to leave it for tonight.

Of course I haven't yet switched the computer on yet, hopefully it will work first time.

When it is running I can think about overclocking it. I've seen suggestions that all the dual core processors can be overclocked to at least 3GHz. In my opinion this is not to be sniffed at

Storage

I'm delighted with the Lacie ethernet disk mini, it does exactly what it says it will and does it quietly, what's more I can add further storage via the USB 2 port should we ever need it. My only quibble is that you cannot map a drive to a level above the shares, so if you have a lot of shares they need to be mapped seperately. I got around this by having one share with subfolders set to my preference.

The device has a gigabit ethernet port and when I started moving our music onto their I realised why. The computer I was moving files from is attached to a 10Mb hub, which is fine for internet use and for moving smaller stuff about but for this it is painfully slow. As a result I am now the proud owner of an 8 port gigabit switch. As soon as I get my wife of her computer I will be able to set this up

New PC

We have decided we need a new PC. I've looked around for what I can get for about a£1000 and there is some nice kit but nothing that really fulfilled my requirements for the money available. In particular I wanted a upper mid range sort of spec.

Then a friend suggested I make one. It's not something I've done before even though I am comfortable taking them apart. Having said that I am a bit out of touch on the technology and so have done a lot of research. In the end I came up with this spec:-

Intel Core 2 Duo E6600
Asus Motherboard P5
Lightscribe DVD writer
Modular Power Supply
Upper mid range Graphics card
Widescreen LCD panel or twin panels

The processor is the slowest Intel currently sell which has 4Mb cache. The cache must be their for some reason so I figured that 4 is better then 2 especially for 2 cores.

Asus motherboards seem to have a lot of good reviews but their range is huge and very confusing to the relative novice. I wanted something that could cope as far as possible with future expansion. This means lots of RAM, more hard discs, possibly a new processor or a second graphics card. My experience is that doubling the normal amount of RAM keeps a machine flying for quite some time. Then I spotted a bundle at CCL which included the processor I wanted, 2GB RAM and an ASUS motherboard, the bundling meant I saved around £100 on buying seperately too.

The Lightscribe DVD is a bit of a gimmick for me but the cost was comparable with normal DVD writers and I like HP kit so I went for the HP DVD 940i

I wanted a modular power supply as I like the idea of them and particularly that with less wires there must be an improved airflow for cooling. I'd also seen a case I liked with a clear side but more of that later. Little did I realise what a minefield it is with something as simple as a power supply. There are so many different makes and variations, some are ATX 2.2 (the latest spec), some are 2.0, so you ask yourself, does this make a difference for me. Having looked around the web a lot I still don't have a clear answer on this one. The specs don't mean a lot to me. They tell me what was added to the spec but it's gobbledegook. This is because I don't understand why anyone would want the extra's. I presume there is a reason.

In the end I went for a Hiper R type as it looked nice, it had some good reviews and it seemed to be able to cope with what I wanted it too. Trying to buy this proved almost as tricky as making the decision to buy in the first place. Almost everywhere was out of stock. Finally I found some on the Chillblast site. When I ordered I got an email that they were out of stock but expecting some more soon. The staff there were great and answered my emails promptly which is a nice change compared to some companies. They told me that the PSU was being discontinued which concerned me but that they were getting one more. When that one arrived it was badly damaged so my search began anew. I ended up with the Jeantech Storm which I am pleased with. It has long leads which fit into my case. This is important for me as the case I have sites the psu at the bottom. I've never really understood what a PSU should be at the top of a case. It also has a little piece of rubber which goes at the end where you connect it to the case. This is to reduce vibration and is a nice touch.

Buying a graphics card was almost as bad as buying a psu. The range is even more intimidating and confusing. I used Tom's Hardware guide to try to understand the different cards. Although it helped a lot, I ended just plumping for one. The one I went for is an XFX 8800 GTS. I ordered this from the same place that I got the lacie which meant I got it for the same price as some 7950's which I figured made it a good deal. I can't say yet as it has been due in 2-3 days for the last week now.

The case that I went for has a lot of cooling and seems well thought out, I didn't want any doors on the front as they just irritate me. I liked the look of this one and I liked the grill and the modularity (and the blue lights).

For the hard disc I went mad and got a Western Digital Raptor. For the money the capacity isn't huge but I have my ethernet disk now and the case will always accommodate more drives. The capacity is more than compensated by the performance

Finally I saw a 22" widescreen LCD panel with a 16:10 aspect at a price too good to resist.

All but the graphics card and the PSU were ordered from CCL and it all arrived 2 days later, (even though the motherboard bundle was special order), which I was very impressed at and meant I had a little time to look through it all

Audio Heaven

I bought my wife a Philips Streamium SLA5520 some time ago. It's a nice little device that can cope with some DRM music and it was cheap. I have a few minor issues with it but I think they can be readily resolved

Firstly it sometimes seems a little slow connecting to the network so I am going to look at upgrading my wireless network
Secondly, it needs a PC to be turned on. My solution to this is the LaCie Ethernet Disk Mini a smart little device that should just do the job. It is accessible from Linux and it has twonky built into it which means it will serve media to both the streamium and my PSP
Finally, the room that the streamium is in is mostly used as a second office at the moment. It therefore means that the laptop is in there. When listening to music it seems to me to make sense to have computer control where available and so I am looking at this for controlling the device.

I think this combination is going to give me a genuinely flexible setup that can be upgraded in future

Datagrids made easy

People seem to struggle with .NET datagrids. Personally I like them as I think they are pretty powerful, however they don't quite work for me as I want so I have created a web user control that I use instead.

This control has a datagrid on it which is exposed as a property, it also has four buttons for navigation (first, previous, last and next buttons) as I prefer to list the page numbers in the datagrid headings.

The code for these buttons is very simple as follows -
Private Sub butFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butFirst.Click
Me.dgData.CurrentPageIndex = 0
BindData()
End Sub

Private Sub butPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butPrev.Click
Me.dgData.CurrentPageIndex = Math.Max(1, Me.dgData.CurrentPageIndex - 1)
BindData()
End Sub

Private Sub butNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butNext.Click
Me.dgData.CurrentPageIndex = Math.Min(Me.dgData.CurrentPageIndex + 1, Me.dgData.PageCount)
BindData()
End Sub

Private Sub butLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butLast.Click
Me.dgData.CurrentPageIndex = Me.dgData.PageCount
BindData()
End Sub

If you remember that the first page is number 0 then this should all make sense.

I also realised that the data that my grids are to show tends to be that from the main database I babysit. I already have a class which calls this in a couple of lines and returns a dataset. This means that all I need to do to my datagrid control is to give it a StoredProcedureDataSource type property and then I will be able to create an Intranet full of datafrids

Monitoring Server Disc Space

One of the more mundane things that I (and many server admins) have to do is to monitor free disc space on servers. There are a number of expensive tools to do this but I wanted a way to look at the information for all my servers at the same time with the possibility of a small pie chart or some such so that I can glance at it and know whether something needs to be done.

My solution to this was to create an RSS feed so that any major (by my definition) changes to free space would be flagged. I have a little routine that checks all my servers and puts the data into a table in an admin database which I can then query easily. The routine to create the feed was adapted from here and converted to VB. The code is simple and effective and it works.

My feed also links to a page which can show more of the history and any other information I want.

SQL Dates and Times

I always have trouble remembering the basic code for manipulating datetimes in SQL server Jeff has posted them in his blog with a few others. In addition there are the functions that Michael has posted for calculating the start of a given time period.