Archive for category Sharepoint 2010

Working with Reporting Services in SharePoint 2010

As you probably knows SharePoint 2010 can be used to show off SQL server Reporting Services (SSRS) reports. By registering a SQL server running Reporting Services in SharePoint Native mode you can deploy reports to a sharepoint Document library and have either the ReportViewer applicationpage og the Report Viewer webpart show to report directly in your browser.

Both the Report viewer applicationpage and the webpart gets available when activating the Sitecollection feature “Report Server Integration Feature”

By default when clicking a deployed (or manually uploaded) report (*.rdl file) SharePoint will let you view it in the Report viewer page (_layouts/ReportServer/RSViewerPage.aspx)

The report viewer page takes the URL for the report as a parameter like so:

_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/SQLServerReports/ProductsSharedDataSource.rdl

You can furthermore add different parameters to the querystring to handle Report input parameters and different settings to control how the report is shown and rendered. More on that later

The Report viewer webpart can either takes the url for the deployed report or you can connect the webpart to a list view webpart that show the reports in a documentlibrary and thereby having the report viewer webpart show the report you select ind the List View webpart.

Setting up reporting Services
There are a lot of guides showing how to set up Reporting Services in ShaerPoint so I’ll only add some links here.

http://www.codeproject.com/Articles/88285/Integrating-Sharepoint-2010-and-SQL-Reporting-Serv

Building reports
When building reports for Reporting Services you should use Business Intelligence Development Studio (also known as BIDS) BIDS is part of the Visual Studio 2008 package and can be installed when installing the SQL Server 2008. Why not Visual Studio 2010 you think! Because there are no SQL Server 2010…

Building report are fairly easy and you don’t even have to known very much SQL to make it work. However when creating advanced reports you’ll most likely have to know some T-SQL to make your way.

The next few steps show you how to build a Report that shows all the products from the Northwind sample database. Can be downloaded here

  1. Open BIDS
  2. Create New project
  3. Select Report Server projet template (Fill in name etc.)

4.  Your Solution explorer should look like this

5. Right click Shared datasource and select “Add New Data Source”
6. Give it a name “Northwind datasource” and select Edit
7. Fill in servername, credentials, select database and test connection, press ok

8. We now have our datasource setup – Click ok
9. In Solution explorer right click Reports and select Add New Report

10. Verify that you have selected the newly created datasource “Northwind datasource” and click Next
11. Click Query builder OR type in your TSQL
12. In the Query Designer hit the “Add table” icon and select table: Products

13. Check columns: ProductID, ProductName, UnitPrice, UnitsInStock and click ok to close the Query Designer and then Next
14. Select the Tabular report type and click next

15. add all columns to details section and click Next
16. Select the Cooperate style and click Next
17. Name the report “Northwind products” and click Finish

18. You have now created your first report
19. You can easily pull or push the columns to make to report look nicer
20. And then try to preview

But that isn’t much fun unless you publish it to a Sharepoint Site so lets move on

Deploy the Report to a SharePoint site
The deployment of your report from BIDS its pretty easy but if you just right click your project and hits Deploy you’ll almost certainly fail

First of all you have to create a site to host your report

1. Create a plain teamsite
http://sharepoint/ReportingServices

2. Create a document library for your Reports
http://sharepoint/ReportingServices/Reports

3. Create a document library for your Shared Data Sources
http://sharepoint/ReportingServices/SharedDataSources

4. Now you need to do some configuring in your BIDS to make the Deployment work. Right click your project in Solution explorer and click Properties.
5. Fill in

– TargetDataSourcefolder: http://sharepoint/ReportingServices/SharedDataSources
– TargetReportFolder: http://sharepoint/ReportingServices/Reports
– TargetServerUrl: http://sharepoint/ReportingServices

The Properties page should look like this

6. Click ok
7. Right click your project in Solution Explorer and select Deploy

Your output should look like this

8. Check your SharePoint site and validate that your shared data source and your report are deployed
9. The datasource is deployed

10. And the report is deployed

11. Now click the “Northwind products” report and lets see it work

Url for the Report is: http://sharepoint/ReportingServices/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/ReportingServices/Reports/Northwind%20products.rdl

Passing parameters to the report
When loading the report we can pass different parameters in the querystring

More on that here: http://blogs.microsoft.co.il/blogs/bilive/archive/2011/04/26/passing-url-parameters-to-reports-in-sharepoint.aspx
and here: http://techpunch.wordpress.com/2008/09/17/sql-server-reporting-services-url-parameters-in-sharepoint-integrated-mode/

Advertisements

Leave a comment

SharePoint Powershell codesamples

Here are some powershell codesamples that I use frequently and got tired of finding the code everytime:

Task: Looping All sites and webs in a webapplication:

Add-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue
$htmlPrefix = "http://" # or use https:// for SSL sites
#$appHostNames = @("intranet.site.com", "internet.site.com", "extranet.site.com")
foreach($appHostName in $appHostNames)
{
  $siteUrl = $htmlPrefix + $appHostName
  Write-Host "Do something with all sites under this webapp: " $siteUrl
  $rootSite = New-Object Microsoft.SharePoint.SPSite($siteUrl)
  $spWebApp = $rootSite.WebApplication
  foreach($site in $spWebApp.Sites)
  {
    Write-Host "You can now do something with this SPSite object : " $site.RootWeb.Url
    foreach($web in $site.AllWebs)
    {
      Write-Host "You can now do something with this SPWeb object : " $web.Title "("$web.Url")"
      # Or if you need to access Publishing web object
      $pWeb = [Microsoft.SharePoint.Publishing.PublishingWeb]::GetPublishingWeb($web)
      $web.Dispose()
    }
    $site.Dispose()
  }
}

Task: Setting New page default pagelayout
This example set the Publishings webs default pagelayout to “MyCustomPageLayouts.aspx” which is located in the gallary. The way to do this by the UI is Site Settings > Page layouts and site templates (Look and Feel section) > New Page Default Settings (section)  (/_Layouts/AreaTemplateSettings.aspx)

Add-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue
$site = Get-SPSite "http://intranet.site.com"
$web = $site.RootWeb
$pweb = [Microsoft.SharePoint.Publishing.PublishingWeb]::GetPublishingWeb($web)
$layout = $pweb.GetAvailablePageLayouts() | ? {$_.Name -eq "MyCustomPageLayout.aspx"}
$pweb.SetDefaultPageLayout($layout, $false)
$pweb.Update()
$web.Dispose()

Task: Adding specific Mime Type to Specific Webapplication
This example (found at Mathieus blog) shows how you can add a specific MimeType to the Allowed Mimetypes for a WebApplication. This example adds the PDF mime type. A large list of Mime types can be found here

Add-PSSnapin Microsoft.SharePoint.PowerShell –ErrorAction SilentlyContinue
#Mime Type Examples: "application/pdf, text/html, text/xml, application/x-shockwave-flash"
$mimetype = "application/pdf"
$webapp = Get-SPWebApplication "http://intranet.site.com"
If ($webapp.AllowedInlineDownloadedMimeTypes -notcontains $mimetype)
{      
  Write-Host "Adding MIME Type..."    
  $webapp.AllowedInlineDownloadedMimeTypes.Add($mimetype)      
  $webapp.Update()      
  Write-Host "Done."
}
Else
{      
  Write-Host -ForegroundColor Green "MIME type is already added."
}

By this way you can add specific allowed mimetypes without having to switch Browser File Handling from Strict to Permissive, which is normally done in Central administration > Manage Webapplications > Mark a Webapplication and select ‘General Settings’ in the Ribbon. With this option set to Permissive, all Mimetypes are allowed.

Browser File Handling settings

Task: Update list item
This example show how to update a listitem. In this case a DateTime field.

$SPSite = new-object Microsoft.SharePoint.SPSite("http://server/sites/sc")
$SPWeb = $SPSite.RootWeb
$SPList = $SPWeb.Lists["YourList"]
$SPItem = $SPList.GetItemById("4")
$SPItem["Some date"] = "01/01/2011 00:00:00"
$SPItem.Update()


Task: Get a list of all WebTemplates with information

This example show how to get a complete list of all web templates that can be used to create sites, including templates saved by users to the site collections template gallary.

Get-SPWeb http://YourSiteUrl | %{$_.GetAvailableWebTemplates($_.Language)} | Format-List

This post will be updated as time goes by and I run into SharePoint Powershell issues…

1 Comment

SharePoint Central Administration Content Database in Suspect Mode

Yesterday my worst fears came true. On my development environment where all my SharePoint 2010 projects are located my Central Administration site suddenly didn’t respond. It only returned HTTP error: 404 – What!!

ISS was running, no problems with AppPool, SQL server was running og the weirdest parts was that all my sites was running as well! Only CA site was down.

So the nasty part began where eventviewers, log file crawling, google searching etc. where analysed in details.

The logs and eventviewers had a lot of
– “Login failed for user ‘INTELLISOFT\sp_workerprocess’. Reason: Failed to open the explicitly specified database.”
but also this
– “During redoing of a logged operation in database ‘SharePoint_AdminContent_00bd9b82-4cf1-4846-975a-2b5b8d5287a5’, an error occurred at log record ID (3173:321:29). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.”

You don’t have to be a SQL Server brain to figure out that this is gonna make your life a nightmare. And on top of all I had just spent several hours updating some Sharepoint solution that needed a blank sitecollection for testing. And with no access til CA, your pretty much out of luck.

So i went to SQL Server Management Studio and noticed that among all my databases the SharePoint_AdminContent_GUID was marked “Suspect”!

After googling this error like crazy the only thing I could find at the beginning was to restore a backup of the AdminContent database. But sadly enough on my dev environment I haven’t got backups of the individual databases. I only backup my Visual Studio projects regular and the entire VMware machine once in a while.

So with many hours of restoration in mind i started copying  the last backup from my NAS to the laptop. Meanwile I googled some more and luckily enough I found this article  by Mehuil K Bhuva who has just become my biggest idol. Mehuil describes how you run some repair jobs on your corrupt database and in my case the database got restored and is now up and running again.

So Mehuil: From now om I am officially your biggest fan. You just saved me hours and hours of restoring my dev environment and now there is a god chance that I can actually deliver the solution to my customer in time. Thanks again for taking your time to write this amazing article. All I did was to replace the DB name with my own. Run all the SQL commands in one query and after a few minutes the “suspect” mark was gone and CA was running again. Can’t get my arms down – you saved my life!

I decided to write this post so that more people can find Mehuils solution and to let everyone know that it worked for me.

For the complete article on what you should do please visit Mehuils blog.
To make sure that this knowledge isn’t lost I have pasted the SQL Query below but all credit goes to Mehuil on this one – Great work!

1. locate your mdf and ldf files in your SQL Server data directory

2. start up SQL Server Management studio at click “New Query”

3. Insert this below and make sure that you replace database name with your own

Use master
–Verify whether Database has any issues
EXEC sp_resetstatus “SharePoint_AdminContent_38c5cc2d-aeec-4dc2-b7a5-65457250ae2c.mdf”

—Alter database and put it on Emergency Mode
ALTER DATABASE “SharePoint_AdminContent_38c5cc2d-aeec-4dc2-b7a5-65457250ae2c” SET EMERGENCY DBCC checkdb(‘SharePoint_AdminContent_38c5cc2d-aeec-4dc2-b7a5-65457250ae2c’)

–Set the database in the Single User mode
ALTER DATABASE “SharePoint_AdminContent_38c5cc2d-aeec-4dc2-b7a5-65457250ae2c” SET SINGLE_USER WITH ROLLBACK IMMEDIATE

–Repair the database and allow data loss
DBCC CheckDB(‘SharePoint_AdminContent_38c5cc2d-aeec-4dc2-b7a5-65457250ae2c’,REPAIR_ALLOW_DATA_LOSS)

–Set the database back to Multi-User mode
ALTER DATABASE “SharePoint_AdminContent_38c5cc2d-aeec-4dc2-b7a5-65457250ae2c” SET MULTI_USER

–Ensure Database is reset
EXEC sp_resetstatus ‘SharePoint_AdminContent_38c5cc2d-aeec-4dc2-b7a5-65457250ae2c’

Hit execute and wait for a few minutes. It took around 4 minutes on my system.

Once done a refresh of your SQL Server object explorer should remove the “Suspect” marking and your CA site should be running again.

, ,

Leave a comment

Installing SharePoint 2010 on a closed network

I just had the “pleasure” of installing SP2010 on a customers offline network. Apparently most prerequisites are downloaded on the fly which result in great troubles when out SharePoint servers aren’t connected to the outside world.

After having manually installed a few prerequisites I discovered this great article contained direct download links to all prerequisites. Thanks Dave!

Note that the 2 prerequisites regarding speech may just open the installer and disappear again but they perform their job anyway. At least as far as SharePoint are concerned. Also note that the Analysis Services ADOMS.NET comes in a version for both SQL Server 2008 and SQL Server 2008R2. I tried using the R2 version because my SQL Server is R2. However the prerequisites installer will only recognise installing the NOT R2 version – didn’t dvelve too much into that.

Leave a comment

Troubleshooting “The search request was unable to connect to the Search Service”

When working with search in SharePoint 2010 you might run into the error “The search request was unable to connect to the Search Service” in the Core Results webpart.

If or when this happens you should check a few things

Is the search service running?
Go to the server that handles search requests and select Start > Administrative tools > Services. Locate the service “SharePoint Server Search 14” and check that it is running

Is the Webapplication associated with the Search Server Service?
Go to Central administration > Manage Webapplications > click on the Webapplication that troubles you > Click Service Connection. In the windows “Configure Service Application associations” ensure that Search Service Application is checked

Have you altered the settings in the Search Core Results webpart?
Locate your Search Core results webpart > Edit Web Part > Location properties > select Location: none. If you might have selected “Local FAST search results” it wil result in the error “The search request was unable to connect to the Search Service” when FAST isn’t configured.

If none of these suggestions work and you might have found something else that could be causing this error please let me know.

1 Comment

Custom mysite masterpage for Personal sites

I just had to write down some notes on the steps needed to take when you want to set your own custom master page for every personal site.

If you don’t have the complete overview on how mysites and personal sites works together you should read this first: Sharepoint 2010 Mysite in detail

Since personal sites are in fact sitecollections (one per user) and masterpage settings are scoped to sitecollection you need feature staplers to have every new site use your own custom master page instead of the one default (v4.master)
You should furhtermore know at personal sites are created on the fly from the Mysite host when clicking “My Content” if you don’t already have one.

So here are the steps.

1. Create your own custom masterpage (ofcourse 🙂 and wrap it into a feature to make it appear to the Masterpage gallary for new sitecollections.

2. Create a feature with a feature receiver for setting the master page

The code for the feature receiver could look like this

        public override void FeatureActivated(SPFeatureReceiverProperties properties)
        {
            try
            {
                SPWeb web = ((SPSite)properties.Feature.Parent).RootWeb;
                web.MasterUrl = web.MasterUrl.Substring(0, web.MasterUrl.LastIndexOf('/') + 1) + "MyOwnCustom.master";
                web.CustomMasterUrl = web.CustomMasterUrl.Substring(0, web.CustomMasterUrl.LastIndexOf('/') + 1) + "MyOwnCustom.master";
                web.Update();
            }
            catch (Exception ex)
            {
                //Write error to ULS (Unified Logging System - 14/Logs) if desired (Remember to specify: using Microsoft.Office.Server.Diagnostics;)
                //PortalLog.LogString("MyOwnCustom master- Feature receiver: Set Master for new Personal sites - Error: " + ex.ToString());
            }
        }

The scope of this feature should be Site (Sitecollection)

3. Create another feature (the stapling feature)
This feature is used to tell Sharepoint which feature should be actived when a site is created using a certain sitedefinition.

<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <FeatureSiteTemplateAssociation Id="A392DA98-270B-4e85-9769-04C0FDE267AA" TemplateName="SPSPERS#0" />
  <!-- Publishing Prerequisites -->
  <FeatureSiteTemplateAssociation Id="AEBC918D-B20F-4A11-A1DB-9Ed84D79C87E" TemplateName="SPSPERS#0" />
  <!-- Publishing Resources -->
  <FeatureSiteTemplateAssociation Id="F6924D36-2FA8-4f0b-B16D-06B7250180FA" TemplateName="SPSPERS#0" />
  <!-- SharePoint Server Publishing Infrastructure - Scope: Site -->
  <FeatureSiteTemplateAssociation Id="D0DF37FC-060A-4F57-AE3F-310C2D438551" TemplateName="SPSPERS#0" />
  <!-- Custom Publishing Layouts -->
  <FeatureSiteTemplateAssociation Id="98D8A361-DDF6-4771-A285-64D23E0E556C" TemplateName="SPSPERS#0" />
  <!-- Feature receiver for setting custom master (code)-->
</Elements>

Notice that some other features should be activated before you can set your own master. (Se the complete list of features here – for use in other scenarios)
The “Custom Publishing Layouts” feature is my own custom feature that holds my custom masterpages, pagelayouts, styles etc.
The “Feature receiver for setting custom master” is the feature that reference my feature receiver (se step 2)
Notice that the sitedefinition for Personal sites are named SPSPERS#0 (See a complete list of sitetemplates here)

The scope of this feature should be farm (Farm) – Se this post if you have trouble with the error: “The Project Item “[Item Name]” cannot be deployed through a Feature with Farm  – It sure as hell gave me some headaches – dammit!
scope” when trying to deploy your solution/WSP

4. Deploy the solution and try to create a new personal site.

If I have left something out please let me know

4 Comments

Sharepoint 2010 sitetemplates

Getting pretty tired of finding the complete list of sitetemplates on Sharepoint 2010.

By using the powershell command: get-spwebtemplate you’ll get the complete list of templates available

The complete list is:

Name Title LocaleId Custom
GLOBAL#0 Global template 1033 False
STS#0 Team Site 1033 False
STS#1 Blank Site 1033 False
STS#2 Document Workspace 1033 False
MPS#0 Basic Meeting Workspace 1033 False
MPS#1 Blank Meeting Workspace 1033 False
MPS#2 Decision Meeting Workspace 1033 False
MPS#3 Social Meeting Workspace 1033 False
MPS#4 Multipage Meeting Workspace 1033 False
CENTRALADMIN#0 Central Admin Site 1033 False
WIKI#0 Wiki Site 1033 False
BLOG#0 Blog 1033 False
SGS#0 Group Work Site 1033 False
TENANTADMIN#0 Tenant Admin Site 1033 False
ACCSRV#0 Access Services Site 1033 False
ACCSRV#1 Assets Web Database 1033 False
ACCSRV#3 Charitable Contributions Web Database 1033 False
ACCSRV#4 Contacts Web Database 1033 False
ACCSRV#6 Issues Web Database 1033 False
ACCSRV#5 Projects Web Database 1033 False
BDR#0 Document Center 1033 False
OFFILE#0 (obsolete) Records Center 1033 False
OFFILE#1 Records Center 1033 False
OSRV#0 Shared Services Administration Site 1033 False
PPSMASite#0 PerformancePoint 1033 False
BICenterSite#0 Business Intelligence Center 1033 False
SPS#0 SharePoint Portal Server Site 1033 False
SPSPERS#0 SharePoint Portal Server Personal Space 1033 False
SPSMSITE#0 Personalization Site 1033 False
SPSTOC#0 Contents area Template 1033 False
SPSTOPIC#0 Topic area template 1033 False
SPSNEWS#0 News Site 1033 False
CMSPUBLISHING#0 Publishing Site 1033 False
BLANKINTERNET#0 Publishing Site 1033 False
BLANKINTERNET#1 Press Releases Site 1033 False
BLANKINTERNET#2 Publishing Site with Workflow 1033 False
SPSNHOME#0 News Site 1033 False
SPSSITES#0 Site Directory 1033 False
SPSCOMMU#0 Community area template 1033 False
SPSREPORTCENTER#0 Report Center 1033 False
SPSPORTAL#0 Collaboration Portal 1033 False
SRCHCEN#0 Enterprise Search Center 1033 False
PROFILES#0 Profiles 1033 False
BLANKINTERNETCONTAINER#0 Publishing Portal 1033 False
SPSMSITEHOST#0 My Site Host 1033 False
ENTERWIKI#0 Enterprise Wiki 1033 False
SRCHCENTERLITE#0 Basic Search Center 1033 False
SRCHCENTERLITE#1 Basic Search Center 1033 False
SRCHCENTERFAST#0 FAST Search Center 1033 False
visprus#0 Visio Process Repository 1033 False

LocaleId’s other than 1033 (english) removed for clarity

, , ,

Leave a comment