K2.net 2003 to K2 blackpearl Migration Utility Complete

It has been talked about for some time now that K2 would provide a migration tool from K2.net 2003 to K2 blackpearl. It was initially targeted for the RTM release of K2 blackpearl but there were some issues at time. Now it is good to go…

------------------

Dear K2 user:

The official release of the K2 blackpearl Migration Utility is here. The K2 blackpearl Migration Utility is used to transfer and manipulate data from existing K2.net 2003 databases to K2 blackpearl databases. This enables you to seamlessly start new and continue the execution of all currently running K2.net 2003 process instances on K2 blackpearl. It requires the K2 blackpearl 0807 release with the KB000370 update, both of which are available on the Customer Portal.

For installation and subsequent migration, we urge you to look at the documentation prior to attempting either! Unless you have already tested with pre-release versions of the K2 blackpearl Migration Utility, it is imperative that you evaluate your K2.net 2003 processes and test the migration in a non-production environment before attempting to migrate production servers.

> If you require K2.net 2003 archiving functionality, we recommend you do not migrate at this time

> If you make use of a user manager other than Active Director (ADUM), please contact support before you begin the migration process, even in a test environment

SUPPORT
Please use the customer portal to open support tickets and/or contact your local support offices to report any issues. Additionally, the K2 blackpearl public forum on K2 Underground is available for community-based discussions: http://k2underground.com/forums/default.aspx?GroupID=21. Note: K2 Underground is a community Web site and should not be used for production support issues or for logging bugs.

When to use a SharePoint List?

It has been over a year since I have wanted to write this blog. It is a little late now but whatever; the topic still seems to come up. This blog struck a chord with me all of the sudden because I got my hands on the Microsoft Patterns and Practices SharePoint Guidance for November 2008. There are some good things in there, but much of it common sense sort of stuff.

This is what is in the SharePoint Guidance for November 2008 article when it comes to make a decision about using SharePoint Lists vs. a Database.

Benefits	Database	SharePoint list
Handles complex data relationships	Yes	No
Handles large numbers of items	Yes	No
Handles transactions	Yes	No
Is easy to use	No	Yes
Accommodates workflows	No	Yes
Includes a standard interface	No	Yes
Can easily add binary data	No	Yes

For the most part, they get it right however I think there are some things that are worth adding. My two cents is if I have any sort of custom application with custom code I usually start moving away from using SharePoint Lists and move towards SQL Server.

Here are two small cases studies. The first I had a client that needed simple application where a custom view of data was needed, the data was flat and non-relational, no reporting required, and we needed ability to approve and deny visibility of data. The solution entailed creating a Feature which deployed a content type, we created a custom list for that content type, we created some event receivers, we used the out of the box content approval for SharePoint lists and we created a web part for the custom view of data. By using a SharePoint list, we were able to get some free functionality like approving the visibility of data. In this case using a SharePoint List made tons of sense and worked very well.

The second case study did not work so well. We had to create a custom task management application. This task management application had several entities and some basic reporting requirements which needed to be exported from SharePoint:

• We used SharePoint content types to model each data entity. Then we put all of the content types and data in the same list (effectively creating a three-dimensional table), pretty cool in concept.
• We used event handlers to implement state management and relational rules between the data entities.
• We wrote a custom data provider to get data out of SharePoint Lists into Reporting Services.
• We over-overrode the list edit controls to we could implement more custom functionality.
• We wrote several custom web parts that used CAML to query items out of the list since we were going go over the 2,000 item threshold. We had over 50,000 items in the list with zero performance problems.

In the end, we met every requirement and implemented on time and budget. The solution did not last long because it was a task management system where functionality needed to change in a rapid fashion and the reporting requirements grew and grew. The business users wanted to do all the reporting in Business Objects which was a tool they were very familiar with. Business Objects can use SQL but not SharePoint Lists so the solution did not scale. Looking back at the architecture of the solution I was disappointed in it because we had considered using SQL Server instead.

What we learned was highly relational data is not easily enforced using event handlers and if there are any sort of reporting requirements SharePoint Lists are not the way to go.

Plus I came out of this with some golden rules:

• If I am writing custom web parts to retrieve and set data (unless I am getting some free SharePoint functionality by using a list item) I am going to go with SQL.
• If I have relational data (other than simple lookups), going to SQL.
• If I have reporting requirements, going to SQL.

Now based on several client experiences and emails I get CC'ed on for other SharePoint projects I felt it was necessary to go ahead and get this down. What I have also seen is lots of functional business users using SharePoint Lists as a way to replace MS Access or they think of it as web enabled Excel Spreadsheets (not to be confused with Excel Services). I have even seen designs from others where they start dropping XML documents into lists and start using that as a way to store enterprise data. I have even heard a request from someone on how to create an entity relationship diagram for list data they have. This is probably just a sample of bad uses of SharePoint Lists...

In the end my rule is simple, use SharePoint Lists for day-to-day collaboration sites. The point you start needing to build an application with custom code with web parts and business reporting stop right there; SharePoint Lists should probably not be used. Reporting seems to always be the deal breaker for a SharePoint List. Usually when building reports there is requirement for relational data. Do not fall into trap where you believe that if you use a SharePoint List you get free read, insert, update and delete screens. Your application will not scale over the long run.

There are several 3^rd party companies that have sprung up created suites of tools for get around this. For instance there is Bamboo Solutions, KWiz, Coras Works, etc.

Workflow SPListItem Out of Date

Error

I ran into this rather strange error a while back that I could not find much information on....

Microsoft.SharePoint.SPException: A file with the name Attachments/oooppp/Sdfkjsdfksdfnjfdsjksfdkjsdfkj fds jksdfkj sdfkj sdffkj fdskj.docx already exists. It was last modified by SHAREPOINT\system on 30 Dec 2008 15:31:28 -0500. ---> System.Runtime.InteropServices.COMException (0x81020067): A file with the name Attachments/oooppp/Sdfkjsdfksdfnjfdsjksfdkjsdfkj fds jksdfkj sdfkj sdffkj fdskj.docx already exists. It was last modified by SHAREPOINT\system on 30 Dec 2008 15:31:28 -0500. at Microsoft.SharePoint.Library.SPRequestInternalClass.PutFile(String bstrUrl, String bstrWebRelativeUrl, Object varFile, PutFileOpt PutFileOpt, String bstrCreatedBy, String bstrModifiedBy, Int32 iCreatedByID, Int32 iModifiedByID, Object varTimeCreated, Object varTimeLastModified, Object varProperties, String bstrCheckinComment, UInt32& pdwVirusCheckStatus, String& pVirusCheckMessage) at Microsoft.SharePoint.Library.SPRequest.PutFile(String bstrUrl, String bstrWebRelativeUrl, Object varFile, PutFileOpt PutFileOpt, String bstrCreatedBy, String bstrModifiedBy, Int32 iCreatedByID, Int32 iModifiedByID, Object varTimeCreated, Object varTimeLastModified, Object varProperties, String bstrCheckinComment, UInt32& pdwVirusCheckStatus, String& pVirusCheckMessage) --- End of inner exception stack trace --- at Microsoft.SharePoint.Library.SPRequest.PutFile(String bstrUrl, String bstrWebRelativeUrl, Object varFile, PutFileOpt PutFileOpt, String bstrCreatedBy, String bstrModifiedBy, Int32 iCreatedByID, Int32 iModifiedByID, Object varTimeCreated, Object varTimeLastModified, Object varProperties, String bstrCheckinComment, UInt32& pdwVirusCheckStatus, String& pVirusCheckMessage) at Microsoft.SharePoint.SPFileCollection.AddInternal(String urlOfFile, Object file, PutFileOpt fileOpt, String createdBy, String modifiedBy, Int32 createdByID, Int32 modifiedByID, DateTime timeCreated, DateTime timeLastModified, Object varProperties, String checkInComment, SPVirusCheckStatus& virusCheckStatus, String& virusCheckMessage) at Microsoft.SharePoint.SPFileCollection.Add(String urlOfFile, Byte[] file, Boolean overwrite, String checkInComment, Boolean checkRequiredFields, SPVirusCheckStatus& virusCheckStatus, String& virusCheckMessage) at Microsoft.SharePoint.SPFileCollection.Add(String urlOfFile, Byte[] file, Boolean overwrite, String checkInComment, Boolean checkRequiredFields) at Microsoft.SharePoint.SPFileCollection.Add(String urlOfFile, Byte[] file) at Portal.Contracts.WF.ContractApproval.CopyArchiveAttachments() at Portal.Contracts.WF.ContractApproval.onCreateAmendmentAttachmentFolder(Object sender, EventArgs e) at System.Workflow.ComponentModel.Activity.RaiseEvent(DependencyProperty dependencyEvent, Object sender, EventArgs e) at System.Workflow.Activities.CodeActivity.Execute(ActivityExecutionContext executionContext) at System.Workflow.ComponentModel.ActivityExecutor`1.Execute(T activity, ActivityExecutionContext executionContext) at System.Workflow.ComponentModel.ActivityExecutor`1.Execute(Activity activity, ActivityExecutionContext executionContext) at System.Workflow.ComponentModel.ActivityExecutorOperation.Run(IWorkflowCoreRuntime workflowCoreRuntime) at System.Workflow.Runtime.Scheduler.Run()

Cause

As usual the error was pretty perplexing until I dug deeper into what I was doing.

The cause has to do with the SPFile and the SPListItem getting out of sync with each other. In my MOSS workflows I move items between libraries as well as modifying XML in InfoPath form instances. The issues is not so much the initial action I take, any second action to the workflow SPListItem will cause the error.

The error was spread across several places in my workflow; I will try to boil it down for you here:

• I get the SPListItem, which in this case is an InfoPath form.
• I get the XML string of the InfoPath form from the SPListItem, deserialize it into a class, do some updates, serialize it back into XML and push it back into the SPListItem.
• Then I would modify some of the permissions to the SPListItem. This is where the error would occur.

Getting this error was pretty perplexing at first. I my InfoPath form utility (which I will be posting shortly), I was getting the XML string, modifying it, and then saving it back into the binary. Below is some extracted code where I was getting the workflow item and pushing in a fileStream with the modified XML.

SPListItem infoPathItem = WorkflowProperties.Item;
infoPathItem.File.SaveBinary(fileStream);
infoPathItem.File.Update();

Then later on in a different code event, I had some more code where I subsequently called:

WorkflowProperties.Item.Update(); 

The moment I call the second update, the error would come up.

Resolution

After much research I found out the following:

1. This error would occur whether I was doing this in WF or in some web part. Basically changing the SPListItem's File.Update() does not sync up the entire SPListItem with SharePoint. So when the second update is called, SharePoint scoffs at you saying the object instance you have is out of date with the one on the server. Valid complaint too!
2. WorkflowProperties.Item is only loaded when the workflow rehydrates. Even though I may reference it many times, across many code events, the WorkflowProperties.Item will only load up once. Let me explain rehydrating (very similar to K2 or BizTalk). What happens is when a WF instances goes into a wait state, like waiting for a TaskChanged event to fire, WF will persist itself to the SharePoint database. When the TaskChanged event fires, the WF instance will rehydrate based on the data that was saved. At that point, the WorkflowProperties.Item will be loaded up. It will not be loaded up every time I reference WorkflowProperties.Item.

My resolution is to add a simple property to my WF class. What it will do is ensure that I always load up the SPListItem every time I need it.

private SPListItem WorkflowItem
{
    get
    {
        SPDocumentLibrary library = (SPDocumentLibrary)WorkflowProperties.Web.Lists[WorkflowProperties.ListId];

        return library.GetItemById(WorkflowProperties.ItemId);
    }
}

References

• http://hristopavlov.wordpress.com/2008/05/14/uploading-a-file-event-receivers-the-file-has-been-modified-by/
• http://geekswithblogs.net/dotnetrodent/archive/2008/08/08.aspx
  

Content Database Restore File Not Found

We recently experienced a rather weird experience when moving some SharePoint content databases. We are not using stsadmn. We have several environments, all the same, and we just wanted to move the content DB from one environment to another. We have done this several times with no error.

This time around when we did it, we were getting File Not Found errors when we try to go to the site. We went into Central Admin and the Content Database was listed on the Manage Content Databases page, however if you went to view the Site Collection list no information was listed. On the Site Collection page you should see things such as a URL, Title, some other stuff and a Database Name. All of this was blank. There were no exceptions in the Event Log on the box and no errors either in the SharePoint log.

We did some searching and could not find anything. Andy on my team found this posting. It had nothing to do with this error however we decided to give it a try. It does not actually delete the databases, it just removes the association between the MOSS site and the database.

Stsadm -o deletecontentdb -url [Site URL] -databasename [database name]

Stsadm -o addcontentdb -url [Site URL] -databasename [database name]

Once we did this, everything started running again.

References:

• deletecontentdb
  
• addcontentdb
  

SharePoint PerformancePoint Services

Some big news went out today around Microsoft's PerformancePoint Server which is part of their BI solution set. In their road map (read here), Microsoft is saying that they plan to finish PerformancePoint Server 2007 "Service Pack 3" in mid 2009 and then Microsoft will stop building it as a separate server as they are rolling it into the SharePoint platform!

Basically they will be creating the PerformancePoint service which will complement Excel Services, BDC and other BI related services in SharePoint.

I think this again demonstrates how Microsoft is really standing behind MOSS and making significant investments into it. We have seen them use MOSS to make Office products available over MOSS like Excel and InfoPath Services. Is the next thing Access Services? Maybe even rolling SQL Reporting Services over – that actually makes a bunch of sense? Seeing announcements like this just continue to support my stance that SharePoint is the delivery platform for .NET applications for the next few years; I am not saying that custom ASP.net will disappear either. However with all the stuff you get with SharePoint, I have a really tough time anymore building stuff web applications from scratch….

Moving SharePoint My Links between SSPs

Background

Well I have had this ongoing annoying issue with a Shared Service Provider (SSP) that I described in this old posting. The issue was an AD account was removed, it had permission set to in the BDC, we were getting an error trying to remove it because if we did not the BDC index would fail. Anyways, the solution was either to buy DeliverPoint to delete this one dead account of the SSP or do something else. Our solution was to recreate the SSP which I discussed in this post. Well to make a long story, longer, our solution did not entail backing up and restoring the SSP because the error would still be there. So our solution was to rebuild the SSP and them move over it. This is how I got to here.

My Links Issue

Normally My Links in SharePoint was not a high priority item. However we found out after we had moved over to the new SSP that users had thousands of My Links set up. In this post, I discussed creating a separate web application for the SSP site and we moved over to a new SSP. One unexpected result was all of the My Links were now missing.

This was particularly confusing because when managing My Links, the user is redirected to My Sites URL for the SSP. We are using My Sites, I had thought this data was stored as part of the My Sites content database. Wrong – not the case at all. All of the My Links are stored in the SSP database. So the issue was how do we get the URLs out of the old SSP database and into the new one?

Resolution

We considered many options, but the best option that was going to move the data between the databases. So we restored the old SSP and found that the URL were stored in UserLinks table. In the UserLinks table, there is a column called RecordID. The RecordID column refers to the user account that the URL is for. Going to the UserProfileValue table, we say that this table stored the information that is retrieved from the Profile Search Service that is in the SSP.

Now the interesting thing is you cannot just insert all of the records in UserLinks table from one SSP database to another. The reason why is because the RecordID will be different on in each SSP database. This is because the Profile Search Service on the old SSP DB indexed different accounts over a year ago versus the new SSP with the fresh brand new index of user profiles we had in.

The following query will show you a report of how to reconcile the RecordIDs across the database.

SELECT UPV1.RecordID, UPV1.PropertyVal, UPV2.RecordID, UPV2.PropertyVal
FROM SharedServices1_DB.dbo.UserProfileValue as UPV1, SharedServices2_DB.dbo.UserProfileValue as UPV2
WHERE UPV1.PropertyID = 3 AND UPV1.PropertyVal = UPV2.PropertyVal 

To resolve the issue, we ran this SQL Script to move the data over from one SSP database to another. One ramification is that if an account was removed between the creation of the old and new SSPs, the URLs from that removed account will not be copied over to the new SSP because the profile service search will not find the account.

The following SQL statement shows how to move the data over.

INSERT INTO SharedServices2_DB.dbo.UserLinks(RecordID, Title, GroupType, GroupTitle, URL, ContentClass, PolicyID, ItemSecurity)
      SELECT UPV2.RecordID, UL1.Title, UL1.GroupType, UL1.GroupTitle, UL1.URL, UL1.ContentClass, UL1.PolicyID, UL1.ItemSecurity
      FROM SharedServices1_DB.dbo.UserProfileValue as UPV1, SharedServices1_DB.dbo.UserLinks as UL1, SharedServices2_DB.dbo.UserProfileValue as UPV2
      WHERE UPV1.PropertyID = 3 AND UPV1.PropertyVal = UPV2.PropertyVal AND UPV1.RecordID = UL1.RecordID

NOTE – Microsoft does not recommend making changes to data in the database. In this case, since it was a straight insert into an empty table with URLS, we were ok with moving forward…

K2 blackpearl Professional Now Available

The Professional K2 blackpearl Wrox book is now available for purchase (ISBN 978-0-470-29305-8). I just received my ten copies and I am super excited it showed up. Here are some links:

• You can buy it here!
• Table of Contents
• Detailed Table of Contents
• Read Chapter 1 for free

I highly recommend getting this if you have K2 or looking for more information about it. You will learn best practices, how to approach building workflow solutions, best practices for K2, detailed architecture, administration, reporting and advanced topics. This was written by both consultants and engineers that have successfully used K2 to deliver workflow solutions.