Application

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 a try_cast function which won’t work prior to SQL2012 I believe. 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 Agent – Environment specific workflow

SQL Agent – Environment specific workflow

I like to keep my SQL Agent jobs in sync between environments, however not all jobs need to be run in all environments. To handle this, I’ve been using some TSQL to force a failure and end the job at a point. Here’s the overall job Within that, the TSQL for the ‘–Finish here if DEV–‘ step is To control the workflow, I’m setting the on success and on failure as follows:

read more

Controlling Chart axis in SSRS

Controlling Chart axis in SSRS

Had a tricky one today where the business wanted more axes to show on an SSRS report. Seems fair enough, cause as this example shows, a single line is pretty bland. After a bit of reading, I found a few clues over here. The concept is to convert a number to scientific notation, then use the first digit as a multiplier against 10 to the power of the exponent. This generates a nice round number, which is also divisible by 5. Here’s the code, I just pass the range of values on the chart (the calc is also used for the max of the chart), and the function returns an appropriate interval Here’s how my chart looks wtih the interval calcs: But… well that doesn’t look great either, because 600, 1200, 1800… and the maximum isn’t shown. So then the code ‘evolved’ again… End result:

read more

SSRS Missing Functionality

SSRS Missing Functionality

Just putting this out there cause surely I can’t be the only one missing this… If you install SSRS on SQL Server 2016 Standard Edition, quite a few of the menu items will be missing. Items such as: Mobile Reports KPI Reports Branding The solution.. install SSRS on Enterprise Edition.

read more

SSIS Framework Selection

SSIS Framework Selection

After 16 years of BI development in the IBM Cognos stack, I’ve recently jumped ship over to Microsoft and have faced a pretty steep learning curve as I plan to build a robust data warehouse and reporting solution in three months! To jump start this activity I’ve been reviewing SSIS Frameworks, which will provide the ‘belts and braces’ around ETL code without my having to invest months building this logic around logging, notification, error detection / recovery and job flow from scratch.

read more

Auto-Generated prompts in Cognos 10 BI

Auto-Generated prompts in Cognos 10 BI

I had an issue with Cognos 10 today whereby the automatically generated prompt control for a BI report was showing a drop down list, rather than a type in prompt. The reason this was an issue is that I wanted to set up a schedule of this report, with this particular prompt being for ‘Client Code’. As we have many thousands of clients, there seems to be a limitation with auto-generated drop down prompts whereby they only showing the first thousand or so records. After some experimenting, I was able to find that coalescing the column with a null string would force the auto-generated prompt to be a type-in box, thus allowing the client code to be entered. Here is the code originally used by my filter: #/* Strategic Client */# [Engagement Revenue].[Strategic Client Level Dimension].[cons_client_cd] = ?pStrategicClient2? and here is the modified code that uses a type-in prompt box: #/* Strategic Client */# coalesce ([Engagement Revenue].[Strategic Client Level Dimension].[cons_client_cd], ”) = ?pStrategicClient? Below is a picture showing both these prompt behaviours: Note: I’ve since discovered you can also set the prompt behaviour in the Framework Package

read more