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;
 }
}

SharePoint people picker is not able to resolve names from different forests / domains

Some time back, I was caught into this issue after upgrading MOSS from SP1 to SP2. People Picker wasn't able to resolve users from different forests / domains.

After struggling for plenty of hours, I got the solution. Actually when you have to fetch users from different forests or domains, you should have user id & password detail of that specific domain with read access to directory services of that AD.

Execute the below mentioned command to edit property of people picker so that it can search cross forest / domain users.

Step 1. stsadm -o setapppassword -password key
Step 2. stsadm -o setproperty -url "http://portal_URL" -pn peoplepicker-searchadforests -pv "forest:FQFN;domain:FQDN",LoginID,Password

Step 1= Encrypts the password
Step 2= sets property of people picker.

a. portal_URL = Specify your portal / application URL
b. FQFN = Fully Qualified Forest Name
c. FQDN = Fully Qualified Domain Name
d. Login ID = Specify Login ID as (Domain Name\xyz)
e. Password = Specify password for the login. If password contains any special character, provide the password details in double chords. e.g "Hello,11"

If you have to add multiple forests / domains to people picker, you may have to execute below mentioned command for the same.

Step 1: stsadm -o setapppassword -password key
Step 2. stsadm -o setproperty -url "http://portal_URL" -pn peoplepicker-searchadforests -pv "forest:FQFN1;domain:FQDN1",LoginID1,Password1;"forest:FQFN2;
domain:FQDN2",LoginID2,Password2

As shown above, you can add multiple domains in single command line.

FQFN1, FQDN1, LoginID1, Password1 = details for first forest / domain
FQFN2, FQDN2, LoginID2, Password2 = details for second forest / domain

Note: Login IDs should atleast have read access to directory services of respective ADs.

Hope this may help you. Happy Blogging !!!

Stsadm to Windows PowerShell mapping (SharePoint Server 2010)

Microsoft has published a very useful new article on Technet that lists STSADM operations and their equivalent PowerShell cmdlets. Check this link.

Disable event firing in sharepoint when updating list item

Below piece of code can be used to update SharePoint list item inside any event receiver. I have written it for Updated event receiver.

public override void ItemUpdated(SPItemEventProperties properties)

{

//some handling here

ListItem item = properties.ListItem;

item["someproperty"] = "some value";

this.DisableEventFiring();

//save changes added

Item.Update();

this.EnableEventFiring();

}

Microsoft Tech.Ed 2010, India: In Bangalore from April 12-14

Yes, Its time to pack our bags for TechEd 2010. I am excited & will be attending the largest technology conference globally this year. This time, it is held up in Bangalore from 12th April to 14th April.

I have got the registration in development track & I am keen interested in watching out SharePoint track there.

I am looking forward to TechEd 2010. Hurray :-)

*Picture taken from TechEd 2010 India Site for Blogging Purpose


For more information visit http://www.microsoftteched.in

Upgrade Checker in SharePoint SP2 – prepare your way to SharePoint Server 2010

The upgrade checker in MOSS/WSS SP2 stsadm operation is very useful. It checks server farm for system requirements, database health and a list of rules. The rules can also be extended.

To use upgrade checker, first open a command line prompt, and run

"X:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\BIN\STSADM.EXE" -o preupgradecheck

(X is the drive letter where you install SharePoint)

Make sure you are in administrator mode. Otherwise it would be denied.



You can see there’re a list of rules checked by the operation.

SearchContentSourcesInfo
SearchInfo
ServerInfo
FarmInfo
UpgradeTypes
SiteDefinitionInfo
LanguagePackInfo
FeatureInfo
AamUrls
LargeList
CustomListViewInfo
CustomFieldTypeInfo
CustomWorkflowActionsFileInfo
ModifiedWebConfigWorkflowAuthorizedTypesInfo
ModifiedWorkflowActionsFileInfo
DisabledWorkFlowsInfo
OSPrerequisite
WindowsInternalDatabaseMigration
WindowsInternalDatabaseSite
MissingWebConfig
ReadOnlyDatabase
InvalidDatabaseSchema
ContentOrphan
SiteOrphan
PendingUpgrade
InvalidServiceAccount
InvalidHostName

A successful run could show the following:




Hey, we got a “OSPrerequisite… Failed” here. So let’s take a look at the report.

The report will give you the following information:

Search content sources and start addresses

Office Server Search topology information

Servers in the current farm

The components from this farm

Supported upgrade types

Site Definition Information

Language pack information

Feature Information

Alternate Access Mapping Url(s) within the current environment that should be considered when upgrading.

Lists and Libraries

Customized field types that will not be upgraded

Windows SharePoint Services Search topology information

And also the failed items it checked.

In my case, because this machine is still on Windows Server 2003 32bit, so it does not meet the requirement of SharePoint Server 2010, which needs to be install on Windows Server 2008 x64.

Failed : This server machine in the farm does not have Windows Server 2008 or higher 64 bit edition installed.

Upgrading to Windows SharePoint Services 4.0 requires Windows Server 2008 or higher 64 bit edition.
Please upgrade the server machines in your farm to Windows Server 2008 64 bit edition, or create a new farm and attach the content from this farm. For more information about this rule, see KB article 954770 in the rule article list at http://go.microsoft.com/fwlink/?LinkID=120257.

Re-Ensure SharePoint Permissions

After a long break, finally the day has come for a new post...
This time, I am talking about SharePoint Resource security...

Below command line reference is used to Secure resources in file system and registry entries
psconfig -cmd secureresources

This command Performs SharePoint Products and Technologies resource security enforcement on the server. For example, security is enforced on files, folders, and registry keys.
And also, ensures permissions/previleges to sharepoint accounts.

This command is helpful in order to make sure that all the sharepoint resources are secure & user accounts have correct access previleges.

Below is the excellent web link for Configuring SharePoint farm using psconfig.exe

http://blogs.msdn.com/yvan_duhamel/

Change in User Information in AD, not getting Sync with SharePoint after Profile import

Few days back, my client started facing an issue.. when there was a change in user information in AD (Active Directory)... SharePoint was not able to synchronize these changes..

Actually for some users, their login id & display name get renamed in AD.. So when they were logging in into the SharePoint portal with New login id, Portal was displaying still the old name... as "Welcome (old display name)"

Problem Description

Consider the following scenario.
You create a user account that is named User A, and you add User A to a Microsoft Office SharePoint Server (MOSS) 2007 Web application.
You import the user profile information in a SharePoint Server 2007 Web application, and the profile synchronization timer job runs.
User A logs off from the MOSS Web site.
You use Active Directory Domain Services (AD DS) to rename User A to User B, and you also change the information for the Account name.
You import the new user profile information, and the profile synchronization timer job runs.
User B logs on to the MOSS site.
In this scenario, when User B clicks My Settings in the MOSS 2007 Web application, the User name box and the Account name box still display the information for User A.


Solution

To resolve this problem, open a command prompt, type the following command, and then press ENTER:

stsadm.exe -o migrateuser -oldlogin -newlogin -ignoresidhistory

Important Run this command only on user accounts that are in the same domain. Otherwise, you may experience permission problems.

This command automatically updates the account name, and the command maintains the correct permissions for the account. To update the user name for the user account that is updated, follow these steps:
Log on to the MOSS Web application.
Click My Settings, click Edit Item, and then change the user name.

Windows SharePoint Services Web Application Required on Farm, not running Starting

I used to find, services are getting stuck under SharePoint Central Admin. This time, it is "Windows SharePoint Services Web Application".



The best way I find to get out of it, is mentioned below,

Step 1. Stop the service from Command Prompt,

Open Command prompt then navigate to 12\bin folder & execute the below command,

stsadm -o provisionservice -action stop -servicetype SPWebService

Step 2. Reset IIS

Open Command Prompt & execute,

iisreset

Step 3. Start the service back from Command Prompt

open Command prompt & navigate to 12\bin folder. Now execute the below command to Start the service again,

stsadm -o provisionservice -action start -servicetype SPWebService

Here it goes. service will start after executing above command. If still, you are not able to Start the service... Shut down your system for 10 minutes & then try the above steps again. It will work.. Cheers :)

Property problems in SharePoint Search

Today I got the below error in SharePoint Search no matter what search string I was attempting to use...



"Property doesn't exist or is used in a manner inconsistent with schema settings."

After some investigation I come to know, the problem is due to a results schema, on search results page, which doesn't match the schema that you are using for your query. This occurred because someone had changed the 'Selected Columns' XML in the 'Search Core Results' Web Part.

The fix for the problem is,

1. Go to the Search Results Page
2. Go into Page Edit Mode
3. Find the 'Search Core Results' Web Part on the page
4. Modify the Web Part, and go to it's properties
5. Expand 'Results Query Options'
6. Go to 'Selected Columns' and edit this field.
   

Actually problem lies here property defines here is incorrect.

The XML, by default, should look something like:

Calling C# Method from Inline JavaScript

This Post will help in calling a C# method from Inline JavaScript.

Inline Javascript:

string strMessage = "Some message";
StringBuilder sb = new StringBuilder();
sb.Append("< language="'javascript'">"); //Remove the in between space of script tag
sb.Append("var results = confirm('" + strMessage + "');");
sb.Append("if (results == true)");
sb.Append("document.getElementById(\"btnHdn\").click();"); //call methodTrue
sb.Append(" < /" + "script >"); //Remove the in between space of closing script tag
this.RegisterStartupScript("startup", sb.ToString());

When "Ok" is clicked from Confirm dialog box. btnHdn button's Click event gets fired.

Note:

1. Define the button btnHdn as mentioned below,

< id="btnHdn" runat="server" onclick="btnHdn_Click">

2. Instead of making button Hidden, use the below properties to show & hide a button

btnHdn.Attributes["Style"] = "display:none"; //To Hide button
btnHdn.Attributes["Style"] = "display:block"; //To display button

Creating Business Data Catalog Application

This Post helps us to Create Business Data Catalog Application from an Application Definition File. After that we will use that Business Data Catalog Application to drive the contents of a Web Part and a SharePoint list.

Prerequisites
a. You should have AdventureWorks Sample Database already installed with SQLServer2005.
b. You should have AdventureWorksDW.xml & AdventureWorks2000.xml Files in your system. You can get those from,
http://msdn2.microsoft.com/en-us/library/ms519241.aspx
http://msdn2.microsoft.com/en-us/library/ms494876.aspx
Copy & Paste the metadata in text files and save those as AdventureWorks2000.xml & AdventureWorksDW.xml files respectively.

Approach in Steps

Create the Business Data Catalog Application
1. Open AdventureWorks2000.xml & AdventureWorksDW.xml files.
2. Review the Contents of the Properties group of elements in the LobSystemInstance element.
3. In the LobSystemInstance metadata, replace the name of the computer running SQL Server that hosts the AdventureWorks2000 database in the Rdb Connection Source property accordingly. If you use a SQL Server instance, use the format, SQLServer\Instance e.g. son15824, if don’t have any instance.
4. Save & Close the xml Files.
5. Now Open Central Administration Web Page. Under Shared Services Administration, click SharedServices1.
6. On the Home Page of SharedServices1, under Business Data Catalog, click Import application definition.
7. From here Import those AdventureWorks2000.xml & AdventureWorksDW.xml application definition files.




Add Business Data List Web Part

1. Add a Business Data List Web Part to the home page of your portal site & Click the Open the tool pane link. In the Type section of Business Data List pane, select the appropriate Business Data Type e.g. Product.



2. Click OK and then Exit Edit Mode.
3. Test the Business Data List Web Part.


Create a Data-Driven Custom List

1. Create a Custom List from Custom Lists section & give it a name e.g. ProductInfo List.
2. On the ProductInfo List page, on the setting menu, click Create Column & give it a name e.g. ProductDetails.
3. Under The type of information in this column is, click Business Data.
4. In the Additional Column Settings section, under Type, provide the appropriate business data type e.g. Product by clicking on Browse button.
5. In the Display this field of the selected type list, click Name.
6. Under Add a Column to show each of these additional fields, Select the Color and the Size check boxes, and then click OK.
7. Now on the ProductInfo List page, create a New Item & provide the necessary information. Click the OK button.



8. A New item is created based on data retrieved from the Business Data Catalog application.

Creating Custom Content Types

1. Create blank word document, excel sheet and power point.
2. Save it in on desktop or any other place with extension as template.
3. Go to the home page of your portal and click on Site Actions.
4. In Site Actions, click on Site Settings.
5. Under Galleries, click on Site Content Types.
6. Click on create.
a. Write the name of your default document.
b. In parent content type form, select special content types.
c. Click radio button of new group and write the group name.
d. Click on ok.
7. In settings, click on Advanced settings.
a. Upload a new document template by clicking on browse. Click ok.
8. Repeat the steps 6 and 7 for other documents by going into Site Content Type Galleries. Add them in the same group that you mentioned for the word document.
9. Go to home page and click on shared documents on your page and then click on settings and then document library settings.
10. Under general settings, click on Advanced settings.
11. Under Allow management of content types? Click yes and then press ok.
12. Under content types, click on Add from existing site content types.
13. Select your group name and add the blank documents given in the box below. Click ok.
14. Go back to your shared documents, and click on new. You will find your blank word document,

Creating Custom Site Definition in MOSS 2007

The new version of SharePoint brings some changes to the way Site Definitions are created and deployed to a SharePoint server. Additionally, the way Site Definitions are now architected has changed with the advent of Features. Although FrontPage 2003 has been significantly enhanced to become the Office SharePoint Designer, Features allow for more flexible deployments of functionality within SharePoint, and Master Pages allow us to change the look and feel of many sites inside SharePoint with a single click of a button - custom Site Definitions can still be leveraged to provide outstanding enterprise wide flexibility and ease of maintenance for SharePoint sites.
Site Definitions are a complex and large topic that we will not delve into in an in depth manner right now. The purpose of this post is to describe how to create a custom Site Definition in order to set the stage for the other documentation.

Site Definition basics

Site Definitions are located in the following folder on the SharePoint server:
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\SiteTemplates
Each Site Definition has its own sub directory under this folder.
Inside each Site Definition sub directory the aspx pages for the various web pages and lists that make up the Site Definition are stored. The ONET.XML file that specifies the various configurations and modules the Site Definition is made up of is stored inside the XML subdirectory inside each Site Definition sub directory.
*Note: I think it is interesting that the SDK refers to Site Templates as the .stp files that are generated when you save a site as a template from the web UI, and the SDK refers to file based site templates as Site Definitions – yet this directory is named SiteTemplates.
Site Definitions are registered with SharePoint and made available via the WEBTEMP.XML file.
These files are located in the following folder on the SharePoint server:
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\1033\XML

Creating a custom Site Definition in MOSS 2007

The following tasks are all you need to do to create a custom Site Definition in MOSS 2007.
Step 1: Clone an existing Site Definition.
Step 2: Create the WEBTEMP XML fragment file to register the Site Definition with SharePoint.
Step 3: Reset IIS
Step 4: Create a site based on the custom Site Definition.

Step 1: Clone an existing Site Definition.

Navigate to the following directory on the SharePoint server in Windows Explorer:
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\SiteTemplates
Copy the STS directory and paste it back into the same directory.
Rename the Copy of STS directory to SAMPLE
Navigate to the following directory on the SharePoint server in Windows Explorer:
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\1033
Copy the STS directory and paste it back into the same directory.
Rename the Copy of STS directory to SAMPLE

Step 2: Create the WEBTEMP XML fragment file to register the Site Definition with SharePoint.

Create a file called WEBTEMPSAMPLE.XML in the following directory:

C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\1033\XML

Put the following XML into the WEBTEMPSAMPLE.XML file:

*Note: The DisplayCategory attribute of the <> element dictates which tab the configuration will appear on in the Template Selection section of the create site web page. You can create your own tabs by putting your own values in this attribute (like we have done here).

Save the file.

Alternate Approach

(You can also Copy & Paste WEBTEMP.XML & Rename that as WEBTEMPSAMPLE.XML & Change the template section)

Step 3: Reset IIS

Open a command prompt on the SharePoint server.

Type in the command: iisreset

Wait for IIS to reset

Step 4: Create a site based on the custom Site Definition.

Open Internet Explorer

Navigate to the Sites Directory in your SharePoint portal (http://SharePointServerName/SiteDirectory)

Click the Create Site Link

Fill out the information on the form.

Notice at the bottom of the page in the Template Selection section the new Custom Site Definitions tab.

Click this tab and select one of the available configurations.

Click the Create button.