Missing Export to Excel option with SharePoint Survey

If you are working with survey data that you'd like to export to a spreadsheet, SharePoint provides a quick way to access survey answers with Microsoft Excel.

If you don't see the option Export to Spreadsheet on your Actions menu, there are two things you should check.

1. Check that the survey view is set to Overview.  The Graphical Summary and All Responses views do not have this option.
2. Go to Site Settings and click Manage Site Features.  Ensure that SharePoint Server Enterprise Site Features is Activated.

Other users have reported that the option only became available when the site collection feature SharePoint Server Enterprise Site Collection Features is activated, but this has not been my experience.

Intellisense and SSMS

Intellisense in the Query Editor was a wonderful addition to SQL 2008.  I find myself using it even more with SQL 2012.  But as a programmer I just hate to see squiggly red lines under my objects, especially when I'm right and Intellisense is wrong.



This is a common occurrence when working on new database objects.  If you create a new table or column and then start writing a query against it, Intellisense will underline those objects and tell you they don't exist.  This is because Intellisense has a cache of objects that just isn't updated as frequently as we need it to be.

To manually refresh the cache and make those terrible squigglies go away, press the key combination CTRL-SHIFT-R.  You can also use the menu and select Edit -> IntelliSense -> Refresh Local Cache, but I've found this doesn't always resolve the issue right away.  The key combination while the query editor window has focus seems to always do the trick for me.

Insert Content from SharePoint

After upgrading from SharePoint 2007 to 2010, whenever we edited a web part page and tried to insert content (such as an image or video) and selected "From SharePoint" as the option, we got a pop up window with the basic SharePoint error information:

 

After some digging, it turns out this is related to the same issue that causes another feature to not work.  In Site Settings, clicking the Content and Structure link under Site Administration generated the same error message.  Now you can dig into the correlation data and try to understand why this sitemanager.aspx page isn't pulling up, but the problem is that you have a corrupted list or library somewhere on the site.

 

You can identify it (the hard way) by viewing all site content and clicking each list or library, and then viewing the Settings page for that object.  When you get to one that throws an error, that is the one you need to delete (SharePoint Designer to the rescue).  You may even get the error before you can click the Settings page. Once removed, your Site Manager page and Insert Content pages should work successfully.

 

Protip: Sometimes you can even recover the deleted list or library from the recycle bin without bringing back the errors!

Deleting SharePoint Site Collections

A great post by Raymund Macaalay explains how you can re-organize your sharepoint sites and data by moving a SharePoint sub-site (SPSite) to a new site collection.  I used this method with great success to take an 80GB site collection and split some 10GB sub-sites out to their own site collections (and content databases).  During this process I had created numerous test site collections to verify the Import-SPWeb task would restore everything correctly.  Once done, I was happy to delete those site collections from the web application.

Since each of these site collections were around 10GB I was expecting a lot of free space to open up in the contentdb, which I would reclaim with a shrink operation.  However, the free space was not there.  This is because deleting a site collection from Central Administration does not permanently delete the site collection, it still exists in the second stage recycle bin which will store it for (default) 30 days.  To remove it from the contentdb immediately you need to run some PowerShell commands.

Get-SPDeletedSite will return a list of deleted sites:

You can permanently remove the sites by using the Remove-SPDeletedSite command:

Once this completes, you will still not see immediate relief in your contentdb.  This is because removing large amounts of content can be a resource intensive operation.  SharePoint has a Gradual Site Delete timer job that by default runs once daily and will gradually remove all content marked for deletion.  If you want to verify that your site is in the queue for the Gradual Site Delete job you can view the data in the dbo.SiteDeletion table of the content database.

After the site deletion job completes its work, you can use a shrink operation in SQL Server to reduce your contentdb to a desireable size.  If you have migrated sub-sites to other site collections like I did, don't forget to purge the original sub-sites from the second stage recycle bin which can be accessed from the Site Collection Administration section of the Site Settings page.

STSADM MigrateUser and Move-SPUser Quirks

It's common for SharePoint administrators to get a request to update someone's name in SharePoint as a result of marriage.  The trusty STSADM migrateuser command has been around since MOSS 2007 as a method to update references from the old account to the new account.

In my experience, AD admins tend to rename existing accounts as opposed to creating new accounts and decommissioning old ones.  Once the account has been renamed, migrateuser can be used in the following fashion to update the SharePoint profile data:

stsadm.exe -o migrateuser -oldlogin domain\originaluserlogin -newlogin domain\newuserlogin

Sometimes this command will return a message "New user account does not have valid SID history.". If this is the case, you can add the -ignoresidhistory argument and cross your fingers that you get a response "Operation completed successfully."

If you do not get a successful message, you may instead see this:
Value cannot be null.
Parameter name: userProfileApplicationProxy

Many search results will point you towards verifying that you are a farm administrator, and of course you are in the farm administrator group, so you brush this off and move on.  Some results point towards a promising PowerShell command Move-SPUser:
Move-SPUser -Identity domain\originaluserlogin -NewAlias domain\newuserlogin

Unfortunately for me I recieved the error:
The parameterless Read method can only be used when this instance was initialized with an SPUser object.

Using Get-SPUser to store the user login in a $user variable to be passed into Move-SPUser returned yet another error:
You must specify a valid user object or user identity.

So the old user doesn't exist in the web anymore, yet when I view the user profile for the user in the User Profile Service in Central Administration, the old login name is still displayed next to all the updated profile information.  Somewhere the old login is still hanging around, so back to migrateuser!

The solution for me was indeed related to the account used to run the migrateuser command. Evidently being a farm administrator is not enough, you need to use the FARM Account.  To find out exactly what this account is, open up Central Administration and click on configure service accounts under the Security heading.

Select Farm Account from the drop down box, and the page will refresh and list an account name in the bottom drop down box.  This is the account you need to log in to the SharePoint server.  I tried using the Run As Another User command on the SharePoint Management Shell and that did not work, you will get a message Access Denied when you do that.  When you login to the server, right click SharePoint Management Shell and choose Run as Administrator.

Hopefully this time you will breathe a huge sigh of relief as you see the coveted Operation completed successfully message.

A quick way to bring down SharePoint 2010

Yesterday I was working through the steps to configure incoming email for my SharePoint 2010 farm.  After some testing, internal mail sent to SharePoint was being dropped into the correct document libraries, as intended.

But somewhere along the way, when configuring the SMTP service on the SharePoint machine to pick up files from the drop folder, I had hit a snag trying to change the SMTP domain from the default <servername> (which was not user friendly) to a more friendly @spmail.company.com.  The emails were being received in the default drop folder in the /mailroot and not in the custom drop folder I had configured on a data volume.

While experimenting with different solutions I had the (un)brilliant idea to add another http binding to the SharePoint Web Services IIS site for the host header spmail.company.com.  I must have been low on coffee at the time because I don't recall even making this change.  This apparently was a ticking timebomb because although SharePoint appeared to work just fine the rest of the day, I was greeted the next morning (no doubt after some nightly timer job bombed) by a nice correlation error on the SharePoint home page. Digging through log files on our WFE and App servers I found a myriad of errors:

WFE log:
system.nullreferenceexception: Object reference not set to an instance of an object.    at Microsoft.Office.Server.Administration.UserProfileApplicationProxy.get_ApplicationProperties()

App log:
Request for security token failed with exception: System.ServiceModel.ServiceActivationException: The requested service, 'http://localhost:32843/SecurityTokenServiceApplication/securitytoken.svc/actas' could not be activated.

Viewing User Profile Service Application in Central Admin:
This User Profile Application's connection is currently not available. The Application Pool or User Profile Service may not have been started.

Now having had NUMEROUS issues with User Profile Service App in the past, my default is always to run IISRESET on the App and WFE machines. This time I refreshed my homepage and discovered the magic bullet didn't work this time.

Troubleshooting the User Service profile app led me down the same paths as many others, checking to make sure the farm account had to proper credentials to run the timer service, FIM services, and the app pool.  Everything checked out, except the profile synchronization service was hung at 'starting'.

I turned my attention to the STS app.  App pool checked out, IIS site checked out.  Then someone suggested opening the STS web service url on the App server to look for clues.  I was presented with this very helpful message:

This collection already contains an address with scheme http. There can be at most one address per scheme in this collection.
Checking the bindings for SharePoint Web Services site revealed the culprit. I removed the binding and checked SharePoint again and voila, immediately sites were working again.  I returned to Central Admin and the User Profile Synchronization service was no longer stuck at Starting and was not Started.

Lesson learned, don't tinker with SharePoint if your coffee level is running low, there is no telling what will happen during your caffeine blackout!

SharePoint 2010 - Sign in as a different user causes immediate 401 UNAUTHORIZED

While working among half a dozen SharePoint 2010 sites one day, spread through Internet Explorer 9 tabs, I was testing some security trimming on some new groups for a particular site. When I tried to sign in as another user, I was immediately given a blank white page that simply said 401 UNAUTHORIZED.

The only related information I could find on the web was a post referring to the error but without a suitable solution: http://www.3guysonsharepoint.com/?p=979

I fired up a session of Chrome and tried the same thing. The Login prompt was displayed and everything worked just fine. I returned to IE9 and tried to sign in as a different user on the other tabs I had open. All returned the 401 UNAUTHORIZED error.

I closed down all instances of IE9, cleared my cache, and fired it back up. This time all the sign in operations worked as expected. I'm not exactly sure which part of the IE session got out of whack, but starting fresh seemed to clear things up. Hopefully it's not a problem that will resurface.