August 15, 2011

FIXED – An attempt has been made to use a data extension ‘ADS’ … with Access Services 2010

The below error might appear if you are using Access Services 2010 for the first time. The local Access 2010 publishing wizard will create RDL and RSDS files for reports you have. However, the SSRS server side configuration may require a minor update to enable ADS (Access Data Server). See http://technet.microsoft.com/en-us/library/ee662542.aspx for full details.

The instructions are pretty clear except for where to find the “rssrvpolicy.config” file. Mine was under C:Program FilesMicrosoft SQL ServerMSRS10_50.MSSQLSERVERReporting ServicesReportServer. Hope that helps. If you’d like to learn Access Service 2010 on your system be sure to check out the Northwind 2010 Web Database at http://office.microsoft.com/en-us/templates/results.aspx?qu=northwind&ex=1

Successful Report

Error Detail

An error has occurred during report processing. (rsProcessingAborted)
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. (rsDataExtensionNotFound)
An error has occurred during report processing. (rsProcessingAborted)
The Group expression for the grouping ‘Group1’ contains an error: Request for the permission of type ‘Microsoft.Office.Access.Server.Security.AccessServicesPermission, Microsoft.Office.Access.Server.Security, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c’ failed. (rsRuntimeErrorInExpression)

February 26, 2011

Server Farm Product and Patch Status

Today I attempted to join a SQL Reporting Servers machine to an existing farm and saw the below error. While self explanatory, I wanted to document it for others that haven’t seen this. WSS 3.0 and MOSS 2007 were more lenient allowing a mix between the two product SKUs. The below screen is new to 2010 and acts as a bouncer to enforce server farm consistency. It appears to check three categories:

Product SKU (Server / Foundation)
Patch (version number)
Language pack(s)

So what I’ll do next is install the full SharePoint Server product (with Dec 2010 CU slipstream on the Updates subfolder) and then try to join Config DB once again.

Error: Some farm products and patches were not detected on this or other servers. If products or patches are missing locally, you must quit this program and install the required products and patches on this server before restarting this wizard. If products or patches are missing on other servers, you must install the required products and patches on the specific servers, and you may then click the Refresh button to perform the status check again.

NOTE – You may need to run the command “Get-SPProduct -local” on each machine after applying the EXE/MSP updates for it to detect correctly in the SP Config Wizard.

August 19, 2010

Enabling SSRS 2008 R2 on SharePoint 2010

Today I felt the need to step outside of SharePoint a little and wander over to the SQL consoles and learn something new. Bottom line: works the same, just more reliable. SSRS 2008 R2 seems to me to be roughly equal on config options to what we had for SQL 2005. Similar wizard to get it created, similar Central Admin link to give the URL, even the menu names match.

However, the whole thing “feels” more reliable. Once complete I can clearly see the SSRS features on the Site Settings menu. Next, I need to learn BIDS (Business Intelligence Development Studio) to create a report (RDL) and try uploading it for rendering.

Steps Taken

Open “Reporting Services Configuration Manager” and follow wizard steps to create SSRS on SQL backend.
When asked use “SharePoint integrated” mode.
It will make a SQL database and IIS website. I used my main SP web application (http://sp2010) so that final URL is http://sp2010/ReportServer/
Open “SharePoint Central Administration”
Navigate to “General Application Settings > Reporting Services Integration”
Type that same URL, choose “Windows Authentication”, and provide a user account.
I’m using DEMOadmin for everything which is not a best practice, but works in DEV systems to avoid any “access denied” messages.
Create desktop shortcut to “C:Program FilesMicrosoft SQL ServerMSRS10_50.MSSQLSERVERReporting ServicesReportServerReportBuilderReportBuilder_3_0_0_0.application”
Click the desktop icon for first install
Enjoy the Report Builder 3.0 application to create .RDL and upload to SharePoint