ThoughtFactory.CreateThought()

One of the features of Access Services, SharePoint 2010’s new “host Access databases on the web” service, involves the promotion of Access reports to SQL Server Reporting Services (SSRS) reports. Essentially the publishing feature of Access Services takes an Access report and generates an RDL file from it, allowing the SSRS Web control to display it inside SharePoint.

This feature requires that you have an SharePoint Integrated mode instance of Reporting Services and have configured Reporting Services Integration in SharePoint Central Administration. Assuming that you’ve done all this, you may expect that your reports just show up. Unfortunately this is probably not going to be the case, and you may find yourself being faced with an error similar to the following:

An attempt has been made to use a data extension ‘ADS’ that is either not registered for this report server or is not supported in this edition of Reporting Services

The following except from a TechNet article gives the solution:

Modify the C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config file on the Reporting Services server. Remove the comments from the ADS data extension. For example:

<!-- <Extension Name="ADS" Type="Microsoft.Office.Access.Reports.DataProcessing.AdsConnection, Microsoft.Office.Access.Server.DataServer, Version=14.0.0.0, Culture=Neutral, PublicKeyToken=71e9bce111e9429c"/> -->TO

<Extension Name="ADS" Type="Microsoft.Office.Access.Reports.DataProcessing.AdsConnection, Microsoft.Office.Access.Server.DataServer, Version=14.0.0.0, Culture=Neutral, PublicKeyToken=71e9bce111e9429c"/> -->

This allows Reporting Services to construct a data set out of a particular data source. You can find more information on Reporting Services Extensions here, and more specifically Data Processing Extensions here.

The interesting thing about all this is that Access Services actually promotes all of your Access tables to SharePoint lists, making me wonder why the generated SSRS reports need to know about Access at all. MIcrosoft.ReportingServices.DataExtensions.SharePointList.SPListConnection already provides an implementation that talks to SharePoint lists, so why not use that? I figured that I’d do a little digging into the SharePoint site generated by Access Services to find out just why they needed their own Data Extension.

If you navigate to the _layouts/viewlsts.aspx page (type it into your address bar directly, so http://mysite/Applications/MyApplication/_layouts/viewlsts.aspx) of your Access Services application you should be able to explore the Report library and associated lists that make up your application. Inside the Report library you’ll notice that there is an "”AccessDataSource”, who’s connection string contains both the SiteId and WebId of your application, and whose Data Source Type is “ADS”. This tells us that, at the very least, the report knows that it’s communicating with a particular SharePoint Site.

If you download a copy of one of the report RDL files and check it out in a text or XML editor you’ll notice something interesting; inside the DataSet element there is a Query definition that looks something like the following:

<Query>
<DataSourceName>ADS</DataSourceName>
<CommandText>_sq_rContactList</CommandText>
<QueryParameters>
<QueryParameter Name="AccSrv_Where">
<Value>=Parameters!AccSrv_Where.Value</Value>
</QueryParameter>
</QueryParameters>
</Query>

The important thing to note here is the CommandText element. Access Services stores most of your Access objects, including queries, as serialised text inside list items in a list called MSysASO. If you inspect that list you’ll find a list item matching up with the CommandText from your DataSet Query inside your RDL file.

If you look at the ServerObject field of this list item you’ll see something that initially looks somewhat like a CAML query but is actually a query from the Access Services namespace, http://schemas.microsoft.com/office/accessservices/2009/04/application.

Here’s an example of one of these queries:

<Query xmlns="http://schemas.microsoft.com/office/accessservices/2009/04/application" Name="_sq_rContactList"><References> <Reference Source="_sq_rContactList_Base" /></References><Results><Property Source="_sq_rContactList_Base" Name="Address" /><Property Source="_sq_rContactList_Base" Name="BusinessPhone" /><Property Source="_sq_rContactList_Base" Name="City" /><Property Source="_sq_rContactList_Base" Name="Company" /><Property Source="_sq_rContactList_Base" Name="ContactName" /><Property Source="_sq_rContactList_Base" Name="CountryRegion" /><Property Source="_sq_rContactList_Base" Name="EmailAddress" /><Property Source="_sq_rContactList_Base" Name="FaxNumber" /><Property Source="_sq_rContactList_Base" Name="HomePhone" /><Property Source="_sq_rContactList_Base" Name="JobTitle" /><Property Source="_sq_rContactList_Base" Name="LastName" /><Property Source="_sq_rContactList_Base" Name="MobilePhone" /><Property Source="_sq_rContactList_Base" Name="StateProvince" /><Property Source="_sq_rContactList_Base" Name="ZIPPostal" /></Results></Query>

At first glance this doesn’t tell us a whole lot; it just seems to be a collection of field names that we would want to fetch in our query, but it doesn’t say anything about where the data is coming from or whether there are any restrictions on what data we should bring back. You will notice, however, that it seems to reference another item in the MSysASO list through the Source attribute of the Property element. In this it is referencing the _sq_rContactList_Base list item, which looks like the following:

<Query xmlns="http://schemas.microsoft.com/office/accessservices/2009/04/application"> <References> <Reference Source="Contacts"/></References><Results><Property Source="Contacts" Name="Company"/><Property Source="Contacts" Name="ID"/><Property Source="Contacts" Name="LastName"/><Property Source="Contacts" Name="FirstName"/><Property Source="Contacts" Name="EmailAddress"/><Property Source="Contacts" Name="JobTitle"/><Property Source="Contacts" Name="BusinessPhone"/><Property Source="Contacts" Name="HomePhone"/><Property Source="Contacts" Name="MobilePhone"/><Property Source="Contacts" Name="FaxNumber"/><Property Source="Contacts" Name="Address"/><Property Source="Contacts" Name="City"/><Property Source="Contacts" Name="StateProvince"/><Property Source="Contacts" Name="ZIPPostal"/><Property Source="Contacts" Name="CountryRegion"/><Property Source="Contacts" Name="WebPage"/><Property Source="Contacts" Name="Notes"/><Property Source="Contacts" Name="_OldID"/><Property Source="Contacts" Name="ContactName"/><Property Source="Contacts" Name="FileAs"/><Property Source="Contacts" Name="Attachments"/><Property Source="Contacts" Name="SharePointEditor"/><Property Source="Contacts" Name="SharePointAuthor"/><Property Source="Contacts" Name="SharePointModifiedDate"/><Property Source="Contacts" Name="SharePointCreatedDate"/></Results><Restriction>=(((Contacts.Company)&lt;&gt;"Local Bank"))</Restriction></Query>

This second query contains the meat and potatoes of the query itself, including any query restrictions (which you can see in my example where I have restricted the data where Contacts.Company <> “Local Bank”) and the source tables and fields where the data is coming from.

As such, each query is actually made of of two serialised objects stored in two separate list items inside the MSysASO list;  _sq_r%ReportName% and _sq_r%ReportName%_Base. With these two objects, when combined with the ADS Data Extension and the SiteId/WebId referenced in the Reporting Services Data Source object previously discussed, Reporting Services now has enough information to fetch the appropriate data for the report.

So it is reasonable to infer from all this that the reason Microsoft don’t use the MIcrosoft.ReportingServices.DataExtensions.SharePointList.SPListConnection Data Extension is because they needed to be able to perform lookups into their shared resources list (MSysASO) to fetch the real query that they want to run, hence needing a custom Data Extension to do so. The use of this central repository makes total sense, especially when combined with Access’ shared query model where multiple reports could be referencing the same query. If they did not do this and simply relied upon the SharePoint List Data Extension then they would essentially have to generate CAML queries for each report, repeating them inside each RDL file as needed.

In the past I’ve been a fairly big advocate of the Microsoft Report Viewer control for accessing SQL Server Reporting Services reports from within a web application, simply because it allows authentication to be proxied through the host application (so you can use a service account to talk to reporting services and control security through your application rather than adding every user in your application to reporting services), but the control requires a traditional ASP.Net webforms Form and page life cycle, so it’s out of the picture for ASP.Net MVC development.

To solve the problem I turned to Reporting Services’ URL Access functionality that allows direct linking to reports while still giving you control over parameters, the toolbars and other functionality through the use of variables passed in via the query string. I used iframes to get the inline reports onto the page, and just linked directly to the reports that were meant to open in their own window.

This was great until I noticed that the data on the report was not being refreshed at a regular interval, leaving users with stale data on screen. A quick check of the report in Report Manager confirmed that the data was in the system and the report updated correctly when shown through Reporting Services’ own interface.

A quick inspection of the execution parameters of my report (located under properties->execution for each report in Report Manager) showed that I was not using the in built Reporting Services caching, so I figured I was dealing with some sort of HTTP caching. This lead me to try one of the oldest tricks in the “how to fool the HTTP cache duration” book, random junk parameters. The theory behind this is that adding a random parameter that is not used by an application to the end of a query string will tell the web server that this is a request that it hasn’t seen before, and hence it will need to figure out the actual result rather than looking in the cache. I postfixed the URL with something along the lines of “&RefreshParameter=” + System.DateTime.Now.Ticks.ToString(), and hoped for the best.

Unfortunately it did nothing. Realising that the problem had nothing to do with HTTP caching I thought that I’d try and apply the same trick to an actual report parameter to see if reporting services could be fooled in a similar manner. The URL Access feature allows you to specify reporting services parameters on the query string in the exact same format as the “RefreshParameter” I had already created, so I simply had to make a new parameter on each of my reports called “RefreshParameter” that accepts blank/null values and does nothing. This worked a treat, enabling me to have current data displayed in iframes on my MVC site.

Update: Peter Van Ooijen suggests that using the rc:ClearSession=true parameter may work in some scenarios (but apparently not all). Some people seem to have had good mileage by combining both the session clearing and the random variables.

I, like many others, have never thought of Linux as a “pretty” operating system, instead choosing to think of it as the home of “best effort” programmer-designed GUIs and “command line first” design.

This line of thought has set my expectations for my desktop Linux environments at home. When I switched to running Linux as my main Desktop machine 18 months ago I noticed that the fonts were nowhere near as nice as my Mac and Windows machines. Blindly assuming that Ubuntu didn’t support ClearType, I simply went about my business and put up with the horrible fonts.

That was up until today, when I finally decided to investigate. It turns out that Ubuntu fully supports ClearType (although they call it “subpixel smoothing”), it’s just turned off by default. There’s even a handy GUI for turning it on. I’m never going to assume that my OS has to look like a dog’s breakfast ever again.

Today I was helping out one of my colleagues with a particularly annoying InfoPath problem; he wanted to enable the multi-line option for a text box bound to a web service, but whenever he did the read-only option would automatically trigger. Truly frustrating.

Not being one to believe that things are impossible, I decided to take a crack at it at home, and as soon as the kids were in bed I got stuck into Everyone’s Favorite Search Engine (TM). Unfortunately, Google was not my friend on this problem; I found all sorts of information about binding Rich Text Boxes to Web Services and forcing text boxes to re-size when using Forms Server, but nothing related to editable mutli-line text boxes.

I decided that the best idea was to try and replicate the problem, so I fired up my trusty Win2k8 VM, made a quick ASMX web service and hooked it up to an InfoPath form. Sure enough, as soon as I tried to enable the Multi-line option of a text box it became read only. Reassuringly, it worked when using an unbound text box, which at least let me know that displaying editable mutli-line text boxes in InfoPath is possible.

The fact that the problem was only occurring with web service data-sources made me suspect that the schema being returned by the web service may be the key, but upon inspection the schema InfoPath created for my web service connection was using exactly the same types as the schema for my default form data-source. The elements were not read only, and I couldn’t see anything else out of the ordinary in the schema. So much for that idea.

Then it clicked. The problem had been staring me in the face the whole time in the guise of a huge red circle with a white “x” through the middle; the form was configured for InfoPath Form Services compatibility, which disables certain options. This MUST have something to do with the problem. I quickly turned off Form Services compatibility then turn on the Multi-line option again, and….

… the field became read only. Doh! But wait! There were two sub-options under Mutli-line: Paragraph breaks and Wrap text. In Form Services compatibility mode I could not edit these options, and selecting Mutli-line highlighted both, which in turn highlighted the read-only option, but with compatibility mode turned off I was now free to edit these values.

Then it hit me. I noticed that, by default, I couldn’t select the Paragraph breaks option unless the read-only option was checked. That was it! For some reason, InfoPath can’t handle having paragraph breaks in a text box when it’s bound to a web service

So instead of clicking on Multi-line I clicked on Wrap text. This checked the Mutli-line option for me, and at the very least let me have a text box that could wrap text onto a new line. I could live with not being able to have paragraph breaks, but unfortunately I could only select this mode in InfoPath Form Services compatibility mode, meaning that I wouldn’t get all the fancy error checking to make sure that it would work when viewed over the web. When in compatibility mode, clicking on Multi-line would still check the Paragraph breaks option which would then force the control to be read only. I couldn’t win.

Or could I? On a whim I turned off compatibility mode again and set up my control to be Mutli-line/Wrap text as described above, then I turned compatibility mode back on. Success! The options stayed as they were, giving me a mutli-line text box that wasn’t read-only. I would just have to make the necessary multi-line related changes to my fields with compatibility mode turned off, and only turne it on for the specific occasions where I would need it.

Not a great solution, sure, but at least it works, and that’s certainly better than nothing. I’m honestly still amazed that I couldn’t find any info about this on Google, though. I would have thought that using a bound multi-line text box would be a more common use case.

As I’ve previously mentioned, as of October 1st 2008 MSDN subscribers who have previously only had access to Visual Studio Team System Development Edition can now also use Database Edition. The reasoning behind this move has now been made clear thanks to the January MSDN Subscription Newsletter:

In the next release of Visual Studio Team System we will be merging the feature sets of the Development Edition and the Database Edition into a single product. The new product will include all of the features in the Development Edition and the Database Edition as well as new capabilities delivering even more value in a single product. This will provide a more complete set of capabilities for building software in today’s data-driven environments. Bringing these two feature sets together enables you to take advantage of the core tools for application development as well as the necessary tools for database development, including performance profiling, code analysis, code metrics, code coverage, database refactoring, Schema Compare, Data Compare, and more

Looks like they’ve finally got the message that a large proportion of developers still need to touch the database, and as a result will be combining the Developer and Database editions of VSTS in the next version. Now if only they could get rid of the comparatively useless Architect edition. Roll on VS 2010!

We’ve got a stock standard Windows 2008 virtual hard drive image at work that I wanted to make some additions to and generally polish up into a decent developer image. The install media was the “Windows Server 2008 Datacenter, Enterprise and Standard (x86) - DVD (English)” ISO sourced from MSDN.

I noticed that the image had not yet been activated, so I set out to rectify the situation. Hopping into the system settings in control panel, I navigated down the the section on product key information and clicked on the link to activate windows. Unfortunately this process errored out with the following message:

Code: 0×8007232B

Description: DNS name does not exist.

I immediately checked network connectivity. Can I get to google? Yes. Can I resolve MS addresses? Yes. Hmm.

Then I happened to notice some text in the bottom right hand corner of my desktop:

Windows Server(R) 2008 Standard

Evaluation copy. Build 6002

Evaluation copy? That means that my product key is not going to be valid. So I grabbed a new product key from MSDN, entered that in and tried the activation process again. Success!

After all that I still can’t figure out what a DNS error has to do with a product key being invalid. I would have been more than happy with something along the lines of “Unable to activate due to invalid product key”. They may as well have told me that activation failed due to insufficient vegemite.

Ferreting around in a spare room/cupboard/garage/other junk storage area can sometimes yield a wonderful surprise. I recently re-discovered an old, broken guitar sitting in our spare bathroom (which has been totally stripped awaiting renovation for.. oh, about 2 years now, and is now serving as an extra large cupboard. But that’s another story); an Epiphone Les Paul Special II that I picked up for AUD200 back in 1999. It was the first electric guitar I ever played on stage, and even though it’s a cheap piece of junk it will always hold a special place in my heart.

Upon inspection it was clear that time had not been kind to the guitar. I had originally put it into storage after the nut broke next to the 6th string, effectively making the guitar useless in it’s current state.

Numerous other problems presented themselves, leading to a rather impressive list of ailments:

• Machine heads that were falling apart and would not keep the strings in tune.
• Broken nut, limiting the guitar to 5 strings
• Crackling tone and volume pots
• A loose input socket
• Weak sound from the pickups
• A noisy pickup switch
• Annoying hum indicating that the guitar was not properly grounded

Given that list and my eagerness to give a task a go as long as it seems interesting enough, I decided to attempt some modifications myself.

First, let me be very clear about something: I know absolutely nothing about guitar modifications, woodworking or electrics. If you’re after a guide on how to do any of this then check out the various articles I reference throughout the post. I did this because I was bored and wanted to find out how to do this sort of thing. The guitar I tried it on was broken anyway and I was fine with the prospect of breaking it some more. Ultimately I’d like to get good enough to make modifications to some of my more expensive instruments, but until that time I’ll settle for practising on guitars that are next to worthless.

If this works out well then I’ve got another two guitars that need physical repairs and another 3 that could do with shielding as well as having the grounding looked at. The cheap Yamaha bass I picked up a few years back suffered from a horrible hum when I’m not touching the strings, which indicates that it’s not properly grounded, so I’ll have to take a look at that, too.

The first thing I had to do was pick replacement parts. The machine heads were easy; I ordered a set of non-locking Gotohs. I’ve never tried locking machine heads, so I figured that I’d stick with what I know. The guitar has a fixed bridge, so it simply needs a decent set of machine heads to keep it in tune.

The nut was a different story. Normally I’d get someone to cut a new bone nut from a blank, but there was no way I had the time or patience to do that this time around. I also couldn’t drop the guitar off at a shop for any period of time, as I’d be putting all the other hardware on it myself so I’d need to have access to it.

I could have bought myself a blank bone nut and shaped it myself, but it looks like a long, complicated process that involves specialist tools, a well thought out plan and actual skill; none of which I have. Fortunately I was able to buy a basic graphite nut in the same profile as the original, broken nut. As long as it is the correct height you can usually get away with attaching the nut without modification. At most I’d be up for a little filing to lower the height slightly.

But what about the electrics? How do I get rid of that hum? How do I rewire the guitar? To the intarwebs!

There’s an excellent article on properly grounding and wiring guitars over at Guitar Nuts that caught my eye. The basic idea is to insulate, create a single path to ground and create a makeshift Faraday Cage. To accomplish this I would need some copper backed tape and a lot of guess work. Unfortunately the article is geared towards a Fender Strat style guitar, so there were still a few problems that I would have to solve.

Darrell Young over at Digital Darrell has an excellent article on replacing the pickups in the exact same model of guitar that I’m working on. When I do the actual modification I’m sure that I’ll be be referring to this with some frequency.

Unlike Darrell there’s no way I’m going to spring for authentic Gibson pickups given that this is the first time I’ve ever tried anything like this. Instead I’m looking at getting a pair of cheap Gotoh humbuckers. Gotoh themselves are better known for their hardware (floating bridges, machine heads etc. In fact, the machine heads I put on the guitar are Gotoh), but they make a range of cheap, relatively high quality pickups that are made in Japan and can be picked up for around AUD40-AUD50. I managed to find a wiring guide for Gotoh pickups online, so I can do some pre-planning to figure out what my circuit is actually going to look like before the pickups arrive.

In order to fix the noisy volume and tone pots I would need to replace them. A pot is basically a variable resistor that can be used to alter the signal coming from your pickups. There are two main levels of resistence used in guitars; 250k and 500k. You also occasionally see 1Meg pots. A high resistance pot will retain more of the high frequencies in the signal.

As a general rule of thumb, the 250k models are normally used with single coil pickups to avoid an overly shrill sound. Since I’ll be putting humbuckers into the guitar I’ll be going with 500k replacements.

Quite a few of the parts have arrived, and over the next few weeks I’ll slowly but surely be progressing with the modification. I’ll be taking plenty of photos, and will be uploading themto a set on my Flickr stream as I go. Next thing on my agenda: remember enough about electronics from high school to avoid electrocuting myself.

Generic error messages are high on my list of pet peeves. Being told that “an error occurred” may be something that you can pass off in a line of business application, especially if the actual error message gets emailed to the help desk or something similar, but it is totally unacceptable in a product targeted towards software developers.

Tonight, when trying to add some managed code to an InfoPath 2007 solution, I ran into one of these horrible errors. InfoPath was presenting me with a highly helpful “InfoPath cannot open the selected form” error message whenever I tried to debugging the solution.

After spending a few minutes scratching my head I remembered that InfoPath forms need to run in Full Trust mode in order to use managed code. I navigated to “Tools>Form Options>Security and Trust” and had a look at the settings, which were still set to the default “Automatically determine security level”. Unfortunately I assumed that, when using this setting, InfoPath would be able to figure out that I was using managed code and select the appropriate security level. Obviously my assumption was incorrect.

After setting the level of trust to “Full Trust” I was finally able to launch the form, complete with managed code.

If I had not previously read about security and trust within InfoPath then I would have had to waste more time floundering on Google with only a generic error message to help me. All that could have been avoided if they had just presented me with a dialog saying “The form has an insufficient trust level to perform this operation”. Well, either that or make the “Automatically determine security level” trust setting actually determine an appropriate trust level in the first place.

The idea of trust within a software development team is an interesting one. Jeff Atwood argues that a lack of trust between management and the development team can lead to “micromanagement zombies” who mindlessly follow anything management tells them without contributing creatively. I believe that the issue is much more complex than this, as demonstrated by the following comment from the post:

OK, what do you do if:

• Your team regularly commits errors to make the editors of Daily WTF blush?
• They’ve been doing it that way their entire career and aren’t very interested in changing?
• They’re above average coders in the SE Asian nation you live in?

At least you can shoot zombies.

What we have here are two totally different approaches to software development: Fast Food and Gourmet.

The Fast Food approach delivers a bland product that is frequently prepared incorrectly, but it does it fast, cheap and using unskilled resources. The Gourmet approach delivers a higher quality product, but may take longer, will certainly cost more and requires a more experienced resource to prepare.

Successful fast food chains invest millions in making meal preparation as idiot proof as possible. Flashing lights and screeching sirens tell their employees are told when to wash their hands, when to flip the burgers and when to change the oil in the fries. They are micro-managed to the hilt simply because management do not trust their employees and expect them to fail if left to their own devices.

In contrast, a gourmet establishment has higher expectations of their staff. At the very least, they expect them to know how to cook as opposed to how to follow a checklist of specific orders. Most restaurants have to trust their staff to be able to perform their jobs to a certain level without specific instructions.

This parallel exists in many industries; for example, a master luthier would be trusted by his or her management more than the employee working the line in a factory that produced cheap, mass production guitars. Each has a place: some people want to spend $10,000 on a quality custom guitar, where as others would prefer to spend $150 on a cheap hunk of plywood that sort of looks like a guitar. To each their own.

The software development industry is no different. As much as some people would hate to admit it, there is a market for horribly written software that is impossible to maintain but was developed for minimum wage. It may be cheaper to produce a quality product using quality resources and best-of-breed practices, but that is irrelevant when the customer simply does not care about, or cannot afford, quality. These are the same customers who consider Access a viable solution, and who would have never been able to afford custom software before the advent of VB6.

To service these clients it may be necessary to use inexperienced or low-skilled workers and use management practices similar to those found in fast food restaurants. If you know that your resources are incapable of developing a product without guidance then all you can do is hope that they follow instructions and review, review, review.

I’m fortunate enough to work in an environment where high quality resources are the norm, but I have also consulted in the small business market and seen first hand just how common Fast Food Development is out there in the “real world”. Regardless of developer idealism, cheap, dodgy software development is the oil that keeps the wheels of business running. As a quality developer you may not want to work in environments such as this, but that is no reason to stick your head in the sand and pretend that they do not exist or are not an important part of the software development landscape.

I’ve never been a fan of “bling”. I don’t wear watches, necklaces, rings, earrings or anything of that sort. If my shirt requires cuff links then I’ll usually wear the standard “monkeys fist” style silk knot cuff links that come with some French cuff shirts.

Saying all that, I love these cufflinks. They were a birthday present from my better half, and are sufficiently geeky without being overly garish.

They aren’t exactly “hey, look at me, I’m totally unique and quirky!” items, unlike, for example, the 8-bit tie, which I could never wear to work with a straight face. Perhaps if it came in a colour that wasn’t bright blue or red?

Update: I’ve had a few people ask me where I got these from. I’ve been informed that they came from http://www.benjamincufflinks.com.au/.