Ivan Krivyakov's Blog

If they tell you the machines will outsmart us very soon, don’t believe it. Here’s why.

Tonight I was trying to add “Unity” dependency injection framework to my .NET 4 project under Visual Studio 2010 via NuGet. Yes, I know, stone axes are no longer supported, but whatever.

Here are the list of steps, just for my record, and perhaps for someone’s sadistic geeky amusement, if they are into that kind of stuff. So,

1. Opened Tools->Library Package Manager->Manage NuGet Packages for Solution. Typed “Unity” in the search box. It came out first.

2. Clicked on “Install”. Was told that this version of Unity requires .NET 4.5 which none of my projects supports (duh!).

3. Searched for a button or menu to list older versions – did not find any.

4. Googled how to install older version with NuGet – did not find any encouraging results. But found NuGet PowerShell reference instead.

5. Did Get-Package -ListAvailable -Filter Unity in Powershell. Got 40 results, in alphabetical order, none of them is Unity. Tried -First and -Skip options: no can do, a subset of the same 40 results.

6. Googled it. Found something about a bug in NuGet that is fixed in the new version.

7. Went to update NuGet to version 2.5 via Visual Studio Extension Manager. Installation failed, complaining something about the signature of my current NuGet not matching what they expect. Well, it did not complain per se, it just stated the failure, and gave the link to an installation log. The message with the actual root cause was in the middle of a very long line towards the end of file, around column 200, so some serious horizontal scrolling was required.

8. Went to uninstall current NuGet in the Extension Manager. Install button is disabled.

9. Googled it. Re-ran Visual Studio as administrator. Uninstalled current Nu-Get.

10. Installed NuGet 2.5. First small victory!

11. Did Get-Package -ListAvailable -Filter Unity in Powershell. It returned over a hundred packages, Unity came 96th. But this displays only the latest version, and I wanted the previous one.

12. Tried to find the way to limit the search, so it would return just Unity. No cigar. According to the documentation -Filter searches in “package Id, the description and tags”. Trying to specify “The Unity Application Block” as a filter does not do any good: even more packages are returned. But -Skip 96 - First 1 gives me only Unity. OK so far.

13. Trying Get-Package -ListAvailable -Filter Unity -AllVersions -Skip 96 -First 1. It waits a while (10 seconds or so), and then gives me Cavity.ServiceLocation.Unit... 1.1.0.787. Well, apparently it skips lines, not packages. Argh.

14. Googled it. No cigar. Found a reference to another command line, the nuget.exe tool.

15. Downloaded nuget.exe. It refused to run, requesting that I set some environment variable. Well, I did. It ran.

16. Did nuget list unity. Get a hundred packages again.

17. Did nuget list Unity -allversions and redirected output to a file. It took several minutes to create the file, and it came out with 1525 lines of text.

18. Found that the previous version of Unity is (ta-da) 2.1.505.2. It’s right there, in front of your eyes, proudly sitting on line 1353.

19. Back to Powershell, Install-Package Unity -Version 2.1.505.2.

Victory at last!

Some points to raise with the NuGet team(s):

- a search by Package Id only would be nice; an exact search by Id would be even nicer

- why is it that when I search for Unity in Gallery it comes up first, and not 96th? do they have a different API? if yes, why isn’t it available via Powershell or command line?

- installing previous version of a package should not require a degree in forensic science; having a menu listing all versions would save me 1.5 hours of work and lots of nerve cells

1. Filter() and IIF work funny in the columns hierarchy.
Consider this:

SELECT
FILTER([MarketValue], [MarketValue] >1000) ON COLUMNS,
[Region].[Region].[Region] ON ROWS
FROM [MyCube]

In this case the MarketValue in red actually means the global number for MarketValue, not the per region one. I.e., if the global MarketValue is 1024, then all regions will be returned. If global market value is 999, none of the regions will be returned. IIF works in a similar way, and so does IsEmpty. Thus,

SELECT NON EMPTY [MarketValue]

will return regions where market value is not empty, whereas

SELECT FILTER([MarketValue], NOT IsEmpty([MarketValue]))

will return market value for all regions, empty or not, provided that the global market value is not empty.

To avoid this shenanigans one should filter on ROWS. E.g.

SELECT
[MarketValue] ON COLUMNS,
FILTER( [Region].[Region].[Region], [MarketValue] > 1000) ON ROWS
FROM [MyCube]

2. Performance of FILTER on even medium sized sets is not so good.
Even when the filter is trivial, something like 1=1, the query still takes several times longer than without the filter. If you filter on multiple fields, it makes situation even worse.

If you rewrite the query above like this

WTIH MEMBER [BigMarketValue] AS IIF([MarketValue]>1000, [MarketValue], NULL)
SELECT NON EMPTY [BigMarketValue] ON COLUMNS,
[Region].[Region].[Region] ON ROWS
FROM [MyCube]

you might get much better performance.CodeProject

Deploying Reports

Download code.

In Part 2 we talked about deploying SSRS data sources. Now we are ready reports.

Reports are deployed using rs.CreateReport() method. It accepts array of bytes instead of .rdl file name. This is relatively minor, since reading contents of a file is not that hard. Here’s the code:

Dim len As Integer
Dim fileBytes As Byte()
Using stream As FileStream = File.OpenRead(path)
    len = stream.Length
    fileBytes = New [Byte](len - 1) {}
    stream.Read(fileBytes, 0, len)
End Using

rs.CreateReport("MyReport", "Reports Folder", overwrite, fileBytes, Nothing)

It would be even easier if Microsoft did not mess up the VB example at the above link: they don’t read the last byte of the stream and the result is invalid XML. Interestingly, in the SQL 2000 example they messed up the array initialization instead, so they send an extra byte to the SSRS server instead. The result is the server complaining about illegal 0×00 character in the end of the report.

It seems difficult to comprehend, even for Microsoft people, that in VB.NET the number of elements in your array is different from the number you specify in Dim.

Updating Data Source References

Another hurdle is that report definitions, as stored by BIDS, contain invalid data source references. When you upload a report like outlined above, you will get a warning similar to this:

The dataset `DataSet1' refers to the shared data source `MyDataSource', which is not published on the report server.

HTTP traffic sniffing shows that when BIDS deploys a report, it gets the same warning. The solution is to update the data source references after the report has been uploaded, changing them to existing data sources in the "/Data Sources" folder.

This requires several steps. First, we need to load the report file as XML and locate all data source references.

Dim doc As System.Xml.XmlDocument = New System.Xml.XmlDocument()
doc.Load(rdlPath)

Dim nsManager As XmlNamespaceManager = New XmlNamespaceManager(doc.NameTable)
nsManager.AddNamespace("r", "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition")
Dim nodes As XmlNodeList = doc.SelectNodes("/r:Report/r:DataSources/r:DataSource/r:DataSourceReference", nsManager)

Then we need to convert those nodes to DataSource objects recognized by SSRS:

Dim dataSources As DataSource() = New DataSource(nodes.Count - 1) {}

For i As Integer = 0 To nodes.Count - 1
    dataSources(i) = CreateDataSourceObj(nodes.Item(i))
Next

Private Function CreateDataSourceObj(ByVal refNode As XmlNode) As DataSource

    Dim reference As DataSourceReference = New DataSourceReference
    reference.Reference = "/Data Sources/" + refNode.InnerText

    Dim result As DataSource = New DataSource
    result.Name = CType(refNode.ParentNode, XmlElement).GetAttribute("Name")
    result.Item = reference

    Return result
End Function

And finally, we need to call rs.SetItemDataSources method:

rs.SetItemDataSources(serverReportPath, dataSources)

Complete code for creating a report is here.
Combined file for creating a data source and a report is here.

It was fun, isn’t it? I am not sure why Microsoft did not make it simpler: SSRS reports are typically needed in an enterprise environment, and production deployment is an important part of development for the enterprise. It would also help if the code sample for CreateReport were right: I spent quite some time trying to figure out why I am getting weird exceptions from SSRS before I noticed the size error.

I hope this text will save others hours of boilerplate coding and frustration. CodeProject

In Part 1 we discussed that in development environemnt we deploy reports by simply clicking “Deploy” in BIDS, but this won’t work in production. To deploy reports in production we need to write an SSRS script, which is actually a piece of VB.NET code executing against SSRS web service.

A typical report consists of the report definition (the .rdl file) that references one or more data sources. And this is where things get tricky.

Deploying Data Sources

Code to deploy a data source.
Code to deploy data source and report.

You create a data source using a call to rs.CreateDataSource() method. Unfortunately, this method does not accept .rds files that BIDS uses to store data source information. Instead it wants a DataSourceDefinition object. This means that one must parse the .rds file and convert it to a DataSourceDefinition instance. Here’s a piece of code that does that. It is geared for datasources with integrated credentials, if you use other kind of credentials, you’ll need to make relevant modifications.

Dim doc As System.Xml.XmlDocument = New System.Xml.XmlDocument
doc.Load(rdsPath)

Dim dataSource As DataSourceDefinition = New DataSourceDefinition
dataSource.ConnectString = doc.SelectSingleNode("/RptDataSource/ConnectionProperties/ConnectString/text()").Value
dataSource.Extension = doc.SelectSingleNode("/RptDataSource/ConnectionProperties/Extension/text()").Value
dataSource.CredentialRetrieval = CredentialRetrievalEnum.Integrated
dataSource.Enabled = True
dataSource.EnabledSpecified = True

Dim name As String
name = doc.SelectSingleNode("/RptDataSource/Name/text()").Value

Dim overwrite As Boolean = false

rs.CreateDataSource(name, "/Data Sources", overwrite, dataSource, Nothing)

This was easy, wasn’t it? Full version of the code with logging and error handling can be found here.

But data sources are only one half of the story. In Part 3 we will discuss how to deploy reports.

CodeProject

So, you built your shiny and flashy SSRS report, and you hit “Deploy” in BIDS, or “SQL Server Data Tools” in newer versions. Your UAT is a breeze, and everything looks great. The million dollar question now becomes

How do you deploy your reports to production?

Download code.

In most “enterprise” environments “production” means “no direct access for developers”. Any changes to production must be carefully documented and performed by dedicated people: release managers, system administrators, DBAs, etc. These dedicated people typically do not have BIDS on their desktops, nor do they want to. Thus, simply telling them to open the project, right click and hit “Deploy” won’t work. Whatever BIDS does when you hit “Deploy”, you’ll need to duplicate it in some way that works without BIDS. This is easier said than done, because, as it turns out, “Deploy” does quite a few things under the covers.

Scripting SSRS

But first off, how do you script SSRS in the first place? It comes with the rs.exe utility which on my machine is located at "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\rs.exe" (your location may vary), and is described on this MSDN page.

The “script” file that rs.exe accepts is regular VB.NET code that can call methods on a global rs object, which is actually a proxy to SSRS web service. You can put any valid VB.NET statements in your script, including reading files, opening sockets, and the like.

WSDL definition for the SSRS web service is located at
http://reportServerName/ReportServer/ReportService2005.asmx?wsdl for 2005 version of the protocol, or at
http://reportServerName/ReportServer/ReportService2010.asmx?wsdl for 2010 version.

In fact, all rs.exe does is:

• Download WSDL from reporting server
• Create a proxy class (perhaps using svcutil)
• Read your script
• Surround it with some extra code that defines the rs variable
• Compile and run

These steps are executed on every invocation of a script, and may take quite some time. Another limitation is that you must use VB.NET, C# is not supported. Also, you can’t have any Imports directives in your code, because it gets surrounded by the code generated by rs, and Imports in the middle of a class are not allowed. So, if you use classes like System.Xml.XmlDocument, they must be fully qualified.

If any of the above is an issue, you may want to bypass rs.exe and generate the proxy on your own. Simply create a console application and add a web reference to ReportServiceXXXX.asmx?wsdl. Your “script” will now be an executable file. The downside is that you lose dynamic compilation: quickly modifying the script “on the fly” is no longer an option.

But what exactly do you put in the script? This is going to be the topic of Part 2.CodeProject

feels like I am trying to download a semi-legal DVD-copying software.

It starts simple enough: just go to this page. Shows a list of things to download, it does not get easier than that. But when you click on the first link, it
- shows you another list
- then says you must accept the license agreement
- then says you must create an account
- this gives you a 3rd degree questionnaire with e-mail, company, your title, etc.
- then sends you an e-mail with a link you must click to verify your account
- the verification link (naturally) brings you to Oracle’s login page
- login page brings you to start page
- go back to downloads
- click link
- see another list, accept license agreement
- click on link, get something else (what gives?)
- click “back”, accept license agreement, click link

F-f-f-f-ew… You finally got it, dude!
Ivan, a proud janitor from Screaming Toys, inc., 42 Avenida de Las Juegas, Thimphu, Bhutan

P.S. Oracle actually knows the telephone code for Bhutan and won’t let you enter anything else for the phone number. How thoughtful of them.

I needed to create a script of medium complexity that goes along these lines:

1. Take a list of databases.
2. Each database has an SVN folder, list of files to execute (with possible wildcards), and list of files to skip.
3. For each database, perform “get” on the SVN folder, ignore the excluded files and run files from the “execute” list in the order specified, respecting the wildcards.

What do they use to write such scripts these days? My first attempt was in

NAnt. We already use NAnt for continuous integration and it seems to work reasonably well with files and external programs. The problem I had with NAnt is that it lacks adequate control structures and data types. All properties are strings, and I needed structured data. The only type of for loop is iteration over a delimited string (or files in a directory). There are no parameterized function calls, although they can be simulated (poorly) with setting global properties and calling a target.

Then I briefly thought about writing it in
C#, but rejected the idea, since I wanted the script to be easily modifiable, and the compilation step gets in the way of that. Also, in compiled languages like C# script parameters are traditionally passed either via command line or via config file that must be parsed. My parameters were too big for the command line, and writing a parser was not in my plans. It is possible to do dynamic compilation in C#, but it is quite cumbersome. You don’t want your config file to start with something like using System; class Config { ConfigItem[] Data = ... , do you?

My next thought was
Powershell, but this one is shipped only with the newest version of Windows Server, and we are not there yet. On older versions it requires installation with admin rights. It may be easier to get an audience with the Pope of Rome than to install an app with admin rights on a production box in a huge financial corporation.

I then settled on
Javascript (WSH), and it worked, but I was plagued by various issues. First off, WSH does not seem to have a built-in way to execute an external command that sends output to current console window and wait for the result. You get either asynchronous execution without wait in current window (Exec), or optionally synchronous execution in external window (Run). I ended up writing a piece of code that uses Exec and then polls the process for the exit status.

Also, WSH version of JavaScript lacks support for include files. My script became big enough that I wanted it, primarily to separate config from code. One can use an eval call to read and execute external file, or use WSF files, but both of these options greatly mess up line numbers and make detecting errors virtually impossible.

var fileSystem = new ActiveXObject("Scripting.FileSystemObject");

function include(fileName)
{
    eval(fileSystem.OpenTextFile(fileName, 1).ReadAll());
}

There are, of course, other alternatives: maybe I will try Python next time. But the situation with a scripting solution available on all currently active version of Windows is bleak. Powershell would be a reasonably good answer if not for Microsoft’s love of admin-rights installers. Requiring an admin right installer is a huge demotivator in the corporate world.
CodeProject

Long story short: if you are building an SSRS report in Microsoft BI studio, and if that report has parameters, you cannot customize the automatically generated MDX query.

If you switch to text mode, make a change (any change, e.g. adding a space) and hit “save”, you get this error:

An MDX expression was expected. An empty expression was specified.

The culprit is this subquery added by the Query Designer:
SELECT ... FROM
( SELECT ( STRTOSET(@parameter, CONSTRAINED) ) ON COLUMNS FROM [data source]) WHERE ...

The problem is explained in more detail in this thread on MSDN forum.

The responder there claims that this is not automatically generated code, but in fact it is. Apparently, someone fiddled with the MDX query generator at the last moment. If you remove the subquery and replace it with just [data source], customization works like a charm.

Admittedly, we are using an outdated version of the BI studio (2008), but still, this is not cool. And the error message is not that helpful either.CodeProject

Surprise-surprise!

WPF: The Image class enables you to load the following image types: .bmp, .gif, .ico, .jpg, .png, .wdp, and .tiff.

Silverlight: [Image class] Represents a control that displays an image in the JPEG or PNG file formats.

No error is displayed, it just shows a blank rectangle. I understand that GIF format was once controversial due to patent issues, but the patent has long expired, and most web browsers support GIFs natively. Also, the decompression algorithm is dead simple (I implemented it in college as an exercise), so it is somewhat surprising that Microsoft won’t include GIF support in a web-oriented library like Silverlight, but will include it in a desktop package like WPF. Anyway, this ship has sailed: both are yesterday’s news now, and Microsoft is currently busy with WinRT CodeProject

I wrote a little web page that “debugs” HTML 5 touch interface (requires touch screen device). Unlike regular mouse clicks, there is no “onTouch” attribute, you have to add and remove listeners via addEventListener() method of a DOM element. Event name is passed as string, which is not very safe, but hey, it’s JavaScript, folks! Nothing is save there.

Another quirk is that unlike MouseUp notification in various graphic systems, touchend event does not give you coordinates. In some way this makes sense, because the mouse has only one on-screen cursor, and touch interface must support multiple touches. By the time of touchend the number of current touches is zero, which is reflected in the event.

Unfortunately, this means that even if you are only interested in the start and end point of a touch, you still must monitor the touchmove event.

I tested the app on three different phones and here are the results:

Motorola Droid Razr Maxx – can handle virtually any number of touches. The maximum I was able to reach was 12; I am sure the software can handle even more, but we ran out of screen space. Just in case: I ran out of my own fingers at 10 touches and had to use other people’s help to reach 12

IPhone 4S – bails out after 5 touches (go backs to zero if I remember correctly). Not very cool and kinda sloppy, but enough for the vast majority of practical applications.

HTC Droid Incredible – I could not get it to work with more than one touch, because it ignores “user-scalable=no” meta tag. When you touch screen we second finger, the touch event is not passed to the web page, and the page is zoomed instead.

Oh, yeah, and the relevant piece of JavaScript code is below. I did not use JQuery or any similar framework on purpose, to get to the bare bones API.

function reporter(name) {
    return function (e) {
        e.preventDefault();

        var message =
            name + "<br/>" +
            "touches: " + e.touches.length + "<br/>";

        for (var i = 0; i < e.touches.length; ++i) {
            var touch = e.touches[i];
            message += "screenX: " + touch.screenX + "<br/>";
            message += "screenY: " + touch.screenY + "<br/>";
        }

        var info = document.getElementById("info");
        info.innerHTML = message;
    };
}

function report(name) {
    window.addEventListener(name, reporter(name), true);
}

report("touchstart");
report("touchmove");
report("touchend");

CodeProject