Lets Learn

Opinion Matters

Archive for the ‘Sp 2007’ Category

Content Types, Features and Events

Posted by Ankush on July 5, 2011

Recently I worked on a case where I had to deploy the XML based site content type through feature and I noticed a strange behavior which makes sense once you know the complete picture. The complete exercise was a very good learning so I thought I will post a blog (or may be a series) on this.

Create a site content type based on XML and have an event receiver attached to it.
Use the content type in a list
Modify the Event handler (add some more events)

Actual Results

The newly added event will not fire

Expected Result
New events should fire. Even though you update the content type but this doesn’t work. why? Lets just start the learning and the final conclusion process.

Step # 1 –> Create the Site Content Type
I started looking out for documentation on this topic and there was not much documentation available> Even if I look into wss.xsd, there was not much help available. So I started building a smaple content type.

So, we need some fields ..lets define fields in an XML file, fields.xml. Create a new XML file in VS and then attach the WSS.XSD and then start typing it.  Please have a look at the sample XML file I have created:

<?xml version="1.0" encoding="utf-8" ?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">

<Field Type=”Note”
Group=”Sample Columns”
ReadOnly=”FALSE” />

<Field Type=”URL”
DisplayName=”How To Link”
Group=”Sample Columns”

<Field Type=”Choice”
Group=”Task Management Columns”




Once, it is done, lets create the content type which will use these fields (name the file, ContentType.xml):

<?xml version="1.0" encoding="utf-8" ?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <ContentType ID="0x012000ACD1F08FAE0E4478960D38AEFE7769C4"
                   Name="Sample Content"
                   Group="Sample Content Management"
                   Description="Contains Sample Field.">
      <FieldRef ID="{165B2AA9-DC9C-4ca5-A004-0CDD19042F20}" Name="SampleNotes" Required="TRUE" ShowInNewForm="FALSE"/>
      <FieldRef ID="{57632474-85EA-4255-ABE5-A1E6B545766B}" Name="SampleHowToLink" Required="TRUE" ShowInEditForm="FALSE"/>
      <FieldRef ID="{10333110-A154-4321-A04D-6B1D9654DEB8}" Name="SampleStatus" Required="FALSE" ShowInEditForm="FALSE"/>




You can easily get help on the attributes declared here doing a web search or in SDK so I am not going in detail explaining about them.

Now all we need is a feature.xml which will use the above 2 files.

<?xml version="1.0" encoding="utf-8" ?>
<Feature xmlns="http://schemas.microsoft.com/sharepoint/" Id="32F1588E-138B-4abd-ABA2-47A585A61DB3" Scope="Site" Title="Sample Feature">
    <ElementManifest Location="Fields.xml"/>
    <ElementManifest Location="ContentType.xml"/>


Now we have everything in place, lets try to activate it.

Go to C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\FEATURES and create a folder, SampleContentType and copy Fields.xml, ContentType.xml, feature.xml into this folder.

Open command prompt and browse to C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN. (If the environment variable is not set for SharePoint). Run the following command
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>STSADM.EXE -o installfeature -name SampleContentType

Go to Site collection Features and check if the “Sample Feature” is available. If yes, activate it and use it in a list and see how it appears.

Well this is all simple huh..now in the next exercise we will add the event receivers and then try to update them.

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

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:


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 ****”);

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());

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(“<SetList Scope=\”Request\”>” + wflistID + “”);
batchString.Append(“<SetVar Name=\”ID\”>” + Convert.ToString(i) + “”);
batchString.Append(“<SetVar Name=\”Cmd\”>Delete”);




web.AllowUnsafeUpdates = true;
Console.WriteLine(“Executing query… “);
Console.WriteLine(“Batch Execution (Start) : ” + DateTime.Now.ToString());
string result = web.ProcessBatchData(batchString.ToString());
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(“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()

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

using (SqlConnection objConn = new SqlConnection(connString))

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.


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 »

Debug a SharePoint Workflow

Posted by Ankush on April 25, 2011

Its been a while since I blogged anything. Main reason was, I was on vacation and since then I have come back, I was really really busy.  It was really good to visit places like Mathura and Muzaffarnagar (my birthplace). Anyway, here I am with my first blog after my vacation.

Recently I worked on case where customer has developed a workflow to send multiple emails (using SMTP Server) when an item was added to the list. The workflow was getting fired but it sending emails only for the first list item. For the second list item, it was kind of stuck(because there was no email sent for the second list item and so forth).

I started with very basic troubleshooting steps, like checking ULS logs, Event Viewer and nothing major was there.  I wish I could have debugged the workflow but it was not possible to install Visual Studio on the problematic server. I wish I could have got workflow debug information and then I got one. As we know it is very painful to debug the workflow. So the easy thing is, open
the web.config and have these entries present

<add name=”System.Workflow LogToFile” value=”1″ />
<add name=”System.Workflow.Runtime” value=”All” />
<add name=”System.Workflow.Runtime.Hosting” value=”All” />
<add name=”System.Workflow.Runtime.Tracking” value=”All” />
<add name=”System.Workflow.Activities” value=”All” />
<add name=”System.Workflow.Activities.Rules” value=”All” />

This will create a log file under C:\WINNT\system32\inetsrv.

Once you have the log file, you can view what activates being executed and if there was any error. And this really helped.  I was able to pin point one activity where the second workflow instance was stuck and it turns out that customer was keeping a constant task id. We changed this to create an new one for each workflow instance and it worked.

Try this in your workflow projects and see if this helps!! And if it does, post a comment here.!!!

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

Inserting Items in/Connecting to SharePoint List using OLEDB (ACE) Provider

Posted by Ankush on February 18, 2011

Recently I worked on a case where customer was trying to insert a record using OLE DB provider in SharePoint list.  Well that’s easy then where is the problem???? Problem came when list has a date filed and for some reason OLEDB provider was not able to update the date field.

So here is some more detail about it:

Problem: Consider you have created a VB.Net which uses the OLEDB provider to connect to a SharePoint list and insert a new record. Now the list contains some date field which should also be modified.

Environment: SharePoint 2007, VB.Net project (or as a matter of fact you can do it anywhere. You just need to refer to the provider. I also did it in using VBA)

Complete Solution:

Assumptions: You have created a list name Test which contains two columns:  Title and Start (which is of date type)

<< Solution which didn’t work>>

Step 1: Create the connection string and declare the appropriate variables. Please note that I have used the GUID of the list which you can easily get.

Dim conSharePoint As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strConnection As String
Dim intIMEX As Integer
Dim strDatabase As String
Dim strList As String
Dim strTable As String

intIMEX = 2                                         ‘IMEX=1 for Read only, IMEX=2 for Read/Write
strDatabase = “http://abha21597318:9354
strList = “{46218811-6D9A-4DBD-AEE4-9F122692E0AE}”  ‘LIST=GUID to specific list or view
strTable = “Test”                                   ‘TABLE=Display name of list
strConnection = “Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=” & intIMEX & “;RetrieveIds=Yes;” & “DATABASE=” & strDatabase & “;” & “LIST=” & strList & “;VIEW=;RetrieveIds=Yes;TABLE=” & strTable

Step 2: Open the connection and recordset

conSharePoint = New ADODB.Connection()
rst = New ADODB.Recordset()
rst.Open(“Test”, conSharePoint, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockPessimistic)

Step 3: Add a new record

With rst
.Fields(“Title”).Value = “Test”
.Fields(“Start”).Value = “2011-04-15 00:00:00” ‘ THIS IS WHERE IT FAILS
End With
Now I tried a lots of date formats but nothing seems to worked for me. If you happen to know a workaround/date format which works, please leave a comment here.

<< Solution which DID work>>

I started thinking about other workarounds and I used the popular workaround SOAP toolkit. I downloaded the SOAP toolkit and used the following code to do it. Fiddler tool really helped me in building the SOAP queries.

Sub InsertUsingSOAP()

Dim oSOAPClnt As SoapClient30
Dim sht As String
Dim wbkPath As String
Dim sts() As Object
Dim sId As String
Dim dom As IXMLDOMSelection
Dim ndListView As IXMLDOMNode
Set oSOAPClnt = New SoapClient30
Dim myXMLNodeList As MSXML2.IXMLDOMNodeList
Dim root As MSXML2.IXMLDOMElement
Dim xmlDoc As New MSXML2.DOMDocument30
Dim strListID As String
Dim xmlText As String

oSOAPClnt.MSSoapInit (“http://abha21597318:9354/_vti_bin/Lists.asmx?WSDL“)
‘ Test -> List Name
Set dom = oSOAPClnt.GetListAndView(“Test”, “”)
Set ndListView = dom.item(0)
‘ Make sure to Change the Attributes index
strListID = ndListView.ChildNodes(0).Attributes(7).NodeValue
xmlDoc.async = False

xmlText = “<root>” + _
“<Batch OnError=’Continue’ ListVersion=’24’ PreCalc=’TRUE’ xmlns=”>” + _
“<Method ID=’1′ Cmd=’New’>” + _
“<Field Name=’Title’>Test Insert from VBA</Field>” + _
“<Field Name=’Start’>2011-04-15 00:00:00</Field>” + _
“</Method>” + _
“</Batch>” + _
xmlDoc.LoadXML (xmlText)
If xmlDoc.parseError.ErrorCode <> 0 Then
Dim myErr
Set myErr = xmlDoc.parseError
MsgBox (myErr.reason)
MsgBox xmlDoc.XML
End If
‘ Set up IXMLDOMNodeList object

Set root = xmlDoc.DocumentElement
Set myXMLNodeList = root.ChildNodes
oSOAPClnt.UpdateListItems strListID, myXMLNodeList

End Sub

Note : The same code which doesnt work in SP 2007 works in SP 2010. So you can use OLEDB provider to insert a record in list in SP 2010.

There is also one interesting scenerio comes when you want to use user name and password in SOAP tool kit. So this is what you can do to do that.

Sample Code
‘provide authentication info in case of basic auth. For windows ‘ authe. you don’t need to pass this info. Our connector asks windows security ‘ layer for this

SoapClient3.ConnectorProperty(“AuthUser”) = “username”
Basically If you don’t pass authentication information then the SoapToolkit’s Connector will  automatically query the windows security layer for the integrated authentication information for the current user and will use these credentials.If you want to supply the authentication information then you can use the ConnectorProperty as demonstrated above to pass the username and password

Happy Reading!!!!!

Update : I thought about some other interestingoptions and here is the list:

1. Have an access database , linked table and then update records from there
2. Create a .net application which can consume web services and make it COM visible. Use this assembly in VBA
3. Use XMLHttp and directly invoke webservice. It will be a bit  tedious job as it requires one to manipulate and construct the XML.


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

%d bloggers like this: