OVER() clause two simple ways on two different days
By Art on September 26, 2012

I’ve been aware of this clause in TSQL for a long time, but I’ve never really used it. Mainly because I’ve rarely had the need to use windowing functions because I primarily work with XML and relational data that doesn’t need to be aggregated that often or have ranking applied to it. In the rare times that I could have […]

Read More
Revised: Difference between collation SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS
By Art on September 20, 2012

This is a revised post of an original article that I first published in December. Even though I thought that I was very meticulous in my research, something was pointed out to me that I was totally unaware of – Unicode character expansions. So apologies to the community with regard the first article that I published as some of the […]

Read More
Script to find orphaned/unreachable job steps within your SQL jobs
By Art on August 24, 2012

SQL jobs are great as the SQL agent job engine allows for very granular control over jobs that are created by allowing great flexibility with the scheduling, execution, flow actions and notifications. Invariably, when jobs are setup, they just run and run without many problems, but the other day I was called in to look at a problem where a […]

Read More
Using XQuery to transform multiple xml nodes to a single xml node with comma separated string value
By Art on July 13, 2012

Not another comma separated string blog post I hear you say! I know that this topic has probably been done to death but I hope that this is a little different as it is about creating a single xml node containing a comma separated string from the values of multiple other xml nodes all within an XQuery expression. Recently I’ve […]

Read More
Moving xml child elements to its parent using XQuery
By Art on July 9, 2012

I haven’t been able to do much XQuery development recently which is a shame as I love developing with the technology in SQL. In the past I used to work with xslt’s a lot and they immensely powerful, but since moving to xquerying in SQL, it is now my first choice for quick xml transformations. Especially if the XML is […]

Read More
Unable to activate a Windows 2003 VM on Hyper-V behind a proxy
By Art on June 25, 2012

Ok, so this is not strictly a post about SQL itself (and it’s about a fairly old OS), but I did run into this problem myself a couple of weeks ago whilst creating a very specific build to test some functionality and seeing as it took me a little bit of googling, I wanted to jot the fix down as […]

Read More
Shredding a simple xml structure to a single row flat table
By Art on May 19, 2012

I was helping out a user on the forums the other day where they were asking questions about shredding an xml structure of which they didn’t know the schema for. They wanted to know the best way about writing a generic function to shred any xml to a flat table. In the past I have used the XMLTable function developed […]

Read More
Command line to configure windows firewall for SQL server access
By Art on May 10, 2012

I regularly deploy SQL Server to new Windows Server 2008 hosts and one thing that I find quite a chore is the wizard to configure the windows firewall on the host. So I done a quick bit of research today and found that the windows firewall can be configured from the command line very easily. Basically all you need to […]

Read More
Script to analyse space usage by your Service Broker
By Art on April 16, 2012

I believe that service broker is one of the best features from SQL server 2005, well along with the xml data type, and I use service broker a lot for messaging between solutions. Over the years it has proven to be extremely reliable; only really stopping because of a poison message or other environment type issue outside the control of […]

Read More
Trello: An agile style kan-ban web app that really works
By Art on February 27, 2012

I am no different to many others where I am being asked to work on various projects concurrently and expected to work through a long list of technical debt that needs addressing with our SQL databases. Throw in the usual day to day production issues as well and you get yourself a long list of jobs to do that have […]

Read More