Saturday, February 21, 2015

How to stop follow sites in Sharepoint Online

Following sites is one of the new features of Sharepoint 2013. It allows users to track sites which are most interesting or relevant for them. In order to follow the site user should click Follow link in top right corner:

image

It will show message that current site is now followed:

image

In order to unfollow the site you need to click on Sites link near Office 365 in top left corner:

image

Here you will see list of all followed site. Near each site there will be context menu which will allow you to unfollow appropriate site:

image

After you will click “Stop following” site will disappear from the list of followed sites.

Thursday, February 12, 2015

Fix “The user does not have permission to perform this action” error from Sharepoint timer service

Recently we faced with the following issue: on one of WFE in production farm the following error from OWSTIMER process flooded the event log:

Unknown SQL Exception 297 occurred. Additional error information from SQL Server is included below.  The user does not have permission to perform this action.

This error occurred for all Sharepoint system databases:

  • search
  • managed metadata
  • user profile
  • usage and health
  • user profile sync
  • web analytics reporting

In order to avoid this error we need to grant VIEW SERVER STATE permissions to account which is used for OWSTIMER process. In Sql management studio run the following command:

GRANT VIEW SERVER STATE TO [domain\user]

After that error should disappear.

Monday, February 2, 2015

How to check in MS Sql Server to what db instance alias belongs to

Db aliases are used as additional level of addressing databases in connection strings: having alias you may change real db instance or even server name without reconfiguration of your apps, because alias will remain the same. But if you see connection string like that:

Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyDatabase;Data Source=test

where “test” specified in Data Source is db alias, how to know to what db instance specified alias belongs to?

The easiest way is to use cliconfg tool. When you run it on the Alias tab you will see list of all used db aliases and real db instances they belong to:

image

Thursday, January 15, 2015

Problem with 401 Unauthorized error when anonymous user tries to open xlsx file

In Sharepoint 2010 when anonymous user tries to open xlsx file the following error may occur:

401 Unauthorized

At the same time old xsl files are opened well. Several solutions for this problems are mentioned there: http://support.microsoft.com/kb/2498047. However if they are not good for your scenario, you may use solution described below. The reason of the problem is the following: for several Excel files (by default they are xlsx, xlsb and xls) Sharepoint generates special link which points to the handler /_layouts/xlviewer.aspx?id=… instead of the link to original file. And when anonymous user tries to open xslx file, we get 401 Unauthorized exception because /_layouts/xlviewer.aspx page is not available for anonymous by default. In order to fix the problem we may do the following.

Excel files for which Sharepoint creates dynamic links are specified in serverfilesExcelServer.xml file which is located in C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\XML folder. By default it looks like this:

   1: <?xml version="1.0" encoding="utf-8" ?>
   2:  
   3: <!-- Copyright (c) Microsoft Corporation. All rights reserved. -->
   4:  
   5: <ServerFiles>
   6:     <Mapping FileExtension="xlsx" RedirectUrlTemplate="/_layouts/xlviewer.aspx?id=|0"
   7:     NoGetRedirect="TRUE" CreateRedirectUrlTemplate="/_layouts/xlviewer.aspx?new=1"/>
   8:     <Mapping FileExtension="xlsb" RedirectUrlTemplate="/_layouts/xlviewer.aspx?id=|0"
   9:     NoGetRedirect="TRUE" CreateRedirectUrlTemplate="/_layouts/xlviewer.aspx?new=1"/>
  10:     <Mapping FileExtension="xlsm" RedirectUrlTemplate="/_layouts/xlviewer.aspx?id=|0"
  11:     NoGetRedirect="TRUE" CreateRedirectUrlTemplate="/_layouts/xlviewer.aspx?new=1"/>
  12:     <Mapping FileExtension="ods" RedirectUrlTemplate="/_layouts/xlviewer.aspx?id=|0"
  13:     NoGetRedirect="TRUE" CreateRedirectUrlTemplate="/_layouts/CreateNewDocument.aspx?id=|0"/>
  14: </ServerFiles>

After we will comment line with xlsx extension, Sharepoint will use regular links which will point to files themselves:

   1: <?xml version="1.0" encoding="utf-8" ?>
   2:  
   3: <!-- Copyright (c) Microsoft Corporation. All rights reserved. -->
   4:  
   5: <ServerFiles>
   6:     <!--<Mapping FileExtension="xlsx" RedirectUrlTemplate="/_layouts/xlviewer.aspx?id=|0"
   7:     NoGetRedirect="TRUE" CreateRedirectUrlTemplate="/_layouts/xlviewer.aspx?new=1"/>-->
   8:     <Mapping FileExtension="xlsb" RedirectUrlTemplate="/_layouts/xlviewer.aspx?id=|0"
   9:     NoGetRedirect="TRUE" CreateRedirectUrlTemplate="/_layouts/xlviewer.aspx?new=1"/>
  10:     <Mapping FileExtension="xlsm" RedirectUrlTemplate="/_layouts/xlviewer.aspx?id=|0"
  11:     NoGetRedirect="TRUE" CreateRedirectUrlTemplate="/_layouts/xlviewer.aspx?new=1"/>
  12:     <Mapping FileExtension="ods" RedirectUrlTemplate="/_layouts/xlviewer.aspx?id=|0"
  13:     NoGetRedirect="TRUE" CreateRedirectUrlTemplate="/_layouts/CreateNewDocument.aspx?id=|0"/>
  14: </ServerFiles>

And after that problem should disappear. Hope it will help you in your work.

Friday, December 19, 2014

Fix “Operation is not valid due to the current state of the object” error during deleting of the field from the list

Some time ago during migrating of the content from old Sharepoint 2007 site to O365 we faced with the following problem: some document libraries in SP 2007 environment had 2 fields with different titles and internal names, but with the same ids. Most probably it was error in one of the custom solution deployed to old site. But because of that problem migration tool could not copy content from such document libraries. Further investigation showed that one of fields with duplicated id is not really used in documents metadata, so it was decided to delete it from doclibs in order to proceed with migration.

I quickly wrote simple utility (remember that source environment was SP2007 where we lived with console utilities instead of PowerShell scripts) which iterates through all sites and lists and checks for duplicated fields. When they are found it deletes field with specified title:

   1: using (var site = new SPSite(url))
   2: {
   3:     using (var web = site.OpenWeb())
   4:     {
   5:         if (!web.Exists)
   6:         {
   7:             return;
   8:         }
   9:  
  10:         // find list by title
  11:         var list = web.Lists.Cast<SPList>().FirstOrDefault(
  12:             l => l.Title == listTitle);
  13:         if (list == null)
  14:         {
  15:             return;
  16:         }
  17:  
  18:         // find field by id and title
  19:         var field = list.Fields.Cast<SPField>().FirstOrDefault(
  20:             f => f.Id == fieldId && f.Title == fieldTitle);
  21:         if (field == null)
  22:         {
  23:             return;
  24:         }
  25:  
  26:         // delete field from list
  27:         list.Fields.Delete(field.InternalName);
  28:         list.Update();
  29:     }
  30: }

It worked properly on test lists created manually, i.e. manually added field was successfully removed by the code above. However on production situation was more complicated: fields were added by content types (although problematic field was not in any bound content types. Probably when developers found the error they removed field from content types, but it stay in provisioned doclibs) and attempt to run the above code there caused exception:

Operation is not valid due to the current state of the object

which was thrown from line 27, i.e. list.Fields.Delete() method. In order to avoid the error I checked implementation of that method in Reflector:

   1: public void Delete(string strName)
   2: {
   3:     this.EnsureFieldsSafeArray(false);
   4:     if (this.ReadOnly)
   5:     {
   6:         throw new SPException(SPResource.GetString(
   7:             "NotAvailOnDetachedFieldCollection", new object[0]));
   8:     }
   9:     SPField fld = this.GetField(strName, false);
  10:     if (fld == null)
  11:     {
  12:         throw new ArgumentException();
  13:     }
  14:     if (!fld.CanBeDeleted)
  15:     {
  16:         throw new InvalidOperationException();
  17:     }
  18:     fld.OnDeleting();
  19:     if (this.m_List == null)
  20:     {
  21:         this.DeleteFromWeb(fld);
  22:     }
  23:     else
  24:     {
  25:         SPSecurity.SetListInHttpContext(HttpContext.Current,
  26:             this.m_List.InternalName);
  27:         this.m_List.Lists.Web.Request.RemoveField(this.m_List.Lists.Web.Url,
  28:             this.m_List.InternalName, fld.InternalName);
  29:     }
  30:     this.SetFieldsStateAsDirty();
  31: }

As shown above InvalidOperationException (exactly for which error message “Operation is not valid …” is displayed for end users) is thrown on line 16 if SPField.CanBeDeleted property of the deleting field is false. Let’s check this property also:

   1: public bool CanBeDeleted
   2: {
   3:     get
   4:     {
   5:         if (this.AllowDeletion.HasValue)
   6:         {
   7:             return this.AllowDeletion.Value;
   8:         }
   9:         return (!this.FromBaseType && !this.Sealed);
  10:     }
  11: }

This is readonly property. However internally it uses another property bool? AllowDeletion which has setter. By default it was null for deleted fields, so in order to avoid the InvalidOperationException I modified the console utility code and added setting of AllowDeletion property to true before to delete the field from list’s fields collection:

   1: using (var site = new SPSite(url))
   2: {
   3:     using (var web = site.OpenWeb())
   4:     {
   5:         if (!web.Exists)
   6:         {
   7:             return;
   8:         }
   9:  
  10:         // find list by title
  11:         var list = web.Lists.Cast<SPList>().FirstOrDefault(
  12:             l => l.Title == listTitle);
  13:         if (list == null)
  14:         {
  15:             return;
  16:         }
  17:  
  18:         // find field by id and title
  19:         var field = list.Fields.Cast<SPField>().FirstOrDefault(
  20:             f => f.Id == fieldId && f.Title == fieldTitle);
  21:         if (field == null)
  22:         {
  23:             return;
  24:         }
  25:  
  26:         // if field can't be deleted, set AllowDeletion to true
  27:         if (!field.CanBeDeleted)
  28:         {
  29:             field.AllowDeletion = true;
  30:             field.Update(true);
  31:         }
  32:  
  33:         // delete field from list
  34:         list.Fields.Delete(field.InternalName);
  35:         list.Update();
  36:     }
  37: }

Additional lines are 26-31. After that code successfully deleted field with duplicated id and it became possible to migrate the content from all document libraries.

Monday, December 15, 2014

Localize content type names and field titles in Sharepoint sandbox solutions

If you work with Sharepoint starting with pre-2013 version most probably you know how to localize strings (content type names, field types, feature names, web part titles, etc.) in farm solutions. In order to do it we need to use provisioning resources with special syntax: $Resources:{resource file name without resx extension},{string identifier}, e.g. “$Resources:MyResource,Foo”. It assumes that specified resx file is provisioned with farm solution to 14 or 15/Resources folder (in VS solution you may create mapped folder for that). But when we work with farm solutions situation is different: files are not saved to the local file system. Because of that we can’t use the same approach for localizing strings for various artifacts which are provisioned with sandbox solution. In this post I will show one approach which may be used for localizing content types names and fields titles which is most probably is most often needed requirement.

First of all I need to say that tried many ways before found really working approach. Also need to say that this approach uses codebehind which is as you probably know is deprecated in sandbox solutions by MS. If it is important for you to not use codebehind in sandbox solutions, you may rewrite the code e.g. via javascript client object model which and use the same idea for running it first time for provisioned site (see below). Also example below is shown for scenario when in the same site collection there may be several different languages. When you have single language per site collection approach will be simpler: instead of fixing content types inheritors in all bound lists it will be enough to fix only site content type and propagate changes to all lists which use this content type.

Ok, lets check the approach itself. Site columns (fields) and content types are provisioned with hardcoded strings (I use English names, but you may use any language for default string values depending on your language):

   1: <Field ID="..."
   2:     Name="MyField"
   3:     SourceID="http://schemas.microsoft.com/sharepoint/v3"
   4:     StaticName="MyField"
   5:     Group="Custom"
   6:     Type="Text"
   7:     DisplayName="My Field"/>

content type:

   1: <ContentType ID="..."
   2:              Name="My Content type"
   3:              Group="Custom"
   4:              Inherits="TRUE"
   5:              Version="0">
   6:   <FieldRefs>
   7:     <FieldRef ID="..." Name="MyField" />
   8:     ...
   9:   </FieldRefs>
  10: </ContentType>

After we will provision these elements to the Sharepoint site they will have names/titles which are hardcoded in xml shown above. Also in all lists where this content type is bound to the same string values will be used. The next step is to run code which will localize these strings depending on the specified language.

As I already mentioned above we will consider the scenario when there may be sub sites (SPWeb) on different languages in the same site collection. In this case we will need to get reference on the list content type which inherits site content type and update strings there. If we would update site content type itself and would propagate changes to all lists which use this content type, all sub sites would have titles on the same language which was last used which is of course not what we want.

Translated strings should be stored in resx files which are embedded to your sandbox solution’s assembly. After you will add them to VS solution you will also need to add additional assemblies to your package manifest (one assembly per each translation):

image

In this example we added Finnish and Russian resources.

After that we are ready to make actual localization. Here is the sandbox code which will localize list content type and its fields:

   1: public static void LocalizeCTFields(SPWeb web)
   2: {
   3:     var list = web.Lists.Cast<SPList>().FirstOrDefault(l =>
   4:         l.RootFolder.Url == "MyList");
   5:     if (list == null)
   6:     {
   7:         return;
   8:     }
   9:  
  10:     var ci = new CultureInfo((int)web.Language);
  11:     web.AllowUnsafeUpdates = true;
  12:     var fieldsList = list.Fields.Cast<SPField>().ToList();
  13:     var ct = list.ContentTypes.Cast<SPContentType>().FirstOrDefault(c =>
  14:         c.Id.IsChildOf(new SPContentTypeId(MY_CONTENT_TYPE_ID)));
  15:     if (ct != null)
  16:     {
  17:         ct.Name = Properties.Resources.ResourceManager.
  18:             GetString("MyContentType_Title", ci);
  19:         ct.Update();
  20:     }
  21:  
  22:     localizeField(fieldsList, new Guid(FIELD1_ID),
  23:         Properties.Resources.ResourceManager.GetString("Field1_Title", ci));
  24:     localizeField(fieldsList, new Guid(FIELD2_ID),
  25:         Properties.Resources.ResourceManager.GetString("Field2_Title", ci));
  26:     ...
  27: }
  28:  
  29: private static void localizeField(List<SPField> fields, Guid fieldId,
  30:     string title)
  31: {
  32:     if (fields.IsNullOrEmpty() || string.IsNullOrEmpty(title))
  33:     {
  34:         return;
  35:     }
  36:     var field = fields.FirstOrDefault(f => f.Id == fieldId);
  37:     if (field == null)
  38:     {
  39:         return;
  40:     }
  41:     field.Title = title;
  42:     field.Update(true);
  43: }

It gets the reference to the SPLIst (lines 3-8), then find needed content type in the list (lines 13-15) and updates content type name (lins 15-20) and all fields in the list which correspond to appropriate content type (lines 22-26) with strings from resource file for the language of the current site (line 10). Note that we get reference on fields collection from SPList.Fields, not from SPContentType.Fields or FieldLinks.

The remaining question is where to put this code and in what moment it should be executed. I tried to use WebProvisioned handler, but it didn’t work: there were no any errors, just changes were not applied to the content types and fields, and they remain with default English texts. The only working approach I found is to put hidden web part with initialization code to the site’s front page. Once site is created user is redirected to the front page of this site. In this moment we may execute necessary post initialization logic. We need to ensure that it will be executed once. In the farm env it will be tricky to ensure that code is running in single thread once, so I will omit these details here:

   1: public class InitializationWebPart : WebPart
   2: {
   3:     protected override void OnLoad(EventArgs e)
   4:     {
   5:         var web = SPContext.Current.Web;
   6:         web.AllowUnsafeUpdates = true;
   7:  
   8:         // site should be initialized once
   9:         if (this.isInitialized(web))
  10:         {
  11:             return;
  12:         }
  13:  
  14:         try
  15:         {
  16:             LocalizeCTFields(web);
  17:         }
  18:         finally
  19:         {
  20:             this.setInitialized(web);
  21:         }
  22:     }
  23:  
  24:     private bool isInitialized(SPWeb web)
  25:     {
  26:         if (!web.AllProperties.ContainsKey("IsInitialized"))
  27:         {
  28:             return false;
  29:         }
  30:         return bool.Parse(web.AllProperties["IsInitialized"] as string);
  31:     }
  32:  
  33:     private void setInitialized(SPWeb web)
  34:     {
  35:         web.SetProperty("IsInitialized", true);
  36:         web.Update();
  37:     }
  38: }

As shown in this example when web part runs the code once, it will update property bag setting and won’t run twice.

This post shows that although in sandbox solution simple operations like localization of content types and fields are more complicated they are still possible and you may use it in your projects.