SharePoint 2010 Office Web Apps

Steps here: http://sharepointgeorge.com/2010/installing-office-web-apps-for-sharepoint-2010/

But remember to read this first on the risks: http://sharepointconnoisseur.blogspot.com/2011/04/risk-to-install-microsoft-office-web.html

I guess it is safer to do it during a managed downtime / schedule outage window.

SharePoint 2010 Survey Issue

My user complained about an issue yesterday. Users not able to submit responses to her survey.

Below is the error.



When I checked the survey question's setting, I found the “Enforce unique values:” was set to Yes. I changed to No and issue was resolved.



 

SharePoint 2010 SQL Queries To Get Largest Documents

Below are queries that we can use to list down the top 100 largest documents in a SharePoint 2010 content DB

1. Query to list 100 largest document

Select top 100 D.DirName, D.LeafName, D.Size
From <content db name>.dbo.AllDocs D With (NOLOCK)
Order by D.Size desc

2. If we need to filter based on a specific site collections, we can use query below

Select top 100 D.DirName, D.LeafName, D.Size
from <content db name>.dbo.AllDocs D With (NOLOCK)
join <content db name>.dbo.Webs W With (NOLOCK)
on D.SiteId=W.SiteId
where W.FullUrl='<url>'
order by D.Size Desc

3. To further filter in a specific file extension (in this case its avi), we can use the query below

select top 100 D.DirName, D.LeafName, D.Size
from <content db name>.dbo.AllDocs D With (NOLOCK)
join <content db name>.dbo.Webs W With (NOLOCK)
on D.SiteId=W.SiteId
where W.FullUrl='<url>'
and D.Extension = 'avi'
order by D.Size Desc

 

4. Total document size. You need to cast as bigint , otherwise most likely to get  arithmetic overflow

select D.Extension, SUM(CAST(D.Size as BIGINT)) AS [Total Size]
<content db name>.dbo.AllDocs D With (NOLOCK)
group by D.Extension
order by [Total Size] desc

 

Calculated Field Formulas

MSDN article for calculated field formulas: http://msdn.microsoft.com/en-us/library/bb862071.aspx

Multiple views in InfoPath 2010 forms

I have not used InfoPath much before until recently when I was asked to proof-of-concept (POC) a approval form in SharePoint. So I explored SharePoint 2010 for providing storage and routing engine (workflow) and InfoPath 2010 for designing the form.  Contrary to my initial expectation, I was actually pleasantly surprised with what I learned about InfoPath 2010. There are a few cool features that I find very useful for form designing. One of them is Views.

We can create multiple views for the same form. In my case, I created a form submitter view, approver view, add approver view and Watcher view. The first 3 are self-explanatory. The Watcher view is a read-only and will be activated when the form has been fully approved or being viewed by persons other than the original form submitter or approvers.

To create a view, you have to click on the Page Design tab and click New View. Once, the views are created, we can use the rules to activate them accordingly. In my case, I created rules during form load. To get there, Click on the Data tab and choose Form Load. On the Rules window, create rules and add conditions and actions ( = switch views).

In this example, Submitter view will be activated/shown when the current user is the form submitter. By the way, you can also switch to another view based on a button click.



Have fun with InfoPath 2010 !!.

Error while visual upgrade

After mounting MySites DB, we performed the visual upgrade using the commands below:

$webapp = Get-SPWebApplication <web apps url>
foreach ($s in $webapp.sites)
{$s.VisualUpgradeWebs() }

but encountered the below error

Exception calling "VisualUpgradeWebs" with "0" argument(s): "Access is denied.
(Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))"
At line:2 char:22
+ {$s.VisualUpgradeWebs <<<< () }
+ CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException

This is because at least one or more of the site collections are in read-only.

To find out which ones, run the commands below

$webapp = Get-SPWebApplication <web apps url>
foreach ($s in $webapp.sites)
{$s | Get-SPSite  | Where {$_.readonly -eq "true" }} ; readonly is the column name !!


We then need to unlock those sites

Set-SPSite -Identity <site collection url> -LockState unlock

Manual creation of a MySite Site Collection

It just happens that sometimes we need to do something out of the normal process. In this case, I had to manually create a MySite site collection for a user.

Manual creation using UI doesn't give any error message but it doesn't work. Site was created & the structure in place but when I click My Contents (content if for a different user), nothing happens.

So I tried using PowerShell. Below is the code that I used, which was adapted from another blog post.

$mysiteHostUrl = "<your MySite Host URL>"
$mysite = Get-SPSite $mysiteHostUrl
$context = [Microsoft.Office.Server.ServerContext]::GetContext($mysite)
$upm =  New-Object Microsoft.Office.Server.UserProfiles.UserProfileManager($context)
$AllProfiles = $upm.GetEnumerator()
foreach($profile in $AllProfiles)
{
   if ($profile.DisplayName -like "User Name")
   {    
      if($profile.PersonalSite -eq $Null)
      {
           write-host "Creating personal site for ", $profile.DisplayName
           $profile.CreatePersonalSite()
           write-host "Personal site created"
      }
      else
      {
           write-host $profile.DisplayName ," has already personal site"
      }
   }
}
$mysite.Dispose();

A Few important notes:

1. Initially, when I test this in my dev environment, the line below returned null all the time even though the personal site exist.

$profile.PersonalSite

I guess something is wrong with the User Profile Service (UPS). When I tried the code in another environment, it returned the expected values. So we need to verify first if the our UPS is working fine before attempting to run the code.

2. If line below gives you an error,

$upm =  New-Object Microsoft.Office.Server.UserProfiles.UserProfileManager($context)

give the account you are running the powershell full control in 2 places - see below



Code above was adapted from: http://blog.bugrapostaci.com/2011/10/03/create-all-users-personal-site-via-powershell-script-sharepoint-2010/