East Anglia SQL Server User Group

Anyone working with SQL Server will be well aware of the mountains of training material, blog posts, videos, code snippets, events and other helpful resources that helps anyone who comes into contact SQL Server regardless if they are a beginner or a seasoned veteran (even MVPs!). One of the pools of knowledge is SQL PASS. is an independent, not-for-profit organization run by and for the community. With a growing membership of more than 100K, PASS supports data professionals throughout the world who use the Microsoft data platform....

January 15, 2016 · 3 min · matt40k

Automated SQL Server BI deployments with OctopusDeploy

Today, my pull request for the SSAS deployment script was merged into the Octopus DeployCommunity Library. So what is Octopus Deployand why should I care? Octopus is a friendly deployment automation tool for .NET developers. But don’t be fooled. Octopus Deployisn’t just limited to .NET developers deploying hip new cloud based applications, it can do much, much more. I currently use Octopusto deploy Jasper reports to our 6 HR environments, I also use it to deploy our HR interfaces which are a series of SSIS packages and SQL objects which uses Microsoft SQL Server 2008....

January 15, 2016 · 3 min · matt40k

GeoJSON

So I’ve been playing wit mapping data, one of the nice things about GitHubis that it supports GeoJSON and automatically renders it With the mapping data already imported into Geographyin SQL Server it was easy enough to convert to GeoJSON (Stackoverflowto the rescue again!)

December 10, 2015 · 1 min · matt40k

Getting collation descriptions

Just an updateas I noticed I forgot to say how to get the names and descriptions of the collations your SQL Server supports

December 10, 2015 · 1 min · matt40k

Using PowerShell to check setup

So I was handed over some new servers, before I got started I wanted to check everything was as it was suppose to be, so I wrote a few PowerShell scripts to check each server was setup correctly. I wanted my DEV to 100% match my UAT and so on. First, I wanted to check who has local administrator access on the servers. Next, I wanted to check the drives – I wanted to make sure each drive letter was the same across environments and all had a proper label....

December 9, 2015 · 1 min · matt40k

Collation

I don’t know a lot about Collation – I know how to set it in SQL Server, I know it’s important, it know chaos can ensure if your not careful for example if you set it to CS rather then CI*, so my general tack is to leave it as default and hope it leaves me alone. Today however, this happened Turns out in the VS SSDT package, I have forgotten to set one of the databases collation in project settings to Latin1_General_CI_AS to match the default on SQL Server (rather then the VS SSDT default)...

November 24, 2015 · 1 min · matt40k

Deploying SQL 2014, looking at SQL 2016 \ PowerBI

My current thoughts on SQL2016/PowerBI as deploying SQL2014. Ok, so we’re looking at moving to pure SQL 2014 for our corporate BI solution. I say pure as we normally use SQL server as the backend then other tools on top, so we’re looking at enabling a lot of extra features we’ve technically had for years but just not enabled\setup\whatever. SQL2016 is on the way and we’re most likely going to upgrade pretty quick, lots of new features that look very useful....

November 6, 2015 · 5 min · matt40k

Problem with ISNUMERIC

Todays problem was brought to you by a highly customizable system with poor user validation and a user who doesn’t know the difference between data and formatting. First the system. The supplier has created a highly user customizable system, they can for example create a web form with zero coding – 100% user config. The problem is, technically, it creates a mess. So your nice new form with a number of questions stores all its answers in a single table with the data going into a nvarchar(max) column – which is fine for free-text columns, but not so good for integer fields....

October 27, 2015 · 2 min · matt40k

Joys of C#

On Friday, I had a problem raised, they want to change one of the automatic rename jobs which renames and moves export files to a shared drive. Lucky, I had created a SSIS package, which was a SQL Job that runs on a scheduled every 15 mins, which uses a script task to perform the logic. Because we also used OctopusDeploy, this really was going to be a 5 min change....

October 10, 2015 · 1 min · matt40k

SQLSaturday #411 – Cambridge 2015

For a while now I’ve been going to the Cambridgeshire SQL Server User Group, which I have found really useful especially as we are at moving towards a more self-service model across IT which requires IT to be a bit more cleverer. Beyond the informative speaker talks you get a really good networking opportunity. The group is run by Mark Broadbent(@retracement) and has recently merged with SharePoint Saturday Cambridge. Although Cambridge is about the same time on the train as London....

September 28, 2015 · 7 min · matt40k

Change text direction – Visual Studio 2013 / SQL Server 2014

I’ve spent a while today trying to change the text direction (orientation) of a header on a SSRS report – the option was rather oddly placed in Visual Studio – the option is within the properties (for the text) Localization > Writing Mode > then change from Default to either Rotate270 or Vertical. I was hoping it would be within the Font options. Guess that would be too easy 😉

September 15, 2015 · 1 min · matt40k

Missing Reporting data toolbox in Visual Studio 2013 / SQL Server 2014

Todays problem, missing the reporting data toolbox in Visual Studio 2013 – once again, a different menu options. Open the actual report (.rdl) View > Report Data Alternatively press Ctrl + Alt + D

September 15, 2015 · 1 min · matt40k

Missing SSIS toolbox in Visual Studio 2013 / SQL Server 2014

Stupidly today, I accidently closed the SSIS toolbox. After a bit of unsuccessful searching in the Visual Studio menus, I gave up and done the IT norm and Google it – hoping I wouldn’t have to reinstall. Luckily I found James Serrahad already blogged about it, the only difference is Microsoft has changed the icon, its still in the same place at least So when is a Visual Studio Toolbox, not a Toolbox?...

September 2, 2015 · 1 min · matt40k

SSIS jumping in Visual Studio 2013 / SQL Server 2014

One really annoying bug in SSIS when using Visual Studio 2013 / SQL Server 2014 is the object jumping, so when you drag an object like a sequence container down the object shoots to the bottom. Thankfully the fix is simple. Close Visual Studio and apply SQL Server 2014 SP1to your machine.

September 1, 2015 · 1 min · matt40k

Documentation

One of the problems we have is documentation and how to keep it up-to-date. We tend to deliver a new batch of reports as part of a CR (Change Request) then make changes as part of BAU (Business as usual) unless its a particularly complex or time consuming change. The problem is often BAU changes happen rapidly often within a short time frame and time isn’t always allocated to updating documentation....

August 30, 2015 · 2 min · matt40k

Small world

So I’ve been following a few folks on Twitter – one of them is Tim Mitchellfrom the US, he tweeted a link to some resourceshe used in a recent SQL Saturdayhe presented at. One of them is Konesans.com. Who happen to be based in Holbeach, which is really close to my home town of Wisbech. You go all over the world in search of knowledge, only to find there’s a guru right on your own doorstep....

August 30, 2015 · 1 min · matt40k

Failed RavenDB backup

I had a rather odd error with OctopusDeploy where the RavenDBbackups were failing. Turns out the problem was because I had assigned 1 IPv4 address to OctopusDeploy and another IPv4 address for TeamCity. Oddly the RavenDB backup routine referenced the localhost address – 127.0.0.1, it appeared to be hardcode with no option other then changing port. This was especially weird as the OctopusDeploy service, was able to access it fine. To resolve – at least long enough to get a backup to migrate to v3 which uses SQL Server– was to setup a port proxy so accessing TCP port 10931 on the local loopback would resolve to the same port but on a the actual public IPv4...

July 22, 2015 · 1 min · matt40k

Moved backup locations

It appears IBM has moved the Cognos backup folder location from: C:\Program Files\ibm\cognos\c10_64\bkp To: C:\Program Files\ibm\cognos\c10_64\uninstall

July 22, 2015 · 1 min · matt40k

Swollen Business Objects database

Our new HR system uses SAP Business Objects for transactional reporting, as this was setup by our HR supplier we haven’t really got involved with it beyond writing a few custom reports, turns out we’ve written quite a few, so much so that the Business Objects CMS database has swollen to over 30 GBs. This, as you might have guessed, is causing a few performance problems. After a discussion with the supplier, we found out it was storing all the reports, even the ad-hoc one-off reports and we should have set a parameter to limit it....

July 19, 2015 · 2 min · matt40k

New Azure features

Two new Azurefeatures have caught my eye – the first is Logic App. In a nutshell this is workflow. You have connectors and you can add triggers and actions. So you could have a trigger on your HR system that when a new employee starts, it creates a user account then sends a welcome message to the existing staff informing them of the new starters. The other is Data Catalog, this basically allows you to generate meta data about your data sources....

July 16, 2015 · 1 min · matt40k

Parking fines issued in Ipswich

I made the above video using Microsoft Excel using data from a FOI request to Ipswich Borough Councilabout the number of Parking fines issued since 2010.

June 16, 2015 · 1 min · matt40k

Toshiba laptop update

Just an update to my previous post about the Toshiba Laptop, I’ve been thinking about reinstalling the Toshiba laptop with Ubuntu, but before I do that, I want a backup. Luckily, I still have the original 500GB hdd spare, I just need a USB caddy. Luckily my local Curry’s had a USB3.0 one (PNY SSD and 2.5″ Hard Drive Enclosure and Upgrade Kit), before anyone comments about how I could of got one cheaper off eBay, just remember, I was able to get it same day and it included Acronis....

May 31, 2015 · 1 min · matt40k

Technical debt

I was talking to a colleague about one of our suppliers and the progress they were making on a new product they were developing. He was surprised to hear how little progress they appear to have made, they had previously been making huge steps in very little time with very few developers, especially surprising when they have been scaling up the development team over the past few months. I explained they are burning some of the technical debt they have collected whilst they still can....

May 26, 2015 · 2 min · matt40k

Toshiba R50-B-12Q

UPDATE: I’ve detailed the Toshiba default build a bit hereAt the beginning of the month I order a new laptop, for the past few weeks I’ve been wanting something more mobile then my desktop, at first I was toying with the idea of selling my desktop and picking up a new Surface. The new Surface 3 which starts at £419 seemed like a good idea what with its sexy full hd touchscreen, however when you scratch the surface (ha ha) it doesn’t look so go...

May 25, 2015 · 3 min · matt40k

Version Control

I’ve been thinking lately about the adoption of version control. One of the common fears of using version control is that its permanent. All mistakes are visible and are in fact, highlight. Originals are updated with new commits which detail only the change and a nice comment. There is no hiding mistakes. The thing is Mistakes are what make us human. Everyone makes mistakes, we need to accept that fact. In doing so we can move forward faster....

May 19, 2015 · 1 min · matt40k