Recover SQL Server Database from SUSPECT Mode

Recently working with one of my colleague, I noticed that the content database of a SharePoint web application on his VM started appearing with "Suspect" mode in SQL Server 2008 R2. As a result of this, dependent web application was not accessible.

This issue occurs when the primary filegroup is damaged and the database cannot be recovered during the startup of the SQL Server. This is the situation when SQL Database goes into "Suspect" mode.

In order to solve this issue, a user who is member of sysadmin fixed server role in SQL Server Database can perform below mentioned command steps:

Step 1 – Use the Master database

Use Master Step 2 – Verify that database has issues EXEC sp_resetstatus ‘Dbname’ Step 3 – Put the database in emergency mode SQL Server's emergency mode allows you to repair the database by repairing to last normal state.  ALTER DATABASE Dbname SET EMERGENCY DBCC checkdb(‘Dbname’)  Step 4 – Set the database in single user mode ALTER DATABASE Dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE  Step 5 – Repair the database with data loss DBCC CheckDB (‘Dbname’, REPAIR_ALLOW_DATA_LOSS)  Step 6 – Set the database in multi-user mode ALTER DATABASE Dbname SET MULTI_USER Step 7 – Verify that database is reset EXEC sp_resetstatus ‘Dbname’

After running the script on the Master database, Dbname (WSS_Content in my case) database suspect mode was fixed and I was able to access the content web application.

Sharepoint CAML quick useful tips

Get list items by lookup id, not value:

Notice that LookupId=”TRUE” is added to the FieldRef and that the value type is Integer, not Lookup.

Get list items that are assigned to the current user ([Me]).

The UserID element in CAML is the same as [Me] in a list filter. This work well with Allow Multi user People & Groups list fields as well.

SharePoint 2010, Programatically change Hidden value of a field if CanToggleHidden is false

We can not delete hidden fields directly from sharepoint list forms. In order to delete it, we need to set CanToggleHidden attribute to true & then we can unhide the field & can delete it using Object Model.


Add System.Reflection namespace.

  using (SPSite site = new SPSite(site url))
    {
    using (SPWeb web = site.OpenWeb())
    {
    SPList testList = web.Lists["Test"];
    web.AllowUnsafeUpdates = true;
    SPField objTitle = testList.Fields["Title"];
    Type type = objTitle.GetType();
    MethodInfo mi = type.GetMethod("SetFieldBoolValue",BindingFlags.NonPublic | BindingFlags.Instance);
    mi.Invoke(objTitle, new object[] { "CanToggleHidden", true });
    objTitle.Hidden = true;   //objTitle.Hidden = false;
    objTitle.Update();
                                 
    }
  }

Once CanToggleHidden attribute is set to "true" , so you can unhide the field as you wish. and further you can delete the hidden field.

Impersonation in ASP.NET causes [COM.Exception(0x80072020) : An Operations error occurred.]

When you run code that uses DirectorySearcher, DirectoryEntry or other classes that communicates with network resources from a webpart in a Sharepoint site, you recieve a: [COMException (0x80072020): An operations error occurred. ]
This is caused by the fact that when a user is authenticated against a sharepoint server using NTLM or Kerberos, a "secondary token" is sent to the server that it uses to authenticate the user. This token cannot be used to authenticate the current user against another server (e.g. a domain controller).
This can be circumvented by reverting the impersonation to the application pool account used by IIS (if this account has access to Active Directory) with the following code (this is equal to running with impersonation set to false in web.config):

C#:

using System.Web.Hosting;
...
...

// Code here runs as the logged on user
using (HostingEnvironment.Impersonate()) {// This code runs as the application pool user
     DirectorySearcher searcher ...}

// Code here runs as logged on user again 

VB:

// Code here runs as the logged on user
Using (System.Web.Hosting.HostingEnvironment.Impersonate())

// This code runs as the application pool user
     DirectorySearcher searcher ... 

End Using

// Code here runs as logged on user again

Writing Messages to Event Log in SharePoint

In SharePoint using System.Diagnostics.EventLog class, you can write messages to event log.
Below is the code snippet for the same,

string source = "Demo Application";
if (!EventLog.SourceExists(source))
EventLog.CreateEventSource(source, "Application");
EventLog.WriteEntry(source, "Message!", EventLogEntryType.Error);

Custom aspx pages in SharePoint 2010

While migrating Custom aspx pages from MOSS 2007 to SP 2010, below mentioned pages directives are to be modified accordingly.

MOSS way of doing

<%@ Page Language="C#" Inherits="Microsoft.SharePoint.ApplicationPages.NewListPage" MasterPageFile="~/_layouts/application_Custom.master" %>

SP 2010 way of declaring the directive,

<%@ Page language="C#"
 MasterPageFile="~/_layouts/application_Custom.master"
 Inherits="Microsoft.SharePoint.WebPartPages.WebPartPage,Microsoft.SharePoint,Version=14.0.0.0,Culture=neutral,PublicKeyToken=71e9bce111e9429c"%>

If the page inherits publishing layouts page, the directive should be modified as shown below,

MOSS way of doing:

<%@ Page Language="C#" DynamicMasterPageFile="~masterurl/default.master" Inherits="Microsoft.SharePoint.Publishing.Internal.CodeBehind.CategoriesPage"%>

SP 2010 way of doing: 

Follow the below two steps to apply master page here. MasterPageFile tag is not supported directly.

1. <%@ Page Language="C#" Inherits="Microsoft.SharePoint.Publishing.PublishingLayoutPage,Microsoft.SharePoint,Version=14.0.0.0,Culture=neutral,PublicKeyToken=71e9bce111e9429c"%> 

2. And, then under script tag, override "OnPreInit" method & assign master page there.

protected override void OnPreInit(EventArgs e)
{
base.OnPreInit(e);
this.MasterPageFile = "SomeOther.master";
}

Copy attachments from one list item to another within a SharePoint site

Attachments for a list item are stored as SPFile objects under a hidden folder in the list where those attachments are stored. Each list item that has an attachment has its own folder with the ID of the item being the folder's name. Here is a code sample to copy attachments from one item to another:

private void CopyAttachments(SPWeb oSPWeb, SPListItem sourceItem, SPListItem targetItem)
{
//get the folder with the attachments for the source item
 SPFolder sourceItemAttachmentsFolder =      
 sourceItem.Web.Folders["Lists"].SubFolders[sourceItem.ParentList.Title]
.SubFolders["Attachments"].SubFolders[sourceItem.ID.ToString()];

//Loop over the attachments, and add them to the target item
 foreach (SPFile file in sourceItemAttachmentsFolder.Files)
 {
   byte[] binFile = file.OpenBinary();
   oSPWeb.AllowUnsafeUpdates=true;
   targetItem.Attachments.AddNow(file.Name, binFile);
   oSPWeb.AllowUnsafeUpdates=false;
 }
}