Connecting To SSAS Remotely Using Excel

If you plan to connect to SQL Server Analysis Services (SSAS) over the network using Excel, and your server uses Windows Firewall for protection, then you’re going to need to know how to open up Windows Firewall in order allow the connection.

Here’s how: http://msdn.microsoft.com/en-us/library/ms174937.aspx

If you try and connect without allowing such connections through the firewall, you will repeatedly get a message saying something along the lines of:

 

If you’ve opened up the firewall and you still see this error then I’d suggest checking your installed versions of MS OLE DB, MDAC and MSXML.

SQL Server 2008 R2 Reporting Services Add-in for Microsoft SharePoint Technologies 2010 (and the trouble it causes)

Recently I had to migrate a site collection on a development server onto a pilot server for a client. PowerShell was used to do the following:

Source Server (Development)

• Back up the site collection using Backup-SPSite
  

Destination Server (Pilot)

• Backup the existing Site Collection using Backup-SPSite
  
• Delete the old Site Collection using Remove-SPSite
  
• Create a new Site Collection as a container (no template) using Create-SPSite
  
• Restore the Site Collection from the Source server using Restore-SPSite
  

The site collection contained two Site Templates in the form of Solutions (.wsp files, stored in the Site Collection Solutions gallery).

The Issue

Whenever I attempted to create a new site based on either of these templates, I was presented with an error stating that there was a missing feature dependency and gave the ID of the missing feature:


[trying to find my screenshot of this – I appear to have misplaced it]


First thing's first, what feature is that GUID referring to? I used the command Get-SPFeature | out-file C:\FeatureList.txt on both the Development and the Pilot server to get a full list of the features. Sure enough, the Development server had the feature and the Pilot server did not. Though more interestingly, the feature in question here is the 'Report Server' feature. With a little more investigation it became clear that the Development server had the 'SQL Server 2008 R2 Reporting Services Add-in for Microsoft SharePoint Technologies 2010' installed whereas the Pilot server did not.


Intriguing… Although I have not explicitly used any element of report server in my Site Collection – more to the point, in my Site Templates – SharePoint 2010 was failing to create a new site due to this feature not being present. What's even more baffling is that if there's such a deep rooted dependency on this feature, why did SharePoint 2010 allow me to restore the site collection in the first place?

Plan of Attack

Two possible options here; Regress the development environment in an attempt to re-migrate the site, minus feature dependency. Also, install the 'SQL Server 2008 R2 Reporting Services Add-in for Microsoft SharePoint Technologies 2010' on the Pilot server. I decided to run both in parallel.

Regression

Having uninstalled the 'SQL Server 2008 R2 Reporting Services Add-in for Microsoft SharePoint Technologies 2010' on the Development server, I discovered that it had left behind 3 Report Server content types that appeared to have lost their names i.e. in a zombie state:

When I tried to delete them I was presented with the error show in the screenshot below:

I could have probably gotten rid of these content types using a more heavy handed/brutal approach but I didn't really have the time, or the tools at hand.

Installing the Add-In

Time for plan B – hope that installing the 'SQL Server 2008 R2 Reporting Services Add-in for Microsoft SharePoint Technologies 2010' works.


Sure enough, following the installation, without modifying what I'd originally deployed – and as if by magic – I was again able to create new sites based on the saved templates </baffled>


Further investigation seems to suggest that the feature dependency existed because the Site Template made reference to the Report Server content types, which, of course, are part of the Report Server feature.

Sysprep SQL Server 2008 R2

Recently I've been having a little play with preparing stand-alone instances of SQL Server 2008, otherwise referred to as sysprep. I considered writing a guide with screenshots but it's already been done. So in the spirit of not wanting to reinvent the wheel, here we go: http://blogs.msdn.com/b/petersad/archive/2009/12/17/sql-server-2008-r2-sysprep-step-by-step.aspx

SharePoint 2010 - New Service Application - Application Pool Already Exists!

Whilst playing around with SharePoint Server 2010 there have been a number of occasions where I’ve created a new Service Application, only to find I’ve either done it wrong or need it configured in a slightly different way for whatever reason.

Typically what I’d do is delete the old SharePoint Service Application and create a fresh one. I quite often want to use the same naming convention and this seems to cause problems.

For example, I created a Secure Store Service, realised I’d done something wrong, deleted it and tried to re-create it using the same settings as before. When I try to do so, SharePoint tells me that there’s already a SharePoint IIS Application Pool (SPIisWebServiceApplicationPool) that exists with the same name:

 

 

So the first thing you think to check is IIS, right? I did that and did not see the Application Pool to delete it.

Not accepting defeat and wanting to retain my naming convention I looked into this a little further; Is SharePoint 2010 storing a list of Application Pools somewhere and not clearing them out properly?

After a little searching, it struck me that perhaps this was something I could sort out with PowerShell. Here’s how:-
I queried the list of Application Pools using Get-SPServiceApplicationPool to ensure the one I’d previously created was indeed still there and accessible to PowerShell

 

 

Then it’s as simple as calling Remove-SPServiceApplicationPool to remove it.

 

 

That's it. You've tidied up the the farm by removing the application pool and got to keep your precious naming convention. Thanks PowerShell - I'll sleep easy now.