Obilogic - SharePoint Community Area

Search Active Directory Objects with PowerShell, SQL, BCS and SharePoint Search - Part 3

aobi — Fri, 16 Oct 2015 13:41:00 GMT

SharePoint provides import and search capabilities for User Accounts and Security Groups stored in Active Directory (AD) via the use of FIM, the User Profile Service, My-Sites and the SharePoint Search Service.

The following article(s) detail how other Objects stored in Active Directory (AD) can be made available in SharePoint Search Results via the use of PowerShell, SQL, BCS and SharePoint Search Display Templates.

Part 3:

Finally, we're going to create a Content Source (pointing at the BCS External Data Source created in Step 2) and a Custom Result Page using SharePoint Search.

Using the PowerShell below, a Content Source can be created by executing the following command:

.\CreateBCSSearchContentSource.ps1 -SearchApplicationName
"Search Service Application" -contentSourceName
"AD2SPDataADComputersViaBCS" -LOBSystemName "SQLSvrName-AD2SPData-Read"

-LOBInstanceName "SQLSvrName-AD2SPData-Read"

(CreateBCSSearchContentSource.ps1)

In addition to creating the Content Source the ‘CreateBCSSearchContentSource.ps1‘ script also runs a "Full Crawl".
Assuming the "Full Crawl" completes successfully (which confirms Step 1 and Step 2 were configured correctly), SharePoint automatically creates the relevant "Crawled Properties" within the SharePoint Search Schema.

However, before we can create the Custom Search Results Page we also need to create the relevant "Managed Properties" and "Map them to the relevant Crawled Property".

Again, using the PowerShell below (along with the ‘ManagedSearchProperties.csv' file provided), this can be achieved by executing the following command:

.\CreateManagedSearchProps.ps1 -ManagedPropertiesCSVFileName "ManagedSearchProperties.csv"

(CreateManagedSearchProps.ps1)

After the "Managed Properties" have been created successfully run another "Full Crawl" of the Content Source (this ensures the new "Managed Properties" are added to/included in the SharePoint Search results)

Now we can move on to setting up the Search Results page.

Open your "Enterprise Search Centre" site (or create one if you haven't already), navigate to the "Pages" document library and created a new Search Results Page (for this example I have create a page called "ADComputers.aspx"

Then navigate to your "Search Settings" page (via "Site Settings > Search Settings")...

... and ‘Add a Link' to the newly created ‘AD Computers' Search Results page.

Next we're going to upload (and publish) the ‘Custom Search Display Templates' that we're going to use on the ‘AD Computers' Search Results page.

To do this, open the "Root Site" and navigate to:

http://<youspwebapplicationurl>/_catalogs/masterpage/Forms/Display Templates.aspx?RootFolder=/_catalogs/masterpage/Display Templates/Search

From the Ribbon click "Files > Upload Document" and upload the "Item_ADComputer.html" and "Item_ADComputer_HoverPanel.html" files provided and "Publish a Major Version" for each.

(this process should automatically create "Item_ADComputer.js" and "Item_ADComputer_HoverPanel.js" files)

Next return to the "ADComputers.aspx" Custom Search Results page we created previously and select "Edit Page"...

... and edit the "Search Results Web Part" to display the "Web Part Properties".

Click "Change Query" and set the "Query text" field to:

{searchboxquery}* ContentSource:<yourSearchContentSourceName>

... and click "OK"

Then expand the "Display Templates" section, click "Use a single template to display items" and finally select "AD Computer Item" from the drop down.

Click to "Apply" and/or "OK" to save these changes to "Search Results Web Part Properties".

Save and Publish the updated "ADComputers.aspx" Custom Search Results page.

When a search is then performed via the "ADComputers.aspx" Custom Search Results page a list of matching Search Results are displayed with the relevant the object metadata information retrieved from Active Directory.

Happy Coding…

Please download attached zip for "full/complete .ps1" files - Examples provided above are simplified abbreviation

SearchADPowerShellSQLSharePoint.zip

Search Active Directory Objects with PowerShell, SQL, BCS and SharePoint Search - Part 2

aobi — Tue, 13 Oct 2015 15:57:00 GMT

Part 2:

Next we’re going to create an External Content Type (ECT) using the Business Connectivity Service (BCS) available in SharePoint.

Before we can do this, we first need to store some ‘Windows Credentials” in the SharePoint ‘Secure Store’, which will be used by the Business Connectivity Service to connect to the SQL Database previously created in ‘Part 1’.

Using the PowerShell below, this can be achieved by executing the following command:

.\CreateSecureStoreTargetApp.ps1 -SiteUrl "http://<yourspwebappurl>" -SecureStoreTargetAppName "AD2SPDataCreds" -SecureStoreTargetAppDispName "AD2SPDataCreds" -SecureStoreTargetAppEmail "<youremail@domain.com>" -SecureStoreTargetAppAdmin "<yourdomain>\SP_Admin" -SecureStoreTargetAppGroup "<yourdomain>\Domain Users" -SecureStoreTargetApp_CredUserAccount "<yourdomain>\

yourSQL_Access_Account" -SecureStoreTargetApp_CredPassword "<yourSQL_Access_Account_Password>"

(CreateSecureStoreTargetApp.ps1)

Now we have credentials stored, we can create the External Content Type for the ‘ADComputers’ SQL table.

External Content Types can be created using SharePoint Designer…

However, for this particular example (and in true Blue Peter style) we’re going to ‘Import’ a BCS Model file (.bdcm) which I created earlier and exported from SharePoint Designer...

To do this we’re going to first use PowerShell to ‘Update the BCS Model’ with the relevant SQL Server, SQL Database, SQL Table and Secure Store Target App information.

Before finally Importing the ‘BCS Model’ into the Business Connectivity Service and setting the permissions accordingly

Using the PowerShell below (along with the ‘ADComputer-template.bdcm’ file provided), this can be achieved by executing the following command:

.\UpdateImport_BCSModel.ps1 -SiteUrl "http://<yourspwebappurl>" -BCSModelTemplateFileName "ADComputer-template.bdcm" -BCSModelOutputFileName "ADComputers.bdcm" -DbServerName "<yourSQLServerName>" -DbName "<yourAD2SPDatabaseName>" -TableName "ADComputers" -SecureStoreAppID "<yourAD2SPDataSecureStoreTargetAppName>" -BCSEntityAdmin "<yourDomain>\SP_Admin" -BCSEntityUsers "<yourDomain>\SP_Crawl_Account"

(UpdateImport-BCSModel.ps1)

Read Part 3 ...

Please download attached zip for "full/complete .ps1" files - Examples provided above are simplified abbreviation

SearchADPowerShellSQLSharePoint.zip

Search Active Directory Objects with PowerShell, SQL, BCS and SharePoint Search - Part 1

aobi — Tue, 13 Oct 2015 15:46:00 GMT

Part 1:

To begin with we're going to create a SQL database using PowerShell.

Next we’re going to create a simple SQL Table that will hold the object metadata retrieved from AD.

Finally, we’re going to populate the SQL Table with information retrieved from AD using the ‘Get-ADComputer’ PowerShell CmdLet

(For this example we’re getting a list of Computers and their OS details)

Resulting in a populated SQL Table.

Read Part 2 ...

Please download attached zip for "full/complete .ps1" files - Examples provided above are simplified abbreviation

SearchADPowerShellSQLSharePoint.zip

SharePoint 2013 - Migrate/Replicate User Profile Service Application Configuration between Farms using PowerShell - Part 3

aobi — Wed, 13 May 2015 22:40:00 GMT

Following my previous posts on this subject, I thought I should add the following ‘Part 3' as a brief wrap up.

Assuming you (now) have a working User Profile Service Application on the existing farm and on the alternative/target farm, with matching "AD Sync Connections" and "User Profile Properties", there are a few other steps that you may need to take into consideration.

1. Permissions

Waldek Mastykarz has some great posts on this subject:
Granting permissions to the User Profile Service Application using PowerShell
Granting the "Retrieve People Data for Search Crawlers" administration permission on the User Profile Service Application using PowerShell

2. Connection Filters

There are a variety of links available on this subject
https://technet.microsoft.com/en-gb/library/ff182925.aspx#connections
https://technet.microsoft.com/en-gb/library/hh227258.aspx
http://sharepointcherie.blogspot.co.uk/2013/06/sharepoint-2013-setting-up-user-profile.html

However, none appear to provide any details on if/how they can be created via PowerShell, so as of this time of writing any 'Connection Filter Creation' steps will have to remain a manual UI task.

3. Re-Order User Profile Properties

Again, there a variety of good posts on this subject:
Change order of user profile properties in SharePoint 2013 with Powershell
SharePoint 2013: Another way to change order of user profile properties via powershell

Once these have been completed (and assuming the previous PowerShell Steps 1 and 2 were successful), you should now have a working duplicate of the User Profile Service Application on the alternative/target farm.

Happy Coding…

SharePoint 2013 - Migrate/Replicate User Profile Service Application Configuration between Farms using PowerShell - Part 2

aobi — Sun, 26 Apr 2015 01:16:00 GMT

Following my previous Part 1 post on this subject, the following ‘Part 2' shows how you can take the "User Profile Property" settings of the existing UPSA and use them to create a matching/duplicate set of "User Profile Properties" on a new UPSA on a different/alternative SharePoint 2013 farm.

Assuming you (still) have a working User Profile Service Application (or UPSA) on an existing farm, with multiple Active Directory (AD) Sync Connections, and have "successfully completed Part 1" so also have "new matching Sync Connections" on the alternative/target farm.

The first step is export the User Profile Properties from the existing farm.

This can be achieved by establishing a connection to the UserProfileConfigManager.ProfilePropertyManager and use the GetCoreProperties method to retrieve all "CoreProperty objects" and the GetProfileTypeProperties method to retrieve "all ProfileTypeProperty objects" and finally establishing a connection to the ProfileSubtypeManager and use the GetProfileSubtype.Properties to retrieve "all profile subtype properties", before finally exporting them to a set of CSV (yes... I know I need/want to update this to XML) files using the Export-CSV cmdlet.

For Example:

This should create an individual “ProfilePropertyManagerCoreProperties.csv”, “UserProfilePropertyManagerUserProfileTypeProperties.csv” and “UserProfileSubTypeManagerUserProfileProperties.csv” file.

However, using the following cmd (from the zip below)..

..also creates an additional file called “CustomUPSProps.csv”.

The next step is to import the “User Profile Properties” to the alternative/target farm using the “CustomUPSProps.csv” file.

Some of the advantages of using this Export / Import approach is the ability to easily create the New/Additional “User Profile Properties” by simply updating the “CustomUPSProps.csv” file with additional rows of data.

*Note: Current scripts will only ever “overwrite existing settings” (excluding internal names), but could theoretically be updated to remove/delete existing properties if NOT found in the CSV file.

Please download attached zip for "full/complete .ps1" files - Example provided above is a simplified abbreviation

UserProfileServiceApp_PowerShell_ExportImportADProperties.zip

Usage:


Export Example
.\ExportImport_UPSProfileProps.ps1 -MySiteURL http/https://<MySiteURL> -DoExport

Import Example
.\ExportImport_UPSProfileProps.ps1 -MySiteURL http/https://<MySiteURL> -DoImport -ImportCSVPropFilePath <FullPathToCSVPropertyFile>

SharePoint 2013 - Migrate/Replicate User Profile Service Application Configuration between Farms using PowerShell - Part 1

aobi — Mon, 20 Apr 2015 06:00:00 GMT

I came across a requirement to quickly and easily create duplicate/matching ‘User Profile Service Application Configurations' between SharePoint Farms, when recently setting up SharePoint 2013 Dev, Staging and Production environments.

Assuming you have a working User Profile Service Application (or UPSA), with multiple Active Directory (AD) Sync Connections, on an existing farm. The following shows how you can take the settings of the existing UPSA and use them to create a new UPSA on a different/alternative SharePoint 2013 farm.

The first step is export the Active Directory Sync Connection configuration.

This can be achieved by establishing a connection to the UserProfileConfigManager.ConnectionManager and using the GetEnumerator method to retrieve and iterate through each data source connection in the current service context, before exporting them to an XML file using the Export-Clixml cmdlet.

For Example:

This should create an individual "UPSConnection_<AD_Sync_Connnection_Name>.xml" file for each Sync Connection found.

The second step (...if you haven't done so already) is to create a new ‘blank' User Profile Service Application on the target Farm.

This can be done via Central Administration or (preferably) via PowerShell...

(...or if you happen to use AutoSPInstaller for your Farm Builds then you can also use it to Provision Service Applications)

Whichever approach you choose, I would advise using the same across on each of the farms.

The next step is to import the Sync Connection configurations into the new/blank USPA.

Now there seems to two possible approaches available for creating new User Profile Sync Connections via PowerShell...

The ~~Add-SPProfileSyncConnection~~ Cmdlet (Removed from TechNet - Not Supported see Harbar.net )

vs.

The AddActiveDirectoryConnection method of the ConnectionManager

Now due to the various (well documented) limitations of the Add-SPProfileSyncConnection cmdlet (such as ‘not being able to set the DisplayName', ‘one connection per forest' and it ‘overwrites the ConnectionSynchronizationOU'), along with the fact we're already exporting the settings via the ConnectionManager, we need to opt for the AddActiveDirectoryConnection method.

Some of the advantages of using this 'Export / Import' approach is the ability to easily create the DirectoryServiceNamingContext from the XML file with a single line of PowerShell ...

...which can then be passed to the AddActiveDirectoryConnection method of the ConnectionManager

You can also easily change/verify the DisplayName of the Sync Connection via PowerShell...

...or via the exported XML configuration file property

As well as being able to select which OU Containers are ‘included' or ‘excluded' by the Sync Connection via the ‘ContainersIncluded' and ‘ContainersExcluded' properties in the exported XML configuration file

I've also put together the following scripts to achieve this via some easy to use PowerShell :

NOTE: Please download attached zip for "full/complete .ps1" files (plus additional Create UPS examples) - All examples provided above are simplified abbreviations

UserProfileServiceApp_PowerShell_ExportImportADConnections.zip

Usage:


.\ExportImport_UPSConnections.ps1 -MySiteURL http/https://<MySiteURL> -DoExport  ## Export All Connections ##
.\ExportImport_UPSConnections.ps1
 -MySiteURL http/https://<MySiteURL> -DoExport 
-ExportConnectionName <SP_ADConnectionName> ## Export Specific 
Connection ##

.\ExportImport_UPSConnections.ps1 
-MySiteURL http/https://<MySiteURL> -DoImport -ImportXMLFilePath 
<FullPathToXMLConnectionFile> -ConnectionPassword 
<ADConnectionPassword> ## Import Specific Connection ##

Creating IFTTT Email Triggers from Office365 SharePoint Alerts

aobi — Sun, 12 Apr 2015 23:31:00 GMT

For those who may not know IFTTT (If This Then That) is a free cloud-based service that enables you to create simple connections, called ‘Recipes', between internet-connected services or ‘Channels' which are triggered by conditional events.

Although Microsoft has blogged about the upcoming release of Office 365 Channels in the future

http://blogs.office.com/2014/10/28/new-office-365-extensibility-windows-ios-android-web-developers/

..at this point in time, there are only Yammer, One Drive and OneNote channels available. None of which currently provide the ability ‘Trigger' the start of a Recipe.

However, one of the useful features of IFTTT is that it provides the ability to ‘Trigger' the start of a ‘Recipe' via its native ‘Email Channel'.

The following shows how you can setup SharePoint Alerts to act as ‘Trigger Events' for IFTTT Recipes
(I'm going to be using Office 365 for this example, but theoretically the same approach could also be taken to implement for On-Prem)

First off, sign up for IFTTT account

https://ifttt.com/join

(although it's probably easier if you sign-up using the email address that is going to receive the SharePoint Alerts, it's not a necessity as the ‘Email Channel' provides option to change/update this independently from the signup/registration address)

..and activate the Email Channel for your account

https://ifttt.com/email

(this process involves a PIN being sent to your email address for verification - again, you can change/update this email address independently from the signup/registration address. It's also worth making sure the address is entered in ‘lowercase' if using an ‘@<###>.onmicrosoft.com' address)

Next, head over to your Office 365 Mail and select ‘Options'

..and select ‘Inbox Rules' and click Add + and enter a Name for your new Trigger Rule

Select a condition for ‘When the message arrives'...
(for my example I've selected ‘Includes the words' "Dept 1" or "Announcement" in the Subject)

For the ‘Do the following' action, select ‘Forward the message as an attachment' and enter the IFTTT trigger email address trigger@recipe.ifttt.com

Next, you need to create your actual SharePoint Alerts.
Obviously you're going to need to give some thought on how you setup your ‘Inbox rules' (above) in relation to which Alert(s) you want to actually trigger the rule (..and the eventual IFTTT Recipe).

For my example, I'm going to create a ‘SharePoint Alert' on an ‘Announcements' list in a site named ‘Dept 1'

Finally, you need to create your ‘IFTTT Recipe'.

For my example, I'm going to create an IF Recipe

..that is Triggered whenever an email is received
(sent from the address previously associated with my ‘Email Channel')

..and then sends a "push notification" to my Windows phone using the Pushalot Channel
(admittedly this is probably a very convoluted way of getting notifications to your Windows device ,but it proves the Recipe(s) work and is good for testing.)

Now whenever a new item is added to the "Dept 1 Announcements" list...

..the SharePoint Alert is received by my inbox and if the subject line meets the condition of the inbox rule (which in my example it always does as ‘Includes the words "Dept 1" or "Announcement" in the Subject line)... it forwards the email to the IFTTT Email Channel, which in turn ‘Triggers' my Recipe..

...which sends a ‘Push Notification' to my Windows device... which is pretty neat!

When you combine this with the fact the IFTTT Email Channel also provides the option to only fire ‘Trigger Events' based on #hashtags in the Subject line, along with the huge amount of well-known social and media service Channels available (such as Facebook, Flickr, GitHub, Instagram, Twitter, WordPress and Android and iOS to name a few). It soon becomes apparent that you could quickly and easily create multiple Recipes to target a variety of service Channels based pure only the #hashtags of the Subject line... which in my example could be achieved simply by including the relevant #hashtags of the "Title" field of Announcement item.

SharePoint Saturday UK 2014 Rundown…

aobi — Wed, 03 Dec 2014 23:01:00 GMT

The fifth SharePoint Saturday in the UK took place a couple of days ago, marking ‘half a decade' of FREE one day conferences. The event is open to the public and provides focused content covering SharePoint and the Office 365 platform.

What is SharePoint Saturday UK?

SharePoint Saturday UK is dedicated to educating and engaging members of the local SharePoint and Office 365 community. SharePoint Saturday UK draws upon the expertise of local and international SharePoint IT professionals, developers, architects, and users who come together to share real world experiences, lessons learned, best practices and general knowledge with other individuals.

These events are the closest you will get to free training.

The event kicked off with an thought-provoking Keynote Presentation by Agnes Molnar (@molaragnes) explaining Search, Delve, Clutter, OfficeGraph and the Key Components of implementing successful ‘Findiability'.

My first official session of the day was "Advanced List rendering using JSLink and Display Templates" presented by Martin Hatch (@martinhatch), in which Martin covered ‘Display Templates - 101' (loading JS, targeting Lists Templates, Page Life Cycle and custom validation ..including potential ‘gotchas'), ‘Multi Language Support' and ‘JSLink - The Magic Plumbing'. Each with plenty of cool demos thrown in for good measure...

Next up was ‘SharePoint Apps for the IT Pro - What you need to know' by Thomas Vochten (@ThomasVochten). Thomas showed how to go about setting up on-premises infrastructure to successfully run SharePoint hosted apps. Describing and demonstrating different configuration options and their associated constraints and/or hazards... He also threw in a couple of handy little tips along the way, which you may (or may not) have come across ...such as ‘certlm.msc' for quick access to the certifications manager snap-in.. and ‘Wildcard SAN Certificates' (.. which was definitely a new one for me!)

Third session was ‘Apps for Office - the hidden gem that's easier than you think' by Chris O'Brien (@ChrisO_Brien). Chris explained that developing ‘Apps for Office' should be ‘fairly straight forward' for existing SharePoint Developers, especially as they are based on core web technologies (HTML, CSS, JS) and are combined with the "Write Once... Run anywhere..." approach (Desktops, Phones, Tablets.. Office for Mac etc). Chris went on to illustrate the different types of the Apps before giving an in-depth demonstration of a full working ‘App for Office' and the various potential gotchas to look out for.

After a great FREE lunch, thanks to the awesome event sponsors (...who said ‘there's no such thing as a free lunch'?), I sat in on ‘Set Your Code Free - SharePoint Apps for Mobile World' presented by Bill Ayers (@SPDoctor). Bill explained how the field of ‘Mobile Device Technology' is rapidly and constantly changing (almost daily), whilst also providing some interesting ‘% of Market Share' data. He then went on to share numerous tips, comparisons and demonstrations around developing ‘Cross Platform Mobile Apps' using C#/XAML, JS/Html5 and a variety of mobile libraries +frameworks

My final session of the day was ‘Writing a JavaScript framework for SharePoint 2013' presented by Hugh Wood (@HughAJWood). To be honest this session was definitely the ‘surprise session of the day', as the title and description really didn't give an indication to the level of content provided. Basically, if (like me) you think you know a little bit about JS because you've done a bit of jQuery or a bit of SharePoint 2013 development using JSOM/CSOM/REST etc... then think again! (and check out this session if/whenever you get a chance). Hugh started off showing us where many of us are going wrong with simple JS (irrespective of SharePoint). He then moved onto to popular JS Design Patterns and the potential additional burden of sealing and unsealing scopes, before revealing some impressive SharePoint specific examples ...including the apparently ‘undocumented' SP.SOD.loadMultiple JS function. I'm think Hugh may have broken ‘official @SPSUK protocol' when he ended the session by asking the audience to pick the winner of the free @SPCaf Professsional License ... but I'm sure that can be overlooked given the quality of the session ;) (... and no, I'm not jealous of @al_eardley for winning... Ok perhaps just a little)

The day was rounded off with the annual "prize gift giving" (again, each kindly donated by the awesome event sponsors) and a SharePint.

As usual a HUGE THANKS goes to the event organisers Brett Lonsdale (@brettlonsdale) , Tony Pounder(@WorTony) and Mark Macrae(@m_macrae) as well as all of the event sponsors who made the whole day possible (... did I mention it was FREE).

@KinetalMedia: Showreel for @spsuk is now live!

http://kinetal.co.uk/2014/12/sharepoint-saturday-uk-2014/

SharePoint Online - Checking Build Versions and JS File Versions

aobi — Fri, 28 Feb 2014 18:39:00 GMT

After experiencing an issue on my O365 Tenancy recently (...namely 'There is no trusted urls configured for the app deployment' errors when attempting to perform ‘Cross Domain REST calls' from SharePoint Hosted Apps - see Thread 1, Thread 2), I wanted to find a way of checking the version and build number of the SharePoint instance...

A quick google search located "Getting the Product Version for SharePoint Online" from Cory Peters which shows how to obtain the current build version via "/_vti_pvt/buildversion.cnf".

Although this allowed me to confirm there were differences in the versions between tenants, there didn't seem to be any correlation between the affected build numbers and the errors being returned.

So, I wanted to check if there were any differences in the ‘SP.js' and/or ‘SP.RequestExecutor.js' files themselves...
(SharePoint 2013 SP1 has/had just been released, so I assumed it was possible that ‘SP.RequestExecutor.js' may have been updated... especially as the "new SP.RequestExecutor()" call seemed to be being affected - again see Thread 1).

Although ‘/_layouts/15/SP.RequestExecutor.js' was reporting the exact same file size (60.3Kb / 61,787 bytes) from both affected and unaffected tenants, a quick "ExamDiff" showed that there were in fact differences ... namely ‘rpr' values.

I then downloaded the ‘SP.RequestExecutor.debug.js' (aka readable version) and found that the ‘rpr' value was regarding the "version" info for the "g_all_modules" array.

My 'problem/affected' tenant reporting:

g_all_modules["sp.requestexecutor.js"] = {
"version": {
"rmj": 16,
"rmm": 0,
"rup": 2607,
"rpr": 1216
}};

My "unaffected" tenant reporting:

g_all_modules["sp.requestexecutor.js"] = {
"version": {
"rmj": 16,
"rmm": 0,
"rup": 2607,
"rpr": 1214
}};

Interestingly enough, neither of which match the build version reported via "/_vti_pvt/buildversion.cnf"

'Problem/Affected' Tenant:
vti_buildversion:SR|16.0.2607.1205

‘Working/Unaffected' Tenant:
vti_buildversion:SR|16.0.2607.1208

The same process can also be repeated for checking the version of the ‘SP.js' file (and/or ‘SP.debug.js'), by simply downloading (‘/_layouts/15/SP.js') and searching for "rup" and/or "rpr" values.

Not sure whether this information will actually turn out to be useful in relation to my particular problem, but thought might be useful/interesting to some.
('every Cloud has a silver lining' and all that)

------------- UPDATE -------------

For anyone interested in the 'There is no trusted urls configured for the app deployment' errors when attempting to make Cross Domain REST calls from a SharePoint Hosted App, the lastest advice from MS is

'This is currently a known issue in the SPO environment and is being investigated and looked into. The work around you have to change the URL to lower case is mentioned and you can continue using it till a fix is applied.'

... the work around being:

exec = new SP.RequestExecutor(appWebUrl.toLowerCase());

Happy Coding....

SharePoint 2013 - Custom CallOut with File Preview

aobi — Thu, 23 Jan 2014 16:25:00 GMT

In addition to the standard 'Modal Dialog' windows in SharePoint 2010, SharePoint 2013 introduced new 'Call Out' windows.

There are plenty of good posts out there detailing how to implement and utilise the new 'Call Out Framework' ('callout.js')

..to name just a few.

However, I was surprised to find that none provided a working example of the 'File Preview' layout which is used in the 'Call Outs' of standard Document Libraries.

So here's a working example of how it can be implemented....



function getCallOutFilePreviewBodyContent(urlWOPIFrameSrc,
pxWidth, pxHeight) {
    var callOutContenBodySection = '<div
class="js-callout-bodySection">';
    callOutContenBodySection += '<div
class="js-filePreview-containingElement">';
    callOutContenBodySection += '<div class="js-frame-wrapper"
style="line-height: 0">';
    callOutContenBodySection += '<iframe style="width: ' + pxWidth + 'px;
height: ' + pxHeight + 'px;" src="' +
urlWOPIFrameSrc + '&amp;action=interactivepreview&amp;wdSmallView=1"
frameborder="0"></iframe>';
    callOutContenBodySection += '</div></div></div>';

    return callOutContenBodySection;
}


function OpenItemFilePreviewCallOut(sender, strTitle, urlWopiFileUrl) {
    RemoveAllItemCallouts();
    var openNewWindow = true; //set this to false to open in
current window
    var callOutContenBodySection =
getCallOutFilePreviewBodyContent(urlWopiFileUrl, 379, 252);
    var c = CalloutManager.getFromLaunchPointIfExists(sender);
    if (c == null) {
        c = CalloutManager.createNewIfNecessary({
            ID: 'CalloutId_' + sender.id,
            launchPoint: sender,
            beakOrientation: 'leftRight',
            title: strTitle,
            content: callOutContenBodySection,
            contentWidth: 420
        });

        var customAction = new CalloutActionOptions();
        customAction.text = 'Open';
        customAction.onClickCallback = function (event, action) {
            if (openNewWindow) {
                window.open(urlItemUrl);
                RemoveItemCallout(sender);
            } else {
                window.location.href =
urlItemUrl;
            }
        };

        var _newCustomAction = new CalloutAction(customAction);
        c.addAction(_newCustomAction);
    }

    c.open();
}

The above function "OpenItemFilePreviewCallOut" could then be used as follows:


<a
id="CallOutExample" onclick="OpenItemFilePreviewCallOut(this, 'My
Title','<WopiFileUrl>')" title="CallOut With File
Preview" h ref="#">
Call Out with File Preview</a>

Where the "<WopiFileUrl>" parameter is provided in the format:

"https://yoursite.sharepoint.com/_layouts/15/WopiFrame2.aspx?sourcedoc=<SPFile Relative Url>"

Probably also worth mentioning that both the '_layouts/15/WopiFrame2.aspx' and the '<SPFile Relative URL>' need to refer to the same SPWeb

Correct Example:
https://yoursite.sharepoint.com/TeamSite1/_layouts/15/WopiFrame2.aspx?sourcedoc=/TeamSite1/Shared Documents/test1.docx

Incorrect Example:
https://yoursite.sharepoint.com/_layouts/15/WopiFrame2.aspx?sourcedoc=/TeamSite1/Shared Documents/test1.docx

...and if you're wondering why I've referred to 'WopiFrame2.aspx' (instead of 'WopiFrame.aspx') above - see the following explanation from Wictor Wilén:

http://www.wictorwilen.se/an-explanation-to-to-start-seeing-previews-please-log-on-by-opening-the-document-in-sharepoint-2013

Finally, here are a few helper functions which can be used to Close and/or Remove existing Call Outs:
(fyi - the 'RemoveAllItemCallouts' and 'RemoveItemCallout' functions are called/used in the 'OpenItemFilePreviewCallOut' example above)



function RemoveAllItemCallouts() {
    CalloutManager.forEach(function(callout) {
        //
remove the current callout
        CalloutManager.remove(callout);
    });
}
function RemoveItemCallout(sender) {
    var callout =
CalloutManager.getFromLaunchPointIfExists(sender);
    if (callout != null) {
        //
remove
        CalloutManager.remove(callout);
    }
} 


function CloseItemCallout(sender) {
    var callout =
CalloutManager.getFromLaunchPointIfExists(sender);
    if (callout != null) {
        // close
        callout.close();
    }
}

Happy Coding....

SQLSaturday #228 Cambridge

aobi — Sat, 12 Oct 2013 00:10:00 GMT

A couple of weeks ago I managed to perform my second ever "SharePoint speaker session", this time at SQLSaturday Cambridge (...have been meaning to blog about this sooner, but simple haven't had time).

This was the first SQL Saturday event I'd ever attended, and I have to say it was a very (very) pleasant and well organised event (...with an obvious huge thanks going to Mark Broadbent @retracement, John Martin @SQLServerMonkey, Kevin Chant @kevchant, Peter Baddeley @baddaz and all other event organisers)

Now some of you might be asking, "what's a SharePoint guy doing speaking at SQL Saturday event?"... well the simple answer is this year they had decided to introduce a ‘dedicated SharePoint track' to focus on the relationship between SQL Server and SharePoint.

After submitting my session (again, covering BCS... although this time using SharePoint 2013), I was pleasantly surprised when I received the email informing me I had been selected from the large number of ‘Submitted Sessions'

Full details, session slides and scripts from my presentation can be found via:
Rapid Data Integration using SharePoint Business Connectivity Services

Full details of the days schedule (along with session downloads) can be found via:
http://www.sqlsaturday.com/228/schedule.aspx#schedulewrap

....again, huge thanks to all involved

2013… a New Year, a New SharePoint Version and a New Set of Challenges - Part 2: App Parts, iFrames and PostMessage Resizing

aobi — Fri, 11 Jan 2013 14:46:00 GMT

Once an App is deployed to SharePoint site, it is listed on the 'Site Contents' page of the host web (is also listed under 'Apps in Testing' on O365 Developer Sites). Users can access (or 'Start') a chosen App by simply selecting it from the list. When opened in this way, the App runs in full-screen mode (via the App Web).

Alternatively, an App can be added to any SharePoint Wiki page or Web Part page in the form of an 'App Part' (a new type of Web Part that is represented by the ClientWebPart class). Like any other Web Part, it appears in the SharePoint 2013 UI from which users add Web Parts.

(In my previous post I covered how to easily create some very simple 'Apps' and 'App Parts' in SharePoint 2013, using Napa Office 365 Development Tools, and some fundamental differences required for each to work correctly)

'App Parts' are essentially wrappers for an iframe element that surface a particular page of the App. (in our case 'ClientWebPart.aspx')

The use of 'iframes' is a significant difference compared to the average SharePoint 'Web Part', as any html output from a normal 'Web Part' is rendered 'directly within' the html of a SharePoint Wiki page or 'Web Part Zone' of a Web Part page.

Now I'm not saying it isn't possible to code an iframe into an average 'Web Part', but to my knowledge this wasn't generally/previously regarded as a 'best practice approach' ...and developers (as well as content managers, power users etc) may have to consider the possibility of network overheads arising from 'page loads' if/when large numbers of 'App Parts' are added to a single Wiki or Web Part page, due to each of the 'iframe elements' resulting in an additional 'http get request' (especially when dealing with the Cloud and o365).

(I'm sure some would argue that a retrieving the entire html of the 'Page' and 'Web Parts' in a 'single http get request' isn't preferable compared to the 'multiple get requests' involved with 'App Parts' and their iframes ...but until I can locate or produce some performance info to prove this either way, I'll leave that discussion for another day ...so I digress).

So what does all mean for developers moving to SharePoint 2013?

According to Eric Shupps 'Introducing the SharePoint 2013 Application Model'

For starters, it means a lot more thought will need to be put into how an application is constructed and deployed. The app model is best suited to HTML + Javascript solutions - traditional code-behind ASP.NET and SharePoint developers will have to enhance their skill set in order to adapt.

Well, one of the first examples of this 'required adaption' that I came across when dealing with 'App Part' development, was dealing with 'dynamically loaded html content'.

To demonstrate what I mean I'll begin with another simple 'App Part', which I'll call 'DynamicDivSharePoint2013App'.

Created using 'Napa Office 365 Development Tools', I get the standard x5 file starting point and make the necessary changes required to the 'ClientWebPart.aspx' and 'App.js' files (see previous post)

... add a new 'Div' element

..and a couple of JavaScript hyperlinks (which will be used to resize the 'DynamicDiv' element).

...and finally create the associated 'resize div' function (in this case 'ResizeDynamicDiv') in the App.js file.

When the App is then published

...and the 'App Part' added to a SharePoint Wiki page

We get the html output as expected

...however, when the 'Large Div' link is then selected, the larger output results in 'horizontal' and 'vertical' scroll bars within the 'App Part', which appear due to the size of the iframe used by the 'App Part' remaining 'static' whilst the height and width of dynamic html output (from the underlying 'ClientWebPart.aspx') increases

Now compared to 'normal Web Parts' this is a new problem that developers might not have previously come across, but may now have to deal with when working with 'App Parts', as 'normal Web Parts' don't (usually) use iframes to display their content.

(oh and if you're thinking of just setting the 'height' and/or 'width' in the 'Web Part Properties'... think again, as they relate to the parent 'wrapper' of the Web Part and not the iframe element within it)

It is possible to set the initial 'Width and Height' properties via the 'App Properties' of the App itself...

(example via Napa) .. but again, these aren't dynamic, so still won't actually resolve the issue for dynamic content.

Luckily I came across the following post from Richard diZerega (... I really was surprised on how little SharePoint 2013 related information I could actually find on this subject), Optimizing User Experience of Apps for SharePoint 2013, which clearly states:

Resizing is controlled by the app part's height and width web part properties, which are ultimately applied to the height and width of the iframe that gets rendered. These properties can be configured to a default size by the developer in the Elements.xml, but can be changed by a site designer when placed on a page. Unfortunately, app parts do not auto-size as the content contained in them grows. Additionally, an app page cannot walk the DOM hierarchy outside the page to adjust itself (this is blocked across domains).

To achieve this Microsoft has implemented the HTML5/javascript postMessage framework to achieve resizing from an 'App Part' on Wiki/Web Part page.
window.postMessage enables safe cross-domain communication between a parent page and iframe page, provided they are listening for messages.

So my second challenge for 2013... dynamic resizing of a SharePoint 2013 App Part using the postMessage framework.

To achieve this I created the following 'PostResizeMessage' JS function to the 'App.js' file...



function PostResizeMessage() {

 var target = parent.postMessage ? parent : (parent.document.postMessage ?
parent.document : undefined);

 var regex = new RegExp(/[Ss]ender[Ii]d=([\daAbBcCdDeEfF]+)/);

 results = regex.exec(this.location.search);

 if (null !== results && null !== results[1]) {


  target.postMessage('<message
senderId=' + results[1] + '>resize(' + ($(document).width() )      + ',' + ($(document).height())
+')</message>', '*');


 }

}

...and added the call to the end of the relative function (in this case 'ResizeDynamicDiv')

You can also add a call within the '$(document).ready(function ()' or 'sharePointReady()' function, to resize the iframe of the 'App Part' on the initial page load.

(... like I said, I was really was surprised on how little information I could actually find on this subject relating to SharePoint 2013, so apologies if it's not 100% 'best practice')

One final step that I also make, mainly for testing/demo purposes, is to add a border to 'DynamicDiv' element via the App.css file (this makes the resizing of the DynamicDiv more visible)

Now when the 'App Part' is re-published ...

..and the 'Large Div' link is selected, the larger output no longer results in any 'horizontal' or 'vertical' scroll bars within the 'App Part'

Happy coding...

2013… a New Year, a New SharePoint Version and a New Set of Challenges - Developing Apps and App Parts

aobi — Thu, 10 Jan 2013 14:38:00 GMT

One of the new 'big things' that come with SharePoint 2013 are 'Apps'.
They're everywhere and everything within SharePoint is now regarded as an 'App' (... even Lists and Libraries are now referred to as 'Apps')

So my first challenge of the year... developing a SharePoint 2013 App!

There are several good resources already available on the subject, from background info (Ted Pattison's 'Introducing SharePoint Apps' for example) to detailed, in-depth guides ( 'SharePoint 2013 apps - architecture, capability and UX considerations' from Chris O'Brien) as well as the usual tutorials, guides and videos available from MSDN (Apps for SharePoint overview, What's new for developers in SharePoint 2013, Build apps for SharePoint)

Armed with this vast amount of information (along with google/bing) ... how hard can it be?

Well to be honest I've got to hand it to Microsoft, getting my first working 'app' up and running was a relatively quick and painless matter (.. in fact I was able to create two).

After setting up my Office 365 Developer site (if you already have an o365 SharePoint site, but can't seem to find the 'Get Tools to Build Apps' link, see 'How to Add a site collection for Office 365 Development Tools' or alternatively you can 'Sign up for a new Office 365 Developer Site'), I simply followed a couple of 'Basic Tutorials' (Introducing Napa - Office 365 Development Tools and How to: Create a basic app for SharePoint by using 'Napa' Office 365 Development Tools) and was pleased to have a couple of working (although limited) SharePoint 2013 Apps to show for my time...

First challenge of the year completed? ... well as it turns out, not quite.

After publishing the completed Apps to my Developer site...

...they became available as anticipated

... and functioned as expected when clicked and accessed (via the App Web)

However, when I then attempted to add them as 'Web Parts' or rather 'App Parts' onto a Wiki page

(... see what I mean, Apps are everywhere!)

I was rather disappointed when I was not presented with 'Hello', but the default 'your content goes here' ...

(along with a rather nasty looking blue background)

Returning to 'Napa Office 365 Development Tool', it becomes apparent that x5 files are automatically generated at creation, as a starting point for each new SharePoint App:

App.css
AppIcon.png
ClientWebPart.aspx
Default.aspx
App.js

App.css and AppIcon.png are both fairly self-explanatory, whilst at first glance the others may not be.

ClientWebPart.aspx -
Contains the html code used whenever the App is rendered via an 'App Part'
Default.aspx -
Contains the html code used whenever the App is rendered via the 'Hosted version' of the App
App.js -
Contains the js code used by the App (and needs to be specifically referenced in both the Default.aspx and/or ClientWebPart.aspx files

So, in this particular instance, it's clearly the ClientWebPart.aspx file that needed updating...

adding a reference to the 'App.js' file

<!-- Add your JavaScript to the
following file -->

<script
type="text/javascript" src="../Scripts/App.js"></script>

and adding a new '<p id="message">' tag (used in the code of the 'App.js' file to output the 'Hello' message)


// This function is executed if the above call is successful

// It replaces the contents of the 'helloString' element with the user name



function onGetUserNameSuccess() {

$('#message').text('Hello ' + user.get_title());

}

Now you might be thinking 'great... everything's ready to go', but if you try re-publishing the App again at this point, the default 'your content goes here' is still displayed.

The problem is due to the fact that that code in the 'App.js' file attempts to make calls to the 'SharePoint Client Object Model'..

To get this working there is one more critical step that's required... you need to add a reference to 'SP.js' (http://msdn.microsoft.com/en-us/library/jj246996.aspx).

Unfortunately referencing "SP.js" is not as simple as you might think, because before you can get SP.js, you have to get SP.Runtime.js...

Luckily Corey Roth has provided a step-by-step on how to get this done:

How to: Use the SharePoint 2013 Client Object Model (SP.js) from a Client Web Part

With this additional JS code added to the 'App.js' file...

and the reference to "jquery-1.6.2.min.js" file added to the ClientWebPart.aspx file..

The much awaited "Hello" message is displayed within the 'App Part'!

(...and in case you want to get rid of that 'rather nasty looking blue background'... update the App.css file accordingly)

SharePoint Saturday UK 2012 Rundown…

aobi — Fri, 21 Dec 2012 17:39:00 GMT

SharePoint Saturday UK 2012, the third of the SharePoint Saturday events held in the UK, took place a couple of weeks ago and I was lucky enough to grab one of the free tickets again for the third year in a row.

This year's event was professionally organised (as always) by the Id-Live & Lightning Tools team (along with their sponsors who are vital, as they keep the event FREE) and was held at the East Midlands Conference Centre in Nottingham for a second year.

Eric Shupps kicked things off with an inspiring Keynote, not only taking a look back over previous SharePoint versions, but also reflecting on the history of the SharePoint Community itself (presenting some very interesting community statistics and acknowledging some of SUGUK Community legends in the process).

He then turned his focus to SharePoint 2013 and some of many new features and architecture changes that come with it (such as ‘shredded storage', ‘distributed cache', ‘workflow management', ‘office web apps', ‘claims', ‘search', ‘social features - reputation points, newsfeeds, bi-directional tasks', ‘device channels', ‘design manager'... and of course not forgetting ‘SharePoint Apps'!!)

The first session of the day was "SP2013 - What's New for IT Pro's" presented by John Timney (@jtimney), in which he covered:

Visio Process Repository (is currently still in RTM, but all doco says is/will be removed)
Design Manager (unfortunately probably only really going to useful with Publishing Sites)
Office web apps (Big Changes as now runs on own server.. single office web app sever can service multiple SharePoint Servers)
Analytics Server (see above - theoretically very similar)

Can run 10,000 users off single office web app server (.. has become v. efficient compared to SP2010)
Work Management Service: Task Aggregation (bi-direction tasks with Exchange - although requires Exchange 2013)
Translation Service: Attaches to 'external translation services' (Potential security issues - what goes on behind the scenes when translation happens? ...what info does Bing keep/store after translation???)
App Management Service: Catalogue is on-premise
No more Social DB (Info stored in Personal Site DB)
'MySites is required from day 1 in SP2013 for Social Features'!! (No more rolling out My Sites in Phase 2 / Phase 3 of a deployment)
Search: Only 1 single Search Engine in SP2013 ... across all versions!!! (and apparently the underlying code ISN'T JUST FAST from SP2010!! ... instead has been rebuilt from ground up)
Upgrades: DB Attach - Only options (yeah!!!)
All content DBs can be upgraded
Not all Service Apps can be upgraded (Due to architecture changes some need to be recreated)
Deferred Site Collections: Run in SP2010 compatibility mode...
Cannot upgrade PowerPoint Broadcast sites (not supported)
Cannot upgrade analytics as have to remove the Analytics DB before an upgrade (cannot just remove Service app)
Skydrive Pro: Works with SP2010 and SP2013 (theoretically 'any version of SP' - SYNCS doc libraries)
Shredded Storage: SQL 2012 required
Always On DB Availability
SQL Mirroring - deprecated in SQL 2012
Distributing Caching
Caching Tokens
Request Management: Route traffic based on 'custom rules' (Rules written in PowerShell)
Browser/ Device Support: ARM Device - IE 9-10 and on Win RT, Windows Phone 7.5 ie9 or later (renders HTML 5)
Companion Device License required for any NON Windows Device (this is in addition to standard SP Cal licensing requirements!!!)

Second session was "Getting Grips with SP2013 Apps" presented by Chris O'brien (@ChrisO_Brien), in which Chris covered the following:

MS potentially trying to shift problematic areas (ie. 'custom code') outside of SharePoint itself
SharePoint Apps require SSL on both SP and APP host for data transfer (Chris recommends SSL on both Prod and Dev environments)
Need wildcard SSL cert AND wildcard CNAME in DNs (APPs require used dynamic URLs)
Considerations: Do want to allow any 'app' or restrict to 'approved apps' (app store vs internal catalogue)
App Hosting Options:
SharePoint-Hosted - runs on same SP server (still isolated) no serverside code, javascript only (client api massively expanded)
Cloud App / Provider Hosted: a) Auto-hosted (o365 + azure etc) - additional azure bundle (for hosting SQL for example) comes free with O365 hosting .. b) provider hosted - you deal with app backend (can code in any language)
Hybrid App (combination of both)
Tenant Install Apps - Single instance of an app shared across multiple sites (instead of instance per site) (see http://msdn.microsoft.com/en-us/library/fp179896.aspx http://msdn.microsoft.com/en-us/library/fp179922.aspx)
"APP ID" section of APP URL auto-generated and is unique for each APP install/upgrade
REST JSON queries apparently easier to work with in jQuery rather than the default XML
Auto-Hosted Apps (auto hookup handles oAuth configuration) vs. Provided Hosted Apps (requires manual oAuth conf)

My third session was "Considerations for Upgrading from 2010 - SP2013" presented by Paul Grimely (@PaulGrimley), in which Paul discussed:

Why upgrade
Managing Demand
Authentication - (claims recommended - create classic WebApp in SP2013 - attach dbs - PowerShell change to claims ...detach dbs.. attach to claims WebApp)
Review unwanted feature set before migrating
Remove unwanted legacy solutions from DB prior to upgrade
Unknown 2010 pre-reqs SP level for upgrade (Paul recommended a minimum of SP1.. currently unconfirmed TechNet)
Ensure all SP2010 are using v4 UI (not v3) prior to upgrade
Service Apps: Office Web Apps, Excel Services (check for user defined functions)
Resource Farms - Upgrade these first!!
Content DBs: before attaching use Health Analyser to check for issues (ensure this IS NOT DONE on live)
Site collection upgrade
Upgrade content db process is way faster compared SP2010
Both "individual" and "bulk" upgrades possible
My Sites - Upgraded first time user hits his/her My Site

Fourth session of the day was "SharePoint 2013 Business Connectivity Services" presented by Penny Coventry (@pjcov) and Brett Lonsdale (@brettlonsdale)

Everything's an APP in SP2013 (just in case you hadn't already noticed... even SPLists, SP Libraries etc. are now referred to as ‘Apps')
BCS External Content Types (ECTs) deployed as ‘Apps'
Authentication Options (pretty much the same as SP2010)
New Data Provider protocol (oData)
oData External Content Type (ECT) only creatable from VS (not available in SPDesigner)
BCS DB naming confusing (Manage Metadata store menu option available from within CA... Nothing to do with Manage Metadata Service)
'Export To Excel' and 'Event Listeners' now available for External Lists (for example, could create 'event listeners' for External Lists, to kick off a workflow based on change to external data)
External Lists and Business Data Column has not really changed much from SP2010
External List - "Item Picker" Still limited to 200 items by default (can be updated from PowerShell)
Word Quick Parts - Create Lookup columns (against external list column) - Lookup column then available as 'quick part' within Word
Client Object Model (CSOM) - contains support for querying BCS content source(s)
Creating oData ECT from Visual Studio 2012 (oData contains "schema info" so VS can create ECT automatically)
‘Related External List' Web Part (Requires 'Create Association' operation at the ECT level)
oData data source available directly as "Add Item" in VS2012 (Simply enter the URL of oData data source - see oData.org for sample data site/services)

Fifth session was "How Workflows have evolved in SharePoint 2013" by Alan Richards (@arichards_Saruk), which he reviewed the new "workflow landscape" in SharePoint 2013:

Stages & Transitions (Stages - carry out specific task/function vs. Transitions -"mark" or "go to" specific 'Stage' of workflow depending on condition(s))
Looping (Loop n times or loop with/based on condition)
Visual Workflow Designer (Requires Visio Professional)
Workflows are Apps ...surprise surprise (they have an identity, so no revert to self, which uses a combination of App/User permissions by default - least permissions wins)
Can use an "App Step" (... effectively re-introducing "revert to self/app" type functionality???)
Standard SP2013 install only gives SP2010 workflows OOB (Need to install Workflow Manager, on separate webserver instance, for SP2013 workflow capabilities
Workflow Client (if Workflow Manager is on Remote Server then must be installed on all SP WFE Server(s), otherwise Workflow Client installed automatically if/when Workflow Manager installed locally on SP Server)
Uses oAuth for all communication between servers
Register-SPWorkflowService (use port 12290 for https and 12291 for http)
SPDesigner (once Workflow Manager is installed + setup, everything works as normal - to edit Workflows open/connect to the SPSite as normal.. not to the Workflow Manager Server)
SSL Certs (Specify https during Workflow Manager config/setup, then install Cert on IIS - thats it!!)

My final session of the day was "What's New in SP2013" presented by Nigel Price (@Nigel_Price), in which Nigel presented a high level overview of a variety new features of SharePoint 2013, including

Looking up CorrelationIDs on O365 (submitted via PowerShell)
Mark Up Improvements (No more tables in Web Parts and Web Part Zones, Client site rendering for Content Search WebPart, CSS completely re-written + simplified)
Display Templates (define visual layout of a Result Type)
Contoso Electronics Demo site
Secondary (Shadow) accounts
PDF Support OOB (no iFilter required)
UPS Sync Performance Changes & Improvements
User Licence Enforcement (can now grant/restrict users from accessing SharePoint Enterprise Features based on Security Group membership)

All in all another great SharePoint Saturday!

Five other things you can do with AutoSPInstaller

aobi — Fri, 26 Oct 2012 20:04:00 GMT

(and the PowerShell REM Command)

As you're probably already aware AutoSPInstaller is a PowerShell script, by Brian Lalancette, for automating SharePoint 2010 (and now SharePoint 2013) installations.

It really is a superb utility that any respectable SharePointer should definitely have in their "toolkit".
(I would even go as far to say "if STSAdmWin was the must have utility for SharePoint 2007, then AutoSPInstaller is the must have utility for SharePoint 2010")

Now I'm not going to cover how to setup and run AutoSPInstaller for new SharePoint installations, there are plenty of great posts already available detailing how this can be done (see Tobias Lekman's "Automated SharePoint Installations: Step-by-step" for example)

Instead, I'm going to demonstrate how AutoSPInstaller can be used to perform other SharePoint related tasks against existing SharePoint environments...

.... using nothing more than the PowerShell REM Command "#"

If you haven't already taken a look, AutoSPInstaller is comprised of x4 PowerShell Scripts ...

AutoSPInstallerMain.ps1
AutoSPInstallerFunctions.ps1
AutoSPInstallerFunctionsCustom.ps1
AutoSPInstallerConfigureRemoteTarget.ps1 ( this file was introduced in v3, so if it's missing it means you're running a pre v3 version)

The vast majority of code is located within "AutoSPInstallerMain" (the script file that serves as the ‘controller' for the scripted process) and "AutoSPInstallerFunctions" (which contains the majority of functions and actual code).

If we take a look inside the "AutoSPInstallerMain" (or "controller") script...

.. we can see there are x9 defined Regions:

1. Setup Paths & Environment
2. External Functions
3. Remote Install
4. Prepare For Install
5. Install SharePoint binaries
6. Setup Farm
7. Setup Services
8. Finalize Install
9. MAIN

Again, I'm not going to provide a detailed description of each region (...perhaps I'll cover that another time), but instead focus on "Prepare For Install", "Install SharePoint binaries", "Setup Farm", "Setup Services", "Finalize Install" and "MAIN".

If we expand "MAIN" and locate the first "Try { }" block, we can see that the functions from the x5 other regions are being called...

... and this will be our starting point for creating all "other uses" for AutoSPInstaller.

PrepForInstall

This is used to perform a series of "prerequisite configuration checks"...

Run-Install

This is used by AutoSPInstaller to perform the actual installation of "Prerequisites", "SharePoint", "Office Web Apps" and "Language Pack" binaries.

Setup-Farm

This is used by AutoSPInstaller to add the FarmAdmin account to the ‘local Administrators' group, load the SharePoint PowerShell cmdlets, create (or join) and configure the SharePoint Farm itself, configure Diagnostic Logging, configure Office Web Apps, configure Language Packs, add Managed Accounts and finally create any Web Applications that are defined in the "AutoSPInstallerInput.xml" config file

Setup-Services

This is where the majority of AutoSPInstaller magic occurs.

All "Service Apps" defined in the "AutoSPInstallerInput.xml" config file are created... "Usage Logging", "Tracing", "Outgoing Email" "Foundation Search" and "PDF Search" are all configured... as well as performing the installation of "ForeFront".

Finalize-Install

This performs some clean-up operations - removing the Farm Account from the local Administrators group and adding Network Service to local WSS_WPG group.

The first of the functions to get called is "PrepForInstall" and also happens to provide the first example of "other potential uses" for AutoSPInstaller...

1. Validate User Credentials & Passphrase

By commenting out the other x4 function calls (along with the conditional ‘Install-Remote' call) ...

.. . and then commenting out the "CheckSQLAccess" call located in the "PrepForInstall" function,

.. when "AutoSPInstallerLaunch.bat" is run (after saving these changes to the "AutoSPInstallerMain.ps1" file) the only actions that are executed are

ValidateCredentials
ValidatePassphrase
CheckConfig

Affectively turning the script into a way of checking that the "User Credentials" specified in the "AutoSPInstallerInput.xml" config file are valid and can authenticate successfully (which can be useful if passwords have been changed at a later date after an install), as well as confirming whether the "Passphrase" meets the necessary complexity requirements.

You can also comment out the "CheckConfig" function if you wish, but as it only checks for the existence of the "configuration file"...

.. I tend to leave it in place.

2. Validate SQL Access and SQL Permissions

This is achieved by commenting out the x4 function calls (along with the conditional ‘Install-Remote' call) located in MAIN ...

.. and commenting out the "ValidateCredentials" and "ValidatePassphrase" calls located in the "PrepForInstall" function (and re-instating the "CheckSQLAccess" call if commented out earlier).

Now when the "AutoSPInstallerMain.ps1" file is saved and "AutoSPInstallerLaunch.bat" is run, the only actions that are executed are

CheckConfig
CheckSQLAccess

Affectively turning the script into a useful way of verifying

a) all "SQL Database Servers" specified in the "AutoSPInstallerInput.xml" config file are valid and accessible from the SharePoint server .
(it will attempt to open and close a connection to each SQL Server specified and also check that the version of SQL meets the minimum compatibility requirements for SharePoint)

b) that the "SQL Alias" specified in the "AutoSPInstallerInput.xml" config file, is still present and valid.
(it will actually attempt to re-create the "SQL Alias" if it has been deleted)

c) that the current user (executing the script) has "dbcreator" and "securityadmin" permissions on each of the SQL Servers specified.
(this can be useful if SQL permissions have been altered after an install)

The script can be run repeatedly in this state quite safely (even under different credentials) and can even be used to check for "sysadmin" SQL permissions, by setting the "PerformancePointService Provision='true'" in the "AutoSPInstallerInput.xml" config file.

The second of the functions to get called from within MAIN is "Run-Install". As mentioned previously its primary function is to perform the actual installation of binaries. However, it also performs a few other useful "server configuration tasks"...

3. Server Configuration: RemoveIEEnhancedSecurity, AddSourcePathToLocalIntranetZone and ConfigureIISLogging

This is achieved by commenting out the "PrepForInstall", "Setup-Farm", "Setup-Services" and "Finalize-Install" function calls (along with the conditional ‘Install-Remote' call) located in MAIN ...

.. . and then commenting out the "DisableLoopbackCheck", "DisableServices", "DisableCRLCheck", "InstallPrerequisites", "InstallSharePoint", "InstallOfficeWebApps", "InstallLanguagePacks" and "FixTaxonomyPickerBug" calls located in the "Run-Install" function

Now when the "AutoSPInstallerMain.ps1" file is saved and "AutoSPInstallerLaunch.bat" is run, the only actions that are executed are

RemoveIEEnhancedSecurity
AddSourcePathToLocalIntranetZone
ConfigureIISLoggingCheckSQLAccess

Affectively turning the script into a useful way of

a) Internet Explorer Enhanced Security Configuration
(prevents those annoying security pop-ups in IE)

b) adding the hostname to the Local Intranet zone
(prevents annoying "Open File - Security Warnings")

c) setting the "IIS Log File Directory" to the location specified in the AutoSPInstallerInput.xml" config file and enabling NTFS compression to save disk space

You can also leave any/all of the "Disable" functions ("DisableLoopbackCheck", "DisableServices", "DisableCRLCheck") uncommented if you wish, as this will cause the script also "disable the loopback check" (for stand-alone demo servers), disable "unused services" ("Spooler","AudioSrv" and "TabletInputService") and disable the "Certificate Revocation List".

The third of the functions to get called from MAIN is "Setup-Farm".
Most of these tasks contained within this function have the ability to update the actual SharePoint Farm Configuration itself. Running them against an "existing/live" SharePoint environment could potentially create problems.

If configuration changes have been made manually on "existing/live" SharePoint environment, but NOT updated in the "AutoSPInstallerInput.xml" config file, then the "Setup-Farm" function can potentially "update" configuration settings back to their original install values.

Due to this potential y risk, I'm going to comment out the majority of the calls, leaving just "Load-SharePoint-Powershell", "CreateOrJoinFarm" and "CheckFarmTopology" as these can be run safely ...

;..."AddManagedAccounts" could also be left uncommented as is reasonably safe to run (and can be used to update ‘SharePoint Managed Accounts')...

It is possible to use the other function calls using the same "REM" approach (just ensure the "AutoSPInstallerInput.xml" config file matches your existing SharePoint configuration 100%).
But due to the potential risk I'm not going to cover using any of them here (... in other words"use with caution!")

The fourth of the functions to get called from MAIN is "Setup-Services" and provides the next set of "other potential uses" for AutoSPInstaller

4. Start SharePoint Services: Sandboxed Code Service and Claims to Windows Token Service

This is achieved by commenting out the "PrepForInstall", "Run-Install" and "Finalize-Install" function calls (along with the conditional ‘Install-Remote' call) located in MAIN

..commenting out the "Setup-Farm" function calls (as detailed below)

..and then commenting out all functions calls located in "Setup-Services", except "StartSandboxedCodeService" and "StartClaimsToWindowsTokenService"

5. Re-Provision/Re-Create Service Applications

This is achieved by first deleting the particular ServiceApp that is to be re-created (via "Manage Service Applications" in Central Admin for example)

Then comment out the "PrepForInstall", "Run-Install" and "Finalize-Install" function calls (along with the conditional ‘Install-Remote' call) located in MAIN

..commenting out the "Setup-Farm" function calls (as detailed above)

..and then commenting out all functions calls located in "Setup-Services", except for the "Create" function call for the particular ServiceApp to be re-created
For example "CreateEnterpriseSearchServiceApp".

(If you're using a pre-v3 version of AutoSPInstaller, there is an additional function call that will need to be made "StartSearchQueryAndSiteSettingsService"

... which appears to have been dropped in v3 and later)

So there you have it.. Five other things you can do with AutoSPInstaller and the PowerShell REM Command.

Clearly there are a variety of the other combinations that could be used to achieve a variety of other tasks (post SharePoint installation), but hopefully these examples can be used to provide a good starting point.

Detecting Duplicate List Items Using XSLT

aobi — Mon, 22 Oct 2012 15:16:00 GMT

A requirement came up the other day, for a quick and easy way of detecting and displaying duplicate items in a SharePoint List.

SharePoint 2010 has a ‘unique column' or ‘unique column constraints' feature which can enforce uniqueness on values stored in a list or library column, effectively creating additional keys on the List.

Although this can be a very useful feature (especially when creating/implementing new SPLists), there are still occasions when ‘unique column constraints' cannot be (or in this case, hadn't been) implemented on a given SharePoint List due to the data being stored, but you still need a way of returning a set of ‘unique values' from the given set of data.

For example:
If you needed to store the following data in a SharePoint List ...

..you would be able to do so and implement a ‘unique column constraints' on the Title (or Name) column without a problem.

However, if you needed to store the following data in a SharePoint List ...

you wouldn't be able to implement a ‘unique column constraints' on the Title (or Name) column.

But might still want to display a unique list of Names...

Luckily Marc D Anderson posted ways of achieving this (along with subtotals) with the use of some XSLT

Showing Subtotals in a DVWP, Sorted by the Subtotals - Part 1

Showing Subtotals in a DVWP, Sorted by the Subtotals - Part 2

Showing Subtotals in a DVWP, Sorted by the Subtotals - Part 3

This is achieved by "sorting" the results by the column in question (in this case "@Title" aka 'Name') and then using the ‘ddwrt:NameChanged' function to check if/when the value changes.

Now this approached works well, assuming that you only want to display/check for duplicate values that are stored in a single column.

If we add the other columns to the output, its limitations quickly become apparent.

In addition, this particular requirement needed to check for duplicate items based on values stored across two (or more) columns ..

..and simply flag them as "duplicates" without making any other visual changes (such as ‘sorting')

Luckily this can be achieved with the use of the some additional XSLT

First declare the following variables within the "dvt_1.rowview" xsl template

<xsl:variable name="currTitle" select="@Title" />
<xsl:variable name="currDateValue" select="@DateValue" />
<xsl:variable name="hasMatchingrows" select="$Rows[@Title=$currTitle and @DateValue=$currDateValue]"/>

The "hasMatchingrows" variable can then be used to perform the check for "duplicate items"

<xsl:if test="count($matchingrows) > 1">
(<img src="_layouts/images/warn16.gif" border="0" /> Duplicate!)
</xsl:if>

Furthermore, if we declare an additional XSLT variable, this can also be used in conjunction with the ‘ddwrt:NameChanged' function

<xsl:variable name="newCurrDateValue" select="ddwrt:NameChanged(string(@DateValue), 0)"/>
    
<xsl:if test="string-length($newCurrDateValue) > 0">
        
<xsl:value-of select="$currTitle "/> - 
<xsl:value-of select="$currDateValue"/> - Total:
    
<xsl:value-of select="sum($hasMatchingrows[current()]/@NumberValue.)"/>
</xsl:if>

One last thing to mention, remember that all XSLT transformations occur on the client.
It does NOT affect the SPQuery being submitted or the XML data set that is returned.

Therefore, each of the XSLT approaches mentioned above will only process

the data retuned by the underlying "List View" or SPQuery
the current ‘page set' of data

So if your "paging" is set to "Display in sets of 5 or 10", you will probably need to increase it (or alternatively perform some appropriate sorting/filtering) to ensure duplicates records DO NOT cross multiple ‘paged' results - otherwise some duplicates may not get flagged accordingly.

(You may want to set paging to "Display All Items" wherever possible, as this will ensure all XML records returned from the SPQuery are made available to the XSLT)

SUGUK Session - Accessing BCS Data: SharePoint Query vs. SharePoint Search

aobi — Thu, 14 Jun 2012 00:34:00 GMT

I managed to perform my first ever "SharePoint speaker session" at SUGUKEA yesterday (after hearing @ToddKlindt ‘step out of your comfort zone' talk at last year's SharePoint Saturday, it was something I'd been meaning to do).

I was lucky enough to get paired with the legendary SharePoint Speaker and Author Penny Coventry.

We had (somehow) both decided to cover BCS related subjects in our sessions and after a very brief discussion at the International SharePoint Conference things were all set to go.

http://sugukea010.eventbrite.com/

Agenda

    6.30pm - Registration
    6:45pm - Penny Coventry - BCS: How to get started
    7:45pm - Break
    8:15pm - Anthony Obi - Accessing BCS Data: Query vs. Search
    9:15pm - SharePint

Speakers and Abstracts

Penny Coventry - BCS: How to get started
Many organizations see Microsoft SharePoint as yet another silo of information that can only exploit data stored in lists and libraries. In this session, Penny will review the different methods of using external data within SharePoint. The rest of the session will concentrate on Business Connectivity Services (BCS). She will show you what you need to do within the SharePoint 2010 Central Administration web site, how to use SharePoint Designer to create external conten types - the components that enable you to connect to external sources. Lastly Penny, will look at external lists.

Anthony Obi - Accessing BCS Data: SharePoint Query vs. SharePoint Search
In this session, Anthony will be looking at two different approaches for retrieving and displaying external data in SharePoint 2010. He will be covering the basic fundamentals required by each for it to work, before attempting to explore and compare a variety of features available within each approach through a series of demonstrations. Finally Anthony will attempt to highlight key points/comparisons to look out for when attempting to identify which approach to use in real world scenarios.

Slides

Obilogic - Accessing BCS Data: Query vs Search (sugukea)

Would just like to give a huge thanks to Penny (@pjcov) and the #sugukea (@sugukea) organisers Peter Baddeley (@baddaz) and Randy Perkins (@SharePointRandy)

The International SharePoint Conference 2012 - Blogs, Slides, Downloads & Photos

aobi — Mon, 30 Apr 2012 00:20:00 GMT

Unfortunately I haven't had time to write my usual rundown for this year's International SharePoint Conference, which took place last week...

So this year's rundown will basically consist of the following, in an attempt to ‘syndicate content' on the subject

Live Blogging :

http://engageinsharepoint.co.uk/2012/03/international-sharepoint-conference-live-bloggers-wanted-isclondon/

http://thomasvochten.com/categories/live/

http://sharepointfrancois.posterous.com/live-blogging-from-the-international-sharepoi

http://engageinsharepoint.co.uk/author/baddaz/

http://engageinsharepoint.co.uk/author/spdoctor/

Slides :

http://www.21apps.com/sharepoint/isclondonbus301/
http://www.criticalpathtraining.com/Members/Pages/Presentations.aspx

http://www.joiningdots.com/blog/2012/04/when-technical-projects-fail/

https://skydrive.live.com/view.aspx?resid=6AF8677B7DC0B21D!2285&cid=6af8677b7dc0b21d
http://www.paulgrimley.com/2012/04/supporting-sharepoint-internally-slides.html
http://techblurt.com/2012/04/28/international-sharepoint-conference-slides/

Downloads :

http://visualstudiogallery.msdn.microsoft.com/166d71d6-0fb8-433d-bf08-72d5a3efe800

http://visualstudiogallery.msdn.microsoft.com/668d3881-b641-4f30-a513-0f9609892ac5

http://visualstudiogallery.msdn.microsoft.com/51a85e45-4a45-42cd-b21f-ff0c4cf729c1

http://spkbase.codeplex.com/

Subsequent Blog Posts :

http://blog.falchionconsulting.com/index.php/2012/05/international-sharepoint-conference-2012-follow-up/ (Note: Gary Lapointe is a Legend!)
http://www.21apps.com/sharepoint/isclondon-a-personal-review/
http://www.sharepoint911.com/blogs/laura/Lists/Posts/Post.aspx?ID=174
http://blogs.msdn.com/b/ukmsdn/archive/2012/05/08/event-international-sharepoint-conference-a-review.aspx
http://www.sharepointstudio.com/Blog/Lists/Posts/Post.aspx?ID=46

http://www.sharepointpromag.com/blog/dan-holmes-viewpoint-on-sharepoint-blog-24/sharepoint/sharepoint-presented-international-sharepoint-conference-142929

http://blog.mastykarz.nl/international-sharepoint-conference-london-2012-recap/
http://ghamson.wordpress.com/2012/04/25/last-day-at-the-international-conference-in-london/
http://www.andrewconnell.com/blog/archive/2012/04/30/wrapup-ndash-international-sharepoint-conference-2012-london.aspx
http://www.thesharepointbaker.co.uk/2012/04/internation-sharepoint-conference-2012/

http://sharepointsarcasm.sharepoint.com/Pages/default.aspx

http://www.sharepointblog.co.uk/2012/04/the-international-sharepoint-conference/

Photos :

www.mattgrovesblog.com/search/label/ISC
photos-from-the-international-sharepoint-conference-2012-Day-1

photos-from-the-international-sharepoint-conference-2012-Day-2

photos-from-the-international-sharepoint-conference-2012-Day-3

Again, huge thanks to @SteveSmithCK, @ZoeWatsonCK and everyone at @CKUKLtd for another great conference.

SharePoint 2010 – Export SPList Data into SQL using PowerShell

aobi — Sun, 22 Apr 2012 00:02:00 GMT

Following my previous post 'Import SQL Query Data into a SPList using PowerShell' I thought I should also post on how to achieve the reverse and export data from an SPList into a SQL Table

(if only for completeness, if nothing else...)

First task is to make the SQL Connection and prepare the SQL Command ..


######connect to SQL database windows authentication ###########

#Set new object to connect to sql database
$connection = new-object system.data.sqlclient.sqlconnection

#ConnectionString setting for <ServerName> <databasename> with window authentication
$Connection.ConnectionString ="server=<ServerName>;database=<databasename>;trusted_connection=true"


#ConnectionString setting for <ServerName> <databasename> with SQL authentication <username><password>
#$Connection.ConnectionString ="server=<ServerName>;database=<databasename>;User Id=<username>;Password=<password>;trusted_connection=False;

Write-host "connection information:"

$connection #List connection information 

Write-host "Connecting to database.." 

$connection.open() #Open Connection

#setting object to use sql commands
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

$SqlCmd.Connection = $connection

Then retrieve the data from SPList...

######### Get SPList ########
$spWeb = Get-SPWeb -identity "http://sp2010server/TeamSite/"  #  Get SPWeb 

$list = $spWeb.Lists["<My List Name>"] # Get SPList



foreach ($item in $list.items)
{ 
ExecuteSQLInsert $item["Title"].replace("'","''") $item["splistColumn2"].replace("'","''") $item["splistColumn3"].replace("'","''") $item["splistColumn4"].replace("'","''") $item["splistColumn5"].replace("'","''")
}


$spQuery = New-Object Microsoft.SharePoint.SPQuery
$spQuery.ViewAttributes = "Scope='Recursive'";
$spQuery.RowLimit = 2000
$caml = '<OrderBy Override="TRUE"><FieldRef Name="ID"/></OrderBy>' 
$spQuery.Query = $caml 

do
{
    $listItems = $list.GetItems($spQuery)
    $spQuery.ListItemCollectionPosition = $listItems.ListItemCollectionPosition
    foreach($item in $listItems)
    {
        ExecuteSQLInsert $item["Title"].replace("'","''") $item["splistColumn2"].replace("'","''") $item["splistColumn3"].replace("'","''") $item["splistColumn4"].replace("'","''") $item["splistColumn5"].replace("'","''")
    }
}
while ($spQuery.ListItemCollectionPosition -ne $null) 
$connection.Close() 

$spWeb.Dispose()

...and finally populate the target SQL Table using the data from the SPListItem
(Obviously, you will need to give some consideration in relation to matching the 'DataTypes' of the SQL Columns, with those of the source SPList)

######### ExecuteSQLInsert function #############
function ExecuteSQLInsert($sqlVAL1, $sqlVAL2, $sqlVAL3, $sqlVAL4, $sqlVAL5)
{

$ErrorActionPreference = 'stop' # Prepare script for stopping
$SqlCmd.CommandText ="INSERT INTO [sqlTABLE] ([sqlCOLUMN1], [sqlCOLUMN2], [sqlCOLUMN3], [sqlCOLUMN4], [sqlCOLUMN5]) VALUES ('$sqlVAL1', '$sqlVAL2', '$sqlVAL3', '$sqlVAL4', '$sqlVAL5')" # Configure TSQL

Try
{
$SqlCmd.ExecuteNonQuery() #
}
Catch
{
Write-Warning "$_" # Report Errors
Write-Warning $SqlCmd.CommandText
Write-host 
} 

}

SP2010ImportExportSPListToSQL - Examples.zip

SharePoint 2010 – Import SQL Query Data into a SPList using PowerShell

aobi — Sat, 21 Apr 2012 00:20:00 GMT

Ever needed to quickly transfer SQL data into a SharePoint list?...

A recent task required exactly this, when i needed to migrate some SQL data held in a legacy application into SharePoint.

After a bit of messing around exporting multiple CSV files from SQL and importing them into SharePoint, I was confident that I could speed up the process with the use of a little 'Powershell'...

First task is to make the SQL Connection ..


######connect to SQL database windows authentication ###########

#Set new object to connect to sql database
$connection = new-object system.data.sqlclient.sqlconnection

#ConnectionString setting for <ServerName> <databasename> with window authentication
$Connection.ConnectionString ="server=<ServerName>;database=<databasename>;trusted_connection=true;"


#ConnectionString setting for <ServerName> <databasename> with SQL authentication <username><password>
#$Connection.ConnectionString ="server=<ServerName>;database=<databasename>;User Id=<username>;Password=<password>;trusted_connection=False;"

Write-host "connection information:"

$connection #List connection information 

Write-host "Connecting to database.." 

$connection.open() #Open Connection

Then retrieve the data from SQL Table.. (or theoretically from a 'SQL View', 'SQL Query' or even 'Stored Procedure')


######### SQL query #############

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand #setting object to use sql commands 

$SqlQuery = "SELECT [sqlCOLUMN1], [sqlCOLUMN2], [sqlCOLUMN3], [sqlCOLUMN4], [sqlCOLUMN5] FROM [sqlTABLEorView] " #set SQL query 

$SqlCmd.CommandText = $SqlQuery # get query 

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter # 

$SqlAdapter.SelectCommand = $SqlCmd # 

$SqlCmd.Connection = $connection 

$DataSet = New-Object System.Data.DataSet 

$SqlAdapter.Fill($DataSet) 

$SqlAdapter.Dispose()

$connection.Close() 

$DataSet.Tables[0]

...and finally populate the SharePoint List using the retrieved data.
(Obviously, you will need to give some consideration in relation to matching the 'DataTypes' of the SQL Columns, with those of the target SPList)


######### Add to SPList ########

$spWeb = Get-SPWeb -identity "http://sp2010server/TeamSite/"  #  Get SPWeb 

$list = $spWeb.Lists["<My List Name>"] # Get SPList

foreach ($Row in $DataSet.Tables[0].Rows)
{ 

$item = $list.Items.Add();

$item["Title"] = $Row["sqlCOLUMN1"]
$item["splistColumn2"] = $Row["sqlCOLUMN2"]
$item["splistColumn3"] = $Row["sqlCOLUMN3"]
$item["splistColumn45"] = $Row["sqlCOLUMN4"] + $Row["sqlCOLUMN5"]

$item.Update()

}

$spWeb.Dispose()

This can greatly speed up the process and also enables to you import data from multiple SQL Tables/Views/Queries and even target multiple SPLists.

SP2010ImportExportSPListToSQL - Examples.zip

The International SharePoint Conference 2012

aobi — Fri, 23 Mar 2012 09:00:00 GMT

With only a month to go until the start of "The International SharePoint Conference 2012" (formerly known as the 'European SharePoint Best Practice Conference'), I thought it was about time I added a post on the subject.

Well, the guys and gals over at CK are planning to do things a little differently at this years conference.

Quote:

In 2012 we are going to be doing something very special and unique which is to have the IT Pro and Developer tracks follow a 3 day solution process and instead of doing standard 1 hour sessions we will be using as much time as necessary to cover a complete topic. How many times have you heard a speaker say ‘you can also do this and that’ but the speaker just runs out of time to show you exactly how. And wouldn’t it be nice to see some of these demos being done in a real world model say over 8 SharePoint Servers in a large farm design! Well this is exactly what our 2012 conference is going to be doing, with speakers from around the world who have the knowledge and real world skills to deliver such a track and over the course of the 3 days build the solution all the way through to the final design, everyone using the exact same servers over the 3 day duration.

http://www.internationalsharepointconference.com/blog/

Full details on the different 'Tracks' (IT Pro, Developer, Information Worker (IW) / End User and Business) as well as 'Full Agenda', with details of the different 'Track Solution Teams', is also now available.

http://www.internationalsharepointconference.com/Pages/Track-Overviews.aspx
http://www.internationalsharepointconference.com/Pages/Agenda.aspx

All in all, it looks like things are shaping up to be another great SharePoint conference!

SharePoint 2010 – Migrate/Replicate Three-state List Workflows between Site Collections with PowerShell

aobi — Fri, 16 Mar 2012 18:59:00 GMT

I had an interesting request come in the other day, which on the face it, sounded a fairly simple thing to resolve. But, as is often the case, it turned out to be a little trickier than I first anticipated.

Basically the requirement was to move one or more SharePoint Lists from one site (or SPWeb) to an alternative site, located in an alternative site collection. The issue being, each list that needed to be moved had a SharePoint Workflow associated with it, which also needed to be moved along with the list.

Now moving a SharePoint list can be achieved fairly easily using the Export-SPWeb/Import-SPWeb cmdlets and just a couple of lines of PowerShell.

For example, to move a standard "issues" list between site collections:

Export-SPWeb http://sp2010dev/ sites/SiteCollection001/Team/ -ItemUrl Lists/Issues -Path C:\Temp\IssuesList.cmp

Import-SPWeb http://sp2010dev/sites/SiteCollection002/Team/ -Path C:\Temp\IssuesList.cmp

For further information on the Export/Import-SPWeb cmdlets see

Export-SPWeb -Identity <Site URL> -Path <Path and file name> [-ItemUrl <URL of site, list, or library>] [-IncludeUserSecurity] [-IncludeVersions] [-NoFileCompression] [-GradualDelete] [-Verbose]
http://technet.microsoft.com/en-us/library/ee428301.aspx#section1

Import-SPWeb -Identity <Site URL> -Path <Export file name> [-Force] [-NoFileCompression] [-Verbose]
http://technet.microsoft.com/en-us/library/ee428322.aspx

However, as you may (or may not) be aware, the Import-SPWeb cmdlet does not successfully rebind any Workflows which may be associated with the source List or Site being exported.

For further information on what doesn't get included with the Built in SharePoint Import/Export see: http://www.sharepointjoel.com/Lists/Posts/ViewPost.aspx?ID=405

So, what to do?

Well, there are various posts and how-to's on how this can be achieved using SharePoint Designer, which essentially boils to down to creating a "blank/empty" workflow on the new/migrated list, then updating the ".xoml, .xoml.rules, .xoml.wfconfig.xml and .xsn" files based on the original workflow (replacing the list GUIDs with those of the new).

However, there are a couple of caveats to this approach. The key one being that this is only possible when dealing with Declarative No-Code Workflows available in SharePoint Designer, the other caveat being one of practicality (having to manually update large numbers of xml and rule files, if dealing with a large number of lists, may simply not be a practical option)

This particular support request was in relation to the OOTB "Three-state" SharePoint workflow (http://office.microsoft.com/en-us/windows-sharepoint-services-help/use-a-three-state-workflow-HA010154431.aspx) which is NOT a Declarative Workflow.

(you can confirm this by creating/assigning a new "Three-state" Workflow on a list and attempting to open the ".xoml, .xoml.rules, .xoml.wfconfig.xml and .xsn" files - they won't be visible / accessible via the "workflows" used by Declarative Workflows)

So, PowerShell to the rescue...

Again, there are various posts and how-to's detailing how to associate a SharePoint Workflow to a SharePoint List using PowerShell.

However, simply "associating" the Three-State Workflow to a list simply creates a new "blank/un-configured" instance of the Workflow on the target list.
It does not set any of the options available for the Workflow.

To fully migrate (or rather "replicate") a Three-State Workflow, it is necessary to get and set the information stored in the "SPWorkflowAssociation.AssociationData" property of the Workflow Association (http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.workflow.spworkflowassociation.associationdata.aspx). Using this approach, not only do you get a new Workflow associated with the migrated List, but also a replica of the Workflow options that were set on the Source Workflow.

I came up with the following "PowerShell" to achieve this:

MigrateWorkflowAssociation_ps1.zip

Usage:



Migrate / Move SharePoint List or SharePoint Site using Export-SPWeb and Import-SPWeb cmdlets

MigrateWorkflowAssociation.ps1 -SrcUrl -SrcList -TargetUrl 
-TargetList -workflowName -workflowAssociationName 
-workflowAssociationTasksList -workflowAssociationHistoryList -replaceExistingMatchingWorkflows

Example:



Export-SPWeb http://sourceweb/Team/ -Path C:\Temp\TeamSite.cmp

New-SPWeb http://targetweb/sites/SiteColl/Team/ 

Import-SPWeb http://targetweb/sites/SiteColl/Team/ -Path C:\Temp\TeamSite.cmp



MigrateWorkflowAssociation.ps1 -SrcUrl 'http://sourceweb/Team/' -SrcList 'Issues' -TargetUrl 'http://targetweb/sites/SiteColl/Team/'
 -TargetList 'Issues' -workflowName 'Three-state' 
-workflowAssociationName 'My Existing Workflow Display Name' 
-workflowAssociationTasksList 'Tasks' -workflowAssociationHistoryList 
'Workflow History' -replaceExistingMatchingWorkflows $true

PowerShell Script:


Param ([string]$SrcUrl, [string]$TargetUrl, [string]$SrcList, [string]$TargetList, [string]$workflowName, [string]$workflowAssociationName, [string]$workflowAssociationTasksList, [string]$workflowAssociationHistoryList, [bool]$replaceExistingMatchingWorkflows)
# Check for the site url input parameter
if(!$SrcUrl -or !$TargetUrl -or !$SrcList -or !$workflowName  -or !$workflowAssociationTasksList -or !$workflowAssociationHistoryList)
{
	Write-Host ""
	Write-Host -ForegroundColor Red "Please specify the following parameters:";
	Write-Host ""
	Write-Host -ForegroundColor Yellow "Parameters:"
	Write-Host ""
	Write-Host -ForegroundColor Yellow "-SrcUrl ['Url of source web containg existing list workflow']"
	Write-Host ""
	Write-Host -ForegroundColor Yellow "-TargetUrl ['Url of target web for new/migrated list workflow']"
	Write-Host ""
	Write-Host -ForegroundColor Yellow "-SrcList ['Name of the list to associated with existing workflow']"
	Write-Host ""
	Write-Host -ForegroundColor Yellow "-TargetList (optional) ['Name of  list the new/migrated workflow is to be to associated with - SrcList Name assumed if omitted']"
	Write-Host ""
	Write-Host -ForegroundColor Yellow "-workflowName ['Name of the workflow (template)']"
	Write-Host ""
	Write-Host -ForegroundColor Yellow "-workflowAssociationName (optional) ['Display Name of the workflow association - if omitted ALL workflows with matching -workflowName (workflow template) will be processed']"
	Write-Host ""
	Write-Host -ForegroundColor Yellow "-workflowAssociationTasksList ['Name of the Workflow Tasks list']"
	Write-Host ""
	Write-Host -ForegroundColor Yellow "-workflowAssociationHistoryList ['Name of the Workflow History list']"
	Write-Host ""
	Write-Host -ForegroundColor Yellow "-replaceExistingMatchingWorkflows (optional) ['Remove & Replace Existing Workflows on Target (Default: False)']"
	Write-Host ""
	
	Write-Host -ForegroundColor Yellow "Example:
	MigrateWorkflowAssociation.ps1 -SrcUrl 'http://sourceweb/' -SrcList 'Issues' -TargetUrl 'http://targetweb/' -TargetList 'Issues' -workflowName 'Three-state' -workflowAssociationName 'My Existing Workflow to be Transferred' -workflowAssociationTasksList 'Tasks' -workflowAssociationHistoryList 'Workflow History' -replaceExistingMatchingWorkflows $true
	"	
	Break
}
Write-Host ""
write-host "Parameters Received:"
write-host "-SrcUrl: "  -nonewline; write-host $SrcUrl
write-host "-SrcList: "  -nonewline; write-host $SrcList	
write-host "-TargetUrl: "  -nonewline; write-host $TargetUrl
write-host "-TargetList: "  -nonewline; write-host $TargetList
write-host "-workflowName: "  -nonewline; write-host $workflowName
write-host "-workflowAssociationName: "  -nonewline; write-host $workflowAssociationName
write-host "-workflowAssociationTasksList: "  -nonewline; write-host $workflowAssociationTasksList
write-host "-workflowAssociationHistoryList: "  -nonewline; write-host $workflowAssociationHistoryList
if(!$workflowAssociationName)
{
	write-host -ForegroundColor Yellow "No -workflowAssociationName Parameter Provided"
	write-host -ForegroundColor Yellow "All Workflows with matching -workflowName ($workflowName) will be processed"
}
write-host ""
write-host "Checking Source Web and Lists."
$srcSiteUrl = $SrcUrl
$targetSiteUrl = $TargetUrl
$site = Get-SPWeb $srcSiteUrl
$targetsite = Get-SPWeb $targetSiteUrl
[Guid]$crTemplateId = New-Object Guid
$srcListName = $SrcList
$targetListName = $TargetList
$wfTemplateName = $workflowName
$wfTaskListName = $workflowAssociationTasksList
$wfHistListName = $workflowAssociationHistoryList
$list = $site.Lists[$srcListName]
$wfTaskList = $site.Lists[$wfTaskListName]
$wfHistList = $site.Lists[$wfHistListName]
if (!$TargetList )
{
	$listTarget = $targetsite.Lists[$srcListName]
} 
else 
{
	$listTarget = $targetsite.Lists[$targetListName]
}
function Add-WorkflowAssociation($listTargetParam, $srcworkflowAssociationParam, $newworkflowAssociationParam ) {
	# Add the workflow association to the list
	$listTargetParam.WorkflowAssociations.Add($newworkflowAssociationParam);
	# Enable workflow  
	$newworkflowAssociationParam.Enabled = $srcworkflowAssociationParam.Enabled
	write-host -ForegroundColor Green ""
	write-host -ForegroundColor Green "WorkflowAssociation: '"$newworkflowAssociationParam.Name"' Added Successfully"
	write-host -ForegroundColor Green "To the list: " $listTargetParam.Title
	write-host -ForegroundColor Green "On the web: " $listTargetParam.ParentWeb.Title
	Write-Host -ForegroundColor Green "Url: " $listTargetParam.ParentWeb.Url
	write-host -ForegroundColor Green ""
}
function Get-WorkflowAssociation($workflowassociation) { 
	write-host ""
	write-host "Get-WorkflowAssociation "
	write-host "Web: "  -nonewline; write-host $_.ParentWeb.Url
	write-host "List: "  -nonewline; write-host $_.ParentList.Title;
	write-host "WorkflowTemplate: "  -nonewline; write-host $_.BaseTemplate.Name
	write-host "WorkflowName: "  -nonewline; write-host $_.Name
	#write-host "Soap Xml:"; write-host $_.SoapXml
	write-host ""
	if(!$workflowAssociationName)
	{
		CreateWorkflowAssociation $targetsite $listTarget $_.BaseTemplate.Name $_.Name $_
	}
	elseif($workflowAssociationName -eq $_.Name)
	{
		CreateWorkflowAssociation $targetsite $listTarget $_.BaseTemplate.Name $_.Name $_
	}
}
function CreateWorkflowAssociation($web, $listTarget, $workflowName, $srcworkflowAssociationName, $srcworkflowAssociation)
{
	write-host "CreateWorkflowAssociation Parameters"
	write-host "web :" $web
	write-host "listTarget :" $listTarget
	write-host "workflowName :" $workflowName
	write-host "srcworkflowAssociationName :" $srcworkflowAssociationName
	#write-host "srcworkflowAssociation :" $srcworkflowAssociation
	write-host ""
	write-host ""
	write-host "Creating Workflow Association" 
	$workflowTemplate=$web.workflowtemplates.gettemplatebyname($workflowName, [System.Globalization.CultureInfo]::CurrentCulture);
    
	if(!$workflowTemplate)
	{
	Write-Host -ForegroundColor Red "No workflow installed or activated with this workflowname : " $workflowName "on the web: "
	Write-Host -ForegroundColor Red "Name : " $web
	   Write-Host -ForegroundColor Red "Url  : " $web.Url
	}
	else
	{
            # Check if the site already has a workflow history list - if not, create it
            if(!$web.Lists[$wfHistListName])
            {
                $web.Lists.Add($wfHistListName, "A system library used to store workflow history information.", [Microsoft.SharePoint.SPListTemplateType]::WorkflowHistory);                
                $wfHistory = $web.Lists[$wfHistListName]
                $wfHistory.Hidden = $true
                $wfHistory.Update()
            }            
            
            #Check if the site already has a workflow tasks list - if not, create it
            if(!$web.Lists[$wfTaskListName])
            {
                $web.Lists.Add($wfTaskListName, "This system library used to store workflow tasks information.", [Microsoft.SharePoint.SPListTemplateType]::Tasks);                
                $wfTasks = $web.Lists[$wfTaskListName]
                $wfTasks.Hidden = $true
                $wfTasks.Update()
            }
            
            $wfHistory = $web.Lists[$wfHistListName]
            $wfTasks = $web.Lists[$wfTaskListName]
            
            #Create new workflow association
            $newworkflowAssociation = [Microsoft.SharePoint.Workflow.SPWorkflowAssociation]::CreateListAssociation($workflowTemplate, $srcworkflowAssociationName, $wfTasks, $wfhistory)
			
            #Set workflow AssociationData and config from source workflow association
            $newworkflowAssociation.AssociationData = $srcworkflowAssociation.AssociationData
            $newworkflowAssociation.AllowManual = $srcworkflowAssociation.AllowManual
            $newworkflowAssociation.AutoStartChange = $srcworkflowAssociation.AutoStartChange
            $newworkflowAssociation.AutoStartCreate = $srcworkflowAssociation.AutoStartCreate
            
            # Optional debug info
            #write-host "ParentWeb: "  -nonewline; write-host $newworkflowAssociation.ParentWeb.Url
            #write-host "HistoryList: " -nonewline;  write-host $newworkflowAssociation.HistoryListTitle
            #write-host "HistoryListID: " -nonewline;  write-host $newworkflowAssociation.HistoryListId
            #write-host "TaskList: " -nonewline;  write-host $newworkflowAssociation.TaskListTitle
            #write-host "TaskListID: " -nonewline;  write-host $newworkflowAssociation.TaskListId
			
            write-host "New Workflow Association Created Succesfully with Following Attributes:"
            write-host ""
            $newworkflowAssociation
            write-host ""
            write-host "Associating New Workflow Association With Target List"
            write-host ""
            [guid]$wfId = New-Object Guid
            [bool]$wfFound = $false
            # Optional step if you want to remove the default Page Approval work association from your list
            #foreach ($wf in $listTarget.WorkflowAssociations) {
            #    if($wf.Name -eq "Page Approval"){
            #        Write-Host -Foreground Yellow "Removing Page Approval work flow association from the target list";
            #        $listTarget.WorkflowAssociations.Remove($wf.Id);
            #    }             
            #}
            
            if(!$workflowAssociationName)
            {
		write-host "-workflowAssociationName not provided"
		write-host "Checking for matches using -workflowName: $workflowName (Workflow Template Name)"
	        write-host ""
		foreach ($wf in $listTarget.WorkflowAssociations) {
	        	if ($wf.Name -eq $newworkflowAssociation.Name) {
				$wfId = $wf.Id
	                	write-host -ForegroundColor Yellow "Workflow " $wf.Name " already exists on the target list:" $listTarget.Title
				write-host -ForegroundColor Yellow ""
	                	$wfFound = $true
	                }
            	}
					            
            	if ($wfFound -eq $true) {
			if($replaceExistingMatchingWorkflows)
			{
	               		write-host -ForegroundColor Yellow "-replaceExistingMatchingWorkflows true"
				# Remove exisiting workflow association
				$listTarget.WorkflowAssociations.Remove($wfId)
	        	        write-host -ForegroundColor Yellow "Removed workflow" $newworkflowAssociation.Name "from the list: " $listTarget.Title
	                	write-host -ForegroundColor Yellow "on the web:"$web
			        Write-Host -ForegroundColor Yellow "Url: " $web.Url
		       		Write-Host -ForegroundColor Yellow ""
	        	  	Add-WorkflowAssociation $listTarget $srcworkflowAssociation $newworkflowAssociation
			}
			else
			{
				write-host -ForegroundColor Yellow "Rerun MigrateWorkflowAssociation.ps1 using '-replaceExistingMatchingWorkflows true'" 
				write-host -ForegroundColor Yellow "or remove Workflow" $wf.Name "from target list:" $listTarget.Title  "manually" 
			}
            	}
            	else
            	{
			Add-WorkflowAssociation $listTarget $srcworkflowAssociation $newworkflowAssociation
            	}
            }
            else
            {
		write-host "-workflowAssociationName provided"
		write-host "Checking for matches using -workflowAssociationName: $workflowAssociationName"
	        write-host ""
		foreach ($wf in $listTarget.WorkflowAssociations) {
	                if ($wf.Name -eq $workflowAssociationName) {
	                    	$wfId = $wf.Id
	                    	write-host -ForegroundColor Yellow "Workflow " $wf.Name " already exists on the target list:" $listTarget.Title
	                    	$wfFound = $true
	                }
	        }
		
		if ($wfFound -eq $true) 
		{
			if($replaceExistingMatchingWorkflows)
			{
		        	#Remove exisiting workflow association
				$listTarget.WorkflowAssociations.Remove($wfId)
				write-host -ForegroundColor Yellow "Removed workflow" $newworkflowAssociation.Name "from the list: " $listTarget.Title 
				write-host -ForegroundColor Yellow "on the web:"
				Write-Host -ForegroundColor Yellow "Name : "$web
			        Write-Host -ForegroundColor Yellow "Url :  " $web.Url
		          	Add-WorkflowAssociation $listTarget $srcworkflowAssociation $newworkflowAssociation
						}
			else
			{
				write-host -ForegroundColor Yellow "Rerun MigrateWorkflowAssociation.ps1 using '-replaceExistingMatchingWorkflows true'"
				write-host -ForegroundColor Yellow "or remove Workflow" $wf.Name "from target list:" $listTarget.Title  "manually"
			}
		}
		else
		{
			Add-WorkflowAssociation $listTarget $srcworkflowAssociation $newworkflowAssociation
		}
            }
    }
}
write-host "Preparing to associate workflow with list."
#
if ($list -ne $null)
{
	write-host "List: "  -nonewline;  $list.Title
	$wfTemplate=$site.workflowtemplates.gettemplatebyname($wfTemplateName, [System.Globalization.CultureInfo]::CurrentCulture);
    
	if(!$wfTemplate)
	{
		Write-Host -ForegroundColor Red "No workflow installed or activated with this workflowname : " $workflowName "on the web: "
		Write-Host -ForegroundColor Red "Name : " $site
		Write-Host -ForegroundColor Red "Url  : " $site.Url
	}
	else
	{
		if ($wfTemplate.Name -eq $wfTemplateName) 
		{
			write-host "Template Name: "  -nonewline;  $wfTemplate.Name
			$wfTemplateId = $wfTemplate.Id
			write-host "Template ID: "  -nonewline; write-host $wfTemplateId
			
		}
		write-host "WorkflowAssociations: "  -nonewline;  $list.WorkflowAssociations.Count
		$list.WorkflowAssociations | foreach { Get-WorkflowAssociation($_) }
	}
	
}
else
{
	write-host "ERROR: Could not find source list, association will have to be made manually." -ForegroundColor red
}
#
$site.Dispose()
$targetsite.Dispose()

SharePoint Saturday UK 2011 Rundown...

aobi — Sun, 13 Nov 2011 18:15:00 GMT

Well SharePoint Saturday UK (#spsuk) has been and gone for another year...

For anyone who's not aware, it's

"an educational, informative & lively day filled with sessions from respected SharePoint professionals & MVPs, covering a wide variety of SharePoint-orientated topics. SharePoint Saturday is FREE, open to the public and is your local chance to immerse yourself in SharePoint!"

This year's event was held at Nottingham Conferences University Park and was attended by 500+ avid SharePointers.

The day kicked off with a great Keynote from Todd Klindt (@ToddKlindt) entitled "Making Nerds Better People" in which Todd attempted to encourage people to "step out of their comfort zone and/or their area of personal expertise". Touching on his own personal experiences of taking the SharePoint Dev exams, whilst primarily being an SP Admin/IT Pro (he did also admit that the x2 main reasons for even attempting the exams were ‘Knowledge' and ‘Spite'). When asked how many people in the room had currently taken (and passed) all x4 SP exams, very few hands went up. Which probably only goes to prove he's got a valid point... we can all learn a bit more about the areas we already focus on, simply by learning about the areas that we don't.

http://toddklindt.com/Slides/spsuk2011/Keynote - Making Nerds better People.pdf

My first session of the day was "Putting you at the centre of the Social Intranet" presented by Wes Hackett (@weshackett), in which he covered the various challenges and approaches of successfully implementing a truly "Social Intranet" using SharePoint. A few of the highlights were ‘making a big social change - centre on collaboration not publishing', ‘attempt to make social spaces primary places of work' and a particular favourite "Location Based Subscriptions" - a custom extension to the in-built SP alerts.

http://weshackett.com/2011/11/sharepoint-saturday-2011/

twitter: #spsuk16

Next session was "Deploying SharePoint 2010 Globally - The options" by Paul Grimely (@paulgrimley), in which he covered the various supported architecture options for deploying SharePoint 2010 across geographically dispersed locations (single farm, multi-farm or 3^rd party replication). As well has the vast amount of considerations that need to be painstakingly thought-out, planned and tested (from Business Requirements and Budgets to careful planning of ‘SP Variations' for Multilanguage deployments)

http://www.paulgrimley.com/2011/11/deploying-sharepoint-globally-slides.html

twitter: #spsuk07

Next up was "How we did it - Combined Knowledge SharePoint Design" presented by Matt Hughes (@mattmoo2), in which Matt not only showed us an "under the hood demo" of the new www.internationalsharepointconference.com website (previously known as the "European SharePoint Best Practices Conference"), but also presented the entire session sporting "full face paint". He also made the recommendation (amongst others) of avoiding the use of "!important" (or bang important) in CSS, whenever attempting to brand SharePoint ...although personally I think the practical do's and don'ts of this in real world scenarios is yet another thing the SP camp could discuss at length (and could even add to the "SharePoint > To Brand or Not to Brand ?" discussion)

http://sp365.co.uk/2011/11/sharepoint-saturday-how-we-did-it-the-combined-knowledge-sharepoint-design-spsuk13/

twitter: #spsuk13

I followed this up with Mark Miller's (@EUSP) "Something Awesome" (aka ‘To Host or Not to Host - the Good, the Bad and the Ugly Decisions') session, which turned out to be one of the most informative sessions of the day. Mark initially covered the pros and cons of ‘On-Premise' vs. ‘Cloud'. Highlighting such issues as ‘On Premise: gives full control but at what cost?' usually has significant on-going infrastructure, monitoring and management overheads. Whereas ‘Cloud' gives less control (no farm solutions - sandbox solutions only, no ‘Central Admin' access - ‘Tenant Admin' access only) but is potentially far more cost effective for SMEs (However, annual subscription costs can quickly spiral for larger enterprise deployments). The session then went on to discuss other potential issues which should also be considered such as the ‘US Patriot Act' (anyone considering Office365 and/or Cloud solutions should seriously look into the implications of this before signing up to anything), using ‘generic cloud providers' who provide reliable infrastructure with %99.9 uptime, but are often not experts in supporting SharePoint. He then continued with the conclusion that in reality the majority of ‘Cloud' solutions over the coming years, will in fact be a combination of both of ‘On-Premise' and ‘Cloud' or "Hybrid solutions".

twitter: #spsuk34

My final session of the day was Todd Klindt's (@ToddKlindt) "PowerShell", which again was another fantastic session from Todd. Even though some may argue that it was less complicated when compared to some other SharePoint PowerShell sessions (such as Gary Lapointe's ‘PowerShell Best Practices for SharePoint Administrators' at last year's "Best Practices conference"), it was still full of various useful tips: such as use ‘Start-Transcript', try to avoid using aliases and always ensure you understand the difference between "evaluation" and "assignment" before running any script (Todd gave an example of how he'd learnt this the ‘hard way' by once successfully re-naming all of his SP databases to "SP_Config")

http://toddklindt.com/Slides/spsuk2011/PowerShell.pdf

http://toddklindt.com/Slides/spsuk2011/PowerShell_transcript.20111112080916.txt

twitter: #spsuk10

The day ended with a Closing Keynote "SharePoint and the Cloud: Crash or Convergence?" from Steve Fox. In which he ran through various demos (at the audience's request) on using Azure with both SharePoint as well as the new ‘Power View' which will ship with SQL Server 2012 aka ('Denali') (His "no code" demo of combining an Azure data source with Excel and publishing it via Excel Services was particularly impressive).

From what I understand there were numerous other "highlight sessions" that ran throughout the day which I wasn't able to attend, as ran simultaneously to those above (Becky Isserman's "HTML 5 vs SilverLight 5" #spsuk01, Eric Schupps' "Customizing the SharePoint Packaging and Deployment Process in Visual Studio 2010" #spsuk02, Chris O'Brien's "Custom Ribbon Development" #spsuk03, Ant Clay and Andrew Woodward's "SharePoint ‘Innovation Games' Workshop" #spsuk38 to name but a few).

All in all another great SharePoint Saturday!

Photos of the day's events are available "here"

SharePoint 2010 - Edit Document via Citrix XenApp

aobi — Tue, 25 Oct 2011 00:30:00 GMT

Whilst recently performing a new SharePoint 2010 deployment for a client, I was presented with the following problem. The client was using Citrix XenApp to deploy and manage Office 2010 on their users' desktops and as such was receiving the following error message

"The document could not be opened for editing. A Microsoft SharePoint Foundation compatible application could not be found to edit the document."

...when attempting to use the "Edit in {office app}" from the document menu.

Now as you may (or may not) already be aware, by default SharePoint is configured to use the ‘owssupp.dll' ActiveX control to open Office documents in "edit mode", which is installed as part of the "SharePoint Support features" of the client's Office installation. (Further info on the subject see: http://bradleychetty.blogspot.com/2011/08/microsoft-sharepoint-foundation.html )

Now as the client was running Office via Citrix, there was no local installation of Office on the desktop machines and therefore no ‘owssupp.dll'.

The obvious solution to the problem was to simply run the "IE browser session" via Citrix as well, which does work as long as the "webclient service" is running on the Citrix server (see http://support.citrix.com/article/CTX125214).

However, the client didn't want to use Citrix for all web and internet traffic and didn't really want to give their users two browser links on the desktops (ie. one to browse SharePoint via Citrix and another local one for all other web/internet access).

So, what to do?

Well, SharePoint uses the DOCICON.XML file to control which documents are associated with a particular application (More information on how this works can be found at "Understanding DocIcon.xml files"), but basically the "OpenControl" attribute is used to specify the name of the ActiveX control used to open the particular document type (see http://msdn.microsoft.com/en-us/library/aa979488.aspx).

So one quick Google search later, and I found a useful article from "Manpreet's SharePoint Developer Blog", which covered the basics of "writing a custom SharePoint.OpenDocuments" control (and even includes source code for his ‘Malag.OpenDocuments control' as a starter for ten!).

It was at this point that the client pointed out they also had some "remote users" (connected over vpn) who didn't have access to Citrix, but DID have a local copy of Office installed on their Laptops and would need the same ability to "Edit Office Documents" directly from SharePoint...

Now as far as I'm aware, the DOCICON.XML file only permits a "one-to-one" association per file type, so using a "custom OpenDocuments" control (to open files via Citrix) wasn't going to be an option as the Citrix PNAgent wouldn't be available on the laptops of the remote users.

It seemed there was a requirement for two "Edit in {office app}" menu items to be available via the SharePoint user interface. One to open/edit the file via Citrix and another to use local installation of Office (ie. the default SharePoint "Edit" action)

So I came up with the following "sandbox solution" to achieve this:

1. Create a "custom menu item"

See the following for further info:

http://www.thespgeek.com/2011/01/how-to-add-custom-menu-item-in.html

http://msdn.microsoft.com/en-us/library/ms473643.aspx

http://msdn.microsoft.com/en-us/library/ms460194.aspx

I set the "Scope" of my feature to "Site", so that it could be activated on a "per site-collection" basis, but obviously if you'd prefer it be controlled on a "per site" basis you could set your scope to be "Web".

2. In the Elements.xml file,

..include a reference to a "CustomAction ScriptBlock", which includes the following code:


<CustomAction

   
Location="ScriptLink"

   
ScriptBlock='function doOpenWithCitrix(paramval, OfficeApp){ 

   
openCitrixOfficApp(getUNCPath(paramval), OfficeApp);}

   
function openCitrixOfficApp(strFileapth, strOfficeAppName){

   
var oShell = new ActiveXObject("Shell.Application");

   
var citrixCmd = "C:\\Program Files\\Citrix\\ICA Client\\pnagent.exe";

  
/*Note - The Path to your pnagent.exe may need to be adjusted */

   
var commandParms = " /QLaunch \""+ strOfficeAppName
+"\" /param:\"" + strFileapth +"\" ";

   
oShell.ShellExecute(citrixCmd, commandParms, "",
"open", "1");}

   
function getUNCPath(url){           


   
var returnVal;

   
returnVal = decodeURI(url);

   
returnVal = returnVal.replace(new RegExp("/", "g"),
"\\");

   
returnVal = returnVal.replace(new RegExp("http:",
"g"), "");

   
returnVal = "\\\\" + location.hostname + returnVal;

   
return returnVal;}'

   
Sequence="101">

</CustomAction>

This JavaScript attempts to run the Citrix PNAgent.exe, along with the path to the Office document stored in SharePoint as a parameter.

For some reason Citrix didn't seem to like "http://" paths, so I have included a ‘getUNCPath' function which attempts to convert the URL of the document item to a UNC path.

(Again, please note that Citrix requires the "webclient service" to be running on the Citrix server so that it is able access the files via UNC paths - or rather via webDAV)

It is possible include the script in a ".js file" if you prefer (see http://blog.voyta.net/2010/09/12/referencing-javascript-files-using-customaction-in-sharepoint-2010-sandboxed-solutions/), but it's not necessary for the purposes of this example.

3. Include the following "Custom Action"(s)


<CustomAction

   
Id="CitrixOffice_Word2010"

   
RegistrationType="FileType"

   
RegistrationId="doc"

   
Rights="EditListItems"


   
ImageUrl="/_layouts/images/icdoc.png"

   
Location="EditControlBlock"

   
Sequence="252"

   
Title="Edit in Microsoft Word 2010 (via Citrix)" >

   
<UrlAction Url="javascript : doOpenWithCitrix('{ItemUrl}', 'Word
2010');"/>

 
</CustomAction>

 
<CustomAction

   
Id="CitrixOffice_Word2010"

   
RegistrationType="FileType"

   
RegistrationId="docx"

   
Rights="EditListItems" 


   
ImageUrl="/_layouts/images/icdoc.png"

   
Location="EditControlBlock"

   
Sequence="252"

   
Title="Edit in Microsoft Word 2010 (via Citrix)" >

   
<UrlAction Url="javascript : doOpenWithCitrix('{ItemUrl}', 'Word
2010');"/>

 
</CustomAction>

 

 
<CustomAction

   
Id="CitrixOffice_Excel2010"

   
RegistrationType="FileType"

   
RegistrationId="xls"

   
Rights="EditListItems" 


   
ImageUrl="/_layouts/images/icxls.png"

   
Location="EditControlBlock"

   
Sequence="252"

   
Title="Edit in Microsoft Excel 2010 (via Citrix)" >

   
<UrlAction Url="javascript : doOpenWithCitrix('{ItemUrl}', 'Excel
2010');"/>

 
</CustomAction>

 
<CustomAction

   
Id="CitrixOffice_Excel2010"

   
RegistrationType="FileType"

   
RegistrationId="xlsx"

   
Rights="EditListItems" 


   
ImageUrl="/_layouts/images/icxls.png"

   
Location="EditControlBlock"

   
Sequence="252"

   
Title="Edit in Microsoft Excel (via Citrix)" >

   
<UrlAction Url="javascript : doOpenWithCitrix('{ItemUrl}', 'Excel
2010');"/>

 
</CustomAction>

  

 
<CustomAction

   
Id="CitrixOffice_Ppoint2010"

    RegistrationType="FileType"

   
RegistrationId="ppt"

   
Rights="EditListItems" 


   
ImageUrl="/_layouts/images/icppt.png"

   
Location="EditControlBlock"

   
Sequence="252"

   
Title="Edit in Microsoft PowerPoint 2010 (via Citrix)" >

   
<UrlAction Url="javascript : doOpenWithCitrix('{ItemUrl}',
'PowerPoint 2010');"/>

 
</CustomAction>

 
<CustomAction

   
Id="CitrixOffice_Ppoint2010"

   
RegistrationType="FileType"

   
RegistrationId="pptx"

   
Rights="EditListItems" 


   
ImageUrl="/_layouts/images/icppt.pngg"

   
Location="EditControlBlock"

   
Sequence="252"

    Title="Edit
in Microsoft PowerPoint 2010 (via Citrix)" >

   
<UrlAction Url="javascript : doOpenWithCitrix('{ItemUrl}',
'PowerPoint 2010');"/>

 
</CustomAction>

Each of these, creates a new Menu Item (with the call to the custom JavaScript ) based on the associated FileType/FileExtension.

Note: the second parameter passed to the "doOpenWithCitrix" function needs to be the Application Name that is published in Citrix.

In this particular example, I have simply specified "'Word 2010', ‘Excel 2010' or ‘PowerPoint 2010'", but obviously these may vary depending on your Citrix setup.

You should then be able to publish your feature as a Sandbox Solution and see the following additional menu item

The final step that is required to get this working is to "Enable" the "Initialize and script ActiveX controls not marked as safe"in the Internet Explorer Security Options.

(unfortunately the "new ActiveXObject("Shell.Application");" javascript call requires this setting to be enabled to function correctly).

NOTE: To prevent creating a security risk I would ONLY RECOMMEND changing this security option for "Trusted Sites" (which obviously then needs to be controlled and/or restricted).

The source code and Visual Studio 2010 project is attached along with a complied ".wsp" file:

EditViaCitrixOffice_MenuItem.zip

Obilogic RSS Reader 2010 Now Available - Free Download

aobi — Sun, 17 Apr 2011 21:52:00 GMT

"RSS Reader" Web Part for SharePoint 2010 is now available to Download ...

Demo available at:
http://wssv3.obilogic.co.uk/ObilogicWebParts/RSSReader/

RSS Reader 2010 is also available for download from Codeplex:
http://obilogicrssreader.codeplex.com/