Using SharePoint 2010 Word Automation Services to convert document synchronously

A few months ago a fellow developer asked me how to use WAS in an application that requires synchronous document conversion. In the current post I show you a simple way for that.

As you might know (if not you can read a bit more about that at the end of this post), you can submit Word documents to WAS and let it convert the documents to PDF or other formats like XPS. WAS works as a timer job, so conversion is done based on the schedule of the job that you should set based on the number of documents to be converted and the free resources of the server. As in the case of any timer job, you can start the Word Automation Services Timer Job immediately using the web UI and from custom code as well.

For the sample method I pass in the document content as a byte array and the converted document is returned by the method as a byte array as well. First I’ve implemented a Stream-based solution but found it is easier to work with byte arrays in this case (see reason a bit later).

After preparing and starting the ConversionJob, we start the WAS timer job if immediate conversion is requested, then wait until the conversion is finished either successfully or unsuccessfully or until the timeout interval elapsed. In case of timeout, we cancel the conversion process. Next we display possible conversion errors and delete the documents from the working document library if requested.

1. private byte[] ConvertDocument(SPWeb web, byte[] docToConvert, bool isImmediate,
2.     String conversionLibName, int timeOutSecs, bool deleteDocs)
3. {
4.  
5.     byte[] result = null;
6.     SPList conversionLib = web.Lists[conversionLibName];
7.  
8.     SPFolder folder = conversionLib.RootFolder;
9.  
10.     // Get the default proxy for the current Word Automation Services instance
11.     SPServiceContext serviceContext = SPServiceContext.GetContext(web.Site);
12.     WordServiceApplicationProxy wordServiceApplicationProxy =
13.         (WordServiceApplicationProxy)serviceContext.GetDefaultProxy(typeof(WordServiceApplicationProxy));
14.  
15.     ConversionJob job = new ConversionJob(wordServiceApplicationProxy);
16.     job.UserToken = web.CurrentUser.UserToken;
17.     job.Settings.UpdateFields = true;
18.     job.Settings.OutputSaveBehavior = SaveBehavior.AlwaysOverwrite;
19.     job.Settings.OutputFormat = SaveFormat.PDF;
20.  
21.     String docFileName = Guid.NewGuid().ToString("D");
22.  
23.     // we replace possible existing files on upload
24.     // although there is a minimal chance for GUID duplicates
25.     SPFile docFile = folder.Files.Add(docFileName + ".docx", docToConvert, true);
26.     conversionLib.AddItem(docFileName + ".docx", SPFileSystemObjectType.File);
27.  
28.     String docFileUrl = String.Format("{0}/{1}", web.Url, docFile.Url);
29.     String pdfFileUrl = String.Format("{0}/{1}.pdf",
30.         web.Url, docFile.Url.Substring(0, docFile.Url.Length – 5));
31.  
32.     job.AddFile(docFileUrl, pdfFileUrl);
33.  
34.     // let's do the job
35.     // Start-SPTimerJob "Word Automation Services"
36.     job.Start();
37.  
38.     if (isImmediate)
39.     {
40.         StartServiceJob("Word Automation Services Timer Job");
41.     }
42.  
43.     ConversionJobStatus cjStatus = new ConversionJobStatus(wordServiceApplicationProxy, job.JobId, null);
44.     // set up timeout
45.     TimeSpan timeSpan = new TimeSpan(0, 0, timeOutSecs);
46.     DateTime conversionStarted = DateTime.Now;
47.  
48.     int finishedConversionCount = cjStatus.Succeeded + cjStatus.Failed;
49.     while ((finishedConversionCount != 1) && ((DateTime.Now – conversionStarted) < timeSpan))
50.     {
51.         // wait a sec.
52.         Thread.Sleep(1000);
53.         cjStatus = new ConversionJobStatus(wordServiceApplicationProxy, job.JobId, null);
54.         finishedConversionCount = cjStatus.Succeeded + cjStatus.Failed;
55.     }
56.  
57.     // timeouted -> cancel conversion
58.     if (finishedConversionCount != 1)
59.     {
60.         job.Cancel();
61.     }
62.  
63.     // we can output the possible failed conversion error(s)
64.     foreach (ConversionItemInfo cii in cjStatus.GetItems(ItemTypes.Failed))
65.     {
66.         Console.WriteLine("Failed conversion. Input file: '{0}'; Output file: '{1}'; Error code: '{2}'; Error message: '{3}';",
67.             cii.InputFile, cii.OutputFile, cii.ErrorCode, cii.ErrorMessage);
68.     }
69.  
70.     SPFile convertedFile = web.GetFile(pdfFileUrl);
71.     // shouldn't be null (unless there is a conversion error)
72.     // but we check for sure
73.     if ((convertedFile != null) && (convertedFile.Exists))
74.     {
75.         Stream pdfStream = convertedFile.OpenBinaryStream();
76.  
77.         result = new byte[pdfStream.Length];
78.         pdfStream.Read(result, 0, result.Length);
79.  
80.         // delete result doc if requested
81.         if (deleteDocs)
82.         {
83.             convertedFile.Delete();
84.         }
85.     }
86.  
87.     // delete source doc if requested
88.     if (deleteDocs)
89.     {
90.         docFile.Delete();
91.     }
92.  
93.     return result;
94.  
95. }
96.  
97. private void StartServiceJob(string serviceTypeName, string jobTypeName)
98. {
99.     SPFarm.Local.Services.ToList().ForEach(
100.         svc => svc.JobDefinitions.ToList().ForEach(
101.             jd =>
102.             {
103.                 if ((jd.TypeName == jobTypeName) && ((serviceTypeName == null) || (serviceTypeName == svc.TypeName)))
104.                 {
105.                     jd.RunNow();
106.                 }
107.             }));
108. }

To start immediate conversion in the ConvertDocument method I used a slightly modified version of the StartServiceJob method already introduced in my former post.

1. private void StartServiceJob(string serviceTypeName, string jobTypeName)
2. {
3.     SPFarm.Local.Services.ToList().ForEach(
4.         svc => svc.JobDefinitions.ToList().ForEach(
5.             jd =>
6.             {
7.                 if ((jd.TypeName == jobTypeName) && ((serviceTypeName == null) || (serviceTypeName == svc.TypeName)))
8.                 {
9.                     jd.RunNow();
10.                 }
11.             }));
12. }
13.  
14. private void StartServiceJob(string jobTypeName)
15. {
16.     StartServiceJob(null, jobTypeName);
17. }

The following code snippet shows a sample for calling the ConvertDocument method. In this case we request an immediate conversion with 240 seconds timeout and use the standard Shared Documents document library as a working folder, deleting the temporary files.

1. DateTime startTime = DateTime.Now;
2. byte[] doc = File.ReadAllBytes(@"C:\Data\HelloWorld.docx");
3. byte[] pdf = ConvertDocument(web, doc, true, "Shared Documents", 240, true);
4. if (pdf != null)
5. {
6.     File.WriteAllBytes(@"C:\Data\HelloWorld.pdf", pdf);
7. }
8. Console.WriteLine("Duration of conversion: {0} ms", (DateTime.Now – startTime).TotalMilliseconds);

The sample above requires further work if you would like to use it in a real application. First, you should add some extra error handling, for example check if default WordServiceApplicationProxy is found at all, etc.

Next, instead of submitting documents one by one to WAS it is better to create a ConvertDocument version that supports multiple document conversion. In this case you should use arrays of byte arrays that I found easier than bothering (like disposing through using blocks) with multiple streams simultaneously.

You can extend the supported conversion options to other formats as well, like XPS.

In a real life application you probably wouldn’t like to start immediate conversions on each requests because it might produce a heavy load on your servers. Instead you can create a specific queue for documents with the option for high privilege users to submit dedicated document types for immediate conversions and leave the default conversion schedule for the others.

Although our original goal was to create a synchronous conversion method, sometimes it is more comfortable to do the conversion asynchronously, for example to avoid locking of the UI thread. To support that in your application, you can start ConvertDocument in a separate thread and raise your custom .NET events to reflect the output of the conversion job.

Getting the process ID of the IIS / SharePoint application pool using PowerShell

It is easy to attach your debugger to the right SharePoint application pool using Visual Studio, especially if specific extensions like CKS.Dev or my VS extension is installed.

Sometimes life is not so trivial, you don’t have Visual Studio installed on the server and / or you should use other tools to debug your application. It would be great to have the right process ID in this case as well.

For example, assume you have to attach WinDbg or other debugging tools like Deblector to the process of the IIS application pool of a selected SharePoint application.

Two years ago I wrote a post about how to attach the VS debugger from a VS macro. In that post I’ve illustrated how to get the right process based on the application pool name (see the GetProcessIdByAppPoolName and GetAppPoolNameFromCommandLine methods in that post). In the post about my VS extension mentioned above you can find a similar solution as well.

In this post I show you two PowerShell methods that can be used when there is no Visual Studio on the computer or you simply do not want to work with that.

You should specify the name of the application pool for the Get-AppPoolProcessIdByName method and it displays the process ID of the matching application pool:

function Get-AppPoolProcessIdByName($name) 
{ 
     Get-WmiObject Win32_Process |
       Where-Object { $_.CommandLine -like "*w3wp.exe*" } |
       ForEach-Object { [regex]::Matches($_.CommandLine, "-ap ""(.+)"" -v") |
       Add-Member NoteProperty -Name ProcessId -Value $_.ProcessId -PassThru } |
       Where-Object { $_.Success -and $_.Groups.Count -gt 1 -and $_.Groups[1].Value -eq $name } |
       ForEach-Object { Write-Host $_.ProcessId }
}

Usage:

Get-AppPoolProcessIdByName("SharePoint – 80")

The Get-SPAppPoolProcessIdByUrl method first determines the corresponding SharePoint web application, then calls the Get-AppPoolProcessIdByName method to get the process ID.

function Get-SPAppPoolProcessIdByUrl($url) 
{ 
     $app = Get-SPWebApplication $url
     Get-AppPoolProcessIdByName($app.DisplayName)
}

Usage:

Get-SPAppPoolProcessIdByUrl("http://sp2010&quot;)

You can use Get-AppPoolProcessIdByName for arbitrary IIS application pool, but as you can see from the code (and as I intended to sign with the Get-SP prefix) the Get-SPAppPoolProcessIdByUrl method is SharePoint specific.

Creating a ToggleButton on SharePoint ribbon with alternating images

Although ToggleButton is a useful control on the ribbon of SharePoint 2010 user interface, unfortunately I found no built-in way to assign separate images to the on / off states of the button.

After playing awhile with the Internet Explorer Developer Tools (F12, see also the Tools menu in IE 8), I find out how to achieve that through the HTML object model built up by the ribbon infrastructure.

The following JavaScript code is from the command handler method of the custom ribbon components and assumes jQuery is already loaded.

Replace the commandId of the ToggleButton and the image properties (src and style attributes) to match your needs.

1. if (commandId === 'Ribbon.Custom.List.MyFeature.SwitchOnOff')
2.     var x = jQuery("a[id|='" + commandId + "']").find("img");
3.  
4.     if (properties.On) {
5.         x.attr("src", "/_layouts/images/MyImages/On.png");
6.         x.attr("style", "top: 0px; left: 0px;");
7.     }
8.     else {
9.         x.attr("src", "/_layouts/images/MyImages/Off.png");
10.         x.attr("style", "top: 0px; left: 0px;");
11.     }

You can use separate image files, or a single one with different vertical / horizontal offset value.

I’ve defined a global switchedOn variable to track the actual status of the ToggleButton easier, and query that value from other JavaScript methods:

var switchedOn = false;

I hope this trick helps you to make your SharePoint UI a bit more dynamic.

How to deploy a custom field with custom properties from a feature – the missing second part

Only readers following my blog from the beginning may remember my post I wrote about the deployment of custom fields having custom properties more than three years ago. Of course that post is about WSS 3.0.

At that time I described how to include the custom properties into the field definition even if these properties are not declared by the XSD validation schema. However, injecting the custom properties the way I described there did not have effect on the values of the custom properties the custom field actually deployed with. I promised then that in the next part I would show you how to resolve this issue. It’s better late than never, the second part comes now, updated for SharePoint 2010.

The trick to achieve our goal seemed originally to be really simple. The base idea behind  it was that an instance of the field must be created sooner or later before usage, and the values we would like to push into the custom properties were available on object creation in the field schema deployed.

I’ve modified the constructors of the custom field, adding a call to my custom InitializeProps method.

1. public SPFieldRegEx(SPFieldCollection fields, string fieldName)
2.     : base(fields, fieldName)
3. {
4.     InitializeProps();
5. }
6.  
7. public SPFieldRegEx(SPFieldCollection fields, string typeName, string displayName)
8.     : base(fields, typeName, displayName)
9. {
10.     InitializeProps();
11. }

The InitializeProps method is responsible for reading up the deployed custom property values from the field schema and setting them to the standard custom location in the schema.

1. // in the custom field feature definition
2. private readonly string customNamespaceUri = "http://schemas.grepton.com/sharepoint/&quot;;
3.  
4. private void InitializeProps()
5. {
6.     String regEx = (String)GetCustomProperty("RegEx");
7.  
8.     // value of custom property is null if not yet set
9.     if (regEx == null)
10.     {
11.  
12.         // load the field schema into an XML document
13.         XmlDocument schemaXml = new XmlDocument();
14.         schemaXml.LoadXml(SchemaXml);
15.  
16.         XmlNode fieldNode = schemaXml.SelectSingleNode("Field");
17.  
18.         if (fieldNode != null)
19.         {
20.             InitCustomProperty(fieldNode, "RegEx");
21.             InitCustomProperty(fieldNode, "ErrMsg");
22.             InitCustomProperty(fieldNode, "MaxLen");
23.         }
24.  
25.     }
26.  
27. }
28.  
29. private void InitCustomProperty(XmlNode fieldNode, String custPropName)
30. {
31.     XmlAttribute custPropOrigAttr = fieldNode.Attributes[custPropName, customNamespaceUri];
32.     // should not be null, but we check it
33.     if (custPropOrigAttr != null)
34.     {
35.         SetCustomProperty(custPropName, custPropOrigAttr.Value);
36.     }
37. }

After deploying my custom field, I’ve checked my field at the Change Site Column page and was happy to see my custom property values there:

However, it turned out quickly that the schema of my field was not updated as expected:

1. <Field
2.   ID="{54634385-A8AC-4898-BF24-E533EB23444F}"
3.   Name="RegExField"
4.   DisplayName="RegExField"
5.   StaticName="RegExField"
6.   Group="Grepton Fields"
7.   Type="SPFieldRegEx"
8.   Sealed="FALSE"
9.   AllowDeletion="TRUE"
10.   SourceID="http://schemas.microsoft.com/sharepoint/v3/fields"
11.   Description="This is the RegEx field"
12.   grp:RegEx="[0-9]"
13.   grp:MaxLen="20"
14.   grp:ErrMsg="Error!"
15.   xmlns:grp="http://schemas.grepton.com/sharepoint/" />

Of course, pressing OK on the Change Site Column page updated the field schema with the custom properties:

1. <Field
2.   ID="{54634385-A8AC-4898-BF24-E533EB23444F}"
3.   Name="RegExField"
4.   DisplayName="RegExField"
5.   StaticName="RegExField"
6.   Group="Grepton Fields"
7.   Type="SPFieldRegEx"
8.   Sealed="FALSE"
9.   AllowDeletion="TRUE"
10.   SourceID="http://schemas.microsoft.com/sharepoint/v3/fields"
11.   Description="This is the RegEx field"
12.   grp:RegEx="[0-9]"
13.   grp:MaxLen="20"
14.   grp:ErrMsg="Error!"
15.   xmlns:grp="http://schemas.grepton.com/sharepoint/"
16.   Required="FALSE"
17.   EnforceUniqueValues="FALSE"
18.   Version="1">
19.   <Customization>
20.     <ArrayOfProperty>
21.       <Property>
22.         <Name>RegEx</Name>
23.         <Value
24.           xmlns:q1="http://www.w3.org/2001/XMLSchema"
25.           p4:type="q1:string"
26.           xmlns:p4="http://www.w3.org/2001/XMLSchema-instance">[0-9]</Value>
27.       </Property>
28.       <Property>
29.         <Name>MaxLen</Name>
30.         <Value
31.           xmlns:q2="http://www.w3.org/2001/XMLSchema"
32.           p4:type="q2:double"
33.           xmlns:p4="http://www.w3.org/2001/XMLSchema-instance">20</Value>
34.       </Property>
35.       <Property>
36.         <Name>ErrMsg</Name>
37.         <Value
38.           xmlns:q3="http://www.w3.org/2001/XMLSchema"
39.           p4:type="q3:string"
40.           xmlns:p4="http://www.w3.org/2001/XMLSchema-instance">Error!</Value>
41.       </Property>
42.     </ArrayOfProperty>
43.   </Customization>
44. </Field>

That gave me the idea that I should try to deploy my custom field using this schema. I removed my custom attributes from the Field element and used the following XML to deploy the field.

1. <?xml version="1.0" encoding="utf-8" ?>
2. <Elements xmlns="http://schemas.microsoft.com/sharepoint/">
3.   <Field
4.     ID="{54634385-A8AC-4898-BF24-E533EB23444F}"
5.     Name="RegExField"
6.     DisplayName="RegExField"
7.     StaticName="RegExField"
8.     Group="Grepton Fields"
9.     Type="SPFieldRegEx"
10.     Sealed="FALSE"
11.     AllowDeletion="TRUE"
12.     SourceID="http://schemas.microsoft.com/sharepoint/v3/fields"
13.     Description="This is the RegEx field"
14.     Version="1">
15.     <Customization>
16.       <ArrayOfProperty>
17.         <Property>
18.           <Name>RegEx</Name>
19.           <Value
20.             xmlns:q1="http://www.w3.org/2001/XMLSchema"
21.             p4:type="q1:string"
22.             xmlns:p4="http://www.w3.org/2001/XMLSchema-instance">[0-9]</Value>
23.         </Property>
24.         <Property>
25.           <Name>MaxLen</Name>
26.           <Value
27.             xmlns:q2="http://www.w3.org/2001/XMLSchema"
28.             p4:type="q2:double"
29.             xmlns:p4="http://www.w3.org/2001/XMLSchema-instance">20</Value>
30.         </Property>
31.         <Property>
32.           <Name>ErrMsg</Name>
33.           <Value
34.             xmlns:q3="http://www.w3.org/2001/XMLSchema"
35.             p4:type="q3:string"
36.             xmlns:p4="http://www.w3.org/2001/XMLSchema-instance">Error!</Value>
37.         </Property>
38.       </ArrayOfProperty>
39.     </Customization>
40.   </Field>
41. </Elements>

I’ve removed my custom codes described above either, and tried to deploy the field.

Voilà! The field is deployed successfully, including the custom properties. At that point I really don’t understand if it would work for WSS 3.0 and if so, how I missed to find that solution three years ago. The main point it is working now and requires no hacking at all.

A mystical observation is that after deploying the field I was not able to get a reference for the new field using PowerShell:

$avFields = $web.AvailableFields
$avFields["RegExField"]

At the same time, the field is visible on the web UI, and the following C# code finds it:

SPFieldCollection avFields = web.AvailableFields;
SPField field = avFields["RegExField"];

If I create the field from code, PowerShell finds that either:

SPFieldCollection fields = web.Fields;
SPField fieldCode = new SPField(fields, "SPFieldRegEx", "RegExCode");
fields.Add(fieldCode);

If you know the reason for that, let me know, please!

Creating more advanced conditions for your ECB menus through jQuery and synchronous Client Object Model / WCF Data Services calls

In my last post I showed you how to alter the Edit Control Block (ECB) menu of SharePoint 2010 based on simple conditions. As I described, if you need to use more advanced conditions, for example based on list item field values or other information not available on the client side, you should apply a few tricks or even hacks.

For these advanced solutions it is useful to provide the context to our allowDeletion method through the ctx parameter, so we add this one to the parameter list of the method.

It means that our original condition in method AddListMenuItems is changed from

if (allowDeletion(currentItemID))

to

if (allowDeletion(ctx, currentItemID))

In the simplest case, the information is already there at the page on client side, you simply have to find the way to get it. Checking the HTML source of the page or using the Developer Tools in the Tools menu of Internet Explorer (you can press F12 as a shortcut key) usually helps you to find the right track.

For example, if you need the title of the item, that is included in the LinkTitle field (as we have an ECB menu linked to the title) for each items. When looking up the right item, we should use the ID of the item (passed as the itemID parameter) and the ID of our list view (available in ctx.ctxId).

Note: Although it should be evident, your page must load the jQuery library before you can use jQuery methods. Similarly, the ECMAScript Client Object Model library must be loaded before you reference its objects in methods later in the posts.

So if we would like to allow deletion only for items having title beginning with ‘V’ then we need to apply a method like this:

1. function allowDeletion(ctx, itemID) {
2.  
3.     var title = jQuery('div[Field="LinkTitle"][CTXName="ctx' + ctx.ctxId + '"][id="' + itemID + '"]').text();
4.  
5.     return (title.toUpperCase().startsWith("V"));
6.  
7. }

Next step is to see how to get the information if it is not available on the client side, but included in the list items on the server side. My first idea was to use the ECMAScript Client Object Model, however it turned out quickly that is not the best choice, as the AddListMenuItems method does not wait for the asynchronous reply supported by the client OM to be returned. Although I created a dirty workaround for that, I will show you that later.

Another alternative is to use REST protocol through the WCF Data Services.

As mentioned, our call must be synchronous, so we have to use jQuery.ajax specifying async: false. The next code snippet shows an example using the same condition applied above, that is title of the item must be started with ‘V’ to enable deletion.

1. function allowDeletion(ctx, itemID) {
2.  
3.     itemUrl = ctx.HttpRoot + "/_vti_bin/listdata.svc/" + ctx.ListTitle +"(" + itemID + ")?$select=Title"    
4.  
5.     var title = "";
6.  
7.     jQuery.ajax(
8.     {
9.         type: 'GET',
10.         url: itemUrl,
11.         dataType: 'json',
12.         success: function (result) {
13.             if (result.isOk != false) {
14.                 title = result.d.Title;
15.             }
16.         },
17.         data: {},
18.         async: false
19.     });
20.  
21.     return (title.toUpperCase().startsWith("V"));
22.  
23. }

Note: When working with jQuery and WCF Data Services, it is useful to know about the parsererror issue with field values containing apostrophe (single quote) and how to fix it.

It is important to note that you are not restricted to the actual list only. With a bit of additional complexity you can query related lists as well using $expand and the lookup field / user field IDs in the current list. If you are unsure how to compose the URL for you request, I suggest you to try to create the filter first in C# using the LINQ syntax, then use Fiddler to capture the request or apply this simple trick from Sahil Malik.

If network bandwidth and speed are limited, then waiting for the synchronous result will cause issues via blocking the UI thread of the browser. If that is the case you should consider “pre-caching” the data required for checks on page load through a single request. If the item count of the list is limited, then you can cache all of the data (in our case, the IDs and the related titles of the items is needed), in the case of a larger list, you should get only the items displayed in the current page of the view. To get the IDs of the items on the page you should run a jQuery select similar to the one we used to get the title in the first example, and submit the REST request using a complex condition.

Last, I would like to show you my workaround for calling ECMAScript Client Object Model synchronously. As you might now, it is officially / theoretically not possible. I found that it can be done technologically, although I had to spend a few hours Fiddlering and digging into the internals of the ECMAScript Client OM (SP.Runtime.debug.js), comparing the JavaScript methods to the ones in the managed Client OM classes using Reflector. If you demand more information about it I can give you more details, but now I publish it “as is”.

To build up the request I use the “traditional” ECMAScript Client Object Model (that, of course, should be already loaded before our script), but before sending it I get the built-up request in XML format from the internal methods of the OM, and send the request through jQuery.ajax in synchronous mode, just as like the case of WCF Data Services. The JSON result is “loaded” into JavaScript objects.

Be aware that the method described here don’t use the public, documented interfaces, so it is probably not supported by MS, and suggested to be used only as an experiment. There is no guarantee that it will work for you, especially if our environments are at different SP / cumulative update level.

1. function allowDeletion(ctx, itemID) {
2.  
3.     var title = "";
4.  
5.     try {
6.         var context = SP.ClientContext.get_current();
7.         var web = context.get_web();
8.         var selectedListId = SP.ListOperation.Selection.getSelectedList();
9.         var selectedListItem = web.get_lists().getById(selectedListId).getItemById(itemID);
10.         context.load(selectedListItem, "Title");
11.  
12.         // start hacking
13.         var pendingRequest = context.get_pendingRequest();
14.         var webRequest = pendingRequest.get_webRequest();
15.  
16.         // get the request XML
17.         var body = pendingRequest.$24_0().toString();
18.  
19.         // get the URL of client.svc
20.         var url = webRequest.get_url();
21.  
22.         // "initialize" request
23.         SP.ClientRequest.$1T(webRequest);
24.  
25.         // we should add digest later to the request as an HTTP header (see below)
26.         var digest = webRequest.get_headers()['X-RequestDigest'];
27.  
28.         jQuery.ajax(
29.         {
30.             type: "POST",
31.             data: body,
32.             url: ctx.HttpRoot + url,
33.             success: function (result) {
34.                 if (result.isOk != false) {
35.                     title = result[result.length - 1].Title;
36.                 }
37.             },
38.             headers: {
39.                 "x-requestdigest": digest
40.             },
41.             contentType: "application/x-www-form-urlencoded",
42.             async: false
43.         });
44.  
45.     }
46.     catch (e) {
47.         alert("Error: " + e);
48.     }
49.  
50.     return (title.toUpperCase().startsWith("V"));
51.  
52. }

BTW, the example above achieves the same, it allows deletion only for items having title beginning with ‘V’. Similarly to the former WCF DS example, the synchronous request through the network blocks the UI thread, and might require pre-caching in case of a slow network.

Disabling item deletion at the SharePoint user interface

Recently I worked on more projects where it was a nice-to-have requirement to disable deletion of specific items from a list.

Well, if you are at least a bit familiar with SharePoint your first idea might be the same as mine, that is to create a list item event receiver to capture ItemDeleting event and cancel it for your specific items. You are right, that should do the job, however I find this approach a bit ugly, as the users still have the option on the user interface to press the Delete Item button on the ribbon or select the Delete Item menu from the Edit Control Block (ECB) just to get that user-unfriendly warning from the event receiver cancellation to their face.

Wouldn’t it be much better to disable the corresponding user interface elements for the specific list items to prohibit deletion? As a safeguard, you can still apply an event receiver, but that would not be the standard way to stop users deleting the items from the browser.

That sounds great, but how to achieve the goal?

First, let’s see where that deletion options are for the user in a standard SharePoint web UI. The individual item can be deleted from the display and edit form ribbon and using the ECB menu of the selected item in a list view. Additionally, there is a Delete Item button on the ribbon of the list view that is to delete selected items.

In fact, disabling deletion on the UI of the display and edit form ribbon is pretty easy. You just need to use the TrimById method of the SPRibbon class in your form component passing the ID of the Delete Item button as the parameter, that is Ribbon.ListForm.Display.Manage.DeleteItem for the display form and  Ribbon.ListForm.Edit.Actions.DeleteItem for the edit form. If you need more info about how to create and inject your custom solution into the standard SharePoint form, read my former post.

In the overridden OnPreRender method of my customized ListFieldIterator I simply check if Delete Item button should be disabled, and if it should, then call the TrimById method using the right control ID.

1. protected override void OnPreRender(EventArgs e)
2. {
3.     SPFormContext formContext = SPContext.Current.FormContext;
4.  
5.     if ((formContext.FormMode != SPControlMode.New) && (!IsDeletionEnabled))
6.     {
7.         String deleteButton = (formContext.FormMode == SPControlMode.Display) ?
8.             "Ribbon.ListForm.Display.Manage.DeleteItem" : "Ribbon.ListForm.Edit.Actions.DeleteItem";
9.         SPRibbon.GetCurrent(this.Page).TrimById(deleteButton);
10.     }
11. }

The IsDeletionEnabled is a custom property where we check (for example, based on list or list item properties or user permissions) if we would like to allow deletion or not.

1. protected bool IsDeletionEnabled
2. {
3.     get
4.     {
5.         bool result = true;
6.  
7.         // your custom logic comes here
8.         if (((String)Item["Title"]) == "Do not delete")
9.         {
10.             result = false;
11.         }
12.  
13.         return result;
14.     }
15. }

That’s about the display / edit forms, let’s see the list view form, harder part of our goal.

As I’ve mentioned above there are two UI items to disable in the list view form, one is on the ribbon and another one in the ECB menu.

A serious difference between the UI components on the display / edit forms and the ones on the list view item forms is that in the first case we can check the condition and disable controls on the server side (we know which item the form is displayed for), while in the second case, user can select any items (or none of them) on the client side, so we should act accordingly on the client side without any postback of the page.

Unfortunately, I’ve started with the ribbon part and it caused me some headache and long hours spent unsuccessfully to play with ribbon extensions to check conditions and interact with the existing controls using jQuery and other tricks as well.

As none of my solutions for the list view ribbon worked perfectly, I switched to ECB.

When you need to work with the ECB menu, this series of post from Jan Tielens provides an excellent starting point. Although it is written originally for WSS 3.0, the content is still valid for SharePoint 2010 as well.

Since my goal was not to add custom items to the menu, but rather to delete an existing one, the trivial solution was to create my custom version of AddListMenuItems. The original version of this JavaScript method (assuming English – United States locale ID 1033) can be found in the 14\TEMPLATE\LAYOUTS\1033\CORE.JS file, but to have a more readable one, get it from CORE.debug.js instead.

Note: There are tons of posts on the web about how to inject JavaScript code into your page. Most of them favor to add a Content Editor Web Part to the page and paste your code into that. However, I feel this approach to be more appropriate for a  power-user, and usually add my scripts through a ScriptLink CustomAction or via an AdditionalPageHead. I found these methods are easier to integrate into and deploy from a Visual Studio 2010 solution, but they have a drawback that they do not affect only the target page. If you need a targeted solution you should first verify  in the code of the AdditionalPageHead if it is the right list then register the script using Page.ClientScript.RegisterClientScriptBlock, or through one of the several version of the static Register* method of ScriptLink  class.

1. SPContext context = SPContext.Current;
2.  
3. if ((context != null) && (context.List != null) && (context.List.Title == "YourList"))
4. {
5.     // register your script here
6. }

To target the script in the case of a ScriptLink CustomAction you can apply the method described in this post.

Back to the topic, whatever solution you choose to reference and deploy the JavaScript code, you should alter the copy of the original version of the AddListMenuItems method as described below. 

Note: It is important not to tamper with the original version as it affects all of your SharePoint sites and what might be even worse, it is not a supported modification.

Look up the following code section in the AddListMenuItems method. It is responsible for creation of the Delete Item menu item in ECB.

1. if (currentItemID.indexOf(".0.") < 0 && HasRights(0×0, 0×8)
2.       && !currentItemIsEventsExcp)
3. {
4.     if (ctx.listBaseType==4)
5.         strDisplayText=L_DeleteResponse_Text;
6.     else
7.         strDisplayText=L_DeleteItem_Text;
8.     strAction="DeleteListItem()";
9.     strImagePath=ctx.imagesPath+"delitem.gif";
10.     menuOption=CAMOpt(m, strDisplayText, strAction, strImagePath, null, 1180);
11.     CUIInfo(menuOption, "Delete", ["Delete"]);
12.     menuOption.id="ID_DeleteItem";
13.     CUIInfo(menuOption, "Delete", ["Delete"]);
14. }

Let’s modify that code a bit as shown here:

1. if (currentItemID.indexOf(".0.") < 0 && HasRights(0×0, 0×8)
2.       && !currentItemIsEventsExcp) {
3.     if (allowDeletion(currentItemID)) {
4.         if (ctx.listBaseType == 4)
5.             strDisplayText = L_DeleteResponse_Text;
6.         else
7.             strDisplayText = L_DeleteItem_Text;
8.         strAction = "DeleteListItem()";
9.         strImagePath = ctx.imagesPath + "delitem.gif";
10.         menuOption = CAMOpt(m, strDisplayText, strAction, strImagePath, null, 1180);
11.         CUIInfo(menuOption, "Delete", ["Delete"]);
12.         menuOption.id = "ID_DeleteItem";
13.         CUIInfo(menuOption, "Delete", ["Delete"]);
14.  
15.     }
16. }

Yes, the only difference is the extra condition that calls allowDeletion method passing the currentItemID parameter. The value of this variable is the ID of the actual list item.

How you implement allowDeletion in your JavaScript is up to your business requirements. Here are two examples of what can be achieved simply:

Disable deleting items having a specific ID. Probably not the most useful example but the simplest one for sure.

1. function allowDeletion(itemID) {
2.     return ((itemID == 1) || (itemID == 3));
3. }

Disable deleting folders. Useful if you deployed a folder structure into your list and would like to prohibit users from destroying that.

1. function allowDeletion(itemID) {
2.  
3.     var selectedItems = SP.ListOperation.Selection.getSelectedItems();
4.     var result = false;
5.  
6.     for (var i = 0; i < selectedItems.length; i++) {
7.  
8.         if (selectedItems[i].id == itemID) {
9.             // true (enable deletion), if it is a list item, not a folder
10.             result = (selectedItems[i].fsObjType == 0);
11.             break;
12.         }
13.     }
14.  
15.     return result;
16.  
17. }

If you need more sophisticated logic, like doing comparison with list item field values, you should apply some trick using WCF Data Services or even hacks to utilize the client object model but more about that in a forthcoming post.

And the most interesting part (at least, for me) of the story is coming now.

After deploying my script and testing the solution I realized that if you select an item that has its Delete Item menu removed from the ECB menu then Delete Item button on the ribbon is disabled. Nice! That is what I worked a lot earlier for without success. And that is the best that if you select multiple items from the list, and deletion is disabled at least for one of them the corresponding ribbon button is disabled as well.

To understand what happens behind the curtains I added some alert statements to my scripts (the simplest way of JavaScript debugging). It turned out, that the AddListMenuItems method (and our allowDeletion method as well) called once for each list item when they got selected first time. It means if you select multiple items by checking the topmost checkbox in the header, the methods are triggered for each items one by one. If a selected item is deselected, and selected the second (or even more) times, the methods are not triggered once more, so the state of the Delete Item menu won’t change. If it was removed (not added to be exact) the first time, it remains removed. If it was added the first time, it remain there. Of course, a page reload resets this kind of caching.

I removed (commented out) the script lines from this part of the function one by one, and found that the menu item actually gets added by this line:

menuOption = CAMOpt(m, strDisplayText, strAction, strImagePath, null, 1180);

However, if I remove this method call (actually, there are two of the same line there):

CUIInfo(menuOption, "Delete", ["Delete"]);

then the Delete Item ribbon button won’t be enabled, even if there are items to delete. In this case, the items can be still deleted through ECB’s Delete Item menu.

If you check the “signiture” of this method it is:

function CUIInfo(menuItem, command, enabledCommands)

That means it registers the “Delete” command for our menu item and sets the “Delete” command as an enabled command as well. I have not tracked the code any further, but it seems that the Delete Item button on the ribbon is enabled only if all of the selected list items have a menu item with enabled commands (CUIEnabledCommands property) containing the “Delete” command. Seems to be a very interesting infrastructure behind this.

Note: The method described here does not protect you from items being deleted in the Datasheet View or other customized views, not to mention deleting items from custom code or web service / WCF calls (like client object model WCF Data Services etc.).

Customizing SharePoint forms via creating our own ListFieldIterator

Altering either the design or the behavior of the standard forms that are used to display, edit existing list items or create new ones is a frequent customer request.

In this post I introduce you a technique I apply daily for such tasks, and give a few examples of possible applications. In later posts I plan to provide examples for the most interesting ones.

The standard forms I’m talking about are DispForm.aspx, EditForm.aspx and NewForm.aspx. Of course, you can customize further your existing custom forms as well using the very same technique.

Create an Empty SharePoint Project in Visual Studio 2010.

Create a SharePoint mapped folder that points to the ControlTemplates folder.

Right click on the ControlTemplates folder in Solution Explorer and select Open Folder in Windows Explorer.

Create a new Text Document file and rename it to MyCompany_MyListForm.ascx. Of course, you can use other file names either as long as you keep the file extension and the name is not conflicting with other files in 14\TEMPLATE\CONTROLTEMPLATES.

Drag’n drop MyCompany_MyListForm.ascx from Windows Explorer to the ControlTemplates folder in Solution Explorer of Visual Studio.

Open 14\TEMPLATE\CONTROLTEMPLATES\DefaultTemplates.ascx in a text editor.

Copy the header section of the user control file (all elements beginning with <%@) from DefaultTemplates.ascx and paste it to MyCompany_MyListForm.ascx in Visual Studio.

Add the following line to MyCompany_MyListForm.ascx (replace TagPrefix and namespace attributes with your ones if you wish, but keep them consistent with later changes in names):

<%@ Register TagPrefix="MyControls" Assembly="$SharePoint.Project.AssemblyFullName$" namespace="MyNameSpace"%>

Look up the RenderingTemplate having id="ListForm" in DefaultTemplates.ascx, select and copy this entire RenderingTemplate.

Close DefaultTemplates.ascx and if prompted, do not save any changes.

In MyCompany_MyListForm.ascx, paste the RenderingTemplate you copied earlier, and set its id attribute to MyListForm.

In MyCompany_MyListForm.ascx, look up this control:

<SharePoint:ListFieldIterator runat="server"/>

and replace it with this:

<MyControls:MyListFieldIterator runat="server"/>

Add a new Class to your SharePoint project (let’s call it MyListFieldIterator.cs), set the namespace to MyNameSpace and add:

using Microsoft.SharePoint.WebControls;

Alter the class definition as:

public class MyListFieldIterator : ListFieldIterator

We finished to create the base of the customized ListFieldIterator. Build and deploy the solution.

Using SharePoint Designer, select a site and list you would like to use with the new ListFieldIterator. Open the form you would like to alter. You can change any or all of DispForm.aspx, EditForm.aspx, NewForm.aspx.

Look up the ListFormWebPart in the Code view and add a new property TemplateName with the value set earlier as the id attribute of the RenderingTemplate to the WebPart:

<TemplateName xmlns="http://schemas.microsoft.com/WebPart/v2/ListForm">MyListForm</TemplateName&gt;

Of course, if the TemplateName already exists there, you should update its value instead of creating a new one.

Note: Instead of using SPD you can deploy this settings from your SharePoint solution as well. For example, we typically deploy it using a custom feature receiver that utilize the SPLimitedWebPartManager and Reflection to set web part properties. I might be to blog about that later as my time allows.

Save the form in SPD and open the list using the web UI. Select an item to display / edit, or start to create a new item base. You should not see any difference now.

Switch back to VS, and add the text Hello World to MyCompany_MyListForm.ascx, just after the opening <Template> tag. Redeploy the solution and check the altered forms on the web UI. The Hello World message should be now visible.

At this point you may say it is much ado about nothing. I admit it is not the simplest way to display a message, but all we discussed earlier is only the base of a framework we can use later to achieve some really powerful results. Just to name some of them:

• You can add custom activity to your form that is performed when the item is saved or specify what action to perform instead of saving. For example, you can cancel saving, send a mail, create a task, set permissions or even call external components.
• You can interact with the list field controls and other controls on the form as well. You can hide some of the controls or set them to read-only mode even in the edit / new mode.
• You can create custom field control arrangements in place of the standard one column multiple rows (one per field) form design.
• Through your control you can insert a link to a custom JavaScript / jQuery file that adds dynamic behavior to your form.
• You can interact with the ribbon from your custom ListFieldIterator, for example, you can disable ribbon elements based on your business requirements.
• You can do custom validation. Although SharePoint 2010 provides validation at the form and the field level, both of these have their limitations. Sometimes you simply need to have multiple validation rules and error messages for a single form / field and you might need to validate field types not supported by the out of the box validation (lookup or user fields, managed metadata or even your custom field types).

If you read my post about custom field types vs. event receivers in the past, you can recognize some of the possibilities listed there, like this one about validation or this other one about hiding fields or displaying them as read-only in edit / new mode. It’s not a random coincidence, you can use both of these methods to achieve the same goals (after all, you can get a reference to the ListFieldIterator instance from the custom field and vice versa), however I recently prefer the technique of custom ListFieldIterator, simply because I found it is simpler and more powerful, easy to maintain and deploy, and probably fits better to the whole picture of form customization.

Hacked solutions in the out of the box SharePoint files

This post is really just a quick and funny side note from the not-always-so-funny life of a SharePoint developer.

A few days ago I had to work with the default XSL files located at 14\TEMPLATE\LAYOUTS\XSL. One of those files is internal.xsl. Opening that file I found an evidence of the fact that not only me, but other developers do sometimes hacks as well to achieve their goals. Even at Microsoft.

At the very beginning of the file there is a comment:

<!– This is a hack ensuring that XSLT runtime tables are changed incrementally. –>

And the match attribute of the template is even more surprising:

<xsl:template match="Hack-Hack-Hack">

After this I searched for other files containing the word hack, and there are really a few one. The SP.UI.Pub.Ribbon.debug.js contains for example a variable called hackElement.

Determining the path of the virtual directory for a SharePoint web application from Visual Studio

The other day I was to add a minor extension to my customized version of CKS.Dev. If I select a deployable item (or a folder containing such items) in Solution Explorer, the extension should display a custom menu item that opens the deployment location using Windows Explorer.

It is similar to the standard Visual Studio 2010 menu item Open Folder in Windows Explorer, but instead of opening the original location, this menu should open the corresponding folder in the 14 hive (for items deployed to {SharePointRoot}) or in the InetPub the (for items deployed to {WebApplicationRoot}).

// BTW, I already added my own version of Open Folder in Windows Explorer to Visual Studio 2008 (and another one that opens the folder in the command shell) a few years ago and so was happy to welcome it built into VS 2010. Seems that other developers missed that feature as well.

It was easy to determine the root of the SharePoint installation (that is the 14 hive). Assuming you have a reference to an ISharePointProject (let’s call it project)  this path is available as project.ProjectService.SharePointInstallPath (the property is populated from the registry hive HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.0 with the Location value ). Actually, the current version of CKS.Dev uses exactly this way to get the path in the QuickDeploymentUtilities.SubstituteRootTokens static method. However, there is nothing that handles the {WebApplicationRoot} token.

So my first idea was to create a custom SharePoint command to retrieve the path of the virtual directory for the SharePoint application set for the current project in Visual Studio. But before doing so, I should have known how to get the value on the server side.

The path we need is available as a DirectoryInfo value in the Path property of the SPIisSettings class (Microsoft.SharePoint.Administration namespace, Microsoft.SharePoint assembly). To get the SPIisSettings instance corresponding to our web application we could use either the IisSettings property or the GetIisSettingsWithFallback method of the SPWebApplication class. In both cases we should specify the zone we need info about.

But after analyzing the Path property using Reflector it turned out that Microsoft has already created the command we need: the GetWebApplicationLocalPath method can be found in the SharePointCommands class (Microsoft.VisualStudio.SharePoint.Commands namespace, Microsoft.VisualStudio.SharePoint.Commands.Implementation.v4 assembly in GAC).

Note: If you check the SharePointCommands class you can see there are a lot of useful commands implemented in this class, and there are many more in other (usually not public) classes provided by Visual Studio 2010. I spent a few days last year with discovering the possibilities of these commands and I can assure you there are a few real gems among them. I’ve been planning a series of blog posts about this topic since then, but unfortunately I missed the time to publish them at the level I would like to do. So might be I will publish them in a kind of raw format to help your work. I think if you are at least aware of the existence of these commands it is a large step into the right direction.

Having the GetWebApplicationLocalPath method found (that is the Microsoft.VisualStudio.SharePoint.Commands.GetWebApplicationLocalPath command) it is quite trivial to get the path of the web application root:

String webApplicationRoot = project.SharePointConnection.ExecuteCommand<String>("Microsoft.VisualStudio.SharePoint.Commands.GetWebApplicationLocalPath");

Creating a view that filters list items based on user permissions using CAML

Recently I got a task that was about creating a SharePoint view that displays only list items that the logged-in user has editor permissions for. Sounds a bit tough at first, but if you have ever heard about the PermMask field (not very well documented, but you can read more about that here and here) then it gives you some hope.

So I’ve created a test list (called SecTest) and created two items. One of my test users has admin rights, the second one has editor permission for the first item and read permission for the second one. The list inherits the permissions of the parent site, the permission inheritance is broken for the second item.

First I’ve tried to use SharePoint designer to customize the existing All Items view of the list.

Added the PermMask to the FieldRefs:

<FieldRef Name="PermMask" />

And created the filter part for the Query:

<Where>
    <Eq>
        <FieldRef Name=’PermMask’ />
        <Value Type=’Computed’>somevalue</Value>
    </Eq>
</Where>

(I’ve tried different permission mask values, all with the same result.)

Saving the view in SPD resulted the following warning:

Pressing the Details I’ve got the following message:

soap:ServerException of type ‘Microsoft.SharePoint.SoapServer.SoapServerException’ was thrown.0×80131904

(I’ve tried to use Text instead of Computed as the Type attribute of the Value in the Where section of the CAML expression, but it did not help.)

Checking the view in the browser produced an error as well.

I’ve found the following related error message in the SharePoint log (one for the saving in SPD and another one for opening the view in IE):

System.Data.SqlClient.SqlException: Conversion failed when converting the nvarchar value ’0xb008431061′ to data type int.     at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)     at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)     at System.Data.SqlClient.SqlDataReader.HasMoreRows()     at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)     at Microsoft.SharePoint.SPSqlClient.ExecuteQueryInternal(Boolean retryfordeadlock)     at Microsoft.SharePoint.SPSqlClient.ExecuteQuery(Boolean retryfordeadl…    
…ock)    
Unable to execute query: Error 0×80131904    
Unknown SPRequest error occurred. More information: 0×80131904    
<nativehr>0×80131904</nativehr><nativestack></nativestack>    
<nativehr>0×80131904</nativehr><nativestack></nativestack>    

FYI, the hexadecimal value causing the conversion error (see the bolded section above, 0xb008431061 = 756052856929 in decimal) corresponds the permission mask of the Read permission level (see the value of the PermMaskfield in the Roles table of the content database, where in the record where Title is Read, that is SELECT PermMask FROM [YourContentDB].[dbo].[Roles] WHERE Title = ‘Read’)

Next, I’ve tried the same from code using a test console application:

1. SPList list = web.Lists["SecTest"];
2. SPQuery query = new SPQuery();
3. // tried with and without these lines
4. //query.IncludeAllUserPermissions = true;
5. //query.IncludePermissions = true;
6. query.Query = "<Where><Eq><FieldRef Name='PermMask' /><Value Type='Computed'>0xb008431061</Value></Eq></Where>";
7. query.ViewFields = "<ViewFields><FieldRef Name='PermMask' /></ViewFields>";
8.  
9. SPListItemCollection items = list.GetItems(query);
10.  
11. // exception is thrown at the next line
12. foreach (SPItem item in items)
13. {
14.     Console.WriteLine(item["PermMask"]);
15. }

The code produced the same error.

I’ve started SQL Server Profiler to create a trace of the SQL side during the code execution. The trace captured the following errors:

Exception
Error: 245, Severity: 16, State: 1
User Error Message
Conversion failed when converting the nvarchar value ’0xb008431061′ to data type int.

The source of the error was the following parameterized query:

exec sp_executesql N’DECLARE @DocParentIdForRF uniqueidentifier SELECT TOP 1 @DocParentIdForRF = Docs.Id FROM Docs WHERE Docs.SiteId = @SITEID AND Docs.DirName = @FDN AND Docs.LeafName = @FLN;   SELECT ScopeId, Acl, AnonymousPermMask FROM Perms WITH (INDEX=Perms_Url) WHERE SiteId=@SITEID AND DelTransId = 0x AND ScopeId IN (SELECT tp_ScopeId FROM Lists WITH (NOLOCK) WHERE tp_WebId=@L7 AND tp_ID = @L2 UNION SELECT t1.ScopeId FROM UserData INNER JOIN Docs AS t1 WITH(NOLOCK) ON (UserData.[tp_ID]=t1.[DoclibRowId]) AND (UserData.[tp_RowOrdinal] = 0) AND (t1.SiteId=UserData.tp_SiteId) AND (t1.SiteId = @SITEID) AND (t1.ParentId = UserData.tp_ParentId) AND (t1.Id = UserData.tp_DocId) AND (UserData.tp_ListId = @L2) WHERE ( (UserData.tp_Level = 1) ) AND (UserData.tp_SiteId=@SITEID AND (UserData.tp_ParentId=@DocParentIdForRF)) AND (UserData.tp_RowOrdinal=0) AND ((UserData.[tp_ID] = N”0xb008431061”) AND t1.SiteId=@SITEID AND (t1.ParentId=@DocParentIdForRF))) OPTION (MAXDOP 1)’,N’@LFFP uniqueidentifier,@SITEID uniqueidentifier,@L2 uniqueidentifier,@FDN nvarchar(4000),@FLN nvarchar(4000),@LISTID uniqueidentifier,@RequestGuid uniqueidentifier,@L7 uniqueidentifier’,@LFFP=’00000000-0000-0000-0000-000000000000′,@SITEID=’D2F4F094-9626-4B84-87D3-260B6B9213EF’,@L2=’C0768FD2-B3D6-478E-ADF3-4A1550DF4CBA’,@FDN=N’Lists’,@FLN=N’SecTest’,@LISTID=’C0768FD2-B3D6-478E-ADF3-4A1550DF4CBA’,@RequestGuid=’E75EC6C1-5CDE-4368-B55C-A9AAA661E539′,@L7=’3ED0DC82-1AC6-4A29-A177-56DA059B2EF8′

I’ve found that removing the bolded condition above helps to run the query without any exception.

In my case the result was (or would be?)  the next one:

The ScopeId field identifies the entity the permission is set on. See the Perms table in the content DB to decode it. In my case the first row is related to the root web, the second on is to the second item in the list, for which we set individual permissions. The Acl field stores the access control list for the entity in a binary format.

It seems that if you include the PermMask field in the CAML query, SharePoint tries to read up the related permission settings but fails due to a bug.

I’ve tried to locate the source of the issue, but was not fully successful. From the stack trace it seems that the ExecuteQueryInternal method of the internal SPSqlClient class (Microsoft.SharePoint namespace, Microsoft.SharePoint assembly) is called to run the SQL query, this method calls the ExecuteReader method of the Microsoft.SharePoint.Utilities.SqlSession, but in the ReadInternal method of System.Data.SqlClient.SqlDataReader the exception is thrown. However I’ve not found the method where the query is incorrectly assembled. I assume it is related to the SetCommand method of the SPSqlClient class but I’ve not yet found the caller of the method. The signature of the method (IntPtr queryText, uint lenQueryText, bool isStoredProc) and the Marshal.PtrToStringUni call within the method suggest that the caller might be even an unmanaged code. See the similar SetCommand method of the SPSqlClientClass class (Microsoft.SharePoint.Library namespace, Microsoft.SharePoint.Library assembly):

[MethodImpl(MethodImplOptions.InternalCall, MethodCodeType=MethodCodeType.Runtime)]
public virtual extern void SetCommand([In] IntPtr pstrQueryText, [In] uint cchQueryText, [In] bool bStoredProcedureSyntax);

Very likely that I have to find an alternative approach to create my permission based view. I have some further ideas, but more about them in a possible post later.