Lets Learn

Opinion Matters

Archive for May, 2011

Purging/Deleting a Huge/Large SharePoint List

Posted by Ankush on May 28, 2011

Recently I worked on a case where customer was trying to delete a huge list (it could be a simple list or workflow history list) and was looking for a custom solution to do it.

In case if it is a workflow history list then you can create a new list and set this as a history list and then you can try to delete the list items.

I started working on this and found a very good sample is already available on the web:

http://code.msdn.microsoft.com/CleanWorkflowHistory

But if you look at the code, it gets the ListItem and then it reads the List Item id so that it can create a batch processing to delete the item.

Everything is fine but still the process was slow…I reserached on this and suggested few points which I wanted to highlight here:

1. When you delete the List Items from List, it goes to first recycle bin and then it goes to the second one. So you may want to turn this off when deleting the items. Sometimes it is not practle to delete the items..so think about the overhead.. ..delete the item from a list, then 1st recycle bin and then 2nd recycle bin.

2. Delete Items in batch. instead of deleting them as a big list, delete them in chunk. (for ex: start with 2000 items)

3. This is something not recommended but can be tried in one-off case. Basically you can read List Item ID from the database instead of reading it using Object Model.

=========

============
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SharePoint;
using System.Data.SqlClient;

namespace PurgeList
{
class Program
{
private static int iteration = 5;
private static int Count = 2000;

static void Main(string[] args)
{
if (args.Length != 1)
{
Console.WriteLine(“first argument: No of iteration”);
Console.WriteLine(“YourprgramName.exe 5”);
Console.WriteLine(“******Program terminated : Argument missing! Please give one argument ****”);

}
else
{
try
{
iteration = Convert.ToInt32(args[0]);

}
catch (Exception ex)
{
Console.WriteLine(“Error : Failed to convert arguments: {0}”, ex.Message);

return; // exit
}
Console.WriteLine(“Current System Time (Start) : ” + DateTime.Now.ToString());
try
{

for (int iterate = 0; iterate < iteration; iterate++)
{
int start = GetMinID();
Console.WriteLine(“Start Index: ” + start.ToString());
using (SPSite site = new SPSite(“http://anksps2010“))
{
using (SPWeb web = site.OpenWeb())
{
SPList wflist = web.Lists[“Workflow History”]; //workflow history
string wflistID = wflist.ID.ToString();

Console.WriteLine(“No of items before deletion: ” + wflist.ItemCount);

Console.WriteLine(“Building query… “);
StringBuilder batchString = new StringBuilder();
batchString.Append(“xml version=\”1.0\” encoding=\”UTF-8\”?>”);
int end = start + Count – 1;
for (int i = start; i <= end; i++)
{
batchString.Append(“<Method>”);
batchString.Append(“<SetList Scope=\”Request\”>” + wflistID + “”);
batchString.Append(“<SetVar Name=\”ID\”>” + Convert.ToString(i) + “”);
batchString.Append(“<SetVar Name=\”Cmd\”>Delete”);
batchString.Append(“</Method>”);

}

batchString.Append(“</Batch>”);

//Console.WriteLine(batchString.ToString());

try
{
web.AllowUnsafeUpdates = true;
Console.WriteLine(“Executing query… “);
Console.WriteLine(“Batch Execution (Start) : ” + DateTime.Now.ToString());
string result = web.ProcessBatchData(batchString.ToString());
//Console.WriteLine(result);
web.AllowUnsafeUpdates = false;

Console.WriteLine(“Batch Execution (END) : ” + DateTime.Now.ToString());

}
catch (Exception ex)
{
Console.WriteLine(“Process batch error : ” + ex.Message);

}
Console.WriteLine(“No of items before deletion: ” + wflist.ItemCount);
using (SPSite site1 = new SPSite(“http://anksps2010“))
{
using (SPWeb web1 = site1.OpenWeb())
{
Console.WriteLine(“No of item after deletion: ” + web1.Lists[“Workflow History”].ItemCount);
}
}

Console.WriteLine(“————————————————–“);

}
}
}
Console.WriteLine(“Current System Time (End) : ” + DateTime.Now.ToString());
Console.WriteLine(“———–Program Completed————“);
}
catch (Exception ex)
{
Console.WriteLine(“Error : ” + ex.Message);
Console.WriteLine(“******Program terminated due to error **** “);

}
}

}

public static int GetMinID()
{

try
{
string connString = @”Data Source=.\SHAREPOINT;Initial Catalog=WSS_Content_c20feb22657e4e2ab82f7db433f3e4c7;Integrated Security=SSPI”;

using (SqlConnection objConn = new SqlConnection(connString))
{
objConn.Open();

string sqlString = “Select min(tp_ID) as col1 from dbo.alluserdata where tp_listid = ‘6463BECE-3560-4D15-B965-B245F3203BEE'”;// workflow list id

SqlCommand cmd = new SqlCommand(sqlString, objConn);
int id = Convert.ToInt32(cmd.ExecuteScalar());
return id;

}
}
catch (Exception ex)
{
Console.WriteLine(“SQL ERROR: ” + ex.Message);
throw ex;

}
}

}
}

==================================

Try this and let me know how it goes!!!!!

Update # 1:

While working on the sample available here (http://code.msdn.microsoft.com/CleanWorkflowHistory), I hit a bug (or should I say a couple of bugs).

Bug # 1:PurgeSiteRecycleBin function again picks the item from the 1st stage (end-user) recycle bin. You need to specify ItemState to get the items from the second stage recycle bin. So currently, It deletes the item twice from the 1st stage recycle bin.

bug # 2: There is no need of PurgeSiteRecycleBin function at all. Basically, when you delete the item using batch processing, it goes into 1st stage re-cycle bin. But when you delete the item from re-cycle bin, it updates the content database so it will land in 2nd stage re-cycle bin. So unless, you want to empty the 2nd stage recycle bin,you can safely remove the code.

 

Advertisements

Posted in SharePoint 2010, Sp 2007 | Tagged: , , , , | Leave a Comment »

SharePoint Object Model Development and 64bit

Posted by Ankush on May 27, 2011

Recently i worked on a case where customer was using SharePoint object model on a 64bit machine in an ASP.NET application and it was failing. The code was failing when I tried to create SPSite object

Here is the error message:

================

The Web application at http://ank2007:45366 could not be found. Verify that you have typed the URL correctly. If the URL should be serving existing content, the system administrator may need to add a new request URL mapping to the intended application

================

I could browse to this site without any problem and SharePoint is installed on the same box. I tried setting the target platform to x64,“Any CPU” , x86 but it didn’t help. I can very well create a webpart and can use the same code without any problem. So here is the environment details:

  • Your machine is 64 bit
  • MOSS 2007 is 64 bit
  • VS is 32 bit

So I started treoubleshooting the problem:

1. First I checked where is the server hosted (You can’t connect via OM to another SP instance on a different server.)
2. Ran the application as an administrator
3. I noticed that when I add reference for SharePOint.dll in Asp.Net web application, it reports an error about Search.dll.
4. As I was not using any serach part so I deleted the code using a post script

cd $(TargetDir)
del Microsoft.SharePoint.Search.dll
del Microsoft.SharePoint.Search.xml

After this I tried a lots of stuff – ProcMon, Fusion to see if there is any mismatch. But finally I got the solution 🙂

Basically when you try to debug the application, it try to load the 64bit MOSS dlls in 32 bit space (as VS is 32bit) and it fails with all the errors.  So this is what I did to make the deployed application to run in 64 bit:

Used http://support.microsoft.com/kb/894435 and tried this

=========
ASP.NET 2.0, 64-bit version

To run the 64-bit version of ASP.NET 2.0, follow these steps:

  1. Click Start, click Run, type cmd, and then click OK.
  2. Type the following command to disable the 32-bit mode:

    cscript %SYSTEMDRIVE%\inetpub\adminscripts\adsutil.vbs SET W3SVC/AppPools/Enable32bitAppOnWin64 0

  3. Type the following command to install the version of ASP.NET 2.0 and to install the script maps at the IIS root and under:

    %SYSTEMROOT%\Microsoft.NET\Framework64\v2.0.50727\aspnet_regiis.exe -i

  4. Make sure that the status of ASP.NET version 2.0.50727 is set to Allowed in the Web service extension list in Internet Information Services Manager.

===========

After this, I checked the app pool identity and matched it with the SharePoint ‘s app pool identity and it worked after that.

And it worked like a charm 🙂

 

 

Posted in Sp 2007 | Tagged: , , , | 3 Comments »

 
%d bloggers like this: