Content Database Growth on DB Attach Migration from WSS3 to SharePoint Foundation 2010

Introduction

I’ve done a lot of SharePoint Services 3 to Foundation 2010 migrations now and one thing I’ve learnt is that they all have to be treated individually on their merits.  A recent one was a move from WSS 3 on SBS 2003 using SQL 2005 Embedded Edition to a SharePoint Foundation as part of SBS 2011 Standard Edition using SQL 2008R2 Express Edition.

The WSS 3 had a single site collection in a single content database.  I was a little concerned that this database was 7.2GB and I’d have a 10GB limit on SQL Express that is on the Foundation Server.  However I thought I had plenty of spare space for this to work.

I did the usual steps to complete the Farm configuration on the new SBS box and applied WSS Service Pack 2 to the old SBS 2003 box.  The Pre Upgrade Check in STSADM gives you a special warning to let you know that you have a content database that is over 4GB and you might have to do something when you migrate.  Your options are along the lines of:

 

1. Deploy SQL Workgroup or above and use that for the content database
2. Remove content from the database before upgrading
3. Use a time machine to return to point of installation and change the design to use multiple site collections in separate databases
4.  Hope that it will fit following the upgrade

 Option 4 may be an attractive one but what if we use a SQL Standard in a test machine to trial the upgrade before we attempt to attach to the live SBS 2011 Server.

 Trial Upgrade

I restored the database to a test machine running SharePoint Foundation 2010 using a SQL Standard and used STSADM –o addcontentdatabase.  I didn’t pre-allocate any space to the database or the log so that I could see the files grow.  The database was set to Simple Logging

The STSADM starts counting its percentages and then stopped at 19.69% going no further.  A check of the upgrade.log file shows that it ends with:

[STSADM] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [5/14/2012 10:13:33 PM]: Populating AllDocStreams Content Column
[STSADM] [SPContentDatabaseSequence] [DEBUG] [5/14/2012 10:13:33 PM]: Executing SQL DDL Script.

SQL is still busy, so I use SQL Activity Monitor to see what it’s doing.   I can see one process of interest on my Content Database that is executing the following:

            INSERT INTO AllDocStreams (
                Id,
                SiteId,
                InternalVersion,
                Content,
                Size,
                RbsId)
            SELECT
                ADV.Id,
                ADV.SiteId,
                ADV.InternalVersion,
        ADV.Content,
              ADV.Size,
                ADV.RbsId
            FROM
                AllDocVersions as ADV
            WITH
                (TABLOCK)

Meanwhile the Log file and the database file both start growing and keep growing until the log file is 5.3GB and the database is 11.7GB.  This happens as the result of a single SQL transaction so, even on simple logging, the transaction log will grow excessively.  You can of course shrink the log afterwards.  If you try this on the live site with a 10GB limit the upgrade will fail on allocating space when the database file gets to 10GB.

Conclusion

In this case the customer archived old data out of the content database and we disabled the Recycle Bin to reclaim space.  The database could then be shrunk to 5.5 GB and upgraded to 9GB.  We now just need to deal with the limited capacity for growth in the content database, could really use that time machine.

CRM 2011 Launch Dialogue from a Button

Introduction

Most of my work is with SharePoint and SQL Server.  However on a recent project I’ve been working with my ConcentrixTSG colleagues on a Dynamics CRM 2011 implementation.  CRM 4.0 was a good product but they’ve certainly added and improved when they released 2011.  On of the new features that mapped well to a customer process was Dialogs.  Currently a prospect may phone up and our customer would talk them through the questions on a New Business Enquiry Form.  These paper forms are then passed around several areas of the business and recorded on some Excel based registers.  These registers are used for tracking and conversion analysis.

This seemed to map well to launching a dialogue that would provide the questions, record the answers and feed them into a new Lead record within CRM.  There are two main issues with CRM to overcome:

• CRM 2011 Processes, whether workflows or dialogues, are still bound to an instance of an entity.  So you must already have a Lead to run a dialogue to create a lead
• I wanted to be able to deliver a button on the ribbon that would launch the dialogue in a user friendly way.

It turns out that these two issues take a fair degree of effort to get around. Fortunately I found some help on the Internet.  The articles that I mention here both helped me but I wanted to record my own experience as well.  For my screenshots you’ll see that Lead has been renamed to New Business Enquiry.

Lead Creation Dialogue without a lead to run it against

The answer here to to run the dialogue off of an entity instance that you know will already exist, collect your answers and then use the dialogue to create a new lead.  CRM Trick Bag gave me the answer here.  When you create the Dialogue Process in CRM 2011 and you have to pick an entity, choose the User entity.  The currently logged in user can always select their user record and press the Start Dialog button.  They can then choose to launch the Lead Data Capture Dialogue, fill in the fields and on completion the dialog will create a new Lead based on the data captured.

For our Ribbon button we are going to put the URL required to call the dialogue directly onto the ribbon.  We thus need to capture that URL somehow.  Once your dialogue has been created and activated, select a user, press the Start Dialog button and choose your dialogue.  On the Dialogue’s first screen that comes up press CTRL+N.  This will launch a new screen.  From this screen you can look at the URL and capture the GUID for the process.  You’ll see this on the Trick Bag, you are looking for the first bit after %7b and up to %7d, these are the escaped versions of { and } used to surround a GUID.  Copy that into a notepad window and keep it for later.

Add a button to the Ribbon

In CRM 4.0 this was a little bit tricky, in 2011 in much harder.  Most of my guidance for this comes from CRM Mongrel so you’ll maybe want to look there as well.  However the first thing you want to do is create some Web Resources so that the button will have something to do.

Create some Web Resources

Go into Settings/Customisations and open the default Solution or one of your own solutions, click into Web Resources and create a new Script file web resource.  You can see that my function constructs a URL using Xrm.Page.context.getServerUrl() to find where you are and Xrm.Page.context.getUserId() to find who you are.  The function then does a window.open to launch the dialog in a new window.  This will pretty much work as-is in most situations as long as it has the correct URL that you got in the previous section.

Web Resource LaunchNBEF

The Web Resource editor.  This one shows the function that launches the Dialog, GUID has been obscured but your GUID will be different to mine.

function callDialog() {
    var url = Xrm.Page.context.getServerUrl();
    url += "/cs/dialog/rundialog.aspx?DialogId=%7bED03DEEA-6AD6-4BE9-92C7-2C831071419D%7d&EntityName=systemuser&ObjectId=" + Xrm.Page.context.getUserId();
    window.open(url, "", "status=no,scrollbars=no,toolbars=no,menubar=no,location=no");
}

For the time being, although the resource needs to have a function defined, it can just be a simple alert(“you pressed a button”).  You can then get the button in and working before pasting in the correct function.

Our button is also going need an icon.  for my example I reused the one from CRM Mongrel you need to add this as another web resource

Add the Ribbon Button

We are then going to edit the ribbon and

• Add a command that calls the function in our first web resource
• Add a button that has the picture from our second resources and when pressed calls the command

Buttons on the ribbon can only be added in valid places on the ribbon.  In order to find a valid place you need to look at the SDK, find the ribbon for your entity and look what position numbers the existing buttons have been given.  In the SDK you need to look in: \resources\exportedribbonxml\.  You’ll find that there is a separate file with the ribbon xml for each of the built in ribbons.  Open the one for the entity that you are interested in and look through it.

To put a button onto the main application you are going to be in the sections about: Mscrm.HomepageGrid.  A Lead form would be: Mscrm.Form.lead.MainTab.  The thing that you need to pay attention to is the Sequence numbers so that you can position your own button.

There are several ways to edit the actual ribbon, my approach was to use the Ribbon Editor for Microsoft Dynamics CRM from Codeplex.  This doesn’t (or at least didn’t) work with Claims Auth enabled; my scenario was pre-live so I turned claims off for the work and back on after.

The first thing is to add a Command Definition to the Lead entity (renamed in this scenario).

I gave it one rule to always enable the button.  This means that anyone who can get to Leads can press the button.  This could be made more complex so that only people with rights to create Leads can have the button.

Then define the Action for the Command Definition that calls the function in the web resource that we defined earlier.

Having got the Command defined we can now add a Custom Action that will be represented by a new button:

The Location box has a text string that contains the information we read in the ribbon xml.  In my example:

Mscrm.HomepageGrid.lead.MainTab.Workflow.Controls._children

We then need to define all the properties for the button

Sequence:60 – identifies the place we chose for our button in ribbonxml
Template:o1 – identifies a big square button

The rest is labels, image, tooltips and a pointer at the command we defined earlier.

CONCLUSION

There’s no getting away from it this is a long and complex process but I think the results of following it through create a good user experience for custom functionality.

External Data List Open in Dialog Missing

On a recent project I have an external data list showing some data from a SQL database that is part of an LOB system.  When I click any of the items in the list I’m getting the list’s display form but it launches in a dialogue.  I really want it to be a full-page so List Tools/List/List Settings but Advanced settings isn’t there because it’s an external data list.

One of the things that would often work in a previous version if a setting got trimmed out of the UI is just construct the URL to the setting.  My list is on the Admin subsite of my web application, so I can use:

http://sharepoint/Admin/_layouts/advsetng.aspx?List=

and then put the GUID of my list on the end.  I get the advanced settings with one option Launch Forms in a Dialog.  If I put this to No and press OK, I can turn dialogues off on the list, just like any other.  There really was no need to trim this setting out of the interface

Logging in the SSIS Script Task and Script Component

Recently I had some fun with logging in the SSIS script component and I thought I’d share it. I have an SSIS package that uses scripts in a data flow to write to Dynamics CRM Web Services. I wanted to add some logging to record the records that got updated. I found the MSDN article Logging in the Script Task but was unable to follow it. The article says that I should be able to use:

Dts.Log(“Rows processed: ” + rowsProcessed.ToString(), 0, emptyBytes);

However when I tried it there was no Dts.Log to be found. Well the answer is this.

Control Flows have Script Tasks and Data Flows have Script Components. Logging is separate and the way it is handled is different.

To Enable Logging for the Script Task:

• On the SSIS Menu select Logging.
• Add a Provider (I’m using the Log Provider for SQL Server and under its configuration have selected an OLEDB connection manager that points at the database that I want to record the logs in
• Enable Logging for the Package in the Containers section
• Click the grey tick for the script task that you want to log (sequence containers that house it/them)
• Add log code as per the link above to your scripts

Log Provider for SQL Server

Script Task Log Settings

To enable a Script Component of a Data Flow task to be logged, select the data flow task and enable the appropriate logging:

Script Component Log

Once the logging is enabled then you just need to add a few lines of code to the script component and/or the script task.

To test and demonstrate I created a new, simple package on a test system.  My simple package looks like:

Control Flow for simple package

In my single data flow I have an XML source that takes its XML from a string variable with a manually typed two lines and then a script component:

Script Component in Data Flow Task

In my Script Task I can use code from the article linked at the beginning:

//script task
 public void Main()
 {
   byte[] emptyBytes = new byte[0];
   Dts.Log("Log from a Script Task", 0, emptyBytes);
   Dts.TaskResult = (int)ScriptResults.Success;
 }

There is an example of the code to use in a script component on a data flow task. Logging in the Script Component. The example uses VB.Net. I had a go at converting that to C# and came up with

//script component
public override void PreExecute()
{
  base.PreExecute();
  byte[] emptyBytes = new byte[0];
  this.Log("Script Task in Dataflow Component startup", 0, emptyBytes);
}

What are the results of this then, my SYSSSISLog table shows the following

SYSSSISLog

PortalSuperUser and broken EditProfile.aspx

Sometimes support is just about figuring out what someone did wrong when configuring an application. I recently did a new install of SharePoint Server 2010 with Enterprise features that was to be used for a migration from an older MOSS 2007 farm. I set my new Server up with SP1 and created mysite and intranet web applications. The Intranet just had a test Team Site ready to be replaced with the migrated content. I set up the User Profile Service and imported objects from Active Directory. The profiles were there, mysites were set up and user search was working. I though everything was fine but then I clicked the Edit my Profile link on the Profile page of a mysite and got an unexpected error.

OK so I’m sure that the tracelogs will help me out here.  I used ULSViewer (http://archive.msdn.microsoft.com/ULSViewer) to capture the trace logs at the point of the error.  Immediately after the unexpected error in the tracelog was:

Application error when access /_layouts/EditProfile.aspx, Error=Server Out Of Memory. There is no memory on the server to run your program. Please contact your administrator with this problem. at Microsoft.SharePoint.Library.SPRequestInternalClass.GetUserToken(String bstrUrl, String bstrLogin) at Microsoft.SharePoint.Library.SPRequest.GetUserToken(String bstrUrl, String bstrLogin) at Microsoft.SharePoint.SPWeb.GetUserToken(String userName) at Microsoft.SharePoint.Publishing.CacheManager.<.ctor>b__0(SPSite newSite) at….

Server out of memory that must be absurd.  After a long spell of troubleshooting with a very good  Microsoft PSS engineer I suddenly realised that if I accessed EditProfile.aspx.aspx from another web app then it worked fine.  Something must be wrong with my mysite web app,

What I eventually worked out was that In order to get rid of the Event Log warning:

Object Cache: The super user account utilized by the cache is not configured. This can
 increase the number of cache misses, which causes the page requests to consume
 unneccesary system resources.To configure the account use the following command 'stsadm -o setproperty -propertyname portalsuperuseraccount -propertyvalue account -url webappurl'. The account should be any account that has Full Control access to the SharePoint databases but is not an application pool account.

On the http://mysite web app (and other web apps), I follow Mirjams article: http://www.sharepointchick.com/archive/2010/10/06/resolving-the-super-user-account-utilized-by-the-cache-is.aspx.  I checked again that I had the accounts in the User Policy:

This all looked right.  So in Powershell then, had I set these correctly?

PS C:\Users\administrator.xxx> $wa=Get-SPWebApplication http://mysite:80
PS C:\Users\administrator.xxx> $wa.properties["portalsuperreaderaccount"]
i:0#.w|xxx\SPsuperreader
PS C:\Users\administrator.xxx> $wa.properties["portalsuperuseraccount"]
i:0#.w|xxx\SPsuperuser

Oh, that’s not right, these are the settings for a Web App that is running in Claims authentication.  The MySites web app is running in Classic authentication so these needed to be re-set.

PS C:\Users\administrator.xxx> $wa.properties["portalsuperuseraccount"] = "xxx\spsuperuser"
PS C:\Users\administrator.xxx> $wa.properties["portalsuperreaderaccount"] = "xxx\spsuperreader"
PS C:\Users\administrator.xxx> $wa.update()
PS C:\Users\administrator.xxx>IISRESET

I’m sure that with a “normal” content Web App when you get these wrong you just get an access denied whenever you try to browse the web app.  It is strange that with a MySite host and SSSC enabled everything works until you get an Unexpected Error/Server out of memory when you try to edit the user profile.

There is now a Technet article linked from Mirjam’s (http://technet.microsoft.com/en-us/library/ff758656.aspx)

The object cache stores properties about items in Microsoft SharePoint Server 2010. Items in this cache are used by the publishing feature when it renders Web pages

Well, if the warning relates tot he Publishing Infrastructure and the object cache I recon it has very little to do with the MySite site collections anyway.  Perhaps the warning should only appear in the event log when Publishing Infrastructure feature is enabled within site collections in a given web app.

So it really is important to get these settings right, if you are going to apply them.

Another round of Microsoft Updates stops SharePoint Services 3 with SQL Embedded from working

At TSG we provide support for a lot of customers. Our support teams tell me of a few recent SharePoint failures. This is at least the third time, that I can think of, a Windows Update has applied a patch to SharePoint Services v3 (there are still a lot of them out there) that it doesn’t recover from.  You seem to get a mixed bag of results dependent upon what Operating System and what SQL version was used and various other factors.

As a general guide we see many SharePoints that just carry on working, several that need Product and Technologies Config Wizard (PSConfigUI) run and a few hardcore ones that need more help.  As a general rule, if SharePoint was installed with SSEE/SQL Embedded/Standalone option it will fall over after some updates.  Running Product and Technologies wizard on its own won’t help as that will fail as well.  If you are in this situation, try following Article ID: 944154 it should get you back and running.

You always need to consider which database engine you are installing with.  SSEE will mean that you don’t have a database size restriction but it comes with a bunch of problems and issues that you also need to be aware of.  Remember with SQL 2008R2 Express Microsoft increased the size limit to 10GB.  Express edition has some of the drawbacks of embedded but it makes several of them go away.  If you are anywhere near that that size for your content databases then you really should consider the features of Workgroup or above.

Reporting From SharePoint Lists

Introduction
Overview
One of my occasional tasks involves taking data that is stored in one, or more, SharePoint lists and presenting it in a formatted table or chart.  The requirements vary as does the infrastructure and there are many options available.  In this article I’m going to take a while to talk through the options available and how to make some of them work in different scenarios.

Scenarios to consider
SharePoint is a big technology, deployed in a wide variety of circumstances and situations.  Part of the job of a consultant is to determine the best way to use the available technology in any given circumstance.  Some of the things that need to be considered when choosing how to report on a SharePoint list include:

1. SharePoint Version (3/2007 or 2010)
2. SharePoint Edition (Services/Foundation, Server Standard, Server Enterprise)
3. SQL Version (2005/2008 or 2008R2)
4. SQL Edition (Express Advanced Services, Workgroup, Standard or above)
5. Report access from LAN only or across an Internet Facing Web App
6. Whether Reporting Services is in Integrated or Native Mode
7. Size and complexity of the SharePoint farm
8. Complexity of the reports required
9. Whether unsupported techniques are allowed

I’ll try to clarify some of these a bit further later on.

Report Mechanism
There are several mechanisms available for displaying the list data.  I’ll list out the ones that I can think of here and then try to talk about each one.  Some I’ll cover in detail.  These include:

1. SharePoint Designer DataView (with/without jQuery)
2. Google Charts
3. ChartPart for SharePoint by Victor Wilen
4. SharePoint built-in Charts
5. Reporting Services using XML Connection Type
6. Reporting Services using SharePoint List Connection Type
7. Reporting Services using a SQL view of the SharePoint Database
8. Codeplex SSIS Extension that connects Integration Services to SharePoint List and warehouse the data in a separate SQL table
9. Third party SharePoint List connection type
10. Chargeable, third-party chart web parts

Discussion
SharePoint Designer DataView
Although it takes a bit of effort and doesn’t have a good deployment story SharePoint Designer can produce some impressive looking tabular data especially when augmented by a bit of jQuery or JavaScript.  There are then some examples of using these to produce simple graphs.  Natalya has a great example of this: Vertical Bar Graph in Data View Web Part (DVWP).  The trouble with these is how quickly the SharePoint UI gives up and you move into hand crafting XSLT to display your data.  The story gets a bit better in some ways with 2010, you can take any existing list view and just add conditional formatting, fonts and other tweaks to it; often without even unghosting things.  Your authentication type and current zone (default/intranet etc) doesn’t prevent things within dataviews from working the same way.

ChartPart for SharePoint by Victor Wilen
You can find this on codeplex http://chartpart.codeplex.com/.  I found this in use on one of our customer sites recently but have no personal experience of it, however it works for them.  The customer in question is currently running Windows SharePoint Services v3 and is talking to us about an upgrade to Foundation 2010.  unfortunately Victor hasn’t yet released a version that works in 2010.

SharePoint built-in Charts
You are running SharePoint Server 2010 with enterprise features; congratulations you now have access to use it’s built-in charts.  I’ve only tried these on one site so far where I wanted to do a pivot chart on a list.  Unfortunately I couldn’t find pivot chart so had to move on and present the data a different way.

Reporting Services using XML Connection Type
David Wise wrote a good article on: Connecting SQL Reporting Services to a SharePoint List.  David’s approach is to use the XML data type in SQL Reporting Services and connect to SharePoint’s lists web service.  The XML data type has been there for a few SQL versions so will work on an older Server as well.  You then construct an XML query that uses the GetListItems method to retrieve a bunch of data from the list.  You can then use the layout tools in reporting services to create a report or chart and finally show this chart inside a SharePoint site with either Page Viewer Web Part (PVWP) or Report Viewer Web Part.  As part of your web service query you can supply parameters that filter the amount of data that is retrieved from the list: How to filter a sharepoint list with report
parameters.  That way you get back the 100 results you want, not the 6000 items in the list.  However because the query is XML there is no useful data typing and you will find yourself using cDate and the like in the report designer to constantly turn things into the correct data type for you to display or sum etc.  Field names also come through in need of attention that adds more time to writing the report.

Immediately we’ve opened up a bunch of other caveats including: how do you authenticate to the lists web service, is Reporting Services in integrated or native mode, are reporting services and SharePoint co-located or on separate Servers, are you in the default zone or across the Internet.

Firstly, the XML data type supports Windows Integrated authentication only.  This means that your users will have access in a report only to lists and items that they have access to in the SharePoint User interface.  If SharePoint and Reporting Services are on the same Server (meaning also that you have a singe Web Front End) then this authentication can be relatively simple whether in Native mode or Integrated mode.  If SharePoint is on one Server and Reporting Services is on another then you need to come up with something else and it varies between modes and your location.

You can have Reporting Services in Native mode and configure SharePoint and Reporting Services to support Kerberos authentication.  This, on a really good day, allows your user’s authentication to double hop between Servers and arrive at the Reporting Services Server.  Remember though that Kerberos requires direct access to the Domain Controllers so even if you publish SharePoint to the Internet (https://sharepoint.example.com) it won’t work from there.  Remember also that you put a PVWP into a Web Part (or 2010 Wiki page) in order to display the report, you then put the internal URL of your report server with some render URL parameters, thus it’s not going to work when you are across the Internet anyway.  Internally you may choose to do away with the need for Kerberos by editing the Data Source once published and using Reporting Services to securely store the credentials for you.  Congratulations your users no longer need access rights to the SharePoint list that you are showing them in the report (maybe this is what you wanted anyway).

You can have Reporting Services in Integrated mode, set its Authentication Mode to Windows Authentication.  You are back to relying on Kerberos for the double hop.  Now you upload your reports to a document library, from the LAN you can click on a report and render it.  However reports only render in the default zone and will fail, if you are in another zone.  You can though create a web part page and put the Reporting Services Report Viewer web part on the page and then point that at the report in the document library.  This trick then allows the report to render from the Internet Zone and it doesn’t matter that we jump to the other Server for which SharePoint has an internal URL.  Oh no, we just failed again – Kerberos won’t work for us because we don’t have direct DC access from the external location.

You can have Reporting Services in Integrated mode, set its Authentication Mode to Trusted Account.  This should solve our need for Kerberos and allow us to render those reports from the Internet without a double hop to a second box.  Fail again, you can’t use data sources that only support Windows Integrated on a Report Server that is Integrated Mode when Authentication Mode is set to Trusted Account.   Oh and you have to look pretty closely into your choice of data source to find out what its authentication options are.

Reporting Services using SharePoint List Connection Type
If you are lucky enough to be using SQL 2008 R2 Reporting Services then you have access to the SharePoint List Connection Type.  Remember though, you won’t get this in Express AS or even Workgroup, its Standard or above (check the comparison matrix on Microsoft).  This connection type gives you a GUI for choosing the columns that you want to pull through, these then arrive with decent names and proper data types.  However there is no filtering in the query; you’ll get back 6000 items and need to use the report to through away all but the 10 you want to show to the user.

The SharePoint List connection type only supports Windows Integrated authentication so all the stuff above about Kerberos and Trusted Accounts applies here as well, meaning that it is also only suitable for the default zone across the LAN or a VPN.

Reporting Services using a SQL view of the SharePoint Database
Be warned this is officially not supported.  You will find people on the Internet warning you off this technique but lets explore it anyway.  SharePoint stores all the data in every list in the alluserdata table of the content database.  If you have control over the SQL Server then you can directly query that table to extract the data in a list.  My preference is to create another database and then create a series of views within it.  Each view looks at the SharePoint content database to retrieve results for a specific list.  I can then use those views to build reports.

Before I talk about how I build the queries, I want to first consider this in comparison to the systems above.  Firstly, here is another example of why you might create a new Site Collection instead of a new Site; consider using STSADM to create a new site collection in a new content database.  On a recent project, the main content database was 8GB so I created an additional one for the new application that I was going to report on, thus my unsupported queries can’t affect the bulk of the data.

Now I’m in TSQL I can do parameterised queries on the table and retrieve just the 10 rows that represent the 10 items in a list of 6000.  How can this not be better than using the SharePoint List connection type, pulling everything back and throwing most of it away.  Oh and check the clustered index on the AllUserData table, it looks like it should help with these queries as well.

Now I’m in TSQL I can use either Windows Authentication or SQL Authentication.  So if I’m in SharePoint Integrated mode and my report Server is on a separate machine, I can now use Trusted Account, store my SQL username and password and solve all my authentication issues.  With one more small trick I can finally make my reports available in ALL SharePoint Zones.

What is that small trick, well if you publish a report to a SharePoint library and click on it, to run it across the Internet you get a message telling you that this can only work in the default zone.  Instead I create a Web Part Page and put a Reporting Services Report Viewer Web Part on the page, I point that web part at the report’s RDL and the report can then render in the non default zone and I finally have my solution.  If it’s not supported, why is it the only way that works.

*****Update March 2012 Relating to SharePoint 2010/SQL 2008R2 Reporting Services**********
If you are using SharePoint 2010 you will have the SQL 2008R2 Report Viewer, you may have this with an older SharePoint version anywhere.  There are two tricks with this to force it to work:
1 – If you use the browse button to find your report in the list then it will put a relative URL in place.  You need to prefix this with the http:// stuff that turns it into an absolute URL for your default zone.
2-You need to expand the View section and untick Show Report Builder Menu
*************End Update

OK, so how do I query the SharePoint Content database.  In my new Site collection, I create a list called Regions:

SharePoint Regions List

This simple list just has the title column that I’ve renamed to Region.  If you use List Settings on the ribbon and inspect the URL, you will be able to see the list ID.  You need to take the %2Ds and turn them into dashes.  Then in SQL Management Studio you can use a query on the database similar to the following

SELECT      tp_ID AS ID, nvarchar1 AS [Region]
FROM         WSS_Content.dbo.AllUserData
WHERE     (tp_ListId = ‘{9045D3AA-475B-48DA-8043-631D4DC79421}’)
  AND (tp_IsCurrentVersion = 1) AND (tp_DeleteTransactionId = 0)
  AND (AllUserData.tp_RowOrdinal = 0)
ORDER BY ID

• tp_ListId indetifies the list that we want data for
• tp_IsCurrentVersion makes sure we only get the latest version of the item
• tp_DeleteTransactionId makes sure we don’t get items that are in the recycle bin

The table has a bunch (64 I think) Varchar columns, 16 Int columns, some date columns, some SQL Variant columns etc.  The data type you pick in SharePoint effects which type of column will store the actual data.  The Title column is always stored in varchar1.  The next Text column you add to the list will be in varchar2.  Calculated columns are in the SQLVariant columns.  Lookups are in the Int columns (you need to watch for that one).  On a simple list, each item will only take one row in the all user data table (tp_RowOrdinal=0).  If you have more than 16 Integers then your list will start using RowOrdinal 0 and RowOrdinal 1 to fully represent each list item.  I don’t know if there is a limit on the number of RowOrdinals that a list can use, however each time it adds one, you take a performance hit reading or writing data to that list.  SharePoint 2010 has a default limit of 6 rows per list, but this can be increased.

Integers can be the biggest cause of a rollover onto another row, they are used for every lookup, also each time you add a workflow to a list there is a workflow status column created, which uses the Integer columns as well.  So 25 lookups, 10 workflows and you are onto three rows.  Remember to consider if you should normalise your design; this gets a chunk easier in 2010 anyway.

You generally need to look at the data in the list, look at a query with all columns in the database and match the two to see which database column/Row Ordinal, matches which list column.  I have also seen a Bamboo Solutions web part that will show this information without you having to work it out yourself.

If you run the query above, you’ll get a result similar to the one below:

I’ll take the query that I’ve worked out above and use it to create the RegionsView or Vw_Regions, if you prefer.  If I have to do a view on another list that uses regions as a lookup I can then either join to the RegionsView, or use a sub select to turn the integer ID into the text of the lookup.

Conclusion
Like most things in SharePoint, there is more than one way to meet the customer’s requirements.  I have seen, in production, sites using: dataviews, reports based on SharePoint List connection type, reports based on SQL Views of the SharePoint database and sites using bespoke/third-party charting web parts.  I think that you have to consider each situation on its merits and decide which option is going to be the best fit for the customer.