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.

* Glossary
CI – case insensitive – recommended
CS – case sensitive – avoid

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. Power BI is out, but… we’re holding back, again we’re going to use it, its just a question of when it becomes the main BI tool.

So, we’ve already got the bulk of the data modelled in a multi-dimensional (MD) format, we just need to built a SSAS MD cube, the little pockets of data will be tabular cubes – users will use Excel for interacting with the data as well as end user reports – SharePoint will be the “portal” – much better then file system. SSRS will be for static reports – such as transactional reports that need to be static.

Going forward, it looks like PowerBI will replace the bulk of the Excel\SharePoint work – this will give a superior end-user interaction with the data, not only will it allow natural language interrogation of the data, but also personalised views – so you’ll see your data off the bat, you won’t have to drill down from your company, then team to get to your data. Data will come off the same ssas cube, so it’s still a single point for the data, its just a new way to interact with the data.

In terms of data security, we can limit PowerBI to just the summary data – via the ssas cube – so the fact PowerBI is hosted outside of the UK shouldn’t be a problem. The detailed stuff will still be on-prem – so SharePoint would still be needed in terms of a repository, but the bulk of the users will be using PowerBI, so its not as key, again, the data will come off the same ssas cube so we won’t have the “PowerBI says this but Excel says this” (insert caveat about people looking at different things – nb: need to check if can add url drill-down in PowerBI to say SSRS report with the detail).

The other bonus of the PowerBI is the friendly-ness towards the micro data sources – like the odd Excel spreadsheet. In terms of SQL2014, I was thinking about pushing towards Access Online – which backends the data into an Azure DB, still this will ultimately depend on policy on the data then the technology. Key is, we’ve got options.

In terms of end-user delivery SQL2014 will be perceived as SharePoint, really it will be Excel. The SharePoint is nothing more than a gloried online file share, we could in theory use OneDrive – at least until dashboards are more common but that won’t occur until the data is more readily accessible. The real power behind Excel will be using it correctly – using Excel as a visualisation tool to interact with the data and letting the SQL Server back end deal with grunt work – both in terms of the database and analysis services – rather then getting Excel to do all the grunt work and wonder why it so pants at it. The “SQL2016”/PowerBI rollout should be easier to land with end-users as there is a new thing being delivered, it’s easier to grasp a new product rather than an enabling some (new) features on an old tool. The key for delivering SQL2014 will be the SharePoint being the main place to go, it could become PowerBI. Plus side it means the SharePoint delivery is less critical as it could be replace if it doesn’t work out, if it does, lets hope this feature request goes head – Does mean I have to have a look at branding and friendly urls for PowerBI.

For public data publication, in terms of “SQL2014”, I am thinking Excel to get the data, then saving as a CSV (or passing onto IT to produce a standard extract, again as CSV) – opening this in notepad or such to validate it contains no unwanted (such as protected fields) data, then publishing (onto public website\github??) – then using Tableau Public to produce graphs for public consumption – Tableau also allows easy export data (as csv). For PowerBI – I would hope you could use PowerBI to interact with the data, then export as a csv – verify it (perhaps in a basic online viewer – similar to the way github displays csv tables), then publish it – ideally on a marketplace type thing so it’s easy for others to find – again as a CSV and ideally with a Tabular Data Package definition file, then allow embedding of a simple interaction of the data on a public website for no cost. So PowerBI license to produce, no license to see \ basic interaction of data (I guess csv file, json definition file, and d3.js) – then a PowerBI license to bring the data into own PowerBI to join with own data.

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. This is especially a problem when you have a form that has drop-down options (luckily stored in a different table more efficiently) which generates an amount which the end user can overtype in order to moderate it up or down, which has zero validation.

The data is “stored” as numeric in the database so, for example, 1200.34, but is formatted as currency – so £1,200.34. The problem occurs when the user overtypes the amount, when they do, they overtype it, say as, 1201.34, but they don’t enter 1201.34. They enter £1201.34. Now this is a problem as when I load the data into the Data Mart, I store the data as a numeric(18,2), which means I need to cast it. This will of course fail if the user has overtyped it as it isn’t a numeric – which has historically happened. The way I resolved it was to strip out the £ sign using a replace then to add a ISNUMERIC statement as a fail safe.

However despite my failsafe it failed today – the problem was with ISNUMERIC – if you read the man, it says “ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($)”. What it doesn’t tell you is it also covers commas – so:
select cast(replace('£1,220', '£', '') as int)
will fail with
Conversion failed when converting the varchar value '1,220' to data type int.
this is despite ISNUMERIC returning 1 (ie valid)
select ISNUMERIC('1,220')

The fix is to replace (well remove) commas as well as the pound sign (£). Going forward, in SQL2012 Microsoft has introduced Try_Cast which might be another option.

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.

The summary of the requirement change was this, currently we export files to:

\Client Name\Month Number-Month Name\

so, for example, for the April export for Client A it would be:

\Client A\04-April\

What they wanted to change it was:

\Client Name\Fiscal Month Number-Month Name\

so, for example, for the April export for Client A would become:

\Client A\01-April\

Again, as this was a script task, it was just a question of adding a new method and adding it in. For reference, here is the method I quickly bosh together:

Thanks to some clever upfront work – with both the script task and OctopusDeploy, this change took 5 mins.

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. I find Cambridge a nice place to visit, not just because it doesn’t involve me going on the underground.



Some how Mark has managed to put together an entire day SQL Saturday event with two precon days – I swear this guy has gotten hold of Mary Poppins bag. Don’t let the precon name fool you, these are (well were) two full days of very good training. After all, its not every day you get a full-days training with a Microsoft Most Valuable Professional (MVP) or the Data Warehouse Architect at UNOPS, the implementation arm of the UN!

One of the downsides of attending was having to get up a 5 am :(

Note to self: Next time, stay in a hotel – yes Mark, I should have read your deals on hotels (and looked at the back of the guide book for the map)

Day 1


My first day was with Penny Coventry – Automating business processes with SharePoint, Office 365 and Azure. This was a really good session where I got to see some of the power of SharePoint and cool new Azure features from the power users perspective rather then the overly technical developer perspective. I’ve often found users will shrug off ideas and solutions when explained by a “techy” as being too “technical” – and to be fair to them, we do get a big excited and go over the config line and into the development world all too often.

The out-of-the-box just config, no development features in SharePoint like Workflows are amazing. From a “techy” point of view, I’m amazed at how easy it is to do something that would be quite complex to create as a bespoke app. It was also good to see how Microsoft are expanding into the cloud, both with Office 365 and Azure. I’ve previously only really looked into Azure from the “techy” side, like Troy Hunt’s “Have I been pwned?” site but I hadn’t had a chance to have a look at things like Logic App. Logic App which looks like its another workflow type services where you can drag and drop connections (kinda like SSIS) and do things like pulling tweets from Twitter into a SharePoint list, or a MS-SQL database or whatever. Again, this is the sort of thing power users can do without IT or a developers. For me this is great news, creating custom connectors in SSIS is a pain, if the power users can get data into a MS-SQL database without me developing anything, great. Makes my like a lot simpler. Combine this with some of the SharePoint features, like Access Web Apps, I can give users access to the static data so they can keep it up-to-date without the manual process without fear of them wrecking it thanks to some (hopefully) clever business logic.

One last comment about the session, Penny completed the entire thing, including the live demos, using her Office 365\Azure subscription. I was amazed at this as normally I find Office 365\Azure professionals always have a “but” when it comes to them being fully cloud, they always spin up a virtual machine or such. Despite being fully cloud, she knew all the little “this isn’t on the on-prem version – yet” or “you can’t do that in the cloud” or “if you don’t see that option on on-prem you need to..”, as well as latest low down on SharePoint 2016.

Other session going on was A Day with Columnstore Indexes with Niko Neugebauer. To be honest, I wimped out of going to this one – I tend to leave the intimate details of indexes to our Senior DBA and just pick up the summary sheet at end and apply the base config and only update it when he says so. Lazy I know, but I can’t learn everything at once!

Day 2

The second day was with Rasmus Reinholdt Nielsen – Building tomorrows ETL architecture with BIML and MDS today and was the main reason for me attending, I spend most of my time in SSIS. Despite packing two laptops, I still found myself popping out to the shops to pick up a USB mouse as I forgot to pack one. I’ve previously heard about BIML and despite being recommended from a lot of folks I just hadn’t had a proper look, the main reason was the resentment towards Microsoft for not providing such a intermediate language – not that Scott Currie hasn’t done amazing job, its just it should be merged into core product. Rasmus started with a introduction to C# which covered the basics, which he quickly skipped through as we all ready had a working knowledge of C# and got onto BIML. Before too long I had created my first basic BIML script. He then went onto some of the more complex features of SSIS and then how to achieve the same in a BIML script. We then moved onto how we use some BIML features and those C# skills to auto generate a SSIS package, then using Master Data Services (MDS) as your meta library to, In short, automated staging.

Mean while in the other session, they were not only failing over servers…

Day 3

The actual SQLSaturday event was hosted at the very nice The Møller Centre, unfortunately this isn’t as close to the train station as the Cambridge City Hotel the precons were hosted at – still it does have free parking.

The day was amazing, the people were are friendly – I managed to go up to a few and ask a few questions which they kindly answered. They even had people handing out free chocolates :)

Being a free event, you expect the sponsors to be spamming you with marketing material – it just didn’t happen, they were all playing it cool and waiting for you to come to them. I did pop over to the Dell Spotlight stand who gave me a demo of some of the cool new features in Spotlight – something we already own but hadn’t had a chance to upgrade – needless to say I’ve been poking our DBAs to upgrade since I got back in the office. I also stopped by the Profisee stand, after Rasmus training I was starting to look more into Master Data Services (MDS) and the word on the street was Profisee was the MDS experts. Even after registering for their free online training, I haven’t had a single sales call.

The sessions were good – there was a nice range this was partly because they had joined up with the SharePoint group. For me personally this was very good as BI sits in the middle. The biggest problem was which one to pick! I managed to pick up a few tips from Chris Webb with my data modelling and MDX which was one of my big ones on my to-do list. I enjoyed Terry McCann session on Reporting on Reporting services, its one of those problem we all have – we have these reports, but are they actually being used? And he explained his journey on how he’s tried to answered that and some of the problems he came across. Its good that he’s shared his experience, its not a particular sexy problem to solve, in fact its a very unsexy, unrewarding, boring chore that all report writers must do and at least now I have a good head start.

Overall I think it was a very worthwhile event. I learnt a lot, my work got some very cheap training, I met a lot of very clever people and had fun doing it all. Can’t wait till the next one :)

You can see Rodney Kidd photos on Flickr







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 😉

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 Serra had 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? When it’s a SSIS Toolbox.