SharePoint Magazine - Part 4 available now

My next article for SharePoint Magazine is available now.

Dissecting Dashboards (Part 4 of 6)

Sharepoint Saturday

Register now for Sharepoint Saturday in Sydney on August 8th.

http://www.sharepointsaturday.org/sydney/default.aspx

No more PPS Planning...

The news, as I'm sure everyone has now heard, is that PerformancePoint Planning is being retired. See the official press release at:

http://www.microsoft.com/presspass/features/2009/jan09/01-27KurtDelbeneQA.mspx

PerformancePoint Monitoring will be bundled into SharePoint as part of Office 14 - and be known as PerformancePoint Services for SharePoint. Wacko.

SQL 2005 SP3 has landed

SP3 for SQL 2005 is now available for download.

Included are a number of Bug Fixes , performance enhancements for using SSRS in SharePoint Integration mode, improved rendering of PDF fonts in SSRS, support for SQL 2008 Dbs with SQL 2005 Notification Services and support for Teradata Dbs as a data source for Report Models.

This Service Pack is cumulative and can be run on any version of SQL 2005.

Using SAP as a Data Source

I was recently asked a question which I thought might be of value to everyone.

Can you please let me know...
1. Is share Point and Performance Point can be used in SAP BI or is it strictly Integrated with MircoSoft Technologies!!

So my response was...

I’m assuming you mean “can you use SAP as a data source?”

Firstly, SharePoint isn’t a BI technology as such. It’s a collaboration, portal & document management tool. However it does have the ability to create lists and simple KPIs which you could use SAP data as a source for if you wanted to.

That said, I’m sure your SAP operations team would never allow you to link directly to SAP – you would need to extract into a database first.

Secondly, you can certainly use SAP as a data source for both PerformancePoint modules - Planning and Monitoring & Analyzing. However you would never link directly to the system from PerformancePoint. You would almost always extract from your transactional system into a datawarehouse and that would be your data source (there may be exceptions from time to time but generally it's is wise to separate your reporting engine and your transactional engine. There are numerous gains in doing so - security, performance, aggregation, modelling).

The other key consideration is licensing. SAP is pretty strict about licensing when it comes to extracting data so you would need to take that into consideration also.

Hope that answers your question!

SharePoint Magazine - Part 2 available now

My next article for SharePoint Magazine is available now.

http://sharepointmagazine.net/technical/administration/sell-sell-sell-why-build-a-dashboard-anyway-part-2-of-6

SharePoint Magazine - Part 1 Out Now!

I've commenced a 6-part series on building PerformancePoint dashboards in SharePoint which you can read at SharePoint Magazine.

I'll be releasing a new part each week for the next 6 weeks covering the various aspects of building dashboards - including why bother at all!

Have a read if you're interested. Feedback so far has been excellent.

Sharepoint 2007 (MOSS) – List “My” Sites

So you want to have a web part which lists the sites which "I" have access to… easier said than done.

Below is my code for doing this… except it doesn't work. Well, it works for me because I'm an administrator but not for other users. It iterates through all site collections in the farm and and does GetSubWebsforCurrentUser. Which is fine, because I have permissions to read the properties of all site collections – a normal user of course, does not!

I tried using elevated authority and all sorts of tricks but so far haven't been able to finagle it. I do have one idea… that is to drop all the info I need into a string array using elevated authority and then do GetSubWebsforCurrentUser on site collections where no errors are raised for the authenticated user. I haven't had a spare second to try that out yet but I will…

So for the benefit of others – and hopefully someone else will have a brainwave in the meantime – here is the errant code in question…

using System;
using System.Text;
using System.Runtime.InteropServices;
using System.Web.UI;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Serialization;
using System.Web;

using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint.WebPartPages;
using Microsoft.SharePoint.Utilities;
using Microsoft.SharePoint.Administration;
using Microsoft.SharePoint.Publishing;

namespace CSR.Sharepoint.WebParts

{

[Guid("0d546513-7447-4211-8fc9-75117af712b0")]

public class MyCollaborationSites : System.Web.UI.WebControls.WebParts.WebPart

{

public MyCollaborationSites()
{
this.ExportMode = WebPartExportMode.All;
}

protected override void Render(HtmlTextWriter writer)
{
SPWebApplication oWebApp = SPContext.Current.Site.WebApplication;
SPSiteCollection oSites = oWebApp.Sites;

foreach (SPSite site in oSites) {
SPWeb oWeb = site.RootWeb;
int ilevel = 1;
if (!PublishingSite.IsPublishingSite(site))//don't include portal
{
GetSubSites(ilevel, oWeb, writer);
};
};

}

private void GetSubSites(int ilevel, SPWeb oWeb, HtmlTextWriter writer)
{
Boolean alternate = false;
foreach (SPWeb oSPWeb in oWeb.GetSubwebsForCurrentUser())
{

if (ilevel != 1)
{
if (alternate == true)
{
writer.Write("<tr><td class=csr-RowGrey><a href='" + SPEncode.HtmlEncode(oSPWeb.Url) + "' target='" + SPEncode.HtmlEncode(oSPWeb.Title.Replace(" ", "")) + "'>" + SPEncode.HtmlEncode(oSPWeb.Title) + "</a></td></td>");
}
else
{
writer.Write("<tr><td class=csr-Rowwhite><a href='" + SPEncode.HtmlEncode(oSPWeb.Url) + "' target='" + SPEncode.HtmlEncode(oSPWeb.Title.Replace(" ", "")) + "'>" + SPEncode.HtmlEncode(oSPWeb.Title) + "</a></td></td>");
alternate = true;
}
}

if (oSPWeb.GetSubwebsForCurrentUser().Count > 0)
{
ilevel = ilevel + 1;
GetSubSites(ilevel, oSPWeb, writer);
}
}
}
}
}

Sharepoint 2007 (MOSS) - Unpublishing/Expiring a Site

It's not immediately obvious how to expire a site… particularly for used to SPS 2003. In 2003, you could just set the end date to sometime in the past or if you really wanted to, the start date to some time in the far off future. Unfortunately with MOSS it's a little more complicated.

You'd think that the "Unpublish" option on the Workflow menu of the Page Editing Toolbar would do the trick but sadly, no! What it does do is roll the page back to it's previous version. Definitely handy. But what if you want the page (which has several versions) to be entirely invisible to users? In theory, you could delete it… but I ask you, how many of your content managers will want to delete page just because it's expired now? None I'd wager!

So what's the answer? Page Scheduling (also referred to as Publication Dates). You set a start and end date for publication and the page is only visible during that period. The period may be from "Immediately" to "Never" so you don't have to think about it to much. Easy huh? Yeah, well….

Every site CAN have publication dates however not every site DOES have them. It has to be enabled. To enable Page Scheduling do the following:

• Go to the site in question
  
• Go to Site Actions > Site Settings > Modify Pages Library Settings


• From Versioning Settings, ensure Versioning and Content Approval are both enabled
• From Manage Item Scheduling, ensure Enable Scheduling is ticked
  

Unfortunately no, it does not apply to subsites of the current site. For the administrators who want to know why - because what you are effectively doing is amending the properties of the Pages list in the site content for that site.

So still you ask, how do I expire the page???? Ok…

1. Show the Page Editing toolbar
2. In the status section of the toolbar, it will have the version, the status and the Publication date. Click on the date (it's a hyperlink)
   
3. The page scheduling options will open
4. Set the end date to the desired date/time
5. Hit OK and from the Page Editing Toolbar hit Publish
   

Again, seemingly simple…. unless you're changing pages migrated from SPS 2003. Mostly I've found the Start and end dates are blank. You can't set the start date or the end date to some time in the past - you will get an error message telling you so. It's a slight irritation sure but is it really necessary? What's the big deal already… just let me set the date to a point in the past! So you can set the start date to 'Immediately' and the End date to 5 minutes from now and once you hit Publish and those 5 minutes have past, the page will magically no longer be visible.

Sharepoint MOSS 2007 - MySite Default Locale

The default locale setting in the MySite template is English-US. To amend this, the simplest approach is:

1. Navigate to http///MySite/_layouts/regionalsetng.aspx *
2. Update the Locale as desired and hit OK
   
   * this assumes your MySite host is at /MySite so adjust as necessary
   

As to sites which have already been created, this won't fix them. The bottom line is that there is NO supported global fix according to Microsoft as it is by design.

As a bodgy workaround you could update every site in the webs table of your _Content database (and limit it to where fullurl like '%personal%'. Simply do an update on the Locale column setting it to the Id of the locale you want. Clearly this is NOT a recommended workaround and may well break any support agreements you have with Microsoft.

Sharepoint MOSS 2007 - Working with the BDC (Business Data Catalog)

Having just started working with the BDC, here's what I've encountered so far.

Using BDCMetaMan

On advice from others I started using this tool however I found it to be a tad unwieldy in that I couldn't work out how to do everything I wanted – for example, use a Stored Proc as my source.

So I started with BDC MetaMan, took the output it gave me and then just screwed around with it to make it work with stored procs. In doing so, I found that writing application definitions is nowhere as complicated as others had lead me to believe. That said, it does make for an easy start point where I could just replace values and avoid the tedium of building up all the XML tags in the right order and syntax and so on…

Application Definition

The basic structure is this:

How to use SQL Authentication

Thanks to Nick Kellet for doing the hard slog and solving this problem….http://planetmoss.blogspot.com/2006/11/using-single-signon-with-database.html

The basic steps are:

1. Enable the Microsoft Single-sign-on service using a domain account (in windows Services)
   


2. Create an SSO Application
   Central Admin > Operations > Security Configuration > Manage Single Sign-on > Manage Server Settings
   Enter Display Name, Application Name (note: this field is what you will enter into your application definition in the LOB instance properties) and Email Address. They don't have to be anything in particular. Ensure the Account Type is Group.
   



3. Associate the SSO Application with the domain users group or some other desired group
   Central Admin > Operations > Security Configuration > Manage Single Sign-on > Manage Account Information. Select the SSO application and type in the "<domain>\domain users" group. Then key in the SQL username and password. Give the application SELECT and EXECUTE rights (assuming you are using Stored Procedures).
   



4. Configure your connection string in the application definition
   <Properties>
   <Property
   Name="AuthenticationMode"
   Type="System.String">RdbCredentials</Property>
   <Property
   Name="DatabaseAccessProvider"
   Type="System.String">SqlServer</Property>
   <Property
   Name="RdbConnection Data Source"
   Type="System.String">SERVER NAME</Property>
   <Property
   Name="RdbConnection Initial Catalog"
   Type="System.String">DATABASE NAME</Property>
   <Property
   Name="RdbConnection Integrated Security"
   Type="System.String">false</Property>
   <Property
   Name="RdbConnection Pooling"
   Type="System.String">true</Property>
   <Property
   Name="SsoApplicationId"
   Type="System.String">SSO APPLICATION NAME</Property>
   <Property
   Name="SsoProviderImplementation"
   Type="System.String">Microsoft.SharePoint.Portal.SingleSignon.SpsSsoProvider, Microsoft.SharePoint.Portal.SingleSignon, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c</Property>
   </Properties>
   



5. Import the application definition
   Central Admin > SSP > Business Data Catalog > Import Application Definition
   



6. Configure permissions for the application definition
   Central Admin > SSP > Business Data Catalog > Permissions > Copy all to descendants (or apply the entity you want to set if not inheritable)
   



7. Add a BDC List web part to a page, configure it and fingers crossed, it will work!
   

One BIG caveat… getting this to work across multiple domains is going to be a problem. For now, I’m ignoring that issue and hoping it doesn’t rear it’s VERY ugly head…

How to use Windows Authentication

Haven’t tried it so I don’t know. But… if you are having problems my first suggestion would be to consider the Kerberos double-hop issue. If you don’t know what that is, google it… it’s a reasonably simple fix, if you are friendly with your AD administrator(s)!

Detailed Application Definition

It’s fairly self-explanatory. The bits in green are the bits you have to fill in.

Notes:
· The available data types are your standard .Net data types.
· Refer http://msdn2.microsoft.com/en-us/library/ms546541.aspx for a BDC overviewBelow is my first pass attempt so caveat emptor… it ain’t perfect folks! Feel free to add your comments with anything you can see that can be done better or anything that I’ve got wrong!

What’s Next - Got any ideas?

So next, I’ve been charged to come up with a BDC-based list where some manual info is also stored – bodgy explanation but allow me to demonstrate.

BDC list contains 20 columns. To this list, we want to add some manually entered data. The list will contain around 2000 records. The BDC data will be current and the data editor will update the manual fields when time permits. The web part should output like:

RequestNo Title DueDate Contact Type Owner OutageRequired OutageNotification

Where OutageRequired and OutageNotification are sourced from manual data and the rest are from the BDC.

So my initial thought was to have a BDC lookup in my manual list where the RequestNo, OutageRequired and OutageNotification are entered. I really hate this idea. It’s ugly and impractical. It means that the user has to know the Request number upfront rather than being able to immediately see which request numbers haven’t been manually updated because the 2 fields are empty . Haven’t had a second to think it through properly yet but if anyone has a better idea please speak up!

Sharepoint 2007 (MOSS) Memory Errors – EventID 6482/6398/7076 - Attempted to read or write protected memory

Sharepoint 2007 (MOSS) Memory Errors – EventID 6482/6398/7076

If you are seeing the following error in your event log: "Attempted to read or write protected memory. This is often an indication that other memory is corrupt"….

…Microsoft do have a Hotfix for this problem – however you will have to contact Microsoft Premier Support to get a copy. Unfortunately if you do not have Support, I can't give you a copy of our hotfix as it is password protected and the password is only valid for 7 days!

The patch in question is a .Net 2.0 hotfix titled 303311_intl_x64_zip.exe for 64bit servers and 303312_intl_i386_zip.exe for 32bit.

BSM 2005 & MOSS x64 don’t play well together….

Here's what you have….

Machine1: Windows 2003 x64 with MOSS 2007 x64
Machine2: Windows 2003 x32 with BSM 2005

Here's where you want to end up:

Machine1: Windows 2003 x64 with MOSS 2007 x64 and BSM 2005 WebPart (only)
Machine2: Windows 2003 x32 with BSM 2005

The article How to switch between the 32-bit versions of ASP.NET 1.1 and the 64-bit version of ASP.NET 2.0 on a 64-bit version of Windows implies that BSM 2005 can't be used in a x64 MOSS environment because the BSM webparts require ASP.Net 1.1 which is 32bit only and IIS can't have BOTH 32bit and 64bit websites running at the same time.

This is shown when attempting to install the BSM web parts on the MOSS server.

1. On the MOSS server, run the server.msi from the BSM compatibility pack
   
2. Select a custom installation > Web Part components only
   
3. It fails because .NetFramework 1.1 is not installed
   
4. Install .NetFramework 1.1 on the MOSS Server
   
5. Execute cscript %SYSTEMDRIVE%\inetpub\adminscripts\adsutil.vbs SET W3SVC/AppPools/Enable32bitAppOnWin64 1
   
6. .Net 1.1 Web Service Extension is set to allowed.
   

Outcomes:

• MOSS site doesn't work - error is "Service Unavailable"
• Compatibility pack (web parts only) still fails because WSS 2.0 (or SPS 2003) is not installed

Question

Can BSM web parts be installed in a MOSS x64 environment? If so, how??

This is the question I have posed to Microsoft Premier Support, it looks like the answer is no but I want confirmation from MS before giving up!

**************** UPDATE FROM MICROSOFT ****************

From: Biao Zhang Sent: 2007年9月13日 14:04To: 'Hodges, Kristen'Subject: RE: SRZ070912000200:BSM2005: Can BSM web parts be installed in a MOSS x64 environment? If so, how??Importance: High

Dear Kristen,

I understand this question is very urgent and I have made some research, here is the update:

1.Business Scorecard Manager Server 2005 does not have 64-bit version and officially, Business Scorecard Manager Server 2005 (including web parts) does not support Windows 64-bit platform.

Sharepoint 2007 (MOSS) Upgrade - TempDb issues

In the middle of a Sharepoint 2007 (MOSS) upgrade....

One key piece of advice I can give - allow TempDb to have PLENTY of space when doing your initial full crawl.... in fact you may find that the % increase approach may not work during this process because it can't get enough space to fulfil it's very hungry needs! A Mb increase approach should work fine.

You may see errors such as "The transaction log for database 'tempdb' is full."

During later incremental crawls it shouldn't need any special handling, this just seems to apply to the intial full crawl.