Configuring SQL Server Denali Reporting Services SharePoint 2010Integration

Great guide pointing out that if you should happen to install SQL Server Reporting Services 2012 before installing SharePoint, you must then let SharePoint 2010 know it's there and install the service.

http://denglishbi.wordpress.com/2011/07/22/configuring-sql-server-denali-reporting-services-sharepoint-2010-integration/

Thanks Dan!

XSLTListViewWebPart: Using CAML Query Parameters

After much searching around followed by a fair bit of trial and error, I figured out how to make use of Data View Parameters in a CAML Query in an XSLTListViewWebPart. Bear in mind I am configuring this using SharePoint designer.
So I have a list web part on a page, I open up that page using SharePoint Designer 2010 and begin editing under the hood. Once you've added a Data View Parameter you're then able to embed it as a parameter into your CAML query like so (Parameters in Bold):

<Query>
     <OrderBy> … </OrderBy>
     <Where>
     <Contains>
           <FieldRef Name="FileRef" />
           <Value Type="Text">{year}/{month}</Value>

</Contains>

</Where>
</Query>

Despite being so easy to implement, not once did I find it documented anywhere. I've labelled this post as I would have searched for it in the hope it helps out others looking for the same thing.

DataSourceID is either missing or set to an empty string

When fiddling around with the CoreResultsWebPart in SharePoint designer, or any web part for that matter, it’s easy to make mistakes in the markup.

However, more specifically, when you make a mistake in the markup of the CoreResultsWebPart it will always give the error DataSourceID is either missing or set to an empty string:

So of course, the first thing you do after seeing this is check your data source. I dragged another web part onto the page and compared the configuration with the one I’d been playing with in SPD. No difference – odd.

It was only through looking through the web part code that I noticed I had not closed a <div> tag that I’d put in for display of an image. I added the closing div tag </div> and voila! The web part no longer presented me with an error.

Having search around for instances where people were having the same or similar problem, somebody had suggested that you get this generic error no matter what’s wrong with your CoreResultsWebPart. So if you see the error and you believe the data source is intact, don’t be too alarmed – check your Web Part markup.

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.