Chandru's SharePoint / MCMS Blog !

Remove duplicate Workflows displayed on the existing sites

Let us consider the following scenario.

• You have a Microsoft Office SharePoint Server 2007 site that includes a document library. This document library contains a document for which the Approval workflow and the Collect Feedback workflow are created.
• You save the site as a template.
• You create a new site that uses the saved template.

In this scenario, two instances of the Approval workflow and of the Collect Feedback workflow are displayed in the document library on the new site.

A workaround to this problem is available at http://support.microsoft.com/kb/951654.  But, what about the sites created earlier as described in the scenario.  How to remove OOB duplicate workflows for existing sites.  Programming comes to the rescue huf! 

Here is the sample code to remove duplicate workflows at the web application level.

SPFarm thisFarm = SPFarm.Local; 

SPWebService service = thisFarm.Services.GetValue<SPWebService>("");

SPWebApplication spWebApp = service.WebApplications["WebApp"];// Name of the web application

SPSiteCollection siteColl = spWebApp.Sites;

foreach (SPSite site in siteColl)

{

    SPWebCollection webColl = site.AllWebs;

    foreach (SPWeb web in webColl)

    {

        try

        {

            SPList spList = web.Lists["Shared Documents"];//Document Library Name

            SPWorkflowAssociationCollection spWFAssociation = spList.ContentTypes["Document"].WorkflowAssociations;

            List<string> aListDuplicates = new List<string>();

            int associationCount = spWFAssociation.Count;

            for (int i = associationCount - 1; i >= 0; i--)

            {

                if ((spWFAssociation[i].InternalName == "Approval" && !(aListDuplicates.Contains(spWFAssociation[i].InternalName))) ||

                              (spWFAssociation[i].InternalName == "Collect Feedback" && !(aListDuplicates.Contains(spWFAssociation[i].InternalName))))

                {

                    aListDuplicates.Add(spWFAssociation[i].InternalName);

                    spList.ContentTypes["Document"].RemoveWorkflowAssociation(spWFAssociation[i]);

                    spList.Update();

                }

            }

        }

        catch (Exception ex)

        {

            Console.WriteLine("Error occured :" + ex.StackTrace.ToString());           

        }

    }

}

Add custom columns in document content type by default when MySite is created and also for Existing MySite

I recently got a requirement asking how can we create Mysite for new users(also existing users) so that it already contains the custom site columns(field type choice) and Document content type with those custom columns. Well, you know the answer “Feature Stapling”. There are good number of articles that explains about Feature Stapling.

Yes, using feature stapling we can get this done easily for newly created MySite. What about existing MySites? We can get this done by creating a small console application having little bit of smart code in it.

Code snippet : Adding custom columns in Document content type.

   1: public override void FeatureActivated(SPFeatureReceiverProperties properties)

   2:         {

   3:             using (SPWeb web = properties.Feature.Parent as SPWeb)

   4:             {

   5:                 try

   6:                 {

   7:                     string columnOne = "Document Class";

   8:  

   9:                     SPContentType documentContentType = web.ContentTypes["Document"];

  10:  

  11:                     //Check if the site column already exists

  12:                     if (!web.Fields.ContainsField(columnOne))

  13:                     {

  14:                         //If the site column doesn't exist then create a new column and assign it to custom columns group

  15:                         web.Fields.Add(columnOne, SPFieldType.Choice, true);

  16:                         SPFieldChoice choiceFields = (SPFieldChoice)web.Fields[columnOne];

  17:                         string[] choices = new string[3] {"Working", "Reference", "Record" };

  18:                         foreach (string choice in choices)

  19:                         {

  20:                             choiceFields.Choices.Add(choice);

  21:                         }

  22:                         choiceFields.Group = "Custom Document Columns";

  23:                         choiceFields.Update(true);

  24:                     }

  25:  

  26:                     if (!string.IsNullOrEmpty(columnOne))

  27:                     {

  28:                         //Get the field reference of new site column

  29:                         SPFieldLink fieldLink = new SPFieldLink(web.Fields[columnOne]);

  30:  

  31:                         if (fieldLink != null)

  32:                         {

  33:                             documentContentType.FieldLinks.Add(fieldLink);

  34:                             documentContentType.Update();

  35:                         }

  36:  

  37:                     }

  38:  

  39:                 }

  40:  

  41:                 catch (SPException ex)

  42:                 {

  43:                     //Write to Logger

  44:                 }

  45:  

  46:             }

  47:     }

For the existing MySite, first install Staplee feature in SharePoint farm.  Then, build a console application having this code in it.

   1: SPWebApplication objWebApplication = SPWebApplication.Lookup(new Uri(" "));//WebApplicaton Uri

   2: SPSiteCollection objSiteCollection = objWebApplication.Sites;

   3:  

   4: foreach (SPSite objSite in objSiteCollection)

   5: {

   6:     if (objSite.RootWeb.WebTemplate.ToString() == "SPSPERS")

   7:     {

   8:         objSite.RootWeb.Features.Add(new Guid("{4DEFA336-EDC4-43cb-9560-FE2E27E76DFB}")); // Guid of Staplee feature

   9:     }

  10: }

Run the executable file of the console application and it will activate the custom feature for all existing MySites under the web application

SharePoint Custom Workflow action to get a list item as an attachment in another list

Using SharePoint Designer, what if we want to have a create a workflow that gets a list item as an attachment in another list based on the item ID of the item that's created.  This is not possible using OOB actions in SPD.  We can create custom action using Visual studio and then integrate it in SPD workflow.

To achieve this task, we need to follow these steps.

1. Write code that has the dependency properties and code to handle the logic

2. Create .action file that can be integrated in SPD.

3. Add entries in web.config file to add authorizedTypes.

1)  Write code that has the dependency properties and code to handle the logic ( Sample cs file ) and then deploy the dll.

   1: namespace GetAttachment

   2: {

   3:     public partial class Activity1 : SequenceActivity

   4:     {

   5:         public Activity1()

   6:         {

   7:             InitializeComponent();

   8:         }

   9:  

  10:         #region DependencyProperties

  11:  

  12:  

  13:         public static DependencyProperty ListIdProperty = DependencyProperty.Register("ListId", typeof(string), typeof(Activity1));

  14:  

  15:         [Category("Cross Site Actions"), Browsable(true)]

  16:         [DesignerSerializationVisibility

  17:           (DesignerSerializationVisibility.Visible)]

  18:         public string ListId

  19:         {

  20:             get

  21:             {

  22:                 return ((string)(base.GetValue(Activity1.ListIdProperty)));

  23:             }

  24:             set

  25:             {

  26:                 base.SetValue(Activity1.ListIdProperty, value);

  27:             }

  28:         }

  29:  

  30:         public static DependencyProperty ListItemProperty = DependencyProperty.Register("ListItem", typeof(Int32), typeof(Activity1));

  31:  

  32:         [Category("Cross Site Actions"), Browsable(true)]

  33:         [DesignerSerializationVisibility

  34:           (DesignerSerializationVisibility.Visible)]

  35:         public int ListItem

  36:         {

  37:             get

  38:             {

  39:                 return ((int)(base.GetValue(Activity1.ListItemProperty)));

  40:             }

  41:             set

  42:             {

  43:                 base.SetValue(Activity1.ListItemProperty, value);

  44:             }

  45:         }

  46:  

  47:         public static DependencyProperty DestinationListProperty = DependencyProperty.Register("DestinationList", typeof(string), typeof(Activity1));

  48:  

  49:         [Category("Cross Site Actions"), Browsable(true)]

  50:         [DesignerSerializationVisibility

  51:           (DesignerSerializationVisibility.Visible)]

  52:         public string DestinationList

  53:         {

  54:             get

  55:             {

  56:                 return ((string)(base.GetValue(Activity1.DestinationListProperty)));

  57:             }

  58:             set

  59:             {

  60:                 base.SetValue(Activity1.DestinationListProperty, value);

  61:             }

  62:         }

  63:  

  66:         public static DependencyProperty __ContextProperty = DependencyProperty.Register("__Context",

  67:                                                                                         typeof(WorkflowContext),

  70:                                                                                             ));

  71:         [ValidationOption(ValidationOption.Required)]

  72:         [Browsable(true)]

  73:         [DesignerSerializationVisibility(DesignerSerializationVisibility.Visible)]

  74:         public WorkflowContext __Context

  75:         {

  76:             get { return ((WorkflowContext)(base.GetValue(Activity1.__ContextProperty))); }

  77:             set { base.SetValue(Activity1.__ContextProperty, value); }

  78:         }

  79:  

  80:  

  81:         #endregion

  82:  

  83:         protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext)

  84:         {

  85:             string str = null;

  86:             using (SPSite oSPSite = new SPSite(__Context.Site.ID))

  87:             {

  88:                 using (SPWeb oSPWeb = oSPSite.RootWeb)

  89:                 {

  90:                     SPList list = oSPWeb.Lists[new Guid(ListId)];

  91:                     SPFile oFile = list.Items.GetItemById(ListItem).File;

  92:  

  93:                     SPList oList = oSPWeb.Lists[new Guid(DestinationList)];

  94:  

  95:                     SPListItemCollection listColl = oList.Items;

  96:                     Guid oguid = new Guid();

  97:                     foreach (SPListItem itm in listColl)

  98:                     {

  99:                         if (itm.Name == ListItem.ToString())

 100:                         {

 101:                             oguid = itm.UniqueId;

 102:                             break;

 103:                         }

 104:  

 105:                     }

 106:                     SPListItem oListItem = oList.Items[oguid];

 107:                     SPAttachmentCollection collAttachments = oListItem.Attachments;

 108:  

 109:                     string strFileName = oFile.Name;

 110:  

 111:                     byte[] binFile = oFile.OpenBinary();

 112:                     collAttachments.Add(strFileName, binFile);

 113:                     oListItem.Update();

 114:  

 115:                 }

 116:             }

 117:             Console.WriteLine(str);

 118:  

 119:             return ActivityExecutionStatus.Closed;

 120:         }

 121:     }

 122: }

2) Create .action file that can be integrated in SPD

   1: <WorkflowInfo>

   2:   <Actions>

   3:     <Action Name="Get item from one list as an attachment"

   4:    ClassName="GetAttachment.Activity1"

   5:    Assembly="GetAttachment, Version=1.0.0.0, Culture=neutral, PublicKeyToken=3893e024533a9d94"

   6:    AppliesTo="all" Category="Cross Site Actions">

   7:       <RuleDesigner

   8:                   Sentence="Get item in %1 as an attachment to the list %2 ">

   9:         <FieldBind Field="ListId,ListItem" Text="this list" Id="1" DesignerType="ChooseDoclibItem" />

  10:         <FieldBind Field="DestinationList" DesignerType="ListNames" Id="2"/>

  11:  

  12:       </RuleDesigner>

  13:       <Parameters>

  14:         <Parameter Name="__Context" Type="Microsoft.SharePoint.WorkflowActions.WorkflowContext" Direction="In" />

  15:         <Parameter Name="ListId" Type="System.String, mscorlib" Direction="In" />

  16:         <Parameter Name="ListItem" Type="System.Int32, mscorlib" Direction="In"  />

  17:         <Parameter Name="DestinationList" Type="System.String, mscorlib" Direction="In" />

  18:       </Parameters>

  19:     </Action>

  20:   </Actions>

  21: </WorkflowInfo>

3) Add entries in web.config file to add authorizedTypes.

   1: <System.Workflow.ComponentModel.WorkflowCompiler>

   2:  

   3:   <authorizedTypes>

   4:     ...

   5:     <authorizedType Assembly="GetAttachment, Version=1.0.0.0, Culture=neutral, PublicKeyToken=3893e024533a9d94" Namespace="GetAttachment" TypeName="*"   Authorized="True" />

   6:   </authorizedTypes>

   7:  

   8: </System.Workflow.ComponentModel.WorkflowCompiler>

Understanding MCMS File Cache

How MCMS file upload works:

When a file is uploaded, MCMS adds and saves them to the database. Once in the DB, it gets cached on the file system when there is a request for it.

What happens when we rename / delete the file in file cache :

When we remove/delete the cached file (the one in RdOnlyRes), we are manually breaking the coordination between the CMS memory node cache and the file cache.  When it is cached on the file system, CMS also maintains a memory node cache saying that "this" file is cached on the file system.  So that any subsequent request on that resource, cms won't go to the database to grab but serve it from the file system (disk cache).  When we modify the file or delete the file ( as we tried in the disk cache), CMS memory node cache is not aware of this.  So it will try to find the file in file cache which is not there.  So, in that case it will throw 404 error.

File deletion Problem:

So what happens if the files in file cache are deleted ?   In this case, to cache the files again in file cache, we need to restart the services.  Once services are restarted, CMS will once again cache the files.  Please note , first time in this case will be slow as it needs to get the files from database.

What needs to be done:

Stop IISAdmin service.  Then stop and start aesecurityservice( CMS service ).  Then start iisadmin and w3svc services.  After doing this, the files will be once again cached and the documents will be shown as expected.

BDC Web Part - Column filtering

When a BDC web part is mapped with an instance of BDC Application Definition file ( that uses SQL database ),  sometimes the column filtering doesn't work.  On filtering of the column, it doesn't return any data.

The reason being the issue integration  of SQL Server with SharePoint when it comes to data types.  Basically, if the data type in the SQL table is CHAR, we don't seem to be able to use the filter feature in Business Data List web part.  This behavior is not seen if the data type is nvarchar/varchar.  Changing the data types from SQL enterprise manager also does not seem to help.  Pumping the SQL server table data to a new table schema (with nvarchar/varchar instead of char) works. In the BDC application definition file for the typedescriptor, the typename is of System.String(<TypeDescriptor TypeName="System.String ">).  System.String maps only with varchar/nvarchar.  It doesn't map with char datatypes.  Changing the tyename to System.Char also doesn't work.  Changing the sql query to cast / convert to varchar datatype also doesn't work.

The workaround to this problem is to change the datatype in SQL table to Varchar/nvarchar instead of char.  Just by changing the datatypes in the table won't work.  We need to write out the SQL server data to a CSV/TXT file and populate them back into the new table schema (with nvarchar instead of char) and that will help fixing this filtering issue.

Let’s discuss about some BDC Web parts !

Business Data Actions Web Part :

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

Business Data Actions Web part is used to display all “Actions” from the business data catalog application definition file. It displays all actions of an individual item which we are selecting in tool panel of the web part.  Actions are associated with entities in the Business Data Catalog. Actions bridge the gap between Microsoft Office SharePoint Server 2007 and a native application user interface by providing a link back to the back-end data source. You can use Business Data Actions to build applications with write-back scenarios, such as a Customer Profile view that allows a user to update profile information directly in SAP. Actions are implemented as links, so you can also use actions to perform simple operations such as sending e-mail messages or opening a customer's home page.

Basically action travels everywhere with entity, That is, after you define an action for an entity, the action shows up everywhere you display that entity, such as in a Business Data Web Part or a Business Data column. But the “Business Data Actions Web part” is especially to display all the actions entitled in the application definition file for a particular item.For example, If you use the business data actionsweb part in your sharepoint site page by importing the attached application definition file with the Adventure work sample customer instance, You can see “view profiles” and “send e-mail” action.This action will be shown for a particular item which you are mentioning in the web part. Because “View Profiles” and “Send E-mail” are the two actions associated with
the entity (customer) in the application definition file.

Business Data Actions : http://msdn.microsoft.com/en-us/library/ms493081.aspx

Editing Business Data Using Business Data Catalog Actions and InfoPath Forms Services: http://msdn.microsoft.com/en-us/library/bb852170.aspx

The Business Data Actions Web Part adds a simple URL to a page associated with the action named in the link. It is used to enable users to perform common actions from business applications directly from the Web browser. Often, a Business Data Actions Web Part is found on the same page with a related business data Web Part, so users can see information and act on it immediately. For example, a Business Data Actions Web Part for the "View Customer Profile" action could be found on a dashboard that has an Excel Web Access Web Part that displays sales for a customer and a KPI Web Part that shows customer satisfaction. An analyst seeing a low customer satisfaction KPI could correlate that information with recent sales reports and then click the business data action link to find out more about the customer and contact them to improve customer service.[ Ref article http://technet.microsoft.com/en-us/library/cc261941.aspx ]

Business Data Related List Web Part:

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

 Business Data Related List display a list of items related to one or more parent items from a data source in the business data catalog. This business data related list web part works based on the “Association” mentioned in the BDC application definition file.

Ø  Creating Association through Business Data Catalog Editor Tool :

In the BDC tool, click on add entities.  In the entities design surface, drag and drop two tables with Primary key -> Foreign Key relation.  In one of the table, you will find Forign Keys tab.  Check the foreign key field and then export the XML file.

After exporting the file, add BDC List web part ( You can also use BDC Item web Part ) and associate with the instance.  Then, add BDC Related List web part and in edit -->Connections --> Choose Get Related Item from BDC List web part ( can be BDC Item web part).  Exit edit mode.  Now for any item that you choose in BDC list web part, all the data for that item will be shown in BDC Related List web part.

Reference article - http://blah.winsmarts.com/2007-4-SharePoint_2007__BDC_-_Introducing_associations_between_entities.aspx

Ø  Creating Association in ADF:

We can also create association directly in ADF. We need to add association tag entry in the ADF.

Article on this : http://msdn2.microsoft.com/en-us/library/bb410047.aspx

Business Data Item Builder Web Part:

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

·         The Business Data Item Builder is a web part that is only visible when the page is in edit mode. Its job it to get identifiers from query string parameters from URL of the page it is placed on. It can then pass these values onto other web parts. For example if the url we use to go to default.aspx is default.aspx?productId=3, it would pass the value 3 to our Business Data Item web part which would display the record which has an ID of 3.

Using User control (Web service call ) in SharePoint Web part

SPS 2003 to MOSS Migration – Applying the same CSS

Many think that just to get same look and feel for any migrated site, we can directly modify Out of the box style sheet pages to get it done.  But, it’s not recommended to modify any Out of the box files in SharePoint.  We need to use custom css file to achieve this task.

1.     Create custom css file and place it under layouts/1033/styles folder.

2.     In MOSS, Out of the box css file core.css is the important file that contains almost all branding for the sites.

3.     So go through the Core.css file and you should be able to find most of the branding works in that file ( menu, web parts,etc..)

4.     In the custom.css file, override the css that you want to.  Only add classes and elements into the custom CSS that you are using to override the default.  For e.g. if we want to change the font of web part title, just have only font-size property.

 .ms-WPTitle

{

 Font-weight:bold;

Color:#4c4c4c;

Padding-left:3px;

Padding-right:2px;

Padding-top:2px;

Padding-bottom:2px;

Font-size:10pt;
}

Have only this elements in the custom css file.

 .ms-WPTitle

{

 Font-size:20pt;
}

5.     Now, in the default.master page (that uses Core.css), we need to point it to custom.css file.  For this, we can specify the custom CSS file in the Master Page settings (Site Actions/Site Settings/Master Page).  In this case, the custom CSS file will get pulled after CORE.CSS. Also the custom CSS overrides and new styles will also be applied to _layouts pages.  

6.     This approach works only for site level.  We need to go to each site and make this changes.  We don’t want to make changes in each of the sites but rather we want to apply custom css throughout the web application level.  This can be achieved by adding custom css url tag in the default.master page ( like <link rel="stylesheet" type="text/css" href="http://blogs.msdn.com/_layouts/1033/styles/custom.css"/> )

7.     Rather than modifying the default.master page, we can have custom master page and add the link tag. 

8.     Create a copy of default.master page and rename to custom.master page and place it under same location.  In the custom.master page, add the following line just below <Sharepoint:CssLink> tag.

                 <link rel="stylesheet" type="text/css" href="http://blogs.msdn.com/_layouts/1033/styles/custom.css"/>

9.     Now, we have custom master page using custom css file.  To use this, we need to point our sharepoint site to the custom master page.  The changes can be done in ONET.xml file ( located under site templates folder).  In the ONET.xml file, we need to add custom.master page in the module section and set default master page to custom master page.

Adding Custom master page URL in the module.

<Modules>
    <Module Name="DefaultMasterPage" List="116" Url="_catalogs/masterpage" RootWebOnly="FALSE">
          <File Url="Custom.master" Type="GhostableInLibrary" IgnoreIfAlreadyExists="TRUE" />
         <File Url="default.master" Type="GhostableInLibrary" IgnoreIfAlreadyExists="TRUE" />
   </Module>
</Modules>

Setting default master page to custom master page.

<Configuration ID="0" Name="Default" MasterUrl="_catalogs/masterpage/Custom.master">

  As modifying out of the box ONET.xml file is not supported, we need to create custom site definition.  Creating Custom Site Definition http://msdn.microsoft.com/en-us/library/ms454677.aspx In that custom site definition, you will find ONET.xml file and make the above mentioned changes in that file.

10.  For creating any sites, use this custom site definition as a template and create.  With this, the default master page for the site will be our custom master page which will have custom css file in it.

Please note that we can also customize the default master page using SharePoint Designer.  For this approach, we need to follow till step 6.  In step 6, add custom css url using sharepoint designer and it should also work fine as expected.  Only if we don’t want to make changes on default master page, we need to use custom master page and custom site definition.

How BIG is your SharePoint

Size of Site collection:

string siteCollectionURL = “http://localhost:80/sites/TestSite”;

SPSiteAdministration oSPSiteAdministration = new SPSiteAdministration(siteCollectionURL);

float scDiskUsage = oSPSiteAdministration.DiskUsed;

float sizeOfSiteCollection = (scDiskUsage / (1024 * 1024));  // In MB

Total size of sub-sites in Site collection :

string siteCollectionURL = “http://localhost:80/sites/TestSite”;

SPSite oSPSite = new SPSite(siteCollectionURL);

SPWeb oSPWeb = oSPSite.RootWeb;

float totalSubSitesUsage = GetWebSize(oSPWeb);

            float GetWebSize(SPWeb web)

            {

               float total = 0;

               foreach (SPFolder folder in web.Folders)

               {

                   total += GetFolderSize(folder);

               }

              foreach (SPWeb subweb in web.Webs)

              {

                 total += GetWebSize(subweb);

                 subweb.Dispose();

              }

             return (total/(1024*1024));

          }

          float GetFolderSize(SPFolder folder)

          {

            float folderSize = 0;

            foreach (SPFile file in folder.Files)

            {

                folderSize += file.Length;

            }

            foreach (SPFolder subfolder in folder.SubFolders)

            {

                folderSize += GetFolderSize(subfolder);

            }

            return folderSize;

          }

Workflow problem in sending Email attachments of large file size

When a large file size ( typically > 1 MB ) is sent as an Email attachment through Workflow, we might see timed out exception.  The exact error message is “System.Net.Mail.SmtpException: The operation has timed out.  at System.Net.Mail.SmtpClient.Send(MailMessage)”

To overcome this, rather than just sending email via SMTP, pickup directory can be used in which case the IIS takes the responsibility of delivering the emails with attachment.   

BDC Error Messages

·         BDC Tool Design Surface Error Message : “Could not process Table ‘xyz’. Make sure you have SELECT rights on the Table/VIEW.”

Possible Cause :  The schema of the database table is Not dbo

Resolution :  Change the schema of the table to dbo

·         SSP File Import  : “Could not create profile page for Entity ‘abc’. The error is: Cannot create a new connection to closed Web Part ‘xyz’”

Possible Cause :  Closed web part is not configured properly with BDC instance.

Resolution :  Go to the page where the closed web part exists. To get the exact page, go to SQL server.  In the “WebPartLists” table, using the web part id find the value in PageURLId column.     Using the PageURLId,  in “AllDocs” table  get the exact URL of the page.  Once you find the closed web part that is causing the problem, delete it.

·         BDC List Web Part :  “An error occurred while retrieving data from <InstanceName>. Administrators, see the server log for more information”.  (Very much generic error).  Also if you note, you will be able to see column headers but not the data in it.

Possible Cause :  Issue with ODP and Oracle Client Tools.

Resolution : When Oracle and MOSS 2007 are on a separate server we must install the Oracle Client Tools, and Oracle Data Provider .NET support on all SharePoint Servers.  You can download ODAC from http://www.oracle.com/technology/software/tech/windows/odpnet/index.html

·         BDC Definition Editor tool : “Cannot connect to LOB system”

Possible Cause : Very common error message.  Possibly Authentication failure.

Resolution :  1. Check Database connectivity credentials.  2. Check authentication mode and its credentials for SharePoint.

·         BDC Definition Editor tool: “The LobSystemInstance Property 'AuthenticationMode' has a value that requires 'Trusted_Connection' in the connection string”

Possible Cause :  When ODBC / OLEDB connection mode is used.

Resolution :  1.Try adding Trusted_Connection parameter in the connection string.  2.  If ODBC is used,  these changes need to be done,

1.     Do not put quotation marks on the SQL query.  The quotation marks are generated by BDC Editor and we need to remove them.

     For eg: Instead of Select "ProductId", "ProductName" from Products - use Select ProductId, ProductName from Products.

2. Do not put parameters like @pk.  This is not accepted by ODBC Driver.  It only accepts parameter using ?

    For eg: Instead of Select "ProductName" from Products where ProductId=@pk  use Select ProductName from Products where ProductId=?

3. Trusted connection should be in title case.  It should be Trusted_Connection and not trusted_connection.

     For eg : Instead of <Property Name="rdbconnection trusted_connection" Type="System.String">yes</Property> use

                                   <Property Name="rdbconnection Trusted_Connection" Type="System.String">yes</Property>    

4. If the Application definition file is to be used in BDC Data List web part then make sure that there is at least one method instance of type "Finder".  By default, BDC editor tool doesn't generate method instance of finder.  It generates method instance of type "IdEnumerator".  So, change "IdEnumerator" to "Finder".

5. Possibilities are there when we map the Application definition file with BDC web part, it might throw an error saying "unable to connect to abc_instance".  In this case change User DSN to System DSN as System DSN is globally available to all users.

·         Event Log : “Could not find exactly one Property with Name ‘Rows’, referred to by child TypeDescriptor with Name 'Rows' . There may be no such Property, or more than one such Property”
Possible Cause :   Serialization / De-Serialization problem in data objects.

Resolution :  Install latest service pack for the .Net framework used.

·         Event Log : “The FilterDescriptor with Name 'ID' and Id '' has a value in Field 'TypeDescriptors' that is invalid”
Possible Cause :   Filters for the methods is configured incorrectly.

Resolution : In BDC Application Definition File, check for the filter configuration.  To configure it manually check http://blogs.msdn.com/chandru/archive/2008/12/07/bdc-how-to-create-filters-in-adf-manually.aspx

CAS in SharePoint

Here i'm going to explain about using Code Access Security (CAS) policy configuration for locking down a webpart that make a "this.Page.LoadControl()" call to load a user control.

Ø  First create a custom web part, in the CreateChildControls() method load a custom user control like

Control control = this.Page.LoadControl("~/usercontrols/MyUserControl.ascx");

Ø  Then you copy the two DLLs (TestUserControl.dll and TestWebPart.dll) to the bin (e.g. ..\Inetpub\wwwroot\wss\VirtualDirectories\80\bin). 

Ø  Now copy the ASCX (TestUserControl.ascx) to the usercontrols directory (e.g. ..\Inetpub\wwwroot\wss\VirtualDirectories\80\usercontrols)

Ø  In order to use these controls in our SharePoint site, we need to add appropriate SafeControl entries to the web.config, for these 2 dlls.

Ø  Make sure the trust level in the web.config is set to "Full" & save.

Ø  In SharePoint go into Site Settings, click on "Web Parts" under "Galleries", click "New" and add the web part to the gallery (e.g. MyWebPart).  Edit a page and add the web part.  The web part will work as expected !!  But remember this uses Full trust policy J

Using Custom CAS Configuration :

Ø  Now let’s add our own Configuration file in the 12 hive CONFIG folder.  The easiest way is to make a copy of the any of the existing config file, say wss_minimal_trust.config policy file and rename it to custom_trust.config.

a.     In the new policy file  add the <SecurityClass> declarations under <SecurityClasses>

        <SecurityClass Name="SharePointPermission" Description="Microsoft.SharePoint.Security.SharePointPermission, Microsoft.SharePoint.Security, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c"/>

        b. make a copy of the <PermissionSet> with the name "SPRestricted", setting the name for the copy to "SPRestrictedCustom"

        c. add the following <IPermission> declaration to the new SPRestrictedCustom <PermissionSet>

<IPermission class="SharePointPermission"

                       version="1"

                        ObjectModel="True"/> 

      d.  add <CodeGroup> declarations for the user control assembly and the web part assembly.:

                                                <CodeGroup

                                class="UnionCodeGroup"

                                version="1"

                                PermissionSetName=" SPRestrictedCustom ">

                             <IMembershipCondition

                                    class="StrongNameMembershipCondition"

                                    version="1"

                                    PublicKeyBlob="0024.."

                                    Name="MyUserControl"/>

                        </CodeGroup>

                        <CodeGroup

                                class="UnionCodeGroup"

                                version="1"

                                PermissionSetName=" SPRestrictedCustom ">

                             <IMembershipCondition

                                    class="StrongNameMembershipCondition"

                                    version="1"

                                    PublicKeyBlob="0024…"

                                    Name="MyWebPart"/>

               </CodeGroup>

Ø  Modify the web.config for the web application as follows:

        a. Add the following <trustLevel> under <system.web><securityPolicy>     

            <trustLevel name="WSS_Custom" policyFile="C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\config\custom_trust.config" />

        b. Change the trust level from "Full" to " WSS_Custom"

Ø  Now try the web part again, and you will end up with this error

The file '/usercontrols/MyUserControl.ascx' does not exist. - StackTrace: at System.Web.UI.Util.CheckVirtualFileExists(VirtualPath virtualPath) at System.Web.Compilation.BuildManager.GetVPathBuildResultInternal(VirtualPath virtualPath, Boolean noBuild, Boolean allowCrossApp, Boolean allowBuildInPrecompile) at System.Web.Compilation.BuildManager.GetVPathBuildResultWithNoAssert(HttpContext context, VirtualPath virtualPath, Boolean noBuild, Boolean allowCrossApp, Boolean allowBuildInPrecompile) at System.Web.UI.TemplateControl.LoadControl(VirtualPath virtualPath) at System.Web.UI.TemplateControl.LoadControl(String virtualPath) at TestWebPart.TestWebPart.CreateChildControls()

So where does the problem lie ?

      Does the error message mean the file MyUserControl doesn’t exist or is the error message misleading us ? The file does exist in the usercontrols folder.  Could the error message be because of security exception ? Possibly could be !

            Let’s first make sure that the minimum permission that is required for some of the assemblies is granted.  This can be done using Minimum Grant Set Determination tool, PermCalc.exe (http://msdn.microsoft.com/en-us/library/ms165077(VS.80).aspx ).  Run this tool with the “-Sandbox” flag and here’s the output we get:

<?xml version="1.0"?>

<Sandbox>

  <PermissionSet version="1" class="System.Security.PermissionSet" Unrestricted="true" />

</Sandbox>

So, this means that we need to have full permission on this class (System.Security.PermissionSet).  In our config file we have,

<SecurityClass Name="NamedPermissionSet" Description="System.Security.NamedPermissionSet"/>

Inheritance Hierarchy

> System.Object
   > System.Security.PermissionSet
      > System.Security.NamedPermissionSet

Hence, in our custom Permission Set named SPRestrictedCustom, add unrestricted=”true”, because its pointing to the class "NamedPermissionSet"

<PermissionSet

                 class="NamedPermissionSet"

                 version="1"

                 Name=" SPRestrictedCustom"

     Unrestricted="true">

            Now, run the SharePoint application with the web part and it should work as expected without any error.

            Reference  MSDN Article : http://msdn.microsoft.com/en-us/library/aa302425.aspx

MCMS 2001 : Adding flash objects on the content page

When we try to add a flash object in the Edit mode of a CMS page, it doesn’t work as expected.  We will get to see only blank black screen and not the actual flash images.  Directly when the images are added in place holders it works.  But when added as an object, it doesn't work.  Also if we make a reference to an asp file ( that contains the flash object), still it won’t work.   

The reason - for security reasons, any form controls(<object>,<script>..) used in HTML place holder in Edit mode gets stripped off.

Then how do we add flash objects in the page ?  One workaround is to use some dummy tags instead of object tag and replace them accordingly in the code.  The other alternative approach is to use <Embed> tag instead of <object> tag.  This tag will work within the place holder control.  To set the plug in URL, the attribute “PLUGINURL” can be used http://www.htmlcodetutorial.com/embeddedobjects/_EMBED.html

Programmatically adding a person or group to a list ( People/Group Column) who is not a part of the Site Collection.

Consider there is a custom list that contains multiple custom columns with one of the column being of type Person or Group column.  Say that we want to show all the site collection in the same Web App in one of the column and show the users of that site collection in the other person/group column.  Programmatically we can add users who belong to the current site collection.  But, when you try to add other users who doesn't belong to the current site collection, it doesn't add those users in the People/Group column.  Through SharePoint UI when we try to implement this it works.

But why ?  To know the reason we need to understand a bit of internal working process when an user is added.  Every time when a new user is added to the site collection, each user gets user id.  It starts with the user id as 8, then goes to 9,10...  So when we try to add an user of other site collection in people/group column, it just gets the id (say for eg 8) and checks in the current site collection and gets the user with the user id 8 in the current site collection.  And then it adds a user with the id 8 in the current site collection instead of the user with the user id 8 in other site collection. If the user id in the current site collecion is not found  ( say for eg 10), error is thrown saying "user not found".

Through UI when we try to achieve the same task it works fine.  Actually what happens is that when we try to add an user from different site collection in a list (in people/group column) before adding to the list, it adds to People and Groups and from there it retrieves the user.  So it works perfectly fine.  First it checks if the user exists in People and Groups and from there the user is retrieved and shown in the list.

This is what we need to know through object model as well.  So programmatically before we add an user of different site collection to a list, first we need to add them in People and Group in the current site collection and then add the user to the list.

First get the user from different site collection.  Then add them to People and Group in the current site collection.  And then programmatically add the user to the list in the current site.

   SPFieldUserValueCollection fieldUserValues = new SPFieldUserValueCollection();

SPUser listUser;

listUser = webApp.Sites[1].OpenWeb().AllUsers.GetByEmail("EMAIL ID");//Get the user from different site collection

webApp.Sites[0].OpenWeb().AllUsers.Add(listUser.LoginName,listUser.Email, listUser.Name,"Test");//Add the user to the actual SC where the list exists

listUser = webApp.Sites[0].OpenWeb().AllUsers.GetByEmail("EMAIL ID"); // Now, get the user from the actual SC.

fieldUserValues.Add(new SPFieldUserValue(webApp.Sites[1].OpenWeb(), listUser.ID, listUser.Name));

Workflow Task Lock issue !

Sometimes when the number of items in the list gets high ( say more than 1500 ) we might get to see the Tasks getting locked issue for some of the items.  In the SharePoint UI, the exception is “This task is currently locked by a running workflow and cannot be edited”

The ULS logs will give some fair idea on the issue.

0x0BAC            Windows SharePoint Services   Workflow Infrastructure              936l      High    Engine RunWorkflow: System.Workflow.Activities.EventDeliveryFailedException: Event "OnTaskChanged" on interface type "Microsoft.SharePoint.Workflow.ITaskService" for instance id "b4077790-6160-4eea-8696-863238532f57" cannot be delivered. ---> System.ArgumentNullException: Buffer cannot be null.  Parameter name: buffer     at System.IO.MemoryStream..ctor(Byte[] buffer, Boolean writable)     at Microsoft.SharePoint.Workflow.SPWinOePersistenceService.LoadWorkflowInstanceState(Guid instanceId)     at System.Workflow.Runtime.WorkflowRuntime.InitializeExecutor(Guid instanceId, CreationContext context, WorkflowExecutor executor, WorkflowInstance workflowInstance)     at System.Workflow.Runtime.WorkflowRuntime.Load(Guid key, CreationContext context, WorkflowInstance workflowInstance)

This exception says that the workflow is not able to deliver the all the events at a given time and so the tasks get locked.  OOB, the default event delivery throttle value is 15.  The value can be increased using STSADM command

stsadm -o setproperty -pn workflow-eventdelivery-throttle -pv "50"

For more details on using the command : http://technet.microsoft.com/en-us/library/cc287939.aspx