SSRS 2016 by default

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.

Comments

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

select

TableSchema = schema_name(schema_id)

,TableName = name

/*
You could also add

other tables

 

*/
from

sys.tables

 

Power Query Formula (M) Language

Double dash (–) No
Single hash (#) Yes
Double forward-slash (//) No
Forward-slash and asterisk (/*) Yes

Transact-SQL (TSQL)

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

 

Fun fact, my most commonly used comment?

To-Do

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.

Finally, I wanted to check the service accounts that my SQL services were running as, again, PowerShell to the rescue.

The term ‘Invoke-WebRequest’ is not recognized

I’ve currently been working on a PowerShell script to build a virtual server using DigitalOcean excellent RESTful API. So far this has gone extremely well and I have been impressed with both PowerShell and DigitalOcean’s API. Today however I tried running on another machine (an important part of development) only to find it doesn’t work. The first error that appears is:

The term ‘Invoke-WebRequest’ is not recognized as the name of a cmdlet, function, script file, or operable progr
am. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:1 char:18
+ Invoke-WebRequest <<<<
+ CategoryInfo          : ObjectNotFound: (Invoke-WebRequest:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException

In simple terms it’s saying it can’t find the ‘Invoke-WebRequest’ cmdlet. This should be a built-in cmdlet in PowerShell, so off I went to TechNet to find the details of the cmdlet – 

http://technet.microsoft.com/en-us/library/hh849901.aspx

The key bit of information was this line:

This cmdlet was introduced in Windows PowerShell 3.0.

The other way is at the top of such documents on TechNet it defaults to the most recent version of the, in this case, cmdlet and next to it gives you the option select a older version, clicking it reveals previous versions and does indeed confirm the above statement that it was not present in any version of PowerShell prior to 3.0.

TechNet
TechNet

Next question, is PowerShell 4.0 installed? Well for Windows 8.1 and Windows Server 2012 R2 it’s built-in, you can check by running (in PowerShell)

$PSVersionTable.PSVersion

sure enough, the other machine (which is Windows 7 Enterprise x64) returns

Major  Minor  Build  Revision
—–  —–  —–  ——–
2      0      -1     -1

For help installing PowerShell 4.0 go to: http://social.technet.microsoft.com/wiki/contents/articles/21016.how-to-install-windows-powershell-4-0.aspx