#spstc SharePoint Saturday Twin Cities quick recap

Last weekend, the SharePoint admin and I went up to the Twin Cities to hit up SharePoint Saturday. It was a good time (ScarePint the night before) and then 4 sessions and networking. Some good content on caching, silverlight vs HTML5, Performance Point and then Visualizations in SharePoint.

The sessions were OK, the people were nice. I wasn’t 100% engaged as I felt the sessions lacked some advanced content, but it was good overall. Definitely a different experience for me since I usually hit up .NET or SQL events.

Geeky/Programming SharePoint SQLServerPedia Syndication

SharePoint 2010: Track User Profile Changes Over Time Using MERGE

I recently blogged about a report you can write to see what users don’t have managers in SharePoint 2010 (get your Org Browser fixed!). Well, how about tracking who get’s added or deleted every day, or edited? You could probably look at the ForeFront Identity Manager that SharePoint 2010 uses and do some logging or use the API to log things, but that is.. well, harder than this.

What I wanted to do was keep logs for debugging and reporting. Who is getting added to SharePoint 2010 user profile sync? Is last user added to Active Directory a valid user, or a system account? Now, you could query Active Directory (just like finding users without managers) but I am guessing that most SharePoint Admins don’t have AD access, so they need another way.

What I did was create another database on my database server where the SharePoint content and profile databases are stored, so I can use it as a dumping ground.

What you need to do is create a table to dump the profile table and then every day do a SQL MERGE into it and save the changes. This is where it gets interesting

First, create your table (for this example I am only tracking NTName and PreferredName, so “domainusername” and “Name, User”, but you could easily add other columns.)

CREATE TABLE [dbo].[SharePointUserTracking](
	[RecordId] [bigint] NOT NULL,
	[NTName] [nvarchar](400) NULL,
	[PreferredName] [varchar](256) NULL,
	[RecordId] ASC

Once you have your table, you are ready to create your MERGE query, track the changes, and the get it scheduled up. So what we want to do is MERGE our UserProfile_Full data from our ProfileDB into our new table, tracking INSERT, UPDATE, and DELETE’s as well, in a tracking table. Let’s create our “tracking changes” table:

CREATE TABLE [dbo].[SharePointUserProfileDelta](
	[ExistingRecordId] [bigint] NULL,
	[ExistingNTName] [nvarchar](400) NULL,
	[ExistingPreferredName] [varchar](256) NULL,
	[ActionTaken] [nvarchar](10) NULL,
	[NewRecordId] [bigint] NULL,
	[NewNTName] [nvarchar](400) NULL,
	[NewPreferredName] [nvarchar](256) NULL,
	[LogDate] [datetime] NULL


Notice the column “ActionTaken” to track what we do, INSERT, UPDATE, DELETE.

Now, for the super merge (note, the profile DB might not be named the same, also note the collation thing you need to do.. may or may not to do this in your environment)

  MERGE dbo.SharePointUserTracking AS target
            (SELECT RecordId,NTName,PreferredName FROM SP2010_ProfileDB.dbo.UserProfile_Full) AS source
            (RecordId,NTName,PreferredName) ON (target.RecordId = source.RecordId)
    WHEN MATCHED AND target.NTName  source.NTName COLLATE SQL_Latin1_General_CP1_CI_AS OR target.PreferredName  source.PreferredName COLLATE SQL_Latin1_General_CP1_CI_AS THEN
        UPDATE SET
                  NTName = source.NTName,
                  PreferredName = source.PreferredName
          INSERT (RecordId,NTName,PreferredName)
          VALUES (source.RecordId,source.NTName, source.PreferredName)
      OUTPUT deleted.*, $action, inserted.*,GETDATE() INTO dbo.SharePointUserProfileDelta;

Now, you can schedule that to run after your daily user profile sync job in SharePoint and then you have a running log of changes, which you could make an SSRS report off of easily or do whatever you want with it. Pretty cool! Just know, you can use this method to track changes to any table.. slowly changing dims and even more.

Geeky/Programming SharePoint

SharePoint Report – Missing Managers

Trying to roll out SharePoint at an org can have it’s hard times. One is trying to get User Profile sync working well and making sure the Org Browser works well goes along with that. You can sync over the manager from AD and everything falls into place, but their might be users you are pulling that don’t have a manager set. Now of course you can query AD for this, but you would have to already know the filters and OU’s you are pulling into SharePoint. Another way to do this (and of course, disclaimer here, don’t try this at home if you are scared of querying SharePoint databases, and yes, it probably isn’t recommend, but I am doing it anyways). Here is a query you can use to get the User’s with no manager, and also join it back to get some other attributes such as department, office, and title so you can figure out where they are and who their manager might be (helpful in a larger org). You can easily throw this in an SSRS report, and have it email whomever maintains the managers in AD or in your organization. (Note, SP2010_ProfileDB might not be the name of your actual profile DB, you would have to change that in the query below)

SELECT up.RecordId,PreferredName,NTName,Email, office.Office, titles.JobTitle, dept.Department
	FROM dbo.UserProfile_Full up
      ,[PropertyVal] AS 'Office'
  FROM [SP2010_ProfileDB].[dbo].[UserProfileValue]
  WHERE PropertyID = 11) office ON up.RecordId = office.RecordId

      ,[PropertyVal] AS 'JobTitle'
  FROM [SP2010_ProfileDB].[dbo].[UserProfileValue]
  WHERE PropertyID = 13) titles ON up.RecordId = titles.RecordId

      ,[PropertyVal] AS 'Department'
  FROM [SP2010_ProfileDB].[dbo].[UserProfileValue]
  WHERE PropertyID = 14)dept ON up.RecordId = dept.RecordId

	Manager IS NULL
	ORDER BY Office

MS Project 2010 Sync with SharePoint 2010

If you get Microsoft Project 2010 and use it to manage your projects (I don’t personally, I use Agile) – then you can sync with with SharePoint 2010.

There is an article, which shows you how to setup the sync, which is pretty cool, but it doesn’t give you tons of detail about the url you need to put in.

The url you would want to put in is the url to your site. Not a page or list.


if your site is


you would use that. Some might think you would put in a link to your task list or something else, but you just need the root site. Now you know!


SharePoint 2010 Blogging: Turning on Comments and Comment Approval

Not sure this is the case in all configurations, YMMV. In SharePoint 2010, it seems like blog commenting isn’t “on” by default. Everything should work fine but once you start posting, other people won’t be able to add comments even though there is a comment link at the bottom of each post. How to get it blog post comments working? A few basic things that I will go into detail on.

First, get to your blog “site” in your browser, and go to “Site Actions->View all Site Content”.

Once there, you will see that Categories, Comments, Links and Posts are just SharePoint lists. Pretty cool. Go into the “Comments” list.


Once in the Comments list, click on “List” at the top, and on the List Ribbon click “List Settings”.


Click on the “Versioning Settings” link. In there, you want to check the setting:

1. Require Content Approval for submitted items? – yes. if you want to have some gatekeeper between new comments and them being published.


Under “Advanced Settings” link

1. Item-Level permissions. You want “read all items” for Read Access and “Create items and edit items that were created by the user” for Create and Edit access.


In the "Permissions for this List” link, you want to probably break Inherited permissions, and then add “Authenticated Users” with “Contribute” and “Read” permissions.


As a final setting, you want to probably get alerted on new comments. So back on the main “List” ribbon, you want to click on “Alert Me” and set up the appropriate alerts, or subscribe to the RSS feed.


That does it, after going through all those motions, you should be able to have other people comment on your SharePoint 2010 blog, get alerted, and approve comments. Pretty cool (For what it’s worth, this shouldn’t be this complicated!)


SharePoint Lists and Missing Items

In SharePoint 2007/2010, if you have a list and you create different views on the list, you better make sure that the filters you set up actually work, otherwise your views will get no items. It is always good to leave a “All Items” view with no filters, etc. That way you can verify that your list is “working”.

Just ran into the issue where a list had 3 views, none of which had any items. You could sit and add new items all day and they would never show up. It had no “All Items” view, which I recreated and saw all the items in the list. Went into the other created filtered views and tweaked the filter and they all worked.

No need to delete that default “All Items”, I am guessing they have it there for a reason 🙂

Business Intelligence Geeky/Programming SharePoint

SharePoint 2007 2nd Stage Recycle Bin and Content DB Size

Recently, have been running into space issues from a content database perspective in SharePoint (MOSS) 2007. The DB server is still running SQL 2000 on a semi-old box, but since there are physical hard drives, hard to extend without a lot of surgery. Anyways, did some digging and found some things that may be useful for others..

1. You need to be Site Collection Admin and “God” on the servers.

I thought I was, but was mistaken. Was set up a while ago by consultant and I wasn’t involved, and added later. I had some pretty good rights, but not what I needed. I had Central Admin “admin” and other but not on the root site. Also some of the domain users that have rights I don’t have passwords for anywhere as an oversight, so kind of at a loss. I had to get that first before moving forward. I found a solution on this blog. I ran the following from the command line on the central admin server for my site and domain and got the access I needed to run what I needed to run.

stsadm -o siteowner -url[sitename] -ownerlogin [DOMAINnetID]


2. I ran Quest’s free server reporting tool.

This is pretty cool, you can go here: and install a little WCF service and then run the reports right from your browser, you just have to have the admin rights to the SharePoint site collection to do it. I ran this and found only around 5 GB of usage, but my database was at 222 GB with 278 MB free space not utilized.



After digging around, I found the issue. First, the recycle bin in SharePoint was full, but also, the “Second Stage Recycle Bin” was ultra full. and Ultra being 217 GB full. What is the second stage recycle bin? Well, users can delete from the site, and it basically changes a db flag to hide the file, then, after 30 days (by default) it moves it to the 2nd stage recycle bin. There it sits and sits and sits. You need to clean this one up, but if you don’t you end up in a situation where I am at.

You can’t turn off the 2nd Stage Recycle Bin because there is too much stuff in it!

3. Delete Items from the 2nd Stage Recycle Bin using PowerShell.

I found this cool PowerShell script here and modified it. First I changed the rowlimit to 10k, and I added a date variable to output the date so I could see that progress was being made. I also am checking my DB size as I run it and watching the free space go up, up up!

# flushrecyclebin.ps1
# Invoke this from a Powershell prompt by calling "./flushrecyclebin.ps1 http://webapp/siteurl".
# This script will only delete items in the second stage (site collection) recycle bin, so you
# will need to manually flush items you wish to delete from the first stage into the second
# stage.

# we accept this parameter on the command line. If you have several sites' recycle bins to
# flush, you could easily turn this into a foreach loop

# set this to be an acceptable number of records to delete in one batch. If your recycle bin
# has more items in it than this, you will need to rerun it
$rowlimit = 10000;

# Don't change below here

[void] [System.Reflection.Assembly]::Load(”Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c”)

$site = new-object Microsoft.SharePoint.SPSite("$param_site");

$query = new-object Microsoft.SharePoint.SPRecycleBinQuery;
$query.ItemState = "SecondStageRecycleBin";
$query.RowLimit = $rowlimit;

$itemcoll = $site.GetRecycleBinItems($query);
foreach ($item in $itemcoll) {
    $a = Get-Date
    $id = $item.ID;
    write-host -nonewline $a " ... Deleting .. " $item.Title " ... ";
    write-host "Done";


So where does this leave you? Well, first, on a new SharePoint 2007 setup you want to setup the Recycle Bin Settings so you don’t get caught like this. If you go to Central Administration > Application Management > Web Application General Settings and at the bottom you will see the Recycle Bin settings:


Like I said, with a full 2nd Stage Recycle Bin, you are stuck and can’t really do much here. But once clean, you probably want to reduce the number of days to like 7 and also possibly turn off the 2nd stage Recycle bin or reduce the size.

After cleanup like I did, you will want your DBA to probably shrink the file, this is one of the times that shrinking makes sense.

Happy SharePoint’ing!

Business Intelligence Geeky/Programming SharePoint SQLServerPedia Syndication

PowerPivot, Excel Services, SharePoint 2010 Farm, and You

The last few days I have spent an exorbitant amount of time (5-6 hours?) getting PowerPivot and Excel Services working on a SharePoint 2010 Farm. I just want to get out there some of the things I had to do to get it all working, and why (at least what I think is why).

First off, most dev setups are using one box for SharePoint, which, in my eyes, masks most every issue you will run into.

Most prod environments are multiple boxes, Web Front Ends, App Servers, etc. This leads to the most pain when setting up these services (PowerPivot, Excel Services) as there are hundreds of different configurations and setups. You need to get it juuuust right.

Anyways, I will explain as best I can.

First, assuming you have PowerPivot running on an app server and Excel Services running to. You upload your PowerPivot workbook to a PowerPivot gallery and you go home happy. But wait, does the data refresh correctly? Can you even open it?

1. If your site is running https, you have to tweak the Excel Services settings.

By default, only http:// shows up here (I think?) so you need to add https:// or you can’t even really get into your PowerPivot.


2. You probably are going to have to up the upload size limit

Some PowerPivot workbooks are big, like 80-100 MB big. Default in SharePoint 2010 for Excel Services is something like 10 MB, and SharePoint default is 50 MB, you need to change for both settings.

3. If you don’t have Kerberos set up, it is tougher.

PowerPivot refreshes data from some system somewhere to its own “cube”, then Excel Services refreshes data from that cube into Excel Web. You have to setup a way for the data to get refreshed into PowerPivot’s cube. No Kerberos? Then you need to use the Secure Store Service and set up a credential, and set that up as the unattended service account for PowerPivot refresh. Then, at least you can get data from somewhere else into PowerPivot’s “cube”

Second step is getting Excel Services to refresh from that cube into Excel web. What I had to end up doing was creating another credential in the Secure Store service for Excel Services Refresh (set up as farm account for now, it has the stroke it needs). And then set that up in Excel Services settings as well for Excel Services unattended refresh account. But also! – you need to change your workbooks before you upload or whatever.

In your workbook, go to your connection, properties, and get to the authentication, and change to “none” instead of “windows authentication”, then your data will refresh from the PowerPivot cube to Excel in SharePoint.

4. Same thing goes for the PowerPivot Management Dashboards

The management dashboards are set to “windows authentication” so they wont work either, you need to change to “none”, in the Management Dashboard site, goto all site content, PowerPivot Management, <some guid> folder, and then 1033 (US English), edit the two workbooks to use “none” and save, and your management dashboard will actually work!

I’m sure there is a ton more I go delve into here, but this is the high level. As Rob (@powerpivotpro) would say – “make sure to click on the slicers!”