Archive for March, 2011

Short one:  A bit of a vague error, “Failed to do column assignments for list”, found in the SharePoint logs after trying to deploy as solution. The solution contained many list definitions, instances and content types and when deployed it was notice that one of the list was missing. I’m sure there could be many reasons for this error as it does seem a bit generic, but in this instance it was caused by either a wrong or a duplicate value being specified in the ColName attribute of a field in the list definition.
ColName Attribute
  1. ColName=nvarchar21 RowOrdinal=0>
This is not an attribute I would add manually, but some of the solution had been reverse engineered from a list. Removing this attribute resolved the problem. SharePoint will assign a value to this itself and is not needed in the XML
Okay, here’s the scenario:
You’ve set up the User Profile Synchronisation using the Microsoft Documentation, and your now at the situation where have the Active Directory Accounts imported into SharePoint. Now, you want to do a data load and match up people picker fields to the actual profiles in the SharePoint format of ID;#FirstName LastName. However, to do this you need to find out what the Id of each user is.
So, here how I went about getting them.
Firstly, the profile import throws all the users into the Profile DB you have set up in the previous step. So, open SQL Server Management Studio –> Profile DB  and return all the user profile records by running the following query
Insert List Item Method
  1. SELECT [PreferredName] ,[Email]
  2. FROM [Profile DB].[dbo].[UserProfile_Full]
Next step is to copy all the returned email addresses into a file and save it somewhere, were going to use the data in a minute.
Now create an console application, remembering to set the framework to 3.5 and the target processor to x64 as I’ve described in a previous blog.
Now, add the text file to the root of the project as an embedded resource.
and the copy in the following code into your program.cs . You will need to change the constant values to suit your environment and add
using Microsoft.Office.Server;
using Microsoft.SharePoint;
Insert List Item Method
  1. class Program
  2. {
  3. private const string siteUrl = http://sharepoint/”;
  4. private const string subSite = “hr”;
  5. private const string outputFileLocation = @”C:\Temp\SharePointIds.txt”;
  6. static void Main(string[] args)
  7. {
  8. Dictionary<string, string> userIdEmailMapping =  GetUserProfiles();
  9. using (StreamWriter writer = new StreamWriter(this.outputFileLocation))
  10. {
  11. foreach (var item in userIdEmailMapping)
  12. {
  13. StringBuilder sb = new StringBuilder();
  14. sb.AppendFormat(“{0},{1}”, item.Key, item.Value);
  15. writer.WriteLine(sb.ToString());
  16. }
  17. writer.Flush();
  18. }
  19. }
  20. private static Dictionary<string, string> GetUserProfiles()
  21. {
  22. Dictionary<string, string> userIdEmailMapping = new Dictionary<string, string>();
  23. using (SPSite site = new SPSite(this.siteUrl))
  24. {
  25. using (SPWeb web = site.OpenWeb(this.subSite))
  26. {
  27. using (StreamReader reader = new StreamReader(Assembly.GetExecutingAssembly().GetManifestResourceStream(Assembly.GetExecutingAssembly().GetName().Name + “.UserEmails.txt”)))
  28. {
  29. while (!reader.EndOfStream)
  30. {
  31. var email = reader.ReadLine();
  32. try
  33. {
  34. SPUser user = web.AllUsers.GetByEmail(email);
  35. userIdEmailMapping.Add(user.Email, user.ID.ToString());
  36. }
  37. catch (Exception ex)
  38. {
  39. userIdEmailMapping.Add(email, ex.Message);
  40. }
  41. }
  42. }
  43. }
  44. }
  45. return userIdEmailMapping;
  46. }
  47. }
Compile the exe, copy it your server and run it as administrator and it should populate the output file in a comma separated list with the ids which are in use.
However, there is one snag.
SPWeb.AllUsers  only returns the user who have actual been parsed in the site. So, to get round this, create a new column on list as a people picker and allow it to hold multiple values. Then, take your list of AD users, who don’t have Id yet and copy them into the people picker and save the list item. This should now create id for those users. I’m not sure if there is a limit for the people picker fields but I successfully added 30. Massive domains may be a bit harder though.
Oh well that’s about it. Cheers Winking smile

Rather than building something which had to be deployed to SharePoint which was of no use to the application. I decided it would be easier to just, quickly, build a console application to get some values and write them to a file. This was the way I had done it before using MOSS. However, when I did this on the Sharepoint 2010, it complained with FileNotFoundException. image As can be seen in the image below.

This puzzled me at first, but after some digging I found that not only do you have to change the .NET Framework imageversion to 3.5, but you also have to set the platform target to x64.  This is done from the build tab of the project properties.

Once you know, it makes sense, but at first it caused a little confusion!

I’ve never really had to do this before as most SharePoint jobs usually operate at Farm level. However, on this occasion I had to run the timer job of a specific Site Collection.

First stop of course was Google, where I found a lot of blogs spouting that the way to do it was to:

  • Add a public field
  • Add the Persisted() attribute to it as can be seen in the code snippet below
Persisted Public Field
  1. [Persisted()]
  2.         public string SiteCollectionUrl;

I might have been doing it wrong, but when I did this all I got was numerous errors in the SharePoint logs indicating that the field would not be upgradable.

The EmployeeDetailsSiteCollectionUrl field in the <CLASSNAME> class has been deleted.  The old value will be preserved in the UpgradedPersistedFields collection under the EmployeeDetailsSiteCollectionUrl key.  Consider writing upgrade code to copy this value to a new location.

So, after seeing this error I decided that this was not the correct way to do it.

Turns out there is a Properties collection property on the base class SPJobDefinition you can add to. To set the SiteCollection url I added the property in the feature receiver as seen in line 15 of the code snippet below. 

Setting a property
  1.         public override void FeatureActivated(SPFeatureReceiverProperties properties)
  2.         {
  3.             SPSite site = properties.Feature.Parent as SPSite;
  5.             // make sure the job isn't already registered
  6.             foreach (SPJobDefinition job in site.WebApplication.JobDefinitions)
  7.             {
  8.                 if (job.Name == Resources.EmployeeDetailsJobName)
  9.                     job.Delete();
  10.             }
  12.             // install the job
  13.             EmployeeDetailsJob employeeDetailJob = new EmployeeDetailsJob(Resources.EmployeeDetailsJobName, site.WebApplication);
  15.             employeeDetailJob.Properties.Add("EmployeeDetailsSiteCollectionUrl", site.Url);
  16.             SPWeeklySchedule schedule = new SPWeeklySchedule();
  17.             schedule.BeginSecond = 0;
  18.             schedule.EndSecond = 59;
  19.             employeeDetailJob.Schedule = schedule;
  20.             employeeDetailJob.Update();
  24.         }


Now, to access the property in the Job just use the code below.

Insert List Item Method
  1. this.Properties["EmployeeDetailsSiteCollectionUrl"].ToString()

After spending quite some time trawling the internet to find that answer to the above how to replace the $ dollar sign with a £ pound sign. I found that none of the solutions fully described how to do this.  Admittedly, it might just be me that’s thick though. Anyway, here’s how you go about it.

Firstly, to get your value into currency, you need to wrap your report value in the FormatCurrency function  in the expression box, as can be seen below.

FormatCurrency function in the Expression box

Next, is the part I had difficulty with! Every solution I found told me to set the default language of the report to United Kingdom. However, none of them told me explicitly how to do this.

So, what you need to do is: Changing the Language of a SQL Report

  • Click on the design surface of the report, outwith the actual reports.
  • Now, you will have access to the properties of the report where you can change the language as can be seen in the screen shot opposite.
  • Choose en-GB, deploy and that should be it fixed.


Easy when you know how, but, when your not from a Reporting background,  trying to access the properties caused me some confusion. 

Hopefully this helps someone else.

Recently, I was trying to set up a mail activity in a SharePoint workflow which, on the front end, seem to be working. i.e no errors. However, when looking in the SharePoint logs I found the following error:  “160005: Bad response from SMTP host xxx 530 5.7.1 Client was not authenticated”

On investigation, it turns out that this can occur when trying to setup outgoing mail on SharePoint 2010 with Exchange 2007.

How do you fix it?

On the Exchange 2007 server, you need to setup a new SMTP Receiver in the Hub Transport section of the Exchange management console.

Choose New Receive Connector

 Exchange Hub Transport New Receive Connector

Specify  the name of the receive connector


Specify the IP of the SharePoint server.

Make sure you check Anonymous users on the Permissions Groups tab.


Hope this helps.

Quick one: Today I tried to change the service account for a SharePoint application pool, running under in IIS 7.5. However, when i tried to load the site it just give me an 500 error.


Turns out that this has to be done through Central Admin by firstly registering a new account and then assigning it to the service. To do this navigate to Security –> Configure Service Accounts and you are shown, the screen below, the credential management page.


From here, click on the Register new managed account hyperlink and you will be directed to the service account credentials screen to add a new account. Once you have done this return to the previous screen and you should now be able to select a service from the top dropdown and assign it to the newly added account in the “Select an account for this component” dropdown.

I don’t know why you can’t just change the account in IIS. My guess is, there must be something written in the Config DB into the background