Snippet

sp_who3

sp_who3

I’ve long been a fan of sp_who2 for identifying blocks on SQL Server, but it’s a bit clunky and there’s a lot of stuff to wade through. Which is why I’ve collated several scripts from around the place and combined them into sp_who3 I’m using this on SQL2016, and it’s likely to work on SQL2012. Install the stored proc with the code at the bottom. To run it, just type¬†into SSMS: This will return three result sets. The first is filtered version of sp_who2 which will show only those SPIDs which are blocked, as well as the SPIDs which are blocking. The second returns the standard sp_who2 list, The third returns the exact SQL for each SPID, as well as detailed performance and blocking stats, c/- a query from msdn.

read more

SQL Server code to pivot Key Value Pairs into fixed Colums

Square peg and a round hole.  Metaphor for a misfit or nonconformist.

Today’s requirement was to translate 550k rows of product data from a long list of name / value pairs into a wide table with up to 15 name value pairs per product.           I tried a few techniques to achieve this, and found SQL Server’s windowing functions to do a pretty good job of building a cross-tab from this flat list.

read more

Seeding an integer sort code into a Data Warehouse from a varchar

Seeding an integer sort code into a Data Warehouse from a varchar

I found this a bit of a challenge this morning, and as it’s my first use of windowing functions in SQL Server I thought I’d share. Our requirement was to add a sort code to our dimension tables for various levels. For some reason many of our business keys are varchars, however I really wanted to get sort codes as integers. Here’s the code: This generates three distinct sort keys, one for each level. Quite nifty I reckon.                       Long term we’ll look to move this stuff into a source system somewhere, but for now, she’s sorted.

read more

Seaching Cognos 10 Content Store for an XML string

Seaching Cognos 10 Content Store for an XML string

We recently migrated our knowledgebase from Collab to sharepoint, which meant all reports that had links to collab needed to be identified and edited. This query proved helpful – it searches the content store for occurrences of the old string, ‘collaboration.au’, and displays the report name and location. The query does take a while to run though – almost 40 minutes against our beast-mode content store…

read more