The WorkerThread Blog

we know stuff so you don't have to

Archive for the ‘Microsoft SharePoint’ Category

SQL Server 2012 – Fixing Up Post-Patch Issues

Posted by workerthread on October 11, 2012

I have an “all in one” virtual machine for demonstrating the Business Intelligence features of SQL Server 2012 in combination with SharePoint 2010.   My VM is based on the “Kiwi Build” setup documented in the TechNet Wiki article here (although mine is using VMWare Workstation instead of Hyper-V).  As well as all varieties of Analysis Services (Tabular, MultiDimensional, PowerPivot) my VM also runs SharePoint, Master Data Services and Data Quality Services.

Since the initial release of SQL Server 2012, there have so far been three Cumulative Updates and this week a very large security update.  I generally check these patches out using my VM and each time I’ve come across specific post-patch issues which need to be addressed.  I thought it would be worth sharing these here, both for my own future reference and hopefully to assist others.   Note that these issues are my experience on my VM – you may encounter very different problems when you apply the patches, most of which are caused by the updated assembly versions that the patch installed – I probably can’t help you with those.

So this week, as usual, I first took a snapshot of the VM, then applied the security update. This installed without problem and I could see following the update that all of the expected SQL Server services were running as was SharePoint Server.   The post-patch fixing up is only needed once you start using the various SQL Server services –

PowerPivot Service in SharePoint

First I went to a PowerPivot Gallery in SharePoint, opened a workbook to display a pivot table with slicers.   When I clicked on a slicer to filter the data, I immediately got a message saying “an error occurred during an attempt to establish a connection to the external data source” –


To fix this and get PowerPivot working again, you need to re-run the PowerPivot Configuration Tool.   When this loads it checks the current farm status and in my case returned the following message telling me “newer versions of the PowerPivot solution files have been detected” –


And as you can see, the “Upgrade” option is already selected –


I did as I was told, ran the “upgrade features” option and once this completed, the error message had gone from SharePoint and I could once again use my published PowerPivot models.

Data Quality Services

Next I opened the Data Quality Services Client and attempted to make a connection to the server.  I got a very verbose error message essentially telling me “an error occurred in the Microsoft .NET Framework while trying to load assembly id 65627” –


A quick online search led me to this document which discusses installing Cumulative Updates or patches on Data Quality Services.   It doesn’t explicitly mention assembly ID 65627 (although other online articles do) but as it says, you do have to run DQSInstaller.exe – upgrade to fix the issue above which did the job for me. 

Master Data Services

Finally (for me anyway) Master Data Services.   When I opened the Master Data Manager web client, the message I got was “the client version is incompatible with the database version” and a request to update components using Master Data Configuration Manager –


So, off I went to Master Data Services Configuration Manager, connected to my MDS database, and got the same advice about upgrading the database –


Which I duly did, and once complete Master Data Services and Manager were back up and running (actually I think this is a little misleading because the original MDS error suggests that the client version is out of step, but the database upgrade fixes it).

So there you have it.   From what I’ve seen so far all or some of the above steps will be needed after patching SQL Server 2012, depending upon which features you have installed.   At the time of writing I haven’t tried applying Service Pack 1 which is currently at CTP level but if anyone has, please leave a comment.

Update December 2012 – I can confirm that after applying SQL Server 2012 Service Pack 1 it was again necessary to complete the steps outlined above fix the issues.   As Cumulative Update 1 for SQL Server Service Pack 1 arrived fairly soon after I also tested this and found that SP1 CU1 didn’t cause anything to break.

Posted in Microsoft SharePoint, PowerPivot, SQL Server | 2 Comments »

Review: Simego Data Synchronisation Studio

Posted by workerthread on May 31, 2011

I recently had the opportunity to take a look at Data Synchronisation Studio 3.0, an application developed and marketed by Simego Limited in the UK.   I was interested to see how it might be used to transfer data from or to SharePoint lists as I’ve noticed a fair amount of hits and comments on previous blog posts I’ve written which touch on this subject, like this one on using Integration Services to get SharePoint list data into a database.

Let’s consider first where Data Sync Studio (I’ll shorten the name from now on) is positioned, and how it might help with data synchronisation requirements.   If you are working with Microsoft SQL Server you will know that one of its very useful components is Integration Services,  an excellent tool for moving high volumes of data between applications.  If you are populating a data warehouse, loading database tables with data from an external source, cleaning, merging or de-duplicating data and you are a SQL Server customer, you need to get familiar with Integration Services.  But of course not everyone needing to move data around has access to Integration Services and even if they do, it may be the case that the data providers they need to work with aren’t catered for, either officially or unofficially.   I’ll look at two of those data providers here, namely SharePoint lists, and Active Directory Users, both available in Data Sync Studio.   You’ll see though at the bottom of this page there are many more, including the usual Microsoft database formats, Microsoft Exchange and CRM as well as Oracle, MySQL and OData.

Installing the Application

Data Sync Studio is a standalone .Net application which comes in 32-bit and 64-bit versions.   I’ve been trying it out on a Windows Server 2008 R2 virtual Machine, but you could just as easily install on a modern Windows desktop PC running Windows 7, Vista or even XP.   It is licensed per developer rather than per server.  Once you create a synchronisation project, if it is something which needs to be run on a scheduled basis then you will need to have a licenced copy of the application installed on the machine (desktop or server) which runs the scheduled job.

At the time of writing (May 2011) a single user licence of Data Synchronisation Studio currently costs 450 GBP / 720 USD.  More information on pricing and licensing options are shown on the product home page.

Importing or Exporting SharePoint Data

Data Sync Studio allows you to use a SharePoint list as either a data source or destination and you can synchronise data in both directions.  This means that you can fairly easily connect your list to a SQL Server table and keep the two in sync.   Of course you should first be sure this is the best approach to solving your business problem.  There are other methods to consider here, not least of all Business Connectivity Services, but BCS is definitely not the answer for everyone as it’s quite difficult to get right and again will need other tools to set up correctly – at the very least access to SharePoint Designer 2010.   Also I’ve often come across situations where data needs to be captured in SharePoint then later moved to an external database such as SQL Server for additional analysis, reporting or even archiving.

I took the example I used in my article on using Integration Services with SharePoint data.  I created the same list in SharePoint 2010 and a destination table in SQL Server 2008 R2.   In Data Sync Studio the SharePoint list became Data Source A and the SQL Server table Data Destination B:


First, I moved all of the current SharePoint list items to SQL Server.   This is easily accomplished using the “Compare A>B” button, which gives me a summary of the differences between source and destination.  I can then use the “Synchronise” button to push these changes to SQL Server.   Updates, additions and even deletions from the SharePoint list can also be handled and sync’d to the SQL Server destination:


And if you want, the sync can be bi-directional – you can specify which synchronisation options are used by setting properties in the source and destination:


Data Sync Studio also includes a “Dynamic Columns” feature which lets you create custom code to express columns.  These can range from fairly simple examples such as concatenating multiple columns (for example first and last name) or performing lookup or validation tasks.  One example provided is a “SharePoint Lookup Helper”.  You would use this if you were importing data into a SharePoint list and during import you needed to match a source value to the ID of an item in a separate lookup list.  Note that this facility is available via a C# programming interface so definitely requires .Net expertise.  You can read more about this here.

I also tried using the SharePoint data provider with Office 365 but couldn’t get this to work as it wouldn’t connect.  Simego tell me they are actively looking at this and I think it would be useful.   I can imagine for example a scenario where external users submit data to an Office 365 application and the data is sync’d with a corporate application or reporting system.

Working with Active Directory Data

Data Sync Studio includes LDAP Active Directory Users as a data provider which can be used to import this data into a database  or SharePoint list:


I tried this out, loading specific columns of the AD user list into a SharePoint list.   I could certainly see some uses for this.   Simego say they already have clients using this to populate a staff or team directory in SharePoint.   Also, although the Active Directory User data provider doesn’t allow inserts or deletes (a good thing) it does allow you to update configured columns.   This could be useful – it’s often the case that when an organisation implements SharePoint they want to get best value from searchable information in Active Directory, but fields related to job title, department, contact phone numbers etc aren’t populated.   This could provide a nominated user (maybe a member of the HR department) with a way of updating the fields in a list exported to SharePoint and then syncing the updated fields back to Active Directory.

It’s probably worth noting that bi-directional synchronisation is also possible with the SharePoint 2010 User Profile service, but again, this will present you with lots of configuration challenges and is definitely not for the faint-hearted.   Data Sync Studio may be a viable alternative here.

Creating a Project Schedule

Data Sync Studio lets you create a schedule for your synchronisation project using a dialog within the Studio itself:


Once you have selected dates and times to schedule a run and entered login credentials, a Windows scheduled task is created automatically for you.   A separate “Data Synchronisation Run Tool” is also provided within which you can schedule multiple projects to run in sequence and it’s also possible to execute a synchronisation project from the command line.

Summing Up

Data Synchronisation Studio is well worth a look if you have a need to move data between line of business applications and your requirement can’t be addressed by SQL Server Integration Services, either because you aren’t able or allowed use it or because the appropriate data providers aren’t available.   Certainly it won’t take the place of Integration Services when you are moving very high volumes of data to or from SQL Server and Simego don’t sell it as a replacement.   The fact that several of the data providers included currently aren’t in Integration Services could mean it pays its way quite quickly (as well as the two mentioned above I could also see it being valuable for Exchange data and for Microsoft CRM for example).   It’s also possible to create your own data providers and make these available as add-ins.   Simego provide some example add-in providers showing how this might be done, via a C# project.

You should be aware though that this isn’t an end-user tool.  It’s really aimed at developers with some coding experience, someone happier with developer tools such as Visual Studio, Integration Services or Business Intelligence Developer Studio.   Also, although the application comes with a help file and lots of online screenshots, it did take me a little while to understand the user interface and perhaps one or two additional “walk-through” sessions in the help file would be useful.   Having said that, you might also find what you are looking for in these blog posts.

Overall then my evaluation experience was a positive one.  It’s also good to see a home-grown (UK) development company coming up with a tool like this (and even spelling synchronisation without a “z”!).

Posted in development, Microsoft SharePoint, Office, SQL Server | Comments Off on Review: Simego Data Synchronisation Studio

SharePoint 2010 RSS Viewer Web Part Tip and Question

Posted by workerthread on December 10, 2010

Here’s a quick tip you may want to use if you are working with the RSS Viewer web part in SharePoint 2010 and want to open articles in an new window – also a quick question I would appreciate any comments on.

The RSS Viewer web part is a good way of adding interesting content to SharePoint site home pages.  There are lots of good quality RSS feeds out there – for example BBC News, various Microsoft Technical resources and many diverse trade publications (or even this blog).   It’s pretty easy to set up once you have chosen a useful feed.  Here’s an example which has been configured to use the TechNet News feed:


Clicking on an article heading will reveal some more detail:


Then if you then click on the “More…” link, you will open the source article in the current window.  Now, I’ve found this is not always ideal and you often want to open the article in a new window, without leaving your SharePoint site page.  To make this happen, it’s necessary to make a change to the “More…” hyperlink.  You do this by editing the web part and clicking on the XSL Editor button to open the text editor:


This exposes the XSL styles which define how the RSS feed is displayed (if you’re looking for XSL syntax info by the way, take a look online for articles by Marc D. Anderson – he’s published some excellent stuff).

One particular line is of interest to us.  It’s in the section “RSSMainTemplate.description” and is the line defining the “href” tag to open the article when clicking on “More…”:

<a href=”{ddwrt:EnsureAllowedProtocol(string(atom2:link/@href))}”>More…</a>

To make the article open in a new window, you need to add a target=”_blank” attribute like so:

<a href=”{ddwrt:EnsureAllowedProtocol(string(atom2:link/@href))}” target=”_blank”>More…</a>

(by the way I prefer to copy the entire XSL into a better text editor to make my changes, then paste the whole lot back into the web part XSL Editor afterwards rather than working in the web part editor)

Once you’ve done that, apply your changes and you will find that your RSS feed articles will open in a new window when you click on the More… link.

And so to My Question –

In some circumstances I’ve noticed there can sometimes be problems with the RSS Viewer web part loading correctly – it seems to get stuck in “Loading” mode without ever completing.  I’ve checked around to see if anyone else is experiencing this with SharePoint 2010 but haven’t found any definitive information.  There’s a school of thought that this could occur when Alternate Access Mappings have been set up for the web application, although I’m not sure why this would be.  So if anyone can shed light on this please leave a comment!

Technorati Tags: ,,

Posted in Microsoft SharePoint | 4 Comments »