For the past 4 months I’ve been using Visual Studio 2015 rather then Visual Studio 2013 and yesterday, I hit a bug with SSRS (shows how much I love SSRS).
The bug appeared when it came to deployment
The error was deploying SSRS report
Exception calling “CreateCatalogItem” with “7” argument(s): “The definition of this report is not valid or supported by this version of Reporting Services. The report definition may have been created with a later version of Reporting Services, or contain content that is not well-formed or not valid based on Reporting Services schemas. Details: The report definition has an invalid target namespace ‘http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition’ which cannot be upgraded.
The report that error deploying was the one I had just changed. Looking at the code diff in Visual Studio Team Services you can see its added in some new 2016 goodness. I checked and double-checked my settings. Visual Studio 2015 wants to give you a 2016 file. If you manually deploy from within Visual Studio it works. Checking out the bin\debug folder reveals that it’s using the 2016 version to build a legacy version, which makes sense as the 2016 is the old (2008R2) with extra goodness slapped in – like being able to define parameter location.
So, we now have to build our SSRS reports. To do so, I’ve just created a PowerShell script that builds it using devenv.exe with the parameters for a silent build. This worked great, only it took over 50mins to run. The main lag was every time it loaded the solution it builds\checks\something with the database projects. It takes an age. The fix was to create a dedicated Report solution file (Report.sln) – I did this with a PowerShell script that copying the main solution file, which included all the SSDT (database), Integration (SSIS) packages, analysis models as well as the SSRS reports, then just removing all projects but the SSRS projects. I did this via PowerShell just because I don’t like extra manual steps. It’s a pain, when you add a new SSRS project you’d have to add it to Report.sln, which you’d probably forget and waste spend time debugging a silly mistake.
Creating the Report.sln reduce the build time down to just over 2mins (from over 50mins!), so it was worth doing.
I recently had incident logged where the drive that hosted SSISDB database filled up on the UAT environment. We had SSISDB set to only keep 2 copies of the projects, however the additional copies are only removed when the SQL job is run – SSIS Server Maintenance job.
The SQL job is scheduled to run at 00:00 (midnight) every day, which is the default, which runs two steps
SSIS Server Operation Records Maintenance TSQL Script: EXEC [internal].[cleanup_server_retention_window]
SSIS Server Max Version Per Project Maintenance TSQL Script: EXEC [internal].[cleanup_server_project_version]
The problem is we do Continuous Deployment (CD), we use Octopus Deploy to automate our deployment process – it’s so effective we often do multiple releases a day. This in turned caused the log file to swell when a large number of old projects are removed, which results in the drive filling – despite the recovery model being set to simple.
The solution was simple, add an additional log file, located on another drive (I used the backup drive as it has tons of free space), run the job, truncate the log files, then remove the extra log file. Then it was just a question of running the two cleanup stored procedures post deployment.
I was re-entering my password into our NowTV box in the bedroom when it occurred to me. Authentication sucks on the Internet of Things (IoT). The problem is you have a simple device with minimal extras. On the NowTV you have a basic remote that looks like this:
Can you imagine entering a password with a length of over 20 that’s a mixture of numbers, special characters and both upper and lower case characters? Now imagine changing that password. Regularly.
If you have to press the arrows 5 times per character, that’s over 100 presses! That’s insane!
So, what’s the solution? Well I think the technology already exists. And PayPal already had it patented. QR codes. Not sure if PayPal had thought about using it for IoT, I suspect they only thought about using it as a way of paying. So you have a QR code on the door then you scan it via the PayPal app, pay, then get your tickets sent to your Wallet to the club. Or scanning the code from the receipt to pay the bill.
For IoT, the device would generate a encryption key, this would be re-generated when the device is wiped, for example when it is resold, the device would then display a QR code, via a small E-Ink display or such, that would allow pairing (or such) between the device and a user account – via a internet connection to a web service. Unpairing the device from the user account would revoke the encryption key requiring the device to regenerate a new key (and a new QR code). However wiping the device would destroy the encryption key but wouldn’t revoke the key, this would cause some housekeeping to occur. Perhaps trying to unpairing first, however it shouldn’t be dependent on a internet connection to a web service in order to work. If the hard reset button is pressed, it must destroy the encryption key regardless if the unpairing fails or not. It must force this.
It’ll be interesting to see if PayPal expands then authentication business beyond just for payments in the future.
This week I’ve been working on another new cube which had the time spent, which is in minutes. To pretty it up it wanted to be in days, mins and hours.
Here is a simplified version of the final output
and the hopefully easier to understand MDX code
Adding the days option isn’t much more work – it does however make the code look alot more angry. Also you have the issue of what is a day – is it 24 hours? Is it the working hours, or is it less, is it the realistic billable time after you deduce admin time?
Distribution list are amazing things, they allow us to send mail to our team without having to remember who exactly is in our team, this is especial true in this modern age where we are part of many different teams, sometimes without even realising. These lists can contain hundreds or even thousands of members and the latest NHS IT bug has left IT departments maybe wanting to double-check their distribution lists – hopefully before the security team comes a knocking.
NHS 850k reply-all email fail: State health service blames Accenture for config cockup < by me https://t.co/TZ1AOqsdKC
Luckily, this is really easy in Excel 2016. Built into Excel 2016 is PowerQuery and one of the out-of-box connectors is Active Directory. With a bit of PowerQuery magic, you can easily pull this data out.
So we want to:
Connect to our Active Directory
List out all our Groups
Filter to email-able Groups
the group owner
any security \ restrictions on who can email the list
Note: If your unluckily enough to be running an old version of Excel, it possible to do the same thing in Power BI Desktop, which is a free download.
One of the annoying things about working with multiple languages is they each have their differences – obviously, otherwise there would only be 1 language! Although there is good reason to have these differences they still have elements that are a pain. One of the most annoying things, other then “do you require a semicolon at the end of the statement?” is comments. Comments aren’t executed or complied but they help developers read code. Remember, the code is a interpretive language, its designed to be gateway between the machines and humans, if you write something complex your more then likely need to be include more comments explain what it does or look at refactoring so other developers can continue your work.
The few options are:
Double dash (–)
— Single line quote
Single Hash (#)
# Single line quote
Double forward-slash (//)
// string domain = “matt40k.uk”;
All of these are single line quotes, as in each line needs the require comment and the comment continues till the end of the line \ carriage break.
You also got:
Forward-slash and asterisk (/*)
/* Multi-Line quote */
Forward-slash and asterisk marks (/*) the start of the comment, the comment continues until the asterisk marks and Backward-slash (*/). The code must close any open Forward-slash and asterisk marks (/*) comments in order to compile.
With Forward-slash and asterisk, you can comment in-line, for example
Select * from /* Change the code to the right */ sys.tables
This is valid T-SQL that will return the all the columns for meta data about tables on the selected database
You can also use this across multiple lines, for example
TableSchema = schema_name(schema_id)
,TableName = name
/* You could also add
Power Query Formula (M) Language
Double dash (–) No Single hash (#) Yes Double forward-slash (//) No Forward-slash and asterisk (/*) Yes
Double dash (–) Yes Single hash (#) No Double forward-slash (//) No Forward-slash and asterisk (/*) Yes
C sharp (C#)
Double dash (–) No Single hash (#) No Double forward-slash (//) Yes Forward-slash and asterisk (/*) Yes
Cut-down version, I’m not working with SIMS anymore and I can’t pass on my work without them getting a large bill from Capita for using the business objects.
Years and years ago I was working on a SIMS help desk at a Local Authority (LA). I had a school log a call asking about importing email addresses. Like many schools this school had just purchased a messaging service that allows sending emails and texts, the problem was they needed them in SIMS .net, as you can imagine the idea of manually retyping 1,000+ email addresses was rather daunting. So, after putting a call into Capita Partner Team asking for the API documentation I spend the evening building a simple import process. The next day I phoned the school to give them the good news and we imported their email addresses. A few days later a few more schools asked the same question and I decided to continue spending my evenings expanding (it imports email, telephone and User defined fields from XML, CSV and Excel spreadsheets) and refining the tool. I’ve always developed in my own time, I’ve never claimed overtime etc and I’ve never charged a penny for using it.
SIMS Bulk Import uses the SIMS .net business objects (API), its uses the the SIMS .net DLLs. Its the exact same .NET libraries that the SIMS .net library uses.
At the time we were in a partnership with BT, who had a procedure for raising new business ideas. The process involved working out what type of change it was – in this case it was a efficiency saving. To put it simply this means you can’t charge more then what you can save, in this case labour.
When you take into account the Capita charge, the handling of money costs (invoicing, collecting and chasing etc), the helpdesk costs, the fact people expect a certain standard, ie you’re going to have to invest alot more in terms of development – including documentation. It just doesn’t make sense. It’s actually more cost effective to hire a temp to manually key in all those details!! Nuts!!
So at this point I basically decide I’ll give it away. I really didn’t want to see my hard work go to waste. I basically managed to wangle it into the public domain without finding a massive Capita bill land on my desk!! Its been in a wild for many, many years (with Capita knowledge) with a grand total of ZERO corrupt SIMS databases. I find this quite an achievement. Don’t get me wrong, SIMS Bulk Import has failed a number of times, but it’s never left your SIMS system in a worse state (unless you’ve done something stupid like successfully imported the same email address to every pupil in SIMS!)
A few years later I switched teams and stopped working with SIMS .net. SIMS Bulk Import has been stable for a while and I’ve had a few commits from individuals. I’m now at the stage where I’m going to leave the LA and go work somewhere else and its unlikely to have a SIMS .net license let alone API access. I needed to find a new owner for SIMS Bulk Import. Anyone who’s talked to me would have described SIMS Bulk Import as the poor man’s Salamander Active Directory, it is simply put the next logical step if you work out how you’d improve SIMS Bulk Import, its what I would have done to make SIMS Bulk Import into a commercial product. Luckily Richard agreed to take it on and even help me recover some of the costs of SIMS Bulk Import. Before you shoot off to SalamanderSoft to download it, let me save you the disappointment. Capita has said they would charge them a license fee for each school using it, ie it wouldn’t be free. At this point I guess you can see where I’m going with this? SIMS Bulk Import isn’t worth paying for and Richard already has the expanded version (that IS worth paying for).
So in short, your options are:
Except you can’t bulk import anymore and get typing or copy and pasting \ hire a temp
Wait for Capita to come out with their own product – I suspect they will and charge. They do a limited SQL script that injects the records directly into the database, ironically bypassing the business objects (but hey, they support it so its all good right?)
Switch MIS who doesn’t charge for partner access \ gives you bulk import routines (Eduware Network has a list of MIS suppliers)
Option 5 is carry on using it. I’m sure even with me saying, no, don’t do that (and I’m sure Capita will agree) – someone will. So a few comments.
Make sure you have the latest (or should that be last) version – its 2.5.0
It should be digital signed, think of it as SSL for your applications. If you right-click on SIMSBulkImport.exe or the .msi installer you should see an extra tab – Digital Signature and you should see its signed by me – Open Source Developer, Matt40k. If your copy doesn’t have the signature its possible code has been injected and it is unsafe.
You should be OK as it uses whatever version of SIMS API you have installed, so it’ll just break one day and by break I mean it won’t let you login or will just give you all import failed (if it does fail in terms of SIMS database corrupt then some thing terrible has happened with Capita API, but I digress).
A few people have forked my code, for whatever reasons, I would just point out that the most up-to-date fork is 80 commits behind mine. That’s a fair amount of work that missing from those forks.
Anyway, hopefully you’ve found it useful whilst it last.