Skip to main content


Showing posts from 2008

MCTS - SQL Server 2008 - Database Developer

Was pleasantly surprised on Friday to discover that I passed the beta exam that I sat a couple of months ago! Just need to get the MCP site logo-builder to work now, so I can update and show-off my MCTS logo ...

... couldn't resist posting this screen-shot tho ;-)

How to enable table-designer updates in SSMS 2008 ...

SQL PASS Community Connection Event 2008 ...

This event was held in Porirua (NZ) on the weekend of 7th and 8th of December '08. Posting this snippet from an email I sent the the NZ SQL Server Mailing List, as a future reminder to myself about how useful the event was ;-)


My personal favourites were Adam and Greg's 'Iron DBA' (which was a nice wakeup for the first morning), Pete Smith's spatial data talk (I'm biased on this one), and James' talk about certification (quite motivating!).

I was also quite interested in Adam's reporting services talk, but I realised there was a server failure at work about that time so spent most of the time doing emergency recovery type stuff :-/ Fortunately my colleagues took lots of notes (I hope!). To top it off, Chris' SQL Data Services talk was a bit of a riot and certainly captured the interest of the whole group I think.

All in all well worth the trip from Auckland. The only thing I will complain about is the weather - why did NZ have to turn on the finest …

RYO AJAX script ...

Because I haven't posted anything for a litle while - I thought I'd dig out some old code and blog it in the name of consistency ...

So here's how to roll your own AJAX - to - .NET XML web-service call:

// Get the XMLHttpRequest object
function getXmlHttpRequest()
if (window.XMLHttpRequest)
    xmlHttpRequest = new XMLHttpRequest();
else if (typeof ActiveXObject != "undefined")
    xmlHttpRequest = new ActiveXObject(" Microsoft.XMLHTTP");
return xmlHttpRequest;

// Send the text to server
function sendSelectedText()
var xmlHttpRequest = getXmlHttpRequest();
if (xmlHttpRequest != null)
 // send data to server
 var url = "http://" + location.hostname + "/AJAXSample/WebService.asmx/GetData?strParam=" + document.getElementById(txtBlah).value…

Beta Exam 71-433: TS: Microsoft SQL Server 2008, Database Development

Did this exam on Friday afternoon (10/10/08).

I wrote down the significant tested 'areas' and counted the number of times they came up. The exam format was 75 X multi choice questions, no practical component. It went a bit like this:
XML ~12 questionsCLR ~2 questionsService Broker ~3 questionsRecursion ~3 questionsTriggers (DML/DDL) ~5 questionsFull Text Index ~3 questionsDatabase Mail ~2 questionsMonitoring and Troubleshooting (DMV, process monitoring, etc) ~7 questionsSpatial Datatypes (Geography/Geometry) 1 questionWindows Powershell 1 question (cmdlet)
Database/Server Collation ~2 questionsChange Tracking ~7 questions
etc ...Actually I think that this preparation guide provides quite an accurate summary of what to expect:

Areas of emphasis seemed to be general query implementation, XML based data management, triggers and change tracking. Some questions were clearly not directly targeting knowledge of these emphasised fea…

One I learnt the hard way ...

Don't use a 32-bit int to represent a phone number.

When I first started writing code for telecommunications applications, I used int all over the place in my TSQL. I soon clicked that this was not such a good idea (prior to any chaos being caused), and went around swapping out my ints for varchars. Unfortunately I didn't get them all ...

In NZ, the longest mobile phone numbers we have had for quite some time have been 10 digits, starting with 02 - so like 0275555555 would have been a long-ish one. As I have noted due to recent 'issues' arising with one of my real-time systems, we have recently moved (in NZ) to include 11 digit mobile numbers. Which pushes my dodgy code into exception (largest signed 32-bit int being 2147483647).

Great, so that kept me very busy for a couple of hours yesterday, extracting the remaining phone number ints out of my TSQL. Sometimes we learn lessons the hard way I guess ;-)

WPF: use IValueConverter to implement dynamic binding to an XML data source ...

Here are some swift details on how to use IValueConverter to implement dynamic binding to an XML data source.Add the XML source to your XAML page/window as a resource. Also add your URL converter that gives you customises your input. You could use XLINQ here to get some nice thingie going. I'm using it for manipulation of a static application map:<Page.Resources><XmlDataProvider x:Key="ApplicationMap" Source="ApplicationMap.xml"/> <src:PageURLConverter x:Key="pageURLConverter"/></Page.Resources>Make yourself a Frame and use the XML source and the converter to build the URL that the Frame will hold:<Frame Name="frameContent" Source="{Binding XPath=@PageLink, Converter={StaticResource pageURLConverter}}"NavigationUIVisibility="Hidden"Focusable="False"></Frame>The converter looks like this:public class PageURLConverter : IValueConverter{public object Convert(object value, Type ta…

SQL Server 2008 and Spatial Data ...

DId a bit of a talk at the most recent Code Camp event in Auckland City. My talk centred around spatial data, and how it is used in the telecommunications industry.

I've been doing a bit of research in relation to this matter recently and found that Microsoft have finally published a nice big section of MSDN stuff dedicated to how to use the spatial data components of SQL 2008. Looking forward to getting stuck into that!

Efficient way to find specific WPF elements further up the Visual Tree ...

Implement a search method like this:

        public static T FindFirstVisualTreeParentByType<T>(DependencyObject initial) where T : DependencyObject
            DependencyObject current = initial;
            while (current != null)
                if (current is Visual || current is Visual3D) { current = VisualTreeHelper.GetParent(current); }
                else { current = LogicalTreeHelper.GetParent(current); }
                if (current is T) return (T)current;
            return null;

And use it like this:

Global.FindFirstVisualTreeParentByType<YourType>(this).YourTypesProperty = value;



Forgot how cool resharper is - brilliant tool for VS!! Does slow the system down a bit however, but not to a level that is intollerable ;-)

Reason number 1: Although I will need to suppliment what I have learnt with further reading on the subject, Resharper has helped me come up to speed with C# 3.0 much more quickly than I would have been able to by reading any book.

Will think of more reasons and post them later ...

SQL Server 2005 - Generic Cursor Code

I always forget how to put a cursor together, so I'm blogging it! DECLARE GenericCursor INSENSITIVECURSORFORSELECT<<fields>>FROM<<table>>WHERE<<criteria>>OPEN GenericCursorDECLARE<<variables>>FETCHNEXTFROM GenericCursor INTO<<variables>>WHILE@@FETCH_STATUS= 0BEGIN<<exec CRUD statement>>FETCHNEXTFROM GenericCursor INTO<<variables>>ENDCLOSE GenericCursorDEALLOCATE GenericCursor There are a few basic rules surrounding the field selection exactly matching the list of variables that you use. These things tend to work themselves out if error messages are correctly interpreted anyway, so I won't give a practical example ;-) All fairly self-explanatory - and I suppose this code does not change betwen versions of SQL - but I have not tested that ...


Not too many postings of late, as have had twins born a few weeks ago which has (and will) kept me very busy!! Anyway, getting back into the swing of things somewhat this week and have taken an interest personally and professionally in LINQ to XML. Found this post on the subject that uses this paradigm in a very innovative way: I like that idea ;-) Anyway, have recently been doing some work with WPF - so hopefully more posts to come on that subject ...

Useful tip for testing with NUnit - for when you are testing a class library that (obviously) has no config file ...

When you are testing a class library, that depends on the executable's config file, you can find yourself in bit of a fix. NUnit does allow you to use a config file, but it needs to be in the same directory as the build of the test project (which is also a DLL) and named <test_lib_name>.dll.config.

This is the easy way to get yourself setup to test against a DLL - you go into the project properties, and you put the following command into the "Pre-Build Event Command Line" box:

copy /Y "$(ProjectDir)App.config" "$(TargetDir)$(TargetFileName).config"

You then copy your App.Config file from your EXE project and away you go - the command provided (above) takes care of naming the config file and copying it to the right directory evey time you build the project!

Test Driven Development (TDD) ... a few thoughts ...

I completed a project a few months ago using TDD (got motivated to give it a go following the architecture camp). The system is in production, and is by all accounts performing well. So yes, pleased with the results. It is difficult however for me to know whether I would have been able to acheive the same results if I had not done TDD - if I were to guess ... I think the result would have been solid either way. The system is non UI, back-end process, so all of the complexities associated with accommodating a UI system (WinForms or ASP.NET, Page/From lifecycle, blah, blah, etc) were removed. The learning curve was certainly frustrating - I think in during steepest part of the learning curve I sent a post to the DNUG entitled 'TDD-ious?' as a result of my frustration :-/ The DNUG however was certainly a valuable resource during this time, and once it was recommended to me - and I started using Resharper and TestDriven.NET - the penny dropped. Actually, I think that doing TDD with…

Add or remove web-service protocols at the server ...

Found this useful blog entry just now:

The crux is this:

Member nameDescriptionAnyHttpSoapAny version of the HTTP SOAP protocol. DocumentationThe Web Services Documentation protocol. HttpGetThe HTTP GET protocol. HttpPostThe HTTP POST protocol. HttpPostLocalhostThe HTTP POST LOCALHOST protocol. HttpSoapThe HTTP SOAP protocol. HttpSoap12The HTTP SOAP version 1.2 protocol. UnknownUnknown protocol. You can use these values in the webServices/protocols element to add and remove capability.  For instance, if you want to remove SOAP 1.2 support for your service, you would include the following in your web.config:
<webServices >
<remove name="HttpSoap12"/>
Similarly, if you want to remove support for SOAP 1.1 and onl…