Tag Archives: PowerShell

My Users Don’t Like SharePoint because it is too slow!

This is Part 7 of my series on ‘My Users Don’t Like SharePoint…

As your SharePoint matures and grows, it can get considerably larger and complex. Content databases can grow to hundreds of gigs, search indexes grow larger, users rely more on Excel services, additional external business data is pulled in, some custom functionality is added, etc. All of this can impact performance when not implemented correctly.

I look at it like it’s a good sign: your users are using SharePoint! However, as your farm grows, you should monitor the farm and possibly reconfigure and add additional servers to the mix. The first step is to figure out why it’s slow.

It’s so slow, what can I do?!

There’s a lot of reasons SharePoint’s performance can dwindle, here’s a few. This is everything I could dream up, dealt with or heard about. Did I miss something? Leave a comment!

  • Is it designed properly? Check out Microsoft’s recommendations for hardware, software and farm architecture to cover the basics. If you’re running your entire farm on a single server, I’d start by adding some more servers. Check out the SharePoint 2010 Technical Diagrams for a great starting point: http://technet.microsoft.com/en-us/library/cc263199(v=office.14).aspx 
  • A very common slow issue is when SharePoint first wakes up. Since SharePoint is a .Net application running on IIS, the application pools need to spin up, compile all of the assemblies and serve up pages. This can take a few minutes when SharePoint initially starts up. In most cases, once you’re past this slow start up, SharePoint will continue to run smoothly throughout the day. One trick to avoid this slow start up is to keep SharePoint awake. There is a simple PowerShell script which you can schedule in Windows Tasks to run, and it’ll keep hitting your SharePoint sites, thereby keeping them awake. Check it out at http://spwakeuppowershell.codeplex.com.
  • Check the Task Manager on your servers. Simple enough, but it tells us a lot. Looking at the stats in Task Manager you can determine which services are taking up loads of RAM or are pinning the processor. If you’re seeing a lot of w3wp.exe processes, take a look at my PowerShell script which marries the w3wp with the process in SharePoint. This might help clarify things a little.

SharePoint's w3wp list

  • Sometimes a specific page or two may be loading slowly. Look into all of the web parts on the page. The more you load on a page, the more it has to do. Consider taking some off, or creating another page that can house some of the web parts.
  • Community Feedback: Thanks Marc! A large number of closed Web Parts, often on the home pages, will slow down a page. Every closed Web Part causes a small amount of processing overhead, and it can add up. To check this, add ?contents=1 to the page’s URL and remove any Web Parts which aren’t displayed. It can make a huge difference.
  • If all pages are doggy, and you have a custom design, it may be a good idea to look into the assets of the design: images, CSS and JavaScript files. If these aren’t sized correctly you might experience slow performance. There are applications available that can assess a page and tell you what’s slow and how large the assets are. I like to use YSlow, and add on for Chrome, gives some pretty neat stats:

SharePoint page stats using YSlow

    • One note about these types of page stats in SharePoint. There are going to be some elements you’re stuck with, namely this first result page. SharePoint has a long list of JavaScript files which are necessary and can’t be removed. These will always skew these stats. Fortunately, the files are minified (art of shrinking a file by removing wasted space) to minimize download time.
  • You should configure your content databases in SQL to handle the anticipated usage and traffic. See my older post which also talked about performance, and has more details on databases: Improving SharePoint’s Performance.
  • Your content databases can grow up to SQL’s limit, which is in the terabytes  It’s not a recommended practice as it does impede the performance of SharePoint. As your site collections grow, consider splitting off site collections to their own content databases. This allows you to manage each database individually, which will help improve performance. Check out this MSDN article on Moving sites between content databases.
  • Caching. Storing data closer to where you need it to help improve performance. Instead of sending SharePoint all the way back to the database to get some data, it can read a local cache instead. Check out MSDN article Plan for caching and performance.
  • Community Feedback: Thanks paslatek! There is a big performance issue when your server does not have internet access and it tries to validate certificates with clr.micosoft.com. I found this TechNet article which may help.
  • Community Feedback: Thanks mansi! Performance issues can be caused by BLOB Storage: since SharePoint saves all the files (documents, images, videos, etc.) in SQL Server in the form of unstructured objects known as Binary Large Objects (BLOBs). Having too many BLOBs can slow down SharePoint’s performance as they take lot of space and require SQL to process more. A solution to work around this issue is to store your BLOBs in a location other than your content database, which SQL refers to as RBS: Remote BLOB Storage. You can read more on this: Storing your SharePoint files outside of the database (RBS).
  • Update: Lists and libraries containing several thousand items can slow things down a lot too. Check out TechNet article Designing large lists and maximizing list performance.

Gah, I think that’s it for now. I’m sure there’s more, but these have been the primary reasons I’ve come across, and have seen some great improvements after applying. If I’m missing any, please leave a comment below.

Til next week, Happy SharePointing!

Advertisements

Use PowerShell to manipulate the values of a SharePoint choice field.

Thanks to Knoots for suggesting the idea for this post, from a comment on Using PowerShell to play with SharePoint Items.

Using PowerShell, we’re going to walk through handling a Choice field in a list. Specifically, this is a calendar list using the Category field. This may come in handy if you want to automate changing the values from another data source that BCS can’t connect to, or is too much work to get it to connect. I always prefer using SharePoint’s features, but sometimes we need to stretch it to make it work.

As a heads up, if you remove a value that someone has used in their item, the value will remain in the item UNTIL they come back in to edit the item. Since the value no longer exists in the option list, their value will be lost. However, if you have the option enabled on the field to specify their own value, then the previous value will be saved there.

Let’s get to it, open SharePoint 2010 Management Shell

$web = get-spweb http://site/web
$list = $web.lists["Calendar"]
$list

I like to run the $list just to ensure we have the list properly. Errors aren’t always displayed in PowerShell. Running $list should return the list name.

$choice = $list.fields["Category"]
$choice.choices

Again, make sure we have the right field, calling $choice.choices will list all of the current values. The Choices property is a StringCollection, so use typical commands to add/remove items.

Important. If you’re going to clear items then add items, or after any .update(), you’ll need to get the field again (type in $choice = $list.fields[“Category”] again) to access the new values properly.

remove all items

$choice.choices.clear()
$choice.update()

remove one item

$choice.choices.remove("Name")
$choice.update()

(remember get the field again to add items after clearing it)

add an array of choices

$choiceArray = @("Meeting","Work Hours","Business","Holiday","Get-together")
$choice.choices.addrange($choiceArray)
$choice.update()

add one at a time

$choice.choices.add("Name")
$choice.choices.add("Name Of Another")
$choice.update()

Happy SharePointing!

Troubleshooting a SharePoint 2010 Service Pack 1 install

I was handed a SharePoint 2010 farm with some weird issues going on, specifically around PowerPivot.

First thing I checked was the status of any updates. Sure enough, there was an upgrade available (as seen in Central Admin > Manage Servers in this Farm). My continual troubleshooting of PowerPivot is in this other post.

I jumped to the SharePoint Management Shell and ran

psconfig -cmd upgrade -inplace b2b -force

And much to my surprise (NOT) I ran into the following error

An exception of type Microsoft.SharePoint.Administration.SPUpdatedConcurrencyExc eption was thrown.  Additional exception information: An update conflict has occ urred, and you must re-try this action. The object SPUpgradeSession Name=Upgrade -20120329-125109-783 was updated by domain\sp_admin, in the PSCONFIG (8484) process, on machine SHAREPOINT.  View the tracing log for more information abou t the conflict.

and from the upgrade log

03/29/2012 12:52:08  12  ERR            Task upgrade has failed with an unknown exception 03/29/2012 12:52:08  12  ERR            Exception: Microsoft.SharePoint.Administration.SPUpdatedConcurrencyException: An update conflict has occurred, and you must re-try this action. The object SPUpgradeSession Name=Upgrade-20120329-125109-783 was updated by domain\sp_admin, in the PSCONFIG (8484) process, on machine SHAREPOINT.  View the tracing log for more information about the conflict.    at Microsoft.SharePoint.Administration.SPConfigurationDatabase.StoreObject(SPPersistedObject obj, Boolean storeClassIfNecessary, Boolean ensure)    at Microsoft.SharePoint.Administration.SPConfigurationDatabase.Microsoft.SharePoint.Administration.ISPPersistedStoreProvider.PutObject(SPPersistedObject persistedObject, Boolean ensure)    at Microsoft.SharePoint.Administration.SPPersistedObject.BaseUpdate()    at Microsoft.SharePoint.Upgrade.SPUpgradeSession.Update()    at Microsoft.SharePoint.Upgrade.SPUpgradeSession.ContinueOnLocalThread(Guid id, Boolean consoleOutput)    at Microsoft.SharePoint.Upgrade.SPManager.ContinueSessionOnLocalThread(Guid id, Boolean consoleOutput)    at Microsoft.SharePoint.PostSetupConfiguration.UpgradeTask.Run()    at Microsoft.SharePoint.PostSetupConfiguration.TaskThread.ExecuteTask()

After some research online, I came across KB939308, which instructs to clear the timer job cache. I did that, and received the same error. I cleared the timer cache again and then it progressed past this error. I’ve had this “clearing of the timer cache twice” occur elsewhere as well, for some reason my initial attempt at clearing the timer cache never works well for me.

Rerunning the psconfig command progressed a little further than last time, but then a new error occurred.

An exception of type Microsoft.SharePoint.PostSetupConfiguration.PostSetupConfig urationTaskException was thrown.  Additional exception information: The upgrade command is invalid or a failure has been encountered. Failed to upgrade SharePoint Products.

and from the upgrade logs

03/29/2012 13:28:54  12  ERR            An exception of type Microsoft.SharePoint.PostSetupConfiguration.PostSetupConfigurationTaskException was thrown.  Additional exception information: The upgrade command is invalid or a failure has been encountered. Failed to upgrade SharePoint Products. Microsoft.SharePoint.PostSetupConfiguration.PostSetupConfigurationTaskException: Exception of type ‘Microsoft.SharePoint.PostSetupConfiguration.PostSetupConfigurationTaskException’ was thrown.    at Microsoft.SharePoint.PostSetupConfiguration.UpgradeTask.Run()    at Microsoft.SharePoint.PostSetupConfiguration.TaskThread.ExecuteTask()

I found a lot of posts online, this issue is due to service applications’ associated services being stopped. I reviewed this and saw that most of the services were stopped on the server. I confirmed what was needed and removed the service applications for what was not needed, and started the services for what was needed.

Ran psconfig again, went a little further but ended up getting the same errors again. I then checked out the Upgrade-DATE-error.log file (didn’t see that one before) and that revealed a lot.

[OWSTIMER] [UpgradeWebConfig (4.0.2.0)] [INFO] [3/29/2012 2:31:41 PM]: Microsoft.SharePoint.Administration.SPIisWebSite [OWSTIMER] [UpgradeWebConfig (4.0.2.0)] [ERROR] [3/29/2012 2:31:41 PM]: Application Web Config for this IIS site (52113925) could not be found at C:\inetpub\wwwroot\wss\VirtualDirectories\site.domain.local80\web.config. [OWSTIMER] [UpgradeWebConfig (4.0.2.0)] [INFO] [3/29/2012 2:31:41 PM]: Microsoft.SharePoint.Administration.SPIisWebSite [OWSTIMER] [UpgradeWebConfig (4.0.2.0)] [ERROR] [3/29/2012 2:31:41 PM]: Unexpected error updating SafeControl entries. Object reference not set to an instance of an object. [OWSTIMER] [UpgradeWebConfig (4.0.2.0)] [INFO] [3/29/2012 2:31:41 PM]: Microsoft.SharePoint.Administration.SPIisWebSite [OWSTIMER] [UpgradeWebConfig (4.0.2.0)] [ERROR] [3/29/2012 2:31:41 PM]: Application Web Config for this IIS site (52113925) could not be found at C:\inetpub\wwwroot\wss\VirtualDirectories\site.domain.local80\web.config. [OWSTIMER] [SPIisWebSiteWssSequence] [INFO] [3/29/2012 2:31:42 PM]: Microsoft.SharePoint.Administration.SPIisWebSite [OWSTIMER] [SPIisWebSiteWssSequence] [ERROR] [3/29/2012 2:31:42 PM]: Action 4.0.2.0 of Microsoft.SharePoint.Upgrade.SPIisWebSiteWssSequence failed. [OWSTIMER] [SPIisWebSiteWssSequence] [INFO] [3/29/2012 2:31:42 PM]: Microsoft.SharePoint.Administration.SPIisWebSite [OWSTIMER] [SPIisWebSiteWssSequence] [ERROR] [3/29/2012 2:31:42 PM]: Exception: Object reference not set to an instance of an object. [OWSTIMER] [SPIisWebSiteWssSequence] [INFO] [3/29/2012 2:31:42 PM]: Microsoft.SharePoint.Administration.SPIisWebSite [OWSTIMER] [SPIisWebSiteWssSequence] [ERROR] [3/29/2012 2:31:42 PM]: at Microsoft.SharePoint.Upgrade.UpgradeWebConfig.EnsureAssemblyRedirection(XmlDocument webConfig) at Microsoft.SharePoint.Upgrade.UpgradeWebConfig.Upgrade() at Microsoft.SharePoint.Upgrade.SPActionSequence.Upgrade() [OWSTIMER] [SPUpgradeSession] [INFO] [3/29/2012 2:31:42 PM]: Microsoft.SharePoint.Administration.SPIisWebSite [OWSTIMER] [SPUpgradeSession] [ERROR] [3/29/2012 2:31:42 PM]: Upgrade [Microsoft.SharePoint.Administration.SPIisWebSite] failed. [OWSTIMER] [SPUpgradeSession] [INFO] [3/29/2012 2:31:42 PM]: Microsoft.SharePoint.Administration.SPIisWebSite [OWSTIMER] [SPUpgradeSession] [ERROR] [3/29/2012 2:31:42 PM]: Inner Exception: Object reference not set to an instance of an object. [OWSTIMER] [SPUpgradeSession] [INFO] [3/29/2012 2:31:42 PM]: Microsoft.SharePoint.Administration.SPIisWebSite [OWSTIMER] [SPUpgradeSession] [ERROR] [3/29/2012 2:31:42 PM]: at Microsoft.SharePoint.Upgrade.UpgradeWebConfig.EnsureAssemblyRedirection(XmlDocument webConfig) at Microsoft.SharePoint.Upgrade.UpgradeWebConfig.Upgrade() at Microsoft.SharePoint.Upgrade.SPActionSequence.Upgrade() [OWSTIMER] [SPUpgradeSession] [INFO] [3/29/2012 2:31:42 PM]: Microsoft.SharePoint.Administration.SPIisWebSite [OWSTIMER] [SPUpgradeSession] [ERROR] [3/29/2012 2:31:42 PM]: Exception: Action 4.0.2.0 of Microsoft.SharePoint.Upgrade.SPIisWebSiteWssSequence failed. [OWSTIMER] [SPUpgradeSession] [INFO] [3/29/2012 2:31:42 PM]: Microsoft.SharePoint.Administration.SPIisWebSite [OWSTIMER] [SPUpgradeSession] [ERROR] [3/29/2012 2:31:42 PM]: at Microsoft.SharePoint.Upgrade.SPActionSequence.Upgrade() at Microsoft.SharePoint.Upgrade.SPUpgradeSession.Upgrade(Object o, Boolean bRecurse) [OWSTIMER] [SPUpgradeSession] [INFO] [3/29/2012 2:31:42 PM]: No context object [OWSTIMER] [SPUpgradeSession] [ERROR] [3/29/2012 2:31:42 PM]: Upgrade Timer job is exiting due to exception: Microsoft.SharePoint.Upgrade.SPUpgradeException: Action 4.0.2.0 of Microsoft.SharePoint.Upgrade.SPIisWebSiteWssSequence failed. —> System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SharePoint.Upgrade.UpgradeWebConfig.EnsureAssemblyRedirection(XmlDocument webConfig) at Microsoft.SharePoint.Upgrade.UpgradeWebConfig.Upgrade() at Microsoft.SharePoint.Upgrade.SPActionSequence.Upgrade() — End of inner exception stack trace — at Microsoft.SharePoint.Upgrade.SPActionSequence.Upgrade() at Microsoft.SharePoint.Upgrade.SPUpgradeSession.Upgrade(Object o, Boolean bRecurse) at Microsoft.SharePoint.Upgrade.SPUpgradeSession.ReflexiveUpgrade(Object o, Boolean bRecurse) at Microsoft.SharePoint.Upgrade.SPUpgradeSession.Upgrade(Object o, Boolean bRecurse) at Microsoft.SharePoint.Administration.SPPersistedUpgradableObject.Upgrade(Boolean recursively) at Microsoft.SharePoint.Upgrade.SPUpgradeSession.ReflexiveUpgrade(Object o, Boolean bRecurse) at Microsoft.SharePoint.Upgrade.SPUpgradeSession.Upgrade(Object o, Boolean bRecurse) at Microsoft.SharePoint.Administration.SPPersistedUpgradableObject.Upgrade(Boolean recursively) at Microsoft.SharePoint.Upgrade.SPUpgradeSession.ReflexiveUpgrade(Object o, Boolean bRecurse) at Microsoft.SharePoint.Upgrade.SPUpgradeSession.Upgrade(Object o, Boolean bRecurse) at Microsoft.SharePoint.Administration.SPPersistedUpgradableObject.Upgrade(Boolean recursively) at Microsoft.SharePoint.Upgrade.SPUpgradeSession.ReflexiveUpgrade(Object o, Boolean bRecurse) at Microsoft.SharePoint.Upgrade.SPUpgradeSession.Upgrade(Object o, Boolean bRecurse) at Microsoft.SharePoint.Administration.SPPersistedUpgradableObject.Upgrade(Boolean recursively) at Microsoft.SharePoint.Upgrade.SPUpgradeSession.ReflexiveUpgrade(Object o, Boolean bRecurse) at Microsoft.SharePoint.Upgrade.SPUpgradeSession.Upgrade(Object o, Boolean bRecurse) at Microsoft.SharePoint.Administration.SPUpgradeJobDefinition.Execute(Guid targetInstanceId)

As you’ll see it mentions the web.config file for the web application, and that it cannot be found. I went and looked myself and discovered that was true, the web.config file was no longer present. After further review, it appears that most of the web applications on this server were stopped in IIS, and have been moved to another farm. This farm remained as a PowerPivot play ground only.

I spoke with the customer to confirm the sites were moved, and I also ran this quick script that loops through the web applications and pings each one, confirming the IP address was not this farm’s IP.

$webapps = Get-SPWebApplication
foreach($web in $webapps)
{
ping $web.url.replace(“http:”,””).replace(“https”,””).replace(“/”,””) -n 1
}

One web app came back as still on this server, and it was the web app used to run PowerPivot. I went ahead and deleted all of the other web applications and reran psconfig.

SUCCESS!

The upgrade finally finished and the server status was back to No Action Required. Now on to troubleshooting PowerPivot.

Troubleshooting PowerPivot on SharePoint

I recently had a battle with a SharePoint 2010 farm and PowerPivot. Customer reported “weird things are going on”, and that was an understatement. This blog post is a transcript of the errors and what I did to resolve them. I hope this helps someone else in the future.

First off, I checked the server’s status (Central Administration > Manage Servers in this Farm) and saw that an upgrade was available. I tackled the upgrade first.

Now that I know my farm is happier, I continued dealing with PowerPivot. First thing I did was go to the service application to view if it was even running properly. When I clicked on the service application I got a nice generic error, but the ULS logs told me more

03/30/2012 12:37:07.88 w3wp.exe (0x2B10) 0x23C4 SharePoint Server Logging Correlation Data xmnv Medium Result=Id=OpenWorkbookAccessDenied; Microsoft.Office.Excel.Server.CalculationServer.FileOpenException: You do not have permissions to open this file. —> Microsoft.Office.Excel.Server.Host.HostFileException at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.GetSPFile(IClaimsIdentity claimsIdentity) at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.CheckForPermissions(IClaimsIdentity claimsIdentity) at Microsoft.Office.Excel.Server.MossHost.MossHostHelperMethods.<>c__DisplayClass4.<TryExecuteWithUserContext>b__2() at Microsoft.Office.Excel.Server.MossHost.MossHostHelperMethods.WithEnsureClaimsIdentitySetOnThread(IClaimsIdentity claimsIdentity, MethodToRun action) at Microsoft.Office.Excel.Server.MossHost.MossHostHelperMethods.TryExecuteWithUserConte 6a104842-30b5-4ca9-b27a-e1f454312700
03/30/2012 12:37:07.88 w3wp.exe (0x2B10) 0x23C4 Excel Services Application Excel Calculation Services ech0 Medium ExcelService.PostProcessRequest: web method: OpenWorkbook, got exception Id=OpenWorkbookAccessDenied; Microsoft.Office.Excel.Server.CalculationServer.FileOpenException: You do not have permissions to open this file. —> Microsoft.Office.Excel.Server.Host.HostFileException at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.GetSPFile(IClaimsIdentity claimsIdentity) at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.CheckForPermissions(IClaimsIdentity claimsIdentity) at Microsoft.Office.Excel.Server.MossHost.MossHostHelperMethods.<>c__DisplayClass4.<TryExecuteWithUserContext>b__2() at Microsoft.Office.Excel.Server.MossHost.MossHostHelperMethods.WithEnsureClaimsIdentitySetOnThread(IClaimsIdentity claimsIdentity, MethodToRun action) at Microsoft.Office.E… 6a104842-30b5-4ca9-b27a-e1f454312700
03/30/2012 12:37:07.88* w3wp.exe (0x2B10) 0x23C4 Excel Services Application Excel Calculation Services ech0 Medium …xcel.Server.MossHost.MossHostHelperMethods.TryExecuteWithUserContext(IIdentity userIdentity, Action`1 method) at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.Init(Uri uri, Guid requestSiteId, IIdentity currentIdentity, IExcelServerDocumentContext documentContext, FileLoaderHostInfo& outFileLoaderHostInfo) at Microsoft.Office.Excel.Server.CalculationServer.SharePointFileLoader.Init(Uri uri, Guid requestSiteId, IExcelServerDocumentContext documentContext) — End of inner exception stack trace — at Microsoft.Office.Excel.Server.CalculationServer.SharePointFileLoader.Init(Uri uri, Guid requestSiteId, IExcelServerDocumentContext documentContext) at Microsoft.Office.Excel.Server.CalculationServer.FileLoader.CreateFromTrustedLocationAndInit(Uri uri, TrustedLoca… 6a104842-30b5-4ca9-b27a-e1f454312700
03/30/2012 12:37:07.88* w3wp.exe (0x2B10) 0x23C4 Excel Services Application Excel Calculation Services ech0 Medium …tion trustedLocationSettings, Guid requestSiteId, IExcelServerDocumentContext documentContext) at Microsoft.Office.Excel.Server.CalculationServer.BaseWorkbookManager.GetBaseWorkbookAndMarkUsedAsync(AsyncHandler`1 callback, Object userState, Request request, Uri uri, Boolean newWorkbook, Boolean useCollection, Boolean loadedOnDemand) at Microsoft.Office.Excel.Server.CalculationServer.Session.OpenWorkbookAsync(AsyncHandler`1 callback, Object userState, Request request, Uri url, Boolean loadedOnDemand) at Microsoft.Office.Excel.Server.CalculationServer.Operations.OpenWorkbookOperation.StartExecution() at Microsoft.Office.Excel.Server.CalculationServer.Operations.Operation.RunOperationAsync() at Microsoft.Office.Excel.Server.CalculationServer.Operations.OperationSite.Prepar… 6a104842-30b5-4ca9-b27a-e1f454312700
03/30/2012 12:37:07.88* w3wp.exe (0x2B10) 0x23C4 Excel Services Application Excel Calculation Services ech0 Medium …eComplete(PrepareAsyncArgs args) 6a104842-30b5-4ca9-b27a-e1f454312700
03/30/2012 12:37:07.88 w3wp.exe (0x2B10) 0x23C4 Excel Services Application Excel Calculation Services 2018 Information Failed to load ‘http://SHAREPOINT:25557/1033/Server%20Health.xlsx&#8217; with error: ‘Access is denied to the specified file.’. [Session: 1.V21.67MiloLbpn+Z+LMu/ertG90.5.en-US5.en-US73.+0300#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.c63405b0-88db-466c-a8d9-f2d11f672c6d1.N User: 0#.w|domain\sp_admin] 6a104842-30b5-4ca9-b27a-e1f454312700
03/30/2012 12:37:07.88 w3wp.exe (0x2B10) 0x23C4 Excel Services Application Excel Calculation Services ech8 Medium ExcelService.PostProcessRequest: Excel Server Recoverable Exception: Id=OpenWorkbookAccessDenied; Microsoft.Office.Excel.Server.CalculationServer.FileOpenException: You do not have permissions to open this file. —> Microsoft.Office.Excel.Server.Host.HostFileException at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.GetSPFile(IClaimsIdentity claimsIdentity) at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.CheckForPermissions(IClaimsIdentity claimsIdentity) at Microsoft.Office.Excel.Server.MossHost.MossHostHelperMethods.<>c__DisplayClass4.<TryExecuteWithUserContext>b__2() at Microsoft.Office.Excel.Server.MossHost.MossHostHelperMethods.WithEnsureClaimsIdentitySetOnThread(IClaimsIdentity claimsIdentity, MethodToRun action) at Microsoft.Office.Excel… 6a104842-30b5-4ca9-b27a-e1f454312700
03/30/2012 12:37:07.88* w3wp.exe (0x2B10) 0x23C4 Excel Services Application Excel Calculation Services ech8 Medium ….Server.MossHost.MossHostHelperMethods.TryExecuteWithUserContext(IIdentity userIdentity, Action`1 method) at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.Init(Uri uri, Guid requestSiteId, IIdentity currentIdentity, IExcelServerDocumentContext documentContext, FileLoaderHostInfo& outFileLoaderHostInfo) at Microsoft.Office.Excel.Server.CalculationServer.SharePointFileLoader.Init(Uri uri, Guid requestSiteId, IExcelServerDocumentContext documentContext) — End of inner exception stack trace — at Microsoft.Office.Excel.Server.CalculationServer.SharePointFileLoader.Init(Uri uri, Guid requestSiteId, IExcelServerDocumentContext documentContext) at Microsoft.Office.Excel.Server.CalculationServer.FileLoader.CreateFromTrustedLocationAndInit(Uri uri, TrustedLocation… 6a104842-30b5-4ca9-b27a-e1f454312700
03/30/2012 12:37:07.88* w3wp.exe (0x2B10) 0x23C4 Excel Services Application Excel Calculation Services ech8 Medium … trustedLocationSettings, Guid requestSiteId, IExcelServerDocumentContext documentContext) at Microsoft.Office.Excel.Server.CalculationServer.BaseWorkbookManager.GetBaseWorkbookAndMarkUsedAsync(AsyncHandler`1 callback, Object userState, Request request, Uri uri, Boolean newWorkbook, Boolean useCollection, Boolean loadedOnDemand) at Microsoft.Office.Excel.Server.CalculationServer.Session.OpenWorkbookAsync(AsyncHandler`1 callback, Object userState, Request request, Uri url, Boolean loadedOnDemand) at Microsoft.Office.Excel.Server.CalculationServer.Operations.OpenWorkbookOperation.StartExecution() at Microsoft.Office.Excel.Server.CalculationServer.Operations.Operation.RunOperationAsync() at Microsoft.Office.Excel.Server.CalculationServer.Operations.OperationSite.PrepareCom… 6a104842-30b5-4ca9-b27a-e1f454312700
03/30/2012 12:37:07.88* w3wp.exe (0x2B10) 0x23C4 Excel Services Application Excel Calculation Services ech8 Medium …plete(PrepareAsyncArgs args) 6a104842-30b5-4ca9-b27a-e1f454312700

I couldn’t find much on this error online so I went ahead and deleted the service application. I then tried to add a new one back but received another error

Could not load type ‘Microsoft.AnalysisServices.SharePoint.Integration.GeminiCreate’

I wasn’t pleased to see this one. I checked the solutions (Central Admin > System Settings > Manage farm solution) and confirmed both powerpivotfarm.wsp and powerpivotwebapp.wsp were both deployed. I retracted both solutions and then repaired the SQL installation for POWERPIVOT instance (Control Panel > Programs and Features, select SQL Server. During the repair process, select the instance for POWERPIVOT).

I deployed the two solutions, and had to deploy the powerpivotwebapp.wsp a second time to include Central Admin. Tried to add the service application again and it worked.

I logged into the PowerPivot site and was able to open up files and use the PowerPivot functionality.

Using PowerShell to access SharePoint Sites, Users, and Groups

In this post I am going to dive into some of the basics of accessing your SharePoint sites using PowerShell. This is a pretty basic concept, but something that I think is worth knowing. Understanding the basics allows us to dive deeper into the more complex stuff.

So let’s start! On one of the SharePoint servers, open your SharePoint 2010 Management Shell (Start > All Programs > Microsoft SharePoint 2010 Products). You may have to right click on the app and select Start as Administrator to get the correct permissions.

PowerShell Window

You will get a nice black window, type in cd\ to get to the root. This gives us a little more room to work with. At any time, you can type in cls to clear the screen, this is helpful to keep your eyes focused.

First things first, let’s get a site collection. Type in the following, followed by the Enter key

$site = Get-SPSite http://yoursitename

Now the $site object is your site collection. If it worked correctly, you’ll just be returned to the command prompt

Get-SPSite

Let’s make sure you have the right site, so lets check the URL. Type in the following and press enter after

$site.URL

And that should return your address to the site collection. Want to see all webs in your site? Type in

$site.allwebs

SPSite all webs

Pretty neat eh? You can get the site owner now

$site.owner

SPSite site owner

You can explore the site object and all of its properties and methods by referencing MSDN article http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spsite_properties.aspx .

Initially we used the Get-SPSite command to get a site. If you leave the address off, and just type in get the command, we can get all sites back

get-spsite

Get-SPSite

Using the previous reference to MSDN, we can add additional properties for a nice report. Let’s pull all site collections with their associated content database.

get-spsite | format-table -property URL,contentdatabase

Get-SPSite properties

And you’ll see all my site collections are in the same database.

Ok, back to our first $site. Now let’s get the root web and check the name.

$root = $site.rootweb
$root.title

Get root web

You just got your first site! Good job! There’s a slightly faster way to get a website. Instead of getting the site then getting the web, you can just call Get-SPWeb http://youraddress, and that will return the web for you.

So if you want to grab a subsite, you would call

$web = Get-SPWeb http://address/site/site/site

Now that you have a web, you can explore it’s properties and find out some more information about it. Check out MSDN article: http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spweb_properties.aspx.

Back to our $root. Let’s get a list of all users in the web.

$root.allusers

Next, let’s see who’s a site admin, and whether or not any of these users are domain groups.

$root.allusers | format-table -property displayname, issiteadmin, isdomaingroup

The additional properties, issiteadmin, isdomaingroup, are from the SPUser object (see http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spuser_properties.aspx for more detail).

Let’s check out the groups of the site.

$root.groups | format-table -property name, owner, users

If you run just $root.groups (try it) it’ll dump out a lot of information at once. Formatting and selecting our data makes it easier to work with.

The above results tells us the groups of the site, and who owns it, and a sneak peak of the users within the group. Let’s explore one group to see all of the users in it.

$group = $root.groups["Sideswipe Members"]
$group.users

To get a user, you need to know the user’s ID or email address. To find this info, you can add the properties to the above command

$group.users | format-table -property displayname, id, email

No, my users do not have email addresses, so I’ll have to use the ID. Either way, you would use

$user = $group.users.getbyemail("emailaddress")

or

$user = $group.users.getbyid(17)

Confirm you have the right users

$user.displayname

This was a very top level overview of accessing some of SharePoint’s objects. From here, now that you know how to get some of the common objects, you can query some more, update values and properties, add users to groups, etc. Microsoft’s MSDN site is extremely helpful in exploring these objects.

Please leave a comment below if you want to see something more specific.