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…


AJAX and Publishing Pages

Last year I was working on a project and was required to use AJAX on a Web Part to display some things on a site.  No problem I thought.  Then, I had to enable this on a publishing site…problems begin.  Tried finding anything and everything regarding this problem and found that several people had the same issue.  I posted on forums, ran it past MS, co-workers, etc, no dice.  Today I was playing with a couple of other things and found the solution.  The issue is in the order of which the master pages display.  To make AJAX web parts work from a publishing site master page, you have to do the following:

1. Turn on AJAX for thw web.config (I follow Jan T’s solution for integrating AJAX the lazy way)

2. Create a web part page (ASP.Net AJAX project with 3.5) and use the UpdatePanel and the ScriptManager
        – There are several ways to do this without adding the ScriptManager to the .master page. 

3.  Make sure to add the EnsurePanelFix JavaScript thing…you can find that about anywhere on the net

4.  The most important thing, you have to make sure the master page is set up properly!!!!!!!!
         – For example, the default.master in SharePoint, works fine, however, blueband.master (and all the other ones in publishing “MasterPages”) have some flaws
                     – <Title>:  This is on separate lines, move this all together:
                    – SPWebPartManager: This needs to be Inside the <form> tag

If you use the default.master page that is in the publishing master pages, it works the chance that they were close and the issue was in the master page was high.  I sure hope this helps someone else.

Walkthrough: Creating a Basic ASP.Net AJAX-enabled Web Part
Good reference, even though this is for the old toolkit. About everything here can be applied to the current stuff

SharePoint Warm Up Script

I have had a lot of people use my other warm up script that I created almost a year ago but several people have asked for one that actually works…doh.  You see, my old one just used the WebRequest object to hit the site.  That does NOT warm up the site.  You actually need to get content from the page first.

This new script is a complete one that can be scheduled on your WFEs.  If you didn’t know already, all the Application Pools that get created are scheduled to reset themselves at a certain time.  Usually around 2 AM in the morning.  So if you want, take this script and put it in a CMD or BAT file and put an IISRESET right in front of it.  Then, add the code I have provided in a PS1 script and you are off and gunning.  You can remove those app pool recycles that are scheduled.

Save the following in a script named SPWarmUp.ps1

#-Running on machine with WSS/MOSS
#-C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN in path
function get-webpage([string]$url,[System.Net.NetworkCredential]$cred=$null)
$wc = new-object net.webclient
if($cred -eq $null)
$cred = [System.Net.CredentialCache]::DefaultCredentials;
$wc.credentials = $cred;
return $wc.DownloadString($url);

#This passes in the default credentials needed. If you need specific stuff you can use something else to
#elevate basically the permissions. Or run this task as a user that has a Policy above all the Web Applications
#with the correct permissions
$cred = [System.Net.CredentialCache]::DefaultCredentials;
#$cred = new-object System.Net.NetworkCredential("username","password","machinename")

[xml]$x=stsadm -o enumzoneurls
foreach ($zone in $x.ZoneUrls.Collection) {
    [xml]$sites=stsadm -o enumsites -url $zone.Default;
    foreach ($site in $sites.Sites.Site) {
        write-host $site.Url;
        $html=get-webpage -url $site.Url -cred $cred;

When you schedule this to run through a scheduled task, make sure the user running this has the correct credentials on the sites being hit.  Obviously, make sure this is on ALL WFEs.

Can you test this without all the other stuff?  Sure, just copy and paste the code and put it in to PowerShell and give it a whirl.

Download the file here

WSS/MOSS on Vista

It appears several have done this so I decided to make the jump.  Might I add…I LOVE THIS.  I took the plunge with MOSS since I tend to do most of my stuff in there.  Here is what you need:

                This tells you about everything you need to know…

                Pay attention to the IIS part so you have everything configured

                One thing that bit me right away was that ASP.Net was defaulted to run as v1.1 so when Central Admin spun it was running under 1.1 at first

                You can do the DBA install by running psconfig BEFORE you run the config like we usually recommend

                Make sure to create local users on your machine.  If you are like me, I tried through the standard “User Accounts” interface and that doesn’t work.  Click the Advanced Tab and then the Advanced button to do it that way


I have not configured the SSP as of yet, nor will I until needed.  This rocks!


Here is my script to config the main site:

psconfig -cmd configdb -create -server oma-khofer -database MOSS_Config -admincontentdatabase MOSS_Admin_Content_01 -user oma-khofer\SPFarm -password pass@word1


#After this is done, launch the wizard…I know there is a command to do that via psconfig, but can’t remember


Also, if you have any problems, tear it down, it is easy…


#PoSH script of course




#Delete SSP

stsadm -o deletessp -title $sspname -deletedatabases -force


#loop through and delete all your web apps you created

stsadm -o unextendvs -url http://oma-khofer:84 -deletecontent -deleteiissites


#Delete CA

stsadm -o deleteadminvs



psconfig -cmd configdb -disconnect



sqlcmd -S $databaseserver -Q "ALTER DATABASE [$databasename] SET SINGLE_USER WITH ROLLBACK IMMEDIATE"

sqlcmd -S $databaseserver -Q "ALTER DATABASE [$databasename] SET SINGLE_USER"

sqlcmd -S $databaseserver -Q "DROP DATABASE [$databasename]"



sqlcmd -S $databaseserver -Q "ALTER DATABASE [$databasename] SET SINGLE_USER WITH ROLLBACK IMMEDIATE"

sqlcmd -S $databaseserver -Q "ALTER DATABASE [$databasename] SET SINGLE_USER"

sqlcmd -S $databaseserver -Q "DROP DATABASE [$databasename]"