SharePoint 2010 – Unable to edit cell in Datasheet view – “This cell is read only”

When I try to edit the excel in SharePoint 2010 list in Datasheet view, I see an error saying that “The Selected Cells are Read-only” as shown the image below.

• I have verified user Permissions to the list. User Having Full control permissions to the list
• Checked-out by any one (It is not document library)
• Any Attachments having read-only – NO
• Is current column is default system column? (Like Created, Modified Etc..) – NO

After checking all these details, I found Microsoft Support article about the fix. We have Content Approval enabled in the list and that is blocking DataSheet View edit. By Turning Off the Content Approval, We can fix the error.

Here are the steps to turn off content approvals,

• In the List select Settings
• Select List Settings
• Select Versioning Settings
• In the Content Approval section select No for "Require content approval for submitted items"

Now we can able to edit the items in DataSheet view.

Hope this helps.

SharePoint User Display issue: Display name incorrect in one site collection

Recently I got an issue in my SharePoint 2013 production environment. One of the user display name not showing properly. Instead of his name, it is showing one of the employee who left the organization. By checking the user details in AD we got the details. AD team updated two emails for one user. One is his own email and other email is one of the employee who left the company. We tracked the issue and updated the profile AD and ran User Profile Sync. Everything worked well till now.

But user still having the issue in one site, his display name not showing correctly. Instead of his name, it is showing employee who left the organization. We are seeing this issue only for one site collection. Tried different approaches like deleting user permissions in the site through UI and using PowerShell commands. Nothing worked.

After Googling the issue, we got the resolution. We are seeing that issue due caching in SharePoint site collection. When I remove and add the data in SharePoint site group, SharePoint site collection user details from all users located in <Site URL>/_layouts/people.aspx?MembershipGroupId=0

To fix the issue, we need to remove the user from all users list and read it to the list. But before that we need to check all the user permissions in the site (including all the groups and individual permissions) and need to re add all the places. 

Hope this helps.

Copy-SPSite: Rename/Recreate SharePoint site collection

Most of my clients had a requirement to rename SharePoint site collection in all SharePoint versions including SharePoint 200, 2010 and 2013. To do that we have to take the existing site collection backup, Delete the site collection and Restore it new site collection. If anything happens in this process, we are gone. Nothing left.

But in SharePoint 2013 we got new command, Copy-SPSite command allows us to copy the site collection. This option allows us to duplicate the site collection to new site collection. We have options to specify the destination DB.

Here is the syntax for Copy-SPSite command,

Copy-SPSite Source Site URL [-DestinationDatabase Destination DB] -TargetUrl Target Site URL

We can use this command to copy the site collection in same content DB. Copy-SPSite command will not work for copy the site collection in other content databases. To do this we have to follow backup and restore process.

We can copy host named site collections using Copy-SPSite command.

Copy-SPSite -Identity Source Host Named Site collection URL -TargetUrl Destination Host Named Site collection URL -HostHeaderWebApplication Host Header web application URL

Before that we need to run Remove-SPSiteUrl command as below.

Remove-SPSiteURL -URL Host named Site collection URL

SharePoint 2013 error – Site stuck up in Read-only mode after interrupted backup

While working with SharePoint 2013 restore and backup I got interesting error in one of my host named site collection (I hope this is generic issue, not specific to host named site collection) saying as shown the image below.

When I try to fix this issue from SharePoint central administration quotas, all the options are disabled and unable to change anything as shown the image below.

By going through some of the blogs, I got the issue. In SharePoint 2013, we are having Manintanence Mode property for SPSite makes SharePoint site is undergoing a Maintenance & is read only. We can set this SharePoint site when content DB is in read only state, or when SharePoint site upgrade, moving SharePoint site in process.

We can fix this error by clearing ClearMaintenanceMode method in SpSiteAdministration object. We can do this by running following command,

$Site = new-object Microsoft.SharePoint.Administration.SPSiteAdministration(SITE URL)  $Site.ClearMaintenanceMode()

This fixed my issue. Hope this helps.

SharePoint – Back to basics – SPLongOperation for lengthy operations

We have many hidden features in SharePoint in look and feel in Out of Box as well as custom. SPLongOperation One of the hidden feature in custom coding side. This feature exists from SharePoint but many of the users may not be aware of this. SPLongOpearation allows us to inform end users on processing screen while operating lengthy operations like moving documents from one site other, deleting sites etc... And at the end we can have options to close/ redirect the page.

Here is the sample code on usage of SPLongOperation,

               try{

                              using (SPLongOperation longOpearation = new SPLongOperation(this.Page))

                              {

                                             longOpearation.LeadingHTML = "Title of the long operation";//title message

                                             longOpearation.TrailingHTML = "this will take few sec”//sub message

                                              longOpearation.Begin();

                                             //Code start

                                             //Write the code that to be processed in long operation

                                             //code ends

                                               HttpContext context = HttpContext.Current;

                                             if (context.Request.QueryString["IsDlg"] != null)

                                             {

                                                            context.Response.Write("<script type='text/javascript'>window.frameElement.commitPopup();</script>");

                                                            context.Response.Flush();

                                                            context.Response.End();

                                              }

                                              else

                                              {

                                                             string url = SPContext.Current.Web.Url;

 longOpearation.End(url, SPRedirectFlags.CheckUrl, context, string.Empty);

                                             }

                              }

               }

               catch (Exception ex)

              {

                              SPUtility.TransferToErrorPage(ex.ToString());//transfores us to the error page with error message

               }

In the Begin and End methods for long operation we can have different overloaded methods. By using SPLongOperation.Begin method (String, String, SPLongOperation.BeginOperation) method we can pass title and sub message. Here in the script end method, iam passing redirection URL and checking flags and current context. For more details, here is the full description in msdn.

SharePoint error – A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.

While working on one of the existing SharePoint server, I tried to open the SharePoint central admin site. I got an error saying that “A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.” As shown the image below.

This is the error due to SQL server service got stopped. To fix the error we have to run the SQL Service as shown the image below.

But in my case, MSSQLSERVER service is started and running. Restarted the service, but it wasn’t worked for me. By digging in to the deep I got few details from system admin. The farm I am working is already had few sites. But before handing that to me all the sites deleted from SQL server. When I try to access the CA site it gives the error message.

Restarted SharePoint timer service, SQL server and all the servers in the farm. Nothing worked for me. Finally ran SharePoint configuration wizard. Super... it worked for me. Now I got the issue that some of my farm users deleted DB’s in sql server directly instead deleting form CA. By running the config wizard

Exception from hresult 0x800a03ec excel - Error ‘Microsoft Office Excel cannot access the file’ while accessing Microsoft Office 11.0 Object Library

We got this error with SharePoint 2007 farm custom coding. Code downloads all the list items to excel template. Generally we can see this error with rows updating in excel. But we haven’t changed anything from DEV to PROD. We have investigated the error in SharePoint logs and Event logs.

We are seeing the error as there are several possible reasons: 

·           The file name or path does not exist.

·           The file is being used by another program.

·           The workbook you are trying to save has the same name as a currently open workbook.

After checking the issue. We have found that there is the error with excel service. To fix that we have to create a folder.

If we are running in Windows Server 2008 64-bit/R2,

·           Navigate to C:\Windows\SysWOW64\config\systemprofile folder and create the following directory “Desktop”

If we are running in Windows Server 32-bit

·           Navigate to C:\Windows\System32\config\systemprofile\Desktop folder and create the following directory “Desktop”

If still you have the issue, you have to change the security setting to run excel application using following steps,

·           Start –> enter DCOMCNFG.exe to open Component Service

·           This will bring up the component services window, expand out "Console Root" -> "Computers" -> "DCOM Config"

·           Find "Microsoft Excel Application" in the list of components.

·           Right click on the entry and select "Properties"

·           Go to the "Identity" tab on the properties dialog.

·           Select "The interactive user."

·           Click the "OK" button.

·           Switch to the services console

·           Start the service automating Excel

·           Test you application again.

Enable Windows authentication in IIS if excel application in your site prompting for credentials.

Get all the site collection with content DB details using powershell commands

As discussed earlier power shell commands are pretty powerful than SharePoint object model code. Some time we can't run SharePoint object model code in production environment like gathering site/server/DB details.

We can get sites and content DB details using following power shell commands 


$rootSite=New-Object Microsoft.SharePoint.SPSite("Web app URL")
$spWebApp = $rootSite.WebApplication 
foreach($site in $spWebApp.Sites) {
    
    write-output "$($site.RootWeb.Url) - $($site.ContentDatabase)"     
  
    $site.Dispose() 
} 

Create explicit managed path for host named site collection using PowerShell commands

In SharePoint 2013, host-named site collections (HNSC) are all the rage. Host-named site collections give you the ability to provide completely different URLs for a site collection regardless of the web application URL hosting it.  For example, you might have a web application URL named http://sharepoint.mycompany.com and under it you have a collaboration site with a URL of http://collaborate.mycompany.com and an Intranet site namedhttp://intranet.mycompany.com.  This allows you to host multiple types of SharePoint sites together on a single web application which ultimately reduces the amount of resources your server farm needs for application pools.

Just like any site collection, you have to create a managed path for the URL you want.  We do this using New-SPManagedPath.  We can add explicit or wildcard inclusions just like you can for path-based site collections.  Wildcard is the default, if you want explicit, include the –explicit parameter.  We also need to add the –HostHeader parameter to indicate this is used with host-named site collections.  Here’s an example, where I created the business-solutions explicit path from our example above.

New-SPManagedPath "managed path name" -HostHeader –Explicit

This technically makes this managed path available on any host-named site collection.  If you want to see what managed paths have been created for host-named site collections, use Get-SPManagedPath with the –HostHeader command.

Get-SPManagedPath –HostHeader

Now, we need to create the site collection using New-SPSite.  It takes a few parameters but the important ones for this scenario is the URL we want for the new site collection and then –HostHeaderWebApplication to specify which web application hosts the new site collection.

New-SPSite “Site collection URL” –HostHeaderWebApplication “host web app URL” -Name "Site collection name" -OwnerAlias domain\user  -Template STS#0

SharePoint 2010 site accessing error - The server is busy now. Try again later

While accessing ee.ihess.com (SharePoint 2010) site, I got the error saying that “The server is busy now. Try again later” as shown the image below.

We can get this error when on the processes/services taking up the bulk of your server resources you can take different actions to reduce this overload (For example: recycling the timer service more often). We have the option to turn off HTTP throttling for an entire web application in Central Administration.

We can fix this error by using the steps below

• Navigate to Central Admin > Application Management > Manage Web Applications. 
• Select the problematic Web App > Select the down arrow next to General Settings and click Resource Throttling.

• On the bottom of the window there will be an option to turn HTTP Request monitoring and Throttling Off. Click OK to save changes.

SharePoint deployment – Back to basics – WSP stuck on “Deploying” state

While deploying a WSP in one of my SharePoint 2010 project, wsp stuck on “Deploying” state. I am Deploying the solution with farm admin credentials using Central Administration globally. But the status was “Deploying” for long time.

I have spent almost 2 hours on that but still the state is on “Deploying”. By verifying the few blog posts on the same error by running all the administration service jobs using stsadm.exe – o execadmsvcjobs issue may fixed.

I ran the command but still the Deployment status showing “Deploying”. I have restarted OWS Timer Service on all the servers in the farm. Finally all the Deployments ran successfully. 

SharePoint 2010 workflow error – “Due to heavy load, the latest workflow operation has been queued”

While working with custom workflows in SharePoint 2010, I got error in workflow as “Due to heavy load, the latest workflow operation has been queued”. After refreshing the page, most of the times error may fixed and workflow completed as usual. Sometimes I used to stuck on the workflow and I can see only the non-reactive screen with the error. I didn’t event fix the error after resetting the workflow and IIS Manager. After digging for the solution finally I got a fix for the error by updating the Workflow Postpone threshold in farm config manager by using following PowerShell commands,

Set-SpFarmConfig -WorkflowPostponeThreshold 30

By running the command, workflow threshold will be increased from 15 to 30

get-sptimerjob | ?{$_.name -like "job-workflow"} | set-sptimerjob -schedule "every 1 minutes between 0 and 59"

By running this command frequency of execution for SP timer job, job-workflow will be increased to every minute. After running the commands, if workflow don’t have any issues will be completed as expected.

SharePoint Restore-SPSite error - Response message does not match the content type of the binding

While working with SharePoint site  restoring with PowerShell commands I got an error saying that, 

Restore-SPSite : The content type text/html; charset=utf-8 of the response message does not match the content type of the binding (application/soap+msbin1). If using a custom encoder, be sure that the IsContentTypeSupported method is implemented properly

After googling some time I got the cause and fix for the solution. In my case in my current secured store service web application web.config file we have some duplicate nodes for forms based configuration.

To fix that we need to follow the steps below,

• Open SharePoint server form and open IIS manager.
• Open SharePoint Security Token Service Application and Explore the web application details for web.config file.
• In the web.config file verify the duplicate nodes and check all the nodes are allocated properly in the web.config file. In my current scenario we got extra node in forms based authentication configuaration. I have removed the duplicate node and restored the site.

Now I am able to restore the site. Hope this will helps.

SharePoint InfoPath form Allow anonymous users to enter data

SharePoint InfoPath forms is one of the way to enter data into SharePoint. To allow anonymous users to enter the data in the forms we have to follow the steps below,

• Configure Anonymous access to SharePoint site
• Create a list that to submit data anonymously, Change anonymous permissions to the list and update list form to InfoPath form

To configure Anonymous access to Site, navigate to SharePoint central Administration, Navigate to Application management -> Manage Web Applications.

Select the web application, click on “Authentication Providers” in the ribbon. Check “Enable anonymous access” checkbox to Enable Anonymous Access to web application.

To Enable Anonymous access to SharePoint site, Navigate to SharePoint site where we need to allow users enable anonymous access. Open Site Settings, Select Site Permissions. In Ribbon, Permissions Tools section, click on Anonymous access button.

Select Entire Web site option in Anonymous access popup.

Create a List in the site. In List permissions, Break permissions from Parent and Enable Anonymous access to Add items to list.

Customize the InfoPath form by  using Customize List option in the ribbon. 

SharePoint content type error “The content type is in use”

One of my project from recent times I was worked on content hub to manage the content types in SharePoint. While changing the content type name, I got an error saying that “The content type is in use” as shown the image below.

I have checked all the content types in current site, I haven’t seen any content type with the current name. By checking few blogs like mavention and sympmarc I got the reasons for this error.

There many reasons for this issue as listed below,

Content type still exists in Site: Even we can’t see the content type in SharePoint content type gallery, it may exists in Recycle Bin (Site Recycle Bin or Site collection Recycle Bin). We have to remove the content type from Recycle Bin too. To remove the content type from Site Collection Recycle Bin, we need to have Site Collection privileges on Site Collection.

Other users might have associated anywhere? There is another chance that other users might have associated with the content type that we can’t see. We can see this behavior in SharePoint document libraries. We can check this by navigating to SharePoint Library settings and click on Manage Check out files. We can see the list of items belongs to other users. If it ok, take the owner ship of the items and check-in them.

Minor Versions:  We have one more possibility that there are some items may checked-in with minor versions in the List/Library. We have to check the appropriate items and need to publish the items with major versions.

This may helps.

SharePoint custom workflow send email task error: The e-mail message cannot be sent. Make sure the email has a valid recipient

While working with SharePoint custom workflow with Send email task, i am seeing “The e-mail message cannot be sent. Make sure the email has a valid recipient” error in workflow history list every time workflow fired. I was in a confusion that why I am seeing that every time.

Very first time designing workflow I have left all properties for “Send Mail” task empty as I am sending emails through code without interacting any property in Send mail task. I have updated all the properties by creating new Field Properties in the code. But still seeing the same error in workflow history list.

By checking few posts, the error may cause because of two reasons,

• ·         There is no e-mail in Active directory for system Account.
• ·         Getting null values for existing properties as not assigning the values.

Here in my case iam getting null values from field properties. So I have updated a dummy email in To field as I am not using the the property anywhere in the code.

Hope this will helps.

SharePoint – Back to basics - Are sure you are SharePoint Farm Administrator?

How can I’ll be SharePoint farm Administrator? By Adding Farm Admin group from SharePoint Central Admin site as shown the below I’ll become Farm Administrator?

But as we have expected by completing all the steps, these will not grant Farm Admin privileges on the server. These steps will add user to farm admin group in SharePoint and WSS_ADMIN_WPG group in SharePoint farm. By updating user to this farm admin, users get access to web site and access to SharePoint resources as logs and web services.

If user not added in SharePoint local Admin group, user is not fully-fledged SharePoint farm administrator. When user runs SharePoint management he will see "The local farm is not accessible. Cmdlets with feature dependency ID is not registered."

To execute PowerShell commands, users need SharePoint_Shell_Access in SQL server SharePoint configuration database and other databases where we need to manage the data.

Therefore, to make a user as SharePoint farm admin, he need to have following permissions,

·      Local Administrator security group for each server in the farm

·      Farm Administration SharePoint Group

·      SQL Server SharePoint_Shell_Access database role for the SharePoint configuration database and all content databases.

SSRS report – SharePoint list error: an error occurred when accessing the specified SharePoint list. The connection string might not be valid

While creating the report by using SharePoint list, I got an error saying that “an error occurred when accessing the specified SharePoint list. The connection string might not be valid” as shown the image below.

I have checked all the possibilities for the error by changing the credentials in the report. It didn’t worked for me. Changed the connection string URL. Restarted BI development studio and Restarted Reporting services and finally restarted the machine too... No luck. L

By checking more than half day I have found the cause. The reason is, I have changed my VPN password recently and disabled UAC in my machine. So by connection the site, it is using my old password stored in windows vault.

We have to change the stored credentials in windows vault by using the steps below,

Navigate to Windows vault and in Windows credentials section select the site and update the credentials.

Now Iam able to access the list from SharePoint list  

PowerShell Commands: Download files/documents from SharePoint document libraries

In SharePoint 2013 we have an option to sync all the data from SharePoint document libraries to local folders by using Shared drive concept. We can download the document s from SharePoint document library using Power Shell commands also. We can do that in overall site collections and all the site collection may contain loads of documents and downloading one by one is a huge task by using windows explorer.

To download the document we can use following script,

$listUrls="Doc lib 1 URL″,” Doc lib 2 URL ″,” Doc lib 3 URL ″," Doc lib 4 URL ″ (All the document libraries URL’s)

#Enter the web site URL

$web = Get-SPWeb -Identity $webUrl

#Load all the list URLs in the web, Open the list URL.

foreach($listUrl in $listUrls)
{

#get the list URL

$list = $web.GetList($webUrl+”/”+$listUrl)

function DownloadDocuments{
    param($folderUrl)
    $folder = $web.GetFolder($folderUrl)
    foreach ($file in $folder.Files) {

        #Ensure destination directory

        $destinationfolder = $destination + “/” + $folder.Url
        if (!(Test-Path -path $destinationfolder))
        {
            $dest = New-Item $destinationfolder -type directory
        }

        #Download file

        $binary = $file.OpenBinary()
        $stream = New-Object System.IO.FileStream($destinationfolder + “/” + $file.Name)
        $writer = New-Object System.IO.BinaryWriter($stream)
        $writer.write($binary)
        $writer.Close()
        }
}

#Download root folder documents

DownloadDocuments ($list.RootFolder.Url)
foreach ($folder in $list.Folders) {

#download all the documents in each sub folders

   DownloadDocuments($folder.Url)
}

Multi domains in SharePoint 2010 people picker

In SharePoint environment people picker is one of the main part in the farm to provide the users available from all the domains to select the users. By configuring stsadm commands we can grab the users from different domains.

SharePoint will use Application pool identity to search users in active directory. If application pool account is not having permissions to do that, we have to encrypt the password.

To do that we can use following command in STSADM,

stsadm -o setapppassword -password  password

We have to set the domains should be searched on WFE for each web application,

stsadm -o setproperty -pn peoplepicker-searchadforests –pv domain:domain1;domain:domain2,domain2\account,password -url WebApplication

Hope this helps.