Bulk documenting common columns in Sql Server 2008

Recently I have just discovered the very cool app that is DataDictionaryCreator http://datadictionary.codeplex.com/. For those of you don’t know, it is a windows application that allows you to document your database with the documentation stored as extended properties within the database itself.

Almost every table that we have in our database contains 2 common fields that have the same meaning called “CreateDate” and “ModifyDate”, I’ll leave you with the hard task of trying to work out what they are for. Anyway to save myself manually adding the same description for these I knocked up a little sql script to write the data to the db (I did cheat a bit and had a peak at the sql exported from DataDictionaryCreator).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
use [DATABASE_NAME]
go

set nocount on;

declare @ColumnName nvarchar (128)
declare @TableName nvarchar (128)
declare @MS_Description nvarchar (500)
declare @Debug bit = 0
declare @SchemaName nvarchar (500)

declare @CommonColumnDocumentation table
(
    ColumnName nvarchar (500),
    MS_Description nvarchar(500)
)

declare @TablesToProcess table
(
    ColumnName nvarchar (500),
    TableName nvarchar(500),
    SchemaName nvarchar(500)
)


insert into @CommonColumnDocumentation
select 'CreateDate', 'This is the date the row was created. It is populated via a default constraint.' union all
select 'ModifyDate', 'This is the date the row was last modified. It is populated via an update trigger (If it is not being updated correctly please check the trigger exists).'

insert into @TablesToProcess
(
    ColumnName,
    TableName,
    SchemaName
)
select
    sc.name as ColumnName,
    so.Name as TableName,
    ss.Name as SchemaName
from syscolumns sc
 join sys.all_objects so on sc.id = so.object_id
 join sys.schemas ss on so.schema_id = ss.schema_id
where so.type = 'U'
    and sc.Name in (select ColumnName from @CommonColumnDocumentation)

while exists (select * from @TablesToProcess)
    begin
        select top 1
            @SchemaName = ttp.SchemaName,
            @ColumnName = ccd.ColumnName,
            @TableName = ttp.TableName,
            @MS_Description = ccd.MS_Description           
        from @TablesToProcess ttp
            join @CommonColumnDocumentation ccd on ccd.ColumnName = ttp.ColumnName
           
        if (@ColumnName is not null and @TableName is not null and @MS_Description is not null)
            begin
                print 'Updating documentation for ' + @SchemaName + '.' + @TableName + '.' + @ColumnName
               
                if(@Debug <> 1)
                    begin
                    if exists ( select 1 from ::fn_listextendedproperty (
                                    N'MS_Description',
                                    'Schema', @SchemaName,
                                    'Table', @TableName,
                                    'Column', @ColumnName))
                        begin
                            exec sp_dropextendedproperty
                              @name = 'MS_Description',
                              @level0type = 'Schema', @level0name = @SchemaName,
                              @level1type = 'Table', @level1name = @TableName,
                              @level2type = 'Column', @level2name = @ColumnName;
                        end
                       
                        exec sp_addextendedproperty
                            @name = N'MS_Description',
                            @value = @MS_Description,
                            @level0type = N'Schema', @level0name = @SchemaName,
                            @level1type = N'Table',  @level1name = @TableName,
                            @level2type = N'Column', @level2name = @ColumnName;
                    end
            end
           
            delete from @TablesToProcess
            where TableName = @TableName
                and ColumnName = @ColumnName       
       
    end

As always use at your own risk, and if anyone has any suggestions…

Team build fails even though the project builds fine.

The last few days we’ve been having a strange problem where TeamBuild on our TFS server was repeatedly failing. What was even weirder was that the solution definitely builds OK and there are no compile errors. So I started digging around in the log file and as you will no doubt know (On average mine are 3k-10k line plain text files!!) this is a rather painful task.

After quite a while looking through this it was a fruitless exercise and was no closer to find out why. Luckily I have already setup other VM build servers and these were running ok so was able to narrow it down to the TFS server itself. Next I tried deleting the BuildSources folder to see if there was any rouge or corrupt files there. Nope, not that. I vaguely seemed to remember that the Build process itself runs as a windows service so I checked that out. Was seemingly working fine but decided to give it a bounce just incase. AS always this action seems to work and the build server started working fine. I just wish I’d always remember to log-out/restart app/reboot first as this would have saved a LOT of time.

Just remember when 1st-line tell you to log-out/reboot, do it, you might be surprised!! 🙂

Using the virtual memory restriction in IIS6

We’ve been having some seemingly random performance issues on some of sites and for a while I was quite stumped. Basically every so often the website would take between 16-20 seconds to load the page, although we were not able to predict when it would happen, as it occurred on different pages within the site.

My initial thought was that it was a network issue due to the fact that it was only being reported on some sites. Luckily the company subscribes to a external monitoring service so I was able to see the sites that it was happening on and how frequently. Once I had a look there I noticed that was happening on all the sites to varying degrees.

One of the servers was taken out of the DMZ to try and isolate the issue and it was only then that I noticed what was going on. With all the other sites shut down and only one or two w3wp.exe process to keep an eye on I was noticing that one of the processes was being shutdown and replaced with a new one. Now I know that this is the what happens when an app pool gets recycled but this shouldn’t have been happening as we had only 1 scenario enabled, the virtual memory limit setting:



As you can see this was set to 250Mb and process itself was never getting anywhere near that!! In fact even before I had thought it was a network problem I did actually check for this, however with so many w3wp.exe processes (~30) it was difficult to see them recycling, and because none of them were going near my limit I quick discounted this idea! So I went ahead and removed the setting and re-ran the tests. Low and behold it fixed the issue. Now the reason I had set it in the first place was due to getting System.OutOfMemory exceptions if too many bots were walking the site at one time. This happens due to the fact that we use the XsltCompiledTransform to clean up content coming from our Homebrew CMS. This generates a lot of assemblies and hence when to much content is accessed too quickly it runs out of memory. Initially I was wondering if there was a bug in IIS6 but it turns out that memory can be consumed directly from the swap disk completely bypassing the physical RAM and hence why the w3wp.exe never got anywhere near my limit.

Anyway in the end the fix was quite simple, just use the physical memory setting in IIS. 🙂

Using PowerShell to record ping failures to a logfile

Recently we’ve been having some major problems with our websites not being able to connect to our Sql2008 cluster. Part of the analysis I was asked to run (yes I do seem to do everything here!!) was a simple ping test. We started off with the classic command line ping to file using the pipeline (ping IPADDRESS -t > LogFile.txt). This is good but has one major flaw, the results don’t record the date and time to file, all you can see is that it DID fail. So I decided to use my favourite scripting language to knock up something a bit better and this is what I came up with:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
$serverName = "[IpAddress]";
$showAllResults = $false;
$logFilename = "[PathToLogFile]";
$ping = new-object Net.NetworkInformation.Ping;
$minRoundTripTime = 500;

function WriteToOutput($filename, $text)
{
    $text | Out-File $filename -Append;
    Write-Host $text;
}

WriteToOutput $logFilename "$(Get-Date): Starting ping logging...`nPress any key to stop!";

do
{
    $result = $ping.send($serverName);
    if($showAllResults -or $result.Status -ne "Success" -or $result.RoundtripTime -gt $minRoundTripTime)
    {          
        WriteToOutput $logFilename  "$(Get-Date) -Status: $($result.Status) -time: $($result.RoundTripTime)`n";
    }
}
until ($Host.UI.RawUI.KeyAvailable);

WriteToOutput $logFilename "$(Get-Date): Ending ping logging...`n`n";

Let me know if you have any suggestions to improve it!! 🙂

Getting Team Build to trigger a new build when you update the MSBuild file

I’ve been working on getting some more builds added to our TFS server today and actually had a moment of inspiration! It doesn’t happen all that often so better blog it while it’s still fresh! 🙂

I have set-up several custom build steps that run after the normal build process has finished. These steps simply remove any files not needed for deployment and deploy to a server so it can be tested/QA. Previously if I updated the MSBuild file that handles the build and deployment (TFSBuild.proj) I had to manually trigger a build. Today I realised that if I add the folder containing the MSBuild file, TFS would automatically detect that it has changed and trigger a build!! I know it’s only a few clicks and will not save a lot of time, but everything counts!!

64-bit W360BT Bluetooth drivers for a Dell D630 laptop

After re-installing my work laptop with the 64-bit trial of Windows Server 2008 I was having trouble getting the bluetooth working due to a lack of drivers. Strangely Dell do not list any compatible drivers when you use the service code however after much googling (and people suggesting using Toshiba drivers which didn’t seem to work) I managed to find some, you’ll never guess where, yes the Dell website!!

The drivers can be found here.

They seem to be working so far even though the GUI comes up with an incompatible OS warning (and I use a Bluetooth mouse!).

Make sure you uninstall obsolete hardware apps when P2V’ing servers!!

The last few days I have been performance testing one of our websites due to a severe performance drop after migrating to a new VMware hosted environment. Admittedly the application is not a great perfomer anyway (due to IMHO major overspeccing on the application security) but there was a significant drop in performance that was causing users (and us) grief.

It turned out that IBM director was using about a constant 30% CPU whilst the box was doing anything and even though it never actually reached 100% the user experience was that of a box under full load. Once it was disabled/uninstalled the box went back to normal.

TBH this was prob an oversight on the person doing the P2V but uninstalling obsolete software is something that really should be done as a matter of course!!

How to call T-mobile UK voicemail using your Flext minutes!

UPDATE: Unfortunately T-Mobile have twigged onto this and started charging for the voicemail retrieval when calling yourself directly. Due to this fact I’ve just ordered the new HTC Desire from three.co.uk as their voicemail minutes come off your call allowance.

T-mobile are a bit sneaky and don’t include any calls you make to your standard voicemail number in your free flext minutes but instead charge you 10p/12p a minute for the pleasure.

Fortunately I have just found a hack (kind-of) the enables the voicemail to come off your flext allowance!! 🙂

I have known for a while that if you setup a PIN to allow remote access to your voicemail you can use another phone and only get charged for a normal call. Thanks to having a work BlackBerry I have been using that, and not getting charged. However it was a bit annoying having to ring, then hang-up or wait for it to go to voicemail and then type in the PIN number.

Now down to the real sweet part. If you notice when you call your voicemail normally the number is basically your mobile number but with a few extra digits. Now if you change it to your exact mobile number it still works as normal and automatically recognises you and bypasses the PIN entry. This then gets listed under your calls as a standard call and is taken out of your Flext allowance!! 😀

If you are using a windows mobile phone it seems that T-mobile block you from changing the voicemail number for obvious reasons (it is usually stored and then read from the SIM). Now with a few little registry changes you can override the one in the SIM. To do it make the following updates (add if missing):

1
2
3
[HKEY_CURRENT_USER\Software\Microsoft\Vmail]
"PhoneNumber1"= "[Your mobile number]"
"RegOverridesSIM"=dword:00000001

Anyway as always be careful editing the registry and don’t come running if you screw it up! Hopefully this will save you a bit of cash!! 🙂

Orcale Enterprise Linux hangs when booting under VirtualBox

Wow, 2 posts in one day this is a new record for me! 🙂

One of my tasks on ATM is to do som PoC work on Oracle WebCenter. Unfortunately the VM supplied by the vendor was corrupted so I have downloaded the ISO’s myself and giving it a go setting up in VirtualBox. I much prefer VirtualBox over VMWare Server or Hyper-V (As with all MS software V3 will be the one!! ;)) but was having problems getting the Enterprise Linux ISO to boot and was hanging here:

Oracle Enterprise on VirtualBox hanging screen

However just a few tweaks of the settings were required to get it to boot to the installation screens. The setting that need enabling was the “Enable IO APIC” setting:

VirtualBox setting required

Solved: Resharper test runner and Gallio not playing nice.

Recently I have been giving the Gallio test suite a go. I have been looking at it for several reasons. Primarily the RollbackAttribute for some integration tests (although also looking at Moq so not sure which route I’ll take yet!).

At first I was a bit stumped trying to get the Gallio Resharper plugin working, it installed fine and was listed in the Resharper plugin dialog but was coming with an “Unsupported” Message:

Gallio/Resharper integration issue

It took me a while to find out why but it was due me referencing an old version of the MbUnit dll that wasn’t compatible with the Resharper plugin. Once updated with the latest version the plugin works fine :D:

Gallio/Resharper issue resolved!

As always there is a moral to this story, ALWAYS use the right dll’s!! 🙂