Sunday, 14 December 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 ...

Thursday, 11 December 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 two days of the year on that weekend??

...

Monday, 17 November 2008

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:

----------------------------------------------------------------------------
-- YOUR SAMPLE JAVASCRIPT FUNCTIONS --
----------------------------------------------------------------------------
// 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)
{
    try
    {
 // send data to server
 var url = "http://" + location.hostname + "/AJAXSample/WebService.asmx/GetData?strParam=" + document.getElementById(txtBlah).value;
 xmlHttpRequest.open("GET", url, false);
 xmlHttpRequest.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");
 xmlHttpRequest.send(null);

 // show returned result
 alert(xmlHttpRequest.responseXML.getElementsByTagName("string")[0].firstChild.data);
    }
    catch(e)
    {
 alert(e.message); 
    }
}
}

...

<input type="button" id="btnBlah" onclick="sendSelectedText()"></input>
<input type="text" id="txtBlah"></input>

------------------------------------------------------------------------------
-- YOUR SAMPLE WEB SERVICE WEBMETHOD --
------------------------------------------------------------------------------
[WebMethod]
public string GetData(string strParam)
{
// your data access code
return "blah blah your data here";
}

------------------------------------------------------------------
-- YOUR SAMPLE WEB.CONFIG FILE --
------------------------------------------------------------------
<system.web>
...
<webServices>
<protocols>
<add name="HttpGet"/>
<add name="HttpPost"/>
</protocols>
</webServices>
</system.web>

------------------------------------------------------------------

... use it if you dare!!


Sunday, 12 October 2008

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 questions
  • CLR ~2 questions
  • Service Broker ~3 questions
  • Recursion ~3 questions
  • Triggers (DML/DDL) ~5 questions
  • Full Text Index ~3 questions
  • Database Mail ~2 questions
  • Monitoring and Troubleshooting (DMV, process monitoring, etc) ~7 questions
  • Spatial Datatypes (Geography/Geometry) 1 question
  • Windows Powershell 1 question (cmdlet)
  • Database/Server Collation ~2 questions
  • Change Tracking ~7 questions
  • etc ...
Actually I think that this preparation guide provides quite an accurate summary of what to expect: http://www.microsoft.com/learning/en/us/exams/70-433.mspx

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 features, but contained a component of the feature that you needed to understand to be able to answer the 'primary' question.

The rest was general query implementation (e.g. "You have an X and a Y table, which of the following queries will produce result Z?"), questions on how to create and call stored procedures, when/where to create a view, etc.

Overall it was a great test - much more enjoyable than 70-431 for me, since it definitely geared specifically towards database development, with minimal emphasis on maintenance.

Finally, was interesting to note that after all the hype around SQL Spatial Datatypes, there was only a single question out of the 75 on this - albeit a fairly curly one ;-)

Friday, 12 September 2008

How to turn [master].dbo.[xp_cmdshell] on - SQL Server 2005/2008 ...

EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXECUTE sp_configure 'xp_cmdshell', '1'
RECONFIGURE WITH OVERRIDE
GO
EXECUTE sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

Thursday, 11 September 2008

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 ;-)

Friday, 5 September 2008

Microsoft SQL Server 2008, Database Development

There is now an MCTS exam directed specifically toward database developers (like me!):
 
 
Looking forward to giving this one a go ...
 

Wednesday, 3 September 2008

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 targetType, object parameter, CultureInfo culture)

    {

        string url = (string)value;

        if (url.StartsWith("http://")) return YourURLManipulateMethod(url);

        else return url;

    }

 

    public object ConvertBack(object value, Type targetType, object parameter, CultureInfo culture)

    {

        return value;

    }

}

 

I used this method in a CRM-type application to whack an Account Code on the end of a URL query string. Allowed me to load a pre-existing ASP.NET component of the CRM into a Frame in the WPF app for the same account the user was working with.

 

Tuesday, 2 September 2008

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!

http://msdn.microsoft.com/en-us/library/bb933876.aspx

Tuesday, 29 July 2008

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;

;-)




Tuesday, 17 June 2008

Resharper

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 INSENSITIVE CURSOR FOR

       SELECT <<fields>>

       FROM <<table>>

       WHERE <<criteria>>

OPEN GenericCursor

DECLARE <<variables>>

FETCH NEXT FROM GenericCursor INTO <<variables>>

WHILE @@FETCH_STATUS = 0

BEGIN

       <<exec CRUD statement>>

       FETCH NEXT FROM GenericCursor INTO <<variables>>

END

CLOSE GenericCursor

DEALLOCATE 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 ...

Tuesday, 10 June 2008

LINQ to XML

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 ...

Tuesday, 8 April 2008

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!

Tuesday, 1 April 2008

SQL Server 2008 Client Tools ...

Is not ready - i.e. is buggy ... and even worse - it bunged my installation of the 2005 client tools :-/
 
Recommend you give it a miss at least until it's out of CTP - unless you're running it on a VM that you can scrap.
 

Wednesday, 26 March 2008

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 without using a tool like Resharper is equivalent to doing .NET development using Notepad (i.e. without VS) - it's really painful and just not worth it when these tools exist.
 
So, my approach was to implement the Dependency Injection pattern, TestDriven.NET and NUnit for testing, etc, NMock for object mocking (Rhino Mocks is probably more popular), and Resharper for everything in between. All of this makes VS feel a little top heavy, and you want to get ontop of your configuration and settings fairly quickly. I think the significant (i.e. frustrating) part of the learning curve was over by the time the work was about 50% complete (by "about 50% complete", I mean "of the total number of hours that went into the project, half of them were gone" - i.e. not "about 50% of the code had been written"), from there I had the DI pattern sorted, had come to grips with Resharper, and was able to implement a test then develop the implied code fiarly efficiently. I still felt a bit clunkly with TDD once I had closed the project, but feel competent enough with it to say that I understand how it works. I estimate that across the course of the project, 20-odd percent of my time could be attributable to 'learning TDD'. I think that this 20-odd percent would have become less and less if I had continued with TDD.
 
Summary - if/when you get a suitable opportunity to give TDD a go, then I recommend you go for it - you have nothing to lose and everything to gain by trying it. You may even be fortunate enough to see the light (I think I caught a glimpse of it ;-)! I think TDD is a useful approach to know how to use, whether you pursue it long-term or not. Also, the TDD following seems to be growing right now, so if you're going to be in the software development business for a while, at some stage you're almost certainly going to encounter a situation that requires an understanding of it.
 
Personally, I'm not using TDD right now - I have no excuses ;-)

Monday, 18 February 2008

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

Found this useful blog entry just now:

http://blogs.msdn.com/kaevans/archive/2005/11/10/491640.aspx

The crux is this:

Member name Description
AnyHttpSoap Any version of the HTTP SOAP protocol. 
Documentation The Web Services Documentation protocol. 
HttpGet The HTTP GET protocol. 
HttpPost The HTTP POST protocol. 
HttpPostLocalhost The HTTP POST LOCALHOST protocol. 
HttpSoap The HTTP SOAP protocol. 
HttpSoap12 The HTTP SOAP version 1.2 protocol. 
Unknown Unknown 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:


<configuration>
<system.web>
<webServices >
<protocols>
<remove name="HttpSoap12"/>
</protocols>
</webServices>
</system.web>
</configuration>

Similarly, if you want to remove support for SOAP 1.1 and only support SOAP 1.2, you would use the following config:


<configuration>
<system.web>
<webServices >
<protocols>
<remove name="HttpSoap"/>
</protocols>
</webServices>
</system.web>
</configuration>


Tuesday, 8 January 2008

2008 Summer Road Trip - Auckland

This is going to be great - really looking forward to it!

http://aucklandroadtrip08.events.live.com/default.aspx

iTextSharp PDF toolkit/library ...

... is an absolutely brilliant set of tools for working with PDF documents (among other things). Wish I had found this toolkit eariler!

It's an open source port of a Java library:

http://sourceforge.net/projects/itextsharp/

Things I have found particularly useful are on-the-fly PDF watermarking and merging.


Friday, 4 January 2008

SQL 2005/2008 TRANSACTION in TRY/CATCH BLOCK

BEGIN TRY
BEGIN TRANSACTION

... your code here ...

COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
DECLARE @ErrorMessage as varchar(max)
SET @ErrorMessage = 'The error message is:' + (SELECT ERROR_MESSAGE())
RAISERROR(@ErrorMessage, 16, 1)
END CATCH

Migrating (and Open-Sourcing) an Historical Codebase: SVN-to-Git

I have a SVN repo on my local machine that I have been shoving stuff into since before I knew how to use revision control systems properly (...