Home

Monday, April 21, 2014

SQL Server Reporting Services, Conditional Formatting Using Switch Function



Description

This post explains how to configure conditional formatting in an SSRS report, for an example that has three conditions.

Solution 

In order to achieve the desired result, we can modify the Background Color property in the SSRS report with a formula, using the Switch function. As described in an MSDN article, "The Switch function returns the value associated with the first expression in a series that evaluates to true" (MSDN, 2012). In the example below we are setting the Variance field Red if the value is less than -100,000, Yellow if the value is between -100,000 and 0, and Green if the value is greater than or equal to 0.
References

MSDN (2012). Expression Examples (Report Builder and SSRS). Retrieved April 21, 2014 from http://msdn.microsoft.com/en-us/library/ms157328.aspx

Thursday, March 27, 2014

SharePoint 2013: How to Configure Visio Graphics Service Application using PowerShell

Last Updated March 28, 2014



Description

This post describes how to configure the SharePoint Server 2013 Visio Graphics Service Application using PowerShell.

Solution

1)  Document the Naming Convention

Determine a naming convention for each of the Visio Graphics Service Application components.  Document the naming convention.

Example:
Application Pool Service Account:  Domain\SvcAccount (this should already be created)
Application Pool: SvcApp_SPServiceApplicationPool_02 (this should already be created)
Service Application: SvcApp_SPVisioServiceApplication_01 

Service Application Proxy:  SvcApp_SPVisioServiceApplication_01_Proxy_01

2)  Open the SharePoint 2013 Management Shell as Administrator.

3)  Verify the Name of the Application Pool.
Get-SPServiceApplicationPool | Select Name

4)  Set the Service Application Pool in a variable.
$SvcAppPool02 = Get-SPServiceApplicationPool "SvcApp_SPServiceApplicationPool_02"

5)  Create the Service Application and assign it to the Application Pool.
$SvcApp = New-SPVisioServiceApplication -ApplicationPool $SvcAppPool02 -Name "SvcApp_SPVisioServiceApplication_01"
 
6)  Verify the Service Application.
Get-SPServiceApplication | Select Name

5)  Create the Service Application Proxy and assign it to the Service Application.
$SvcAppProxy = New-SPVisioServiceApplicationProxy -ServiceApplication SvcApp_SPVisioServiceApplication_01 -Name "SvcApp_SPVisioServiceApplication_01_Proxy_01"

7)  Verify Service Application Proxy.
Get-SPServiceApplicationProxy | Select Name



References

Microsoft TechNet (February, 2014).  New-SPVisioServiceApplication.  Retrieved March 27, 2014 from http://technet.microsoft.com/en-us/library/ff608096%28v=office.15%29.aspx

Microsoft TechNet (February, 2014).  New-SPVisioServiceApplicationProxy.  Retrieved March 27, 2014 from http://technet.microsoft.com/en-us/library/ff608020%28v=office.15%29.aspx

SharePoint 2013: How to Configure Work Management Service Application using PowerShell

Last Updated March 28, 2014



Description

This post describes how to configure the SharePoint Server 2013 Work Management Service Application using PowerShell.

Solution

1)  Document the Naming Convention

Document a naming convention for each of the Work Management Service Application components. 

Example:
Application Pool Service Account:  Domain\SvcAccount (this should already be created)
Application Pool: SvcApp_SPServiceApplicationPool_02 (this should already be created)
Service Application: SvcApp_SPWorkManagementServiceApplication_01

2)  Open the SharePoint 2013 Management Shell as Administrator.

3)  Verify the Name of the Application Pool.
PS C:\ Get-SPServiceApplicationPool | Select Name

4)  Set the Service Application Pool in a variable.
$SvcAppPool02 = Get-SPServiceApplicationPool "SvcApp_SPServiceApplicationPool_02"

5)  Create the Service Application and assign it to the Application Pool.
$SvcApp = New-SPWorkManagementServiceApplication -ApplicationPool $SvcAppPool02 -Name "SvcApp_SPWorkManagementServiceApplication_01"
 
6)  Verify the Service Application.
PS C:\ Get-SPServiceApplication | Select Name



References

Microsoft TechNet (February, 2014).  New-SPWorkManagementServiceApplication.  Retrieved March 27, 2014 from http://technet.microsoft.com/en-us/library/fp161254%28v=office.15%29.aspx

Monday, March 03, 2014

SPS Boston: Reporting and Data Integration Methodologies and Mechanics for SharePoint and SQL Server

Event:
SharePoint Saturday Boston
Saturday, April 12, 2014

Session Title:
Reporting and Data Integration Methodologies and Mechanics for SharePoint and SQL Server

Abstract:
First, we’ll review the architecture of SharePoint with SQL Server Reporting Services (SSRS) and SQL Server Integration Services (SSIS) and define the role that the Business Intelligence capabilities play in an organization’s Enterprise Architecture (EA). From there, we will evaluate sample business scenarios and compare and contrast different possible solution approaches. We will design a reporting solution, and then build the solution step by step using SharePoint, SSRS, and SSIS. Finally, we will reflect on the overall approach that we followed and discuss further ideas and possibilities. This session covers high-level concepts as well as practical, hands-on instructions, tips, and tricks.

Slides:
Reporting and Data Integration Methodologies and Mechanics SP2013 SQL2012.pdf
Note:  the instructional slides begin at slide #23

SharePoint Server or Project Server Patch Installed Patch Missing



Description

After installing a SharePoint Server or Project Server Service Pack or Cumulative Update, the Configuration Wizard shows that the Patch is missing from servers in the farm.  In this case, the Configuration Wizard on Server 1 is showing that the Project Server 2013 SP1 Patch is missing, yet it was already installed.

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.




Attempting to run the Project Server 2013 SP1 Patch installation again from the .exe verifies that the Patch is already installed on the system.  Herein lies the problem.  On one hand, the Configuration Wizard will not run because it thinks the patch is missing.  On the other hand, it is not possible to install the patch because it is already installed on local server.  Patch installed, patch missing, patch installed, patch missing.



Solution

The solution to this problem is to run a PowerShell cmdlet on each of the servers that the Configuration Wizard is reporting, patch missing.

1.  Log into the SharePoint Server

2.  Open the SharePoint Management Shell as Administrator

3.  Get-SPProduct -local

This cmdlet retrieves a list of the SharePoint-related products installed in the farm.  It also retrieves the version information for updates installed.  Using the -Local switch retrieves information for local server only.


It may take some time for the cmdlet to run, but once it completes on the servers, you can do a refresh on the Configuration Wizard and the Configuration will recognize that the patches are, in fact, installed on the servers and it will advance automatically to the next screen.  You can continue run the Configuration Wizard upgrade procedure.



References

Microsoft TechNet (May, 2010).  Document farm configuration settings (SharePoint Foundation 2010).  Retrieved March 3, 2014 from http://technet.microsoft.com/en-us/library/ff645390%28v=office.14%29.aspx

Microsoft TechNet (January, 2014).  Document farm configuration settings in SharePoint 2013.  Retrieved March 3, 2014 from http://technet.microsoft.com/en-us/library/ff645391.aspx

Microsoft TechNet (July, 2012).  Get-SPProduct.  Retrieved March 3, 2014 from http://technet.microsoft.com/en-us/library/ff607885.aspx

Friday, February 14, 2014

SharePoint 2013: How to Increase Shutdown Time Out on Central Administration Application Pool




Description

The Process Model, Shutdown Time Limit setting determines how long a worker process is allowed to stay alive.  The default value is 90 second, however, if a process does not finish in time, it will be shutdown.  In a sluggish environment that does not have adequate RAM, then it is common to see issues when interacting with Central Administration.

For example, when creating a Web Application using Central Administration, the process may only partially complete before it is shutdown, leaving a Web Application in Central Administration, a content database in SQL Server, a Virtual Directory folder in the file system, but no corresponding Web site in IIS.  In this case, you have to delete the Web Application from Central Administration and recreate it.


Solution

If possible, meet the minimum hardware requirements for RAM (Microsoft TechNet, 2014).

Additionally, it is possible to increase the value of the Shutdown Time Limit to allow Central Administration more time to process the types of changes that are prone to timeout in an environment that is resource-challenged.

To make this configuration change, open Internet Information Services (IIS) Manager and then expand the server node and click on Application Pools.



Right-click the SharePoint Central Administration Application Pool and select Advanced Settings.



The default Shutdown Time Limit (seconds) is 90.


 Increase the Shutdown Time Limit (seconds) as you see fit.  The example below shows the setting at 480 seconds.



References

Microsoft TechNet (2014).  Hardware and software requirements for SharePoint 2013.  Retrieved February 14, 2014 from http://technet.microsoft.com/en-us/library/cc262485.aspx



Wednesday, February 12, 2014

SharePoint 2013: Web Application General Settings: Error, Something Went Wrong...



Description

When attempting to modify the Web Application General Settings of a particular Web Application, an error occurs and it is not possible to view the Web Application General Settings page in Central Administration.

Error:  Sorry, something went wrong.  Updates are currently disallowed on GET requests.  To allow updates on a GET, set the 'AllowUnsafeUpdates' property on SPWeb.




Solution

1.  Open the SharePoint Management Shell

2.  Type $WebApp = Get-SPWebApplication http://host.domain.com and press Enter

3.  Type $WebApp.HttpThrottleSettings and press Enter

4.  Type  $WebApp.Update() and Press Enter

5.  Attempt to open the Web Application General Settings from Central Administration.  The page should work at this point.



References

Microsoft TechNet (July, 2012).  Get-SPWebApplication.  Retrieved February 12, 2014 from http://technet.microsoft.com/en-us/library/ff607562.aspx



Wednesday, January 29, 2014

SharePoint 2013: Set-SPEnterpriseSearchTopology : Could not connect to the HostController



Description

After configuring all of the Enterprise Search components for a new Search Topology, the activation of the Search Topology fails.


Error Message


PS C:\> Set-SPEnterpriseSearchTopology -Identity $SearchTopologyName
Set-SPEnterpriseSearchTopology : Could not connect to the HostController
service on server SERVERNAME Topology Activation could not be started.
At line:1 char:1
+ Set-SPEnterpriseSearchTopology -Identity $SearchTopologyName
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidData: (Microsoft.Offic...tSearchTopology:
SetSearchTopology) [Set-SPEnterpriseSearchTopology], InvalidTopologyExcept
ion
+ FullyQualifiedErrorId : Microsoft.Office.Server.Search.Cmdlet.SetSearchT
opology




Troubleshooting Steps

In Services.msc and in Central Administration, verified the Search Host Controller Service is "Started" on all SharePoint servers in the Topology.

Restarted the Search Host Controller Service


Solution

The Search Host Controller Service was running under the identity of an Application Pool Account, which did not have the required permissions to establish the connection.

In Central Administration, Security, Configure Service Accounts, changed the Service Account assigned to the Search Host Controller Service, to the SharePoint Farm Account.  Ran the activation again, and it was successful.


References

Microsoft TechNet (2013).  Change the default search topology in SharePoint Server 2013.  Retrieved January 29, 2014 from http://technet.microsoft.com/en-us/library/jj862356.aspx

SharePoint 2013: How to Rename the Search Service Application Using PowerShell



Description

This post explains how to change the name of the Enterprise Search Service Application using PowerShell.


Key Words
PowerShell, Enterprise Search Service Application.


Solution

1.  Set a variable

$SvcAppSearch = Get-SPEnterpriseSearchServiceApplication


2.  Verify the current name

$SvcAppSearch.name


3.  Change the name

$SvcAppSearch.name = "SPEnterpriseSearchServiceApplicationNewName"


4.  Update

$SvcAppSearch.Update()


5.  Verify the new name in PowerShell

Get-SPEnterpriseSearchServiceApplication


6.  Run IIS Reset on the Web server which is hosting Central Administration


7.  Verify the new name in Central Administration

Central Administration, Application Management, Manage Service Applications


References

Microsoft TechNet (2013).  Get-SPEnterpriseSearchServiceApplication.  Retrieved January 29, 2014 from http://technet.microsoft.com/en-us/library/ff608050.aspx

SharePoint 2013: How to Configure the Search Service Application using PowerShell




Description

This post describes how to configure the SharePoint Server 2013 Search Service Application using PowerShell. 

In this example, there is one Database Server (SERVER01), one Web Server (SERVER03), and one Application Server (SERVER04).


Solution

This solution is for a small farm environment that has one Database Server, one Web Server, and one Application Server.  The steps below will result in a complete Search Topology. 

The steps provided do not represent an automated script that you would copy and paste into a window and run all at once.  Instead, the steps are intended to be run manually, one at a time, in the PowerShell window. The post is written this way on purpose and provides a way to step through each cmdlet one at a time.

If you have a different server topology or if you would prefer a fully automated script to provision the Search Service Application, then I recommend that you look at the References section and possibly utilize one of the more sophisticated examples that exist in the community knowledge base.



Before Starting...

Ensure that the Service Account to be used for the Service Application Pool exists and has been registered in Central Administration as a Managed Account

Ensure that the Host Controller Service in Central Administration is registered under a Service Account that has adequate permissions to activate the Search Topology (e.g. Farm Account)


1.  Prepare the PowerShell Session
 
Open the PowerShell window as Administrator.

Clear-Host

Set-ExecutionPolicy unrestricted
Respond Y to confirm

Get-PSSnapin -name Microsoft.SharePoint.PowerShell -EA SilentlyContinue


2.  Determine Naming Convention and Set Initial Variables

Set the Variables

$Svr01 = "SERVER01"
$Svr03 = "SERVER03"
$Svr04 = "SERVER04"
$SvcAppPl03 = "SvcApp_SPServiceApplicationPool_03"
$SvcAppSrch = "SvcApp_SPEnterpriseSearchServiceApplication_01"
$SvcAppSrchPrxy = "SvcApp_SPEnterpriseSearchServiceApplication_01_Proxy_01"
$SvcAppDBSvr = "SERVER01"
$SvcAppDB = "SvcApp_SPEnterpriseSearchServiceApplication_01_DB_01"
$SvcAppSrchAdmnCmp = $SvcAppSrch | Get-SPEnterpriseSearchAdministrationComponent
$ESSI03 = Get-SPEnterpriseSearchServiceInstance -Identity "SERVER03"
$ESSI04  = Get-SPEnterpriseSearchServiceInstance -Identity "SERVER04"
$QSSN03 = Get-SPEnterpriseSearchQueryAndSiteSettingsServiceInstance -Identity "SERVERN03"
$QSSN04 = Get-SPEnterpriseSearchQueryAndSiteSettingsServiceInstance -Identity "SERVERN04"
$IndexLocP = "I:\IndexPrimary"
$IndexLocR = "I:\IndexReplica"


3.  Start the Search Service Instances

Start-SPEnterpriseSearchServiceInstance -Identity $Svr03

Start-SPEnterpriseSearchServiceInstance -Identity $Svr04


4.  Start the Query and Site Settings Service Instances

Start-SPEnterpriseSearchQueryAndSiteSettingsServiceInstance -identity $QSSN03

Start-SPEnterpriseSearchQueryAndSiteSettingsServiceInstance -identity $QSSN04


5.  Create the Search Service Application

New-SPEnterpriseSearchServiceApplication -Name $SvcAppSrch -ApplicationPool $SvcAppPl03 -AdminApplicationPool $SvcAppPl03 -DatabaseName $SvcAppDB -DatabaseServer $SvcAppDBSvr


6.  Set the Search Admin Component

Set-SPEnterpriseSearchAdministrationComponent -SearchServiceInstance $Svr04


7.  Create and Configure Search Topology 02

$SrchTop01 = Get-SPEnterpriseSearchTopology -SearchApplication $SvcAppSrch

$SrchTop02 = New-SPEnterpriseSearchTopology -SearchApplication $SvcAppSrch -Clone -SearchTopology $SrchTop01


8.  Create the Search Admin Component

New-SPEnterpriseSearchAdminComponent –SearchTopology $SrchTop02 -SearchServiceInstance $Svr04


9.  Create Search Content Processing Components

New-SPEnterpriseSearchContentProcessingComponent –SearchTopology $SrchTop02 -SearchServiceInstance $Svr04

New-SPEnterpriseSearchContentProcessingComponent –SearchTopology $SrchTop02 -SearchServiceInstance $Svr03


10.  Create Analytics Processing Components

New-SPEnterpriseSearchAnalyticsProcessingComponent –SearchTopology $SrchTop02 -SearchServiceInstance $Svr04

New-SPEnterpriseSearchAnalyticsProcessingComponent –SearchTopology $SrchTop02 -SearchServiceInstance $Svr03


11.  Create Search Crawl Components

New-SPEnterpriseSearchCrawlComponent –SearchTopology $SrchTop02 -SearchServiceInstance $Svr04

New-SPEnterpriseSearchCrawlComponent –SearchTopology $SrchTop02 -SearchServiceInstance $Svr03


12.  Create Search Query Processing Components

New-SPEnterpriseSearchQueryProcessingComponent –SearchTopology $SrchTop02 -SearchServiceInstance $Svr04

New-SPEnterpriseSearchQueryProcessingComponent –SearchTopology $SrchTop02 -SearchServiceInstance $Svr03


13.  Create and Assign Index Partitions

New-SPEnterpriseSearchIndexComponent –SearchTopology $SrchTop02 -SearchServiceInstance $Svr04 -RootDirectory $IndexLocP -IndexPartition 0

New-SPEnterpriseSearchIndexComponent –SearchTopology $SrchTop02 -SearchServiceInstance $Svr03 -RootDirectory $IndexLocR -IndexPartition 0

New-SPEnterpriseSearchIndexComponent –SearchTopology $SrchTop02 -SearchServiceInstance $Svr03 -RootDirectory $IndexLocP -IndexPartition 1

New-SPEnterpriseSearchIndexComponent –SearchTopology $SrchTop02 -SearchServiceInstance $Svr04 -RootDirectory $IndexLocR -IndexPartition 1


14.  Activate Search Topology 02

Set-SPEnterpriseSearchTopology -Identity $SrchTop02


15.  Remove Search Topology 01

$SrchTop01 = Get-SPEnterpriseSearchTopology -SearchApplication $SvcAppSrch | where {($_.State) -eq "Inactive"}

Remove-SPEnterpriseSearchTopology -Identity $SrchTop01 -Confirm:$false


16.  Create Search Service Application Proxy

$SvcAppSrchPrxy = New-SPEnterpriseSearchServiceApplicationProxy -Name "SvcApp_SPEnterpriseSearchServiceApplication_01_Proxy_01" -SearchApplication $SvcAppSrch



Key Words
Search Service, Search Service Application, Search Topology.



References

ChandrasekarN (February, 2013).  SharePoint 2013 - Configuring Search Service Application and Topology using PowerShell.  Retrieved January 29, 2014 from http://blogs.msdn.com/b/chandru/archive/2013/02/19/sharepoint-2013-configuring-search-service-application-and-topology-using-powershell.aspx

Earp, S. (September, 2013).  Configuring SharePoint 2013 Search with PowerShell.  Retrieved January 27, 2014 from http://blogs.technet.com/b/ptsblog/archive/2013/09/19/configuring-sharepoint-2013-search-with-powershell.aspx

Harbar, S. (July, 2012).  Multi Tenancy with SharePoint 2013: What’s new and changed.  Retrieved January 29, 2014 from http://www.harbar.net/articles/sp2013mt.aspx

Klindt, T. (December, 2012).  Create SharePoint 2013 Search service application with PowerShell.  Retrieved January 29, 2014 from http://www.toddklindt.com/blog/Lists/Posts/Post.aspx?ID=378

Microsoft TechNet (2013).  Create and configure a Search service application in SharePoint Server 2013.  Retrieved January 27, 2014 from http://technet.microsoft.com/en-us/library/gg502597.aspx

Microsoft TechNet (2013).  Get-SPSearchService.  Retrieved January 27, 2014 from http://technet.microsoft.com/en-us/library/ee620539(v=office.14).aspx

Microsoft TechNet (2013).  Get-SPEnterpriseSearchServiceApplication.  Retrieved January 27, 2014 from http://technet.microsoft.com/en-us/library/ff608050.aspx

Microsoft TechNet (2013).  Get-SPEnterpriseSearchServiceInstance.  Retrieved January 27, 2014 from http://technet.microsoft.com/en-us/library/ff607740.aspx

Microsoft TechNet (2013).  Get-SPEnterpriseSearchTopology.  Retrieved January 27, 2014 from http://technet.microsoft.com/en-us/library/jj219578.aspx

Microsoft TechNet (2013).  Get-SPEnterpriseSearchQueryAndSiteSettingsServiceInstance.  Retrieved January 27, 2014 from http://technet.microsoft.com/en-us/library/ff608115.aspx

Microsoft TechNet (2013).  New-SPEnterpriseSearchServiceApplication.  Retrieved January 27, 2014 from http://technet.microsoft.com/en-us/library/ff607751.aspx

Microsoft TechNet (2013).  New-SPEnterpriseSearchServiceApplicationProxy.  Retrieved January 27, 2014 from http://technet.microsoft.com/en-us/library/ff607722.aspx

Microsoft TechNet (2013).  Set-SPEnterpriseSearchServiceApplication.  Retrieved January 27, 2014 from http://technet.microsoft.com/en-us/library/ff607974.aspx

Microsoft TechNet (2013).  Set-SPEnterpriseSearchTopology.  Retrieved January 27, 2014 from http://technet.microsoft.com/en-us/library/jj219653.aspx

Microsoft TechNet (2013).  Start-SPEnterpriseSearchQueryAndSiteSettingsServiceInstance.   Retrieved January 27, 2014 from http://technet.microsoft.com/en-us/library/ff607759.aspx

Microsoft TechNet (2013).  Start-SPEnterpriseSearchServiceInstance.  Retrieved January 27, 2014 from http://technet.microsoft.com/en-us/library/ff607852.aspx

Microsoft TechNet (2013).  Remove-SPEnterpriseSearchTopology.  Retrieved January 27, 2014 from http://technet.microsoft.com/en-us/library/jj219505.aspx

Ravikanth (April, 2013).  Creating and configuring a SharePoint 2013 search service application.  Retrieved January 29, 2014 from http://www.powershellmagazine.com/2013/04/29/creating-and-configuring-a-sharepoint-2013-search-service-application/

Monday, January 27, 2014

Solution for Site Collection Lock, Missing Site Settings Menu in SharePoint 2007




Description

Logged into a SharePoint 2007 Site Collection as a Site Collection Administrator with Full Control permission level, the Site Actions, Site Collection Settings menu is not present.  The reason for this is that the Site Collection has a lock on it and is in a "Read-Only" state.  In a case observed, this lock occured because a disk partition lacked adequate free space to complete a backup when new Site Collection backup process began.

Once the disk space and backup issues are corrected, an STSADM.exe command can be run from the Command Line that will put the Site Collection in a "Not Locked" state.  This will make the Site Collection Settings menu appear.

As explained below, the desired state is "Not Locked" but problem state is "Read-only":
  • Read-only - Blocks additions, updates, and deletions to the site collection.
  • Not Locked - Sets the site collection to an unlock status. This is the default setting.
(TechNet, 2008)


Solution

1) Open the CMD line (Start, Run, CMD)

2) Change Directory to Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\Bin directory

3) Run this command to unlock the Site Collection:
stsadm -o setsitelock -url http://hostname.domainname.com -lock none

4)  Run this command to verify the Site Collection is Not Locked (None):
stsadm -o getsitelock -url http://hostname.domainname.com

(Aquino, 2009)


Key Words
Site Settings missing, Site Collection Settings menu missing, Site Collection lock, Backup ran out of space. 



References

Aquino, F. (November, 2009).  Moss 2007 Site Action -> Site Settings have disappeared.  Retrieved January, 2014 from http://stackoverflow.com/questions/1809512/moss-2007-site-action-site-settings-have-disappeared

Microsoft TechNet (May, 2008).  Lock site collections (Office SharePoint Server).  Retrieved January 27, 2014 from http://technet.microsoft.com/en-us/library/cc508855(v=office.12).aspx

Followers