SharePoint 2010 list suddenly could not export to Excel

Few weeks ago, a site administrator called me for help . He has a list with only 350 rows and he could not export data recently. When I did investigation and compare to other lists, I could export another list with more than 4000 rows. When I tested this particular one, I could export up to 100, but not 101 or more. When this happened, Excel would freeze for a while and then display a pretty vague error message.

There was no errors logs on the server side, nor on the local machine. Since I was able to export another list with lot more rows, this has nothing to do with SharePoint or Excel.

I kept looking around and found out the list was customized with InfoPath. I open the form template with client and noticed that there were changes made to the list, but has not implemented to the form template.

From my prior reading on-line, there were posts mentioned that Excel exporting feature is pretty picky. Since the list was changed, I asked the site administrator to refresh and re-publish the for template. Once he did that, he could export the whole list without any errors.

Next time when you encounter issues like this one, check whether the list is customized with InfoPath and whether the template is up to date.

SharePoint 2013 User Profile Service requires SQL 2008 R2 Native Client

User Profile Service is one of the service that needs additional attention and exact steps to plan and configure it. If you miss any steps during the configuration, you may have to delete it and start all over again. The best information on line to take you through the process is still the one from SharePoint 2010 time:Rational Guide to implementing SharePoint Server 2010 User Profile Synchronization.

Even though you may follow his and other articles precisely., you may still run into few issues. One of them I have experienced recently and took me two weeks to figure it out.

The environment I am configuring is using SQL 2014 with Windows 2012 R2. When I configured User Profile Service and started first sync, everything ran correctly. Two weeks later, I found out that the service did not run any more. I tried few times, not working. I reviewed my steps, change servers and check databases, no progress. The problem I had was: after I created User Profile Service Application, I could start User Profile Service from CA, but not Sync Service. When I reviewed the Services console, both FIM and FIM Sync service were disabled.

I tried to manually start FIM service and got an error message as following:

.Net SqlClient Data Provider: System.Data.SqlClient.SqlException: Could not find stored procedure 'RegisterService'.

I also noticed that the Sync database did not have any table or stored procedures populated. I tried to find more information regarding why this database was not created correctly when Profile and Social databases were provisioned correctly? Some articles mentioned that the Farm Admin account (the one is running CA application pool if you have separated setup and farm admin accounts) should own the DBO schema. However, this is not the case to me.

After a while, I was told that perhaps I should check my SQL native client installation. From an article regarding User Profile Service with SQL 2012, I noticed that SQL 2008 R2 native client may be required even though I am using SQL 2014. I also recalled that I have uninstalled SQL 2008 R2 native client after the User Profile Service was provisioned successfully. 

I went to re-install SQL 2008 R2 native client to all of my SharePoint 2013 servers. Finally I could create this service successfully. During the last provision, I had chance to observe how the service and databases were populated. I will included them at the end of the blog. So, even though you are using SQL 2014, you still need SQL 2008 R2 native client to create and run User Profile Service. So, do un-install it like I did.

The service provision process is here:

1. When I created the service application, all three databases were created. Sync DB was empty. No tables nor stored s[procedures.
2. When I started User Profile service from CA, nothing really changed.
3.  When I started User Profile Sync Services from CA, things changed like this:

a.      FIM Synch service changed from disabled to automatic first.

b.      FIM Synch service started.

c.      Sync DB started having tables and stored procedures populated.

d.      FIM service changed from disabled to automatic

e.      FIM service started.

Enable Windows 2012 R2 Features for SharePoint 2013 SP1

The information regarding installing SharePoint 2013 Sp1 on Windows 2012 R2 is limited, especially what do you need to do an offline installation. In our case, we have Windows 2012 R2 configured with a standard configuration from the server team and deliver to us. These servers have no internet connection at all. So we have search on line and found some good articles to start with. Here are the ones I have referenced to:

http://blogs.technet.com/b/cjrawson/archive/2014/04/06/offline_2d00_sharepoint2013sp1_2d00_prerequisites_2d00_server2012r2.aspx

http://social.technet.microsoft.com/wiki/contents/articles/14582.sharepoint-2013-install-prerequisites-offline-or-manually-on-windows-server-2012-a-comprehensive-guide.aspx

http://soussi-imed.over-blog.com/article-installing-sharepoint-2013-on-windows-server-2012-r2-120547920.html

Before you run the Prerequisite, you need to configure server roles and enable some features. If you can run the feature-enable PowerShell script from those articles, congratulations!! In our case, we could not.

So, we have to manually using UI to enable required roles and features. All of the articles I have read so far mentioned following features are needed:

Roles

• Application Server
• Web Server (IIS)
• IIS 6 Management Console

Features   

• ASP.NET 4.5
• Windows Identity Foundation 3.5

During the process, you also need to enable IIS Support (separate item to check the check box). There are few required features needed that would install together.

We followed and enabled all of them in one of our servers. However, Prerequisite returned en error regarding it could not configure IIS. After looking into the error log, we found out Prerequisite was looking .NET 3.5, which was not mentioned in any of the articles we found.

Once we have .NET 3.5 enabled, it went through like a charm.

I believe sometime when we put down some information to share, we may accidentally assumed something was there or available. In this case. .NET 3.5 was needed but not mentioned.

Before you start your journey, make sure .NET 3.5 is also enabled with your Windows 2012 R2 servers.

Good luck!

SharePoint User Profile Services and Different Domain Names

If your environment has User Profile Service, have you noticed that whether all users are under the same domain? In our case, we were not and it created other issues when we want to use profile data. For example, when we want to query a user profile data, we don't know which domain this user was mapping to and it is hard to predict. In order to fix this problem, we have to fix this problem and have all users under the same Net BIOS domain name.

There are articles to show you how to do it. For example, you can find steps from this blog: http://tremblayse.wordpress.com/2011/10/17/ups-and-netbios-domain-name-different-then-fqdn-domain-name/. Since there are enough information on-line already, I would like to just mention our experience when we perform this change.

1. Make sure you have tested it in a non-production environment and document all your steps. This is very important to help you when you implement it in your production environment.
2. We have User Profile Service been running for a while. Some articles mentioned that you may want to delete it first. From my opinion, you don't have to, especially if your environment has large number of users or they have updated/added information to their profiles.
3. Before you start making changes, make sure both services are running. If you are not sure their status, you may want to go to the Central Administration site and restart both of them, one at a time.
4. After using PowerShell to enable NetBiosDomainNamesEnabled attribute, run the command with "True" to confirm it is up and running.
5. Reset IIS and restart timer job services. I did not do it in the test environment but had to do it in production one.
6. Some article mentioned that you can delete the profile sync connection before you executing the PowerSheel command. I did not do this first and you may want consider to delete it first. However, make sure you have a screen shot of the connection settings page before you delete it.
7. When you delete the connection and failed, don't panic and just restart both profile services again from the Central Administration site. Try to delete the connection again. If it is not there after you restart both services, you are fine.
8. After the new connection is created, run full sync once and give it several night to verify the number of users are imported. If the number does not seem to be right, make sure you have selected all OUs that have users. Rerun full sync if necessary.

The whole process may take over night to import and re-process all profile data. When it is done, you should be able to find all users are mapping to the Net BIOS domain name.

Hope this will help you to go through the changes smoother.

Account Permissions Needed to Integrate SSRS with SharePoint 2010 in Separated Machines

When Microsoft released SharePoint 2010 and SSRS 2008 R2, the integration process is lot smoother and easier then before. There are tons of information on-line to show you how to do it. I can easily find how to plan the environment, what kind of service accounts and permissions I need, how do I configure in different scenarios.

So, with so much information, I should be able to do it easily without too much trouble. Well, not exactly. One of the problems I have experienced in the past years with SharePoint configuration information is, a high percentage of them out there have assumed something but were not described clearly. For example, when I tried to configure MOSS 2007 to integrate with SSRS 2005, most of the articles I found at the time assumed that your environment was using Kerberos. However, we did not use Kerberos so it's hard to find out what should be done. This is no one's fault and it's almost impossible to put every possible scenarios into an articles. We just need to keep this in mind when we do our research.

The environment I was trying to work on was:
One WFE
One SSRS without database
One database server with all SharePoint databases

The service accounts I have are:
One Farm setup and admin account, which has DB Creator and Security Admin role. It does not have any other roles to the database system and it could not drop any databases.
One SSRS service account, which has no other rights in the database or farm.

The farm setup and admin account had created databases for web and service applications. When I logged on to the SSRS server using the farm admin account and started the SSRS Configuration tool, I had no problems to add SSRS service account and create web application for Report Server. I came to an error messages when I executed script to generate two databases for SSRS service. Though the databases were created but the whole process stopped and returned Insufficient Permissions errors.

I tried to add the SSRS service account with DBO role to the two newly created databases, I was not able to connect them through the SSRS configuration tool again. I got different error message said these two databases were not in the correct format.
I have followed the articles I can find regarding service account requirements and still not able to figure out why. Then I recalled that when we configured MOSS and SSRS 2005 integration, we needed farm admin account to have database System Admin role to the instance. Usually this is only assigned to the service accounts used by DBA team and not service accounts used by applications. I asked for this role in our instance and everything went through without another errors.

As far as I could recall, I haven't read an article mentioned this role or I may not do a good job when I researched it. Any way, this is my experience and if you run into errors when integrate SSRS with SharePoint 2010, give this a try.

Install SQL 2008 Express Management Studio

If you are using standalone instance of SharePoint 2010 and want to add SQL Express 2008 Management Studio to the machine, you may encounter errors during installation. The option looks valid is to add feature to current instance. however, it is not the correct one.

After some search on line, I found this blog with detail information Turns out you need to select New Instance option (I have no idea why) to continue. So if you were not able to install management studio for SQL Express 2008, try this article first.

Where is the Datasheet View in SharePoint 2010

If you have installed Office 2010 64-bit to a 64-bit OS machine, you may find out you are not able to use Datasheet view in a library or list. This will happen to users who is working on a single machine or VM environment with SharePoint 2010 and Office 2010 installed together.

So, what can you do?

According to Microsoft, you can install Office 2010 32-bit instead. The other option is mentioned in this blog, install Access Runtime for 2007 or 2010. When that blog was published, Access Runtime 2010 is still in Beta version. Now you download the full version from here.