BizTalk 2009 SharePoint Service Adapter with SharePoint 2010

Several people have stated that this works, but unfortunately no one has really told you the steps. 

Assumptions:  Is this a proposal?  🙂

  1. SharePoint 2010 running on Windows 2008 R2 (64 bit of course)
  2. You have at least configured a couple of Web Applications in SP2010 to make the rest of this work

The main thing that happens here is you try to install BizTalk 2009 and you have the option for the SharePoint Service Adapter all greyed out.  This doesn’t work so go ahead and do the following:

  1. Download WSS 3.0 with SP2.  NOTE:  This is required for Windows 2008 R2 as it will not let anything else be installed
  2. Before you get to the area to actually “Configure SharePoint…”, hit “Cancel”
  3. Run the install for BizTalk and you can not install the adapter
  4. Configure the adapter as you would because it will recognize SharePoint is installed
  5. After it is configured, change the web.config for the BizTalk adapter to use the services (See this post here)

Hopefully that at least gets you a bit further.  Hopefully BizTalk 2009 R2 will have a lot of this resolved.

Custom SharePoint Alert Filter

I am always looking for ways to make things work the way you REALLY want them to.  Take the alerts in SharePoint as an example.  Wouldn’t it be great to say “only alert me when this criteria is met”.  Most of you are probably thinking that is as easy as a workflow, right?  Well what if you really want to test the BEFORE and AFTER values of a field to make sure.  Hmm…that might mean a custom workflow.  New solution, look at the SPAlert object and look at SQL to see how this baby really works.

You ever looked at what happens after you go to a list and sign up to receive something that you didn’t change?  If you see the CAML query that SharePoint generates, you might be asking yourself “what is this?”.  The FieldRef points to a “Editor/New” column.  What the heck?  That doesn’t exist anywhere.  Then again, Editor does, but what is this “New” thing?  Look at some other types and you will see the “Editor/Old” popping up.  Getting where I am headed?  You know the SPAlert.Filter property has a get; and a set;, right?  Hmmmmm…let your imagination do the rest.  Create the SPAlert manually, or script it…you will like it.

$alert = $web.AllUsers["domain\kirkhofer"].Alerts.Add();
$alert.Filter = "<Query><Neq><FieldRef Name="_Status/Old"/><FieldRef Name="_Status/New"/></Neq></Query>";
$alert.List = $list;
$alert.AlertType = [Microsoft.SharePoint.SPAlertType]::List;
$alert.Title = "Status Change";
$alert.EventType = [Microsoft.SharePoint.SPEventType]::Modify;
$alert.AlertFrequency = [Microsoft.SharePoint.SPAlertFrequency]::Immediate;

Move MOSS Web To WSS

Ever try to import a web from a MOSS server to a WSS server and get those little errors about “Could not find feature xxxx”.  More specifically, the first one you run in to is “Could not find feature OffWFCommon”.  Here is a little trick to get rid of those before you migrate.

$siteurl = "http://blah"
$site = new-object Microsoft.SharePoint.SPSite $siteurl
$web = $site.OpenWeb()

stsadm -o deactivatefeature -url $siteurl -name OffWFCommon
stsadm -o deactivatefeature -url $siteurl -name TranslationWorkflow

$site.features|%{$_.Definition}|?{$_.ReceiverAssembly -like "*Office*"}|select DisplayName
$site.features|%{$_.Definition}|?{$_.ReceiverAssembly -like "*Office*"}|%{stsadm -o deactivatefeature -url $site.url -name $_.DisplayName}

stsadm -o deactivatefeature -url $siteurl -name TransMgmtLib

$web.features|%{$_.Definition}|select displayname,status

Simple Date Entry for InfoPath

Had a request to enter in dates with a simple MMddYY format.  This is easier than you think.

  1. Create a for and add a Date Picker field to the view
  2. Double-click the field and add a new Rule
  3. Set the Condition as such: 
    1. string-length(.) = 6 and
    2. contains(“/”, .) = false and
    3. contains(“-“,.) = false
  4. Add an Action to Set a Fields Value
    1. Current Field or “.”
    2. Value: concat(substring(today(), 1, 2), substring(., 5, 2), “-“, substring(., 1, 2), “-“, substring(., 3, 2))
  5. Test out the for.  Enter a date like “050109” and it will put it in as 2009-05-01.

That is it.  Simple and easy to use

InfoPath Multi Column Drop Down

If you need to add a drop-down to an InfoPath form that has a Display Name with multiple columns, you can’t do this out of the box.  In order to make this work, you have to manually edit the XSL behind the scenes.  I don’t believe in adding code behind if you don’t need to but the XSL does not make this form need a Full Trust or anything, so this seems cleaner.

Save your InfoPath form File -> Save As Source Files…  From there go in and edit the view1.xsl or whatever you named your view.  Look for the <option> tags and in there you will find the <xsl:value-of/> tag with the field you chose in the display name.  Now, just add the additional stuff you want, and whala:


Save the file and open the manifest.xsf in Design mode and take a look


For those of you that run in to this, it is MUCH FASTER and way more SQL friendly to use MERGECONTENTDBS instead of the BACKUP and RESTORE option WHEN MOVING FROM THE SAME DB SERVER AND SAME WEB APPLICATION.  Why?  Think of what a BACKUP and RESTORE do.  First, the BACKUP writes all the contents of the site to a file, that could be rather big.  Then, the RESTORE puts it back in, which blows up the transaction log as well.  So what does MERGECONTENTDBS do that is so much better?  ANSWER: Skips the whole file process…big improvement in speed and efficiency

Let’s assume the following scenario: 

Database moving from: DB1

Database moving to: DB2

Site GUID:  A63289FB-5404-4B17-BB49-4378EEF2CDB7


Steps in the SQL Profiler world…not exact but you get the point

–Lock the site in DB1

–Same as STSADM -o setsitelock -url site -lock noaccess

exec proc_SetSiteFlags ‘A63289FB-5404-4B17-BB49-4378EEF2CDB7’,131072,131072


–Run a bunch of INSERT INTO DB1.[dbo].[Table] (Col1,Col2,…) SELECT Col1, Col2,… FROM DB2.dbo.Table











































exec proc_SetSiteFlags ‘A63289FB-5404-4B17-BB49-4378EEF2CDB7’,0,131072

exec proc_DeleteSite ‘A63289FB-5404-4B17-BB49-4378EEF2CDB7’


–Config DB

exec dbo.proc_dropSiteMap @Id=’A63289FB-5404-4B17-BB49-4378EEF2CDB7′


exec sp_executesql N’UPDATE [DB2].[dbo].[Sites] SET BitFlags = @Flag WHERE Id = @SiteId’,N’@SiteId uniqueidentifier,@Flag int’,@SiteId=’A63289FB-5404-4B17-BB49-4378EEF2CDB7′,@Flag=0


–Config DB

declare @p8 int

set @p8=1

exec dbo.proc_putSiteMap @ApplicationId=’A32D6D58-99F5-437F-BE32-3233D242BF91′,@DatabaseId=’DF61C0F9-1F4C-49B2-B1A9-99276320359E’,@SiteId=’A63289FB-5404-4B17-BB49-4378EEF2CDB7′,@Path=N’/sites/tester’,@Pairing=0,@RedirectUrl=NULL,@HostHeaderIsSiteName=0,@CurrentDatabaseSiteCount=@p8 output

select @p8


That is it…I know, not the prettiest thing, but SQL people get it and now hopefully so does everyone 


BUG: ACCESS DENIED. Adding/Removing Users/Groups

Ever get the error “Access Denied” while trying to add/remove a user/group from a SharePoint Site Collection? If you are like me and realize that the user you are logged in with has Site Admin priveleges, you are probably scratching your head like I was. If you are like me, those scratches left a mark on your bald head too! Enter bug in SharePoint.

If you have a Site Collection marked as “readonly” and it happens to be the last Site in the SharePoint table Sites, it will basically disable you from editing EVERY user/group in SharePoint that is within the same Content Database.

So how could someone figure this out? A lot of SQL skills help, and I gots em…

To replicate this problem, do the following:

  1. Create a new Web Application and a new Content Database in Central Administration (WEB APP=http://test, Content Database=SP_TEST_CONTENT)
  2. Create as many site collections as you want:
    1. “/” root Blank
    2. “/sites/T1” Blank
    3. “/sites/T2” Blank
  3. Open up SQL Management Studio (SSMS) and run a query like this: SELECT,s.BitFlags,w.FullUrl FROM SP_TEST_CONTENT..Sites s JOIN SP_TEST_CONTENT..Webs w ON s.Id = w.SiteId
  4. Take note to the one that is last, if it is “”, it is root.  Now, go to a command line and do the following: “stsadm -o setsitelock -url http://test/<Full URL from above> -lock readonly”
  5. Go to any of the site collections that ARE NOT marked as read-only and try to add a user/group…ACCESS DENIED

So what is the deal?  SQL skills take over…  I opened up SQL Profiler and watched what was going on.  The one line that got me was calling “proc_SecAddUserToSiteGroup”.  Upon looking at this SPROC with “sp_helptext proc_SecAddUserToSiteGroup” I saw another call to “fn_CanUserEditMembershipOfGroup”.  After doing the same thing on this call, found a line like the following:

SELECT @SiteFlags = BitFlags FROM Sites

All in all valid SQL statement, however, one big gaping hole…uh….WHERE CLAUSE!!!!!!!!

After this call, it looks for IF @SiteFlags & 131072 = 131072 RETURN 0.  DOH…that pretty much mean ACCESS DENIED!!!!

Microsoft, please fix this bug…