Monthly Archives: September 2013

Office 365/SharePoint Online – Getting Around the List View Threshold Error

I recently ran into a scenario where an Office 365 (SharePoint Online) list was over 10,000 items and I was attempting to delete the site, only to run into the dreaded List View Threshold Hold error. There are a lot of easy ways around this on premise to get rid of this site/list such as using PowerShell, changing the LVT for administrators, turning off LVT for this list, and many more creative/fun ideas. Office 365 doesn’t really have any of these options. The best way for handling this type of scenario that I’ve found is to use a little Access magic! Fire up Access and do the following:

1. Create a new Blank Desktop Database

2. Click External Data | More (Under Import & Link) | SharePoint List

3. Type the name of the SharePoint Online site you are connecting to and select Link to the data source by creating a linked table

4. clip_image002

5. Now sign in to your Office 365 tenant and click Next. Select the list(s) you want to link to and click OK

6. clip_image004

7. Open up the table containing the SharePoint list and create a query to run against it (Click Query Design | SQL View):

8. You will get a prompt indication that the query is running against data in linked tables. Click Yes

9. You will get another prompt indicating that you are able to delete X Number of rows in the specified table. Again, Click Yes

Now check the SharePoint site and notice that it is 10 rows lighter! Change the number in the SQL query to adjust how many items you want to delete at one time. The most I’ve deleted at once is 5000

Windows Server 2012 PowerShell Web Access

PowerShell Web Access is an awesome new feature which allows you to control your server environments in the web browser. Here is a list of compatible browsers:

· Windows® Internet Explorer® for Microsoft Windows® 8.0, 9.0, and 10.0

· Mozilla Firefox® 10.0.2

· Google Chrome™ 17.0.963.56m for Windows

· Apple Safari® 5.1.2 for Windows

· Apple Safari 5.1.2 for Mac OS®

I was even able to login and execute PowerShell Commands from my Nokia Lumia 1020!

Here’s a screenshot for proof:

clip_image002

I followed the steps laid out here: http://technet.microsoft.com/en-us/library/hh831611.aspx. There were a few items that I did differently so here’s my experience:

Step 1 – Install Windows PowerShell Web Access Using PowerShell

Log into the Windows Server 2012 server you’d like to test this on. In my case this was a test SharePoint 2013 VM Running Server 2012 and SQL Server 2012. Run Windows PowerShell as an administrator and run the following command, which installs the Windows PowerShell Web Access feature (This could also be done from Server Manager, but this post is all about PowerShell so might as well keep it consistant):

Step 2 – Configure the Windows PowerShell Web Access gateway with a test certificate

Now that we’ve installed the PSWA feature onto the server, we need to create the ‘Gateway’ or IIS Web Site. This step can be as simple as running: Install-PswaWebApplication –UseTestCertificate. This will use a self-signed certificate (only recommended in dev/test environments!) and will create a new web application underneath the Default Web Site and bind the certificate to it. Here’s a list of the defaults:

· Path: /pswa

· ApplicationPool: pswa_pool

· EnabledProtocols: http

· PhysicalPath: %windir%/Web/PowerShellWebAccess/wwwroot

In my case I didn’t want to install the PSWA application under the Default Web Site. I create a new IIS Web Site Called “PSWA” and assigned it port 8001 to start. Don’t forget to give ‘Everyone’ read access to this site or you may get a 401 error.

I ran the following command after setting up the new IIS Web Site (Note: You can use the parameter WebApplicationName to change the application from /pswa to something else):

 

Step 3 – Configure a restrictive authorization rule

Now that everything is setup you should be able to browse https://servername/pswa and get to the PowerShell Web Access login screen (after a certificate warning in my scenario). Sweet! Now you try logging in and get presented with the following screen:

clip_image004

This is because we have not granted anyone access to this server yet. The Add-PswaAuthorizationRule command comes to the rescue! You can run this command for a specific user or a group of users. Just make sure the ConfigurationName parameter is correct! You can create your own session configuration or you can use a built-in one like Microsoft.PowerShell, which gives full access to all cmdlets, modules, and providers. You could essentially create a session configuration for a Server Admin and a SharePoint Admin and give them access to different sets of commands. Again, this is test so I’m giving my user full reigns.

Here’s the command to do so:

Now run the following command to check your work:

It should return something similar to this:

Id RuleName User Destination ConfigurationName

— ——– —- ———– —————–

1 Rule 1 ajb\sp13_install ajb\sp2013ent2 microsoft.power…

 

You should now be able to login using the forms login screen at https://servername/pswa!

If this is a SharePoint server I can now add the SharePoint PowerShell Snap in and start playing..Don’t have too much fun 🙂

clip_image006

SharePoint 2013 – Creating a Product-Centric Site

In preparation of a new project I decided to run through this series of Blog posts: http://blogs.technet.com/b/tothesharepoint/archive/2013/02/14/how-to-set-up-a-product-centric-web-site-in-sharepoint-2013.aspx

I highly recommend going through this series of posts if you will be working with SharePoint 2013 Publishing Sites! A lot of good stuff on Product Catalogs, Managed Metadata, Search, etc.

-AJB

SharePoint 2010 – Enable Remote BLOB Storage (FILESTREAM)

There are many great sources out there that cover this topic already, but this is my own personal experiences and any issues I ran into during this setup and configuration..and oh boy was it a fun first go around 🙂

Issues I ran into:

  1. The account you setup RBS as should have elevated access to SQL Server (In my case I temporarily granted the sp_install account sysadmin access to the SQL Server because it needed access to alter database settings)
  2. The Named Pipes protocol must be enabled in SQL Server Configuration Manager
  3. SQL Server Browser Service MUST BE STARTED or else you will get faked out and think RBS is installed, but it won’t be!

First you must enable FILESTREAM on the Database Server (Note: In my testing I had a single SharePoint/DB Server. Based on http://msdn.microsoft.com/en-us/library/cc645923.aspx):

  1. Open SQL Server Configuration Manager and right click SQL Server (MSSQLSERVER)| Properties
  2. Click the FILESTREAM tab and enter the appropriate settings
  3. clip_image001
  4. Open SQL Server Management Studio and run this query:
  5. Restart the SQL Server service

Run SQL Queries to provision a BLOB Store for the content database:

  1. Run the following queries from SQL Server Management Studio to provision a BLOB Store for the SharePoint Content Database (Note: I created the content database ahead of time. Do not create the BLOB Store location ahead of time or SQL will yell at you):

Install the RBS Client Library onto the SharePoint Server:

  1. Download 1033\x64\RBS.msi from http://www.microsoft.com/en-us/download/details.aspx?id=26728
  2. Open Command Prompt as Administrator (Change directory to file):

Check to make sure the RBS tables were created in SQL by running the following query (If they are not make sure Named Pipes is enabled and the SQL Server Browser Service is started):

If the following tables aren’t present check the RBS_Install_Log.txt for errors:

RBSTables

Previously, I was receiving this error: “Info 2769. The installer has encountered an unexpected error. The error code is 2769. Custom Action CreateFilesNoUI did not close 21 MSIHANDLEs.”

Make sure if you previously tried to install RBS and it didn’t work that you go and uninstall (using add/remove programs) and then run the msiexec command again).

Run PowerShell to enable RBS for Content Database:

Sources I used:

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

http://toddklindt.com/blog/Lists/Posts/Post.aspx?ID=174

http://technet.microsoft.com/en-us/library/ee663474(v=office.14).aspx

SharePoint 2010 Service Pack 2 – Server 2012 Support

Short and sweet here. If you want to install SharePoint 2010 on a Server 2012 machine you need to go to MSDN and grab the updated ISO. This new ISO file has updated installers (prerequisites included) that now support Windows Server 2012. A slipstreamed install with previous releases will not work like you want it to. If you do not grab the new ISO and you want SharePoint 2010 on a Server 2012 machine take a look at the steps needed to get that done: http://sharepoint.nauplius.net/2012/06/installing-sharepoint-2010-and-sql-server-2012-on-windows-server-2012-release-preview/. This is a great post by Trevor Seward, but I’d definitely rather just have it work out of the box.

-AJB