Tuesday, July 16, 2013

The URL 'Shared Documents/filename' is invalid. It may refer to a nonexistent file or folder, or refer to a valid file or folder that is not in the current Web.

After the RBS Installation was complete and I was trying to upload a file to the document library, I got this error

Sorry, something went wrong


The URL 'Shared Documents/filename' is invalid.  It may refer to a nonexistent file or folder, or refer to a valid file or folder that is not in the current Web.



Reason

Permission issue for uploading files to the content database

How to fix this

I added the application pool account for the web application as dbowner to the content DB where RBS was implemented.This fixed the issue

Pivot Table Operation Failed: An error occurred while working on the Data Model in the workbook.

Recently I got this error while I was trying to a refresh on an Excel document which had PowerPivot on it.

Pivot Table Operation Failed: An error occurred while working on the Data Model in the workbook. Please try again.


How did this happen

1) Sharepoint Farm is set up.
2) Excel Services is configured as service application
3) PowerPivot Installed and Configured to work
4) Installed Office Web Application later

What happens in this scenario?

The Excel can be opened either with Excel Services or Office Web Apps. But since Office Web Apps was installed last, it over rides the Excel Service Application features for Excel documents.
I have posted an article on the difference between Excel Services for Sharepoint and Excel Web App here.

How did I fix this?

I ran the New-SPWOPISuppressionSetting command and fixed it. The details are here.

New-SPWOPISuppressionSetting

This command is used when we need to enable Excel Services for excel workbooks and disable Excel Web App.

New-SPWOPISuppressionSetting

Below is the command to run in SharePoint 2013 Management Shell as Administrator :

New-SPWOPISuppressionSetting –Extension “XLSX” -Action “view”

New-SPWOPISuppressionSetting –Extension “XLS” -Action “view”


Excel Services in SharePoint and Excel Web App (in Office Web Apps) in SharePoint 2013

Excel Services in SharePoint

Excel Services is technology that enables you to view and interact with a workbook in a browser window similar to how you would use Excel on your computer.
Excel uses a built-in calculation engine to connect to external data and render the results of formulas and queries.
Excel Services uses server technology to provide similar functionality in a browser window.
Excel Services was introduced in Office SharePoint Server 2007.
In SharePoint Server 2013, Excel Services is available only in the Enterprise edition.

Excel Web App in Office Web Apps

Excel Services technology is available in Office Web Apps Server as Excel Web App, and in SharePoint as Excel Services.
Excel Web App and Excel Services in SharePoint have a lot in common, but they are not the same.
Both applications enable you to view workbooks in a browser window, and both enable you to interact with and explore data.


However, there are certain differences between Excel Web App and Excel Services in SharePoint.

Excel Services supports external data connections, data models, and the ability to interact with items that use data models (such as PivotChart reports, PivotTable reports and timeline controls).
Excel Web App enables users to view and interact with workbooks, but Excel Web App does not support external data connections or the ability to interact with reports and timeline controls that use data models.
Excel Services provides more business intelligence functionality than Excel Web App, but Excel Services does not enable users to create or edit workbooks in a browser window.
Excel Services is available only in the Enterprise edition of SharePoint Server 2013. Excel Web App is available in SharePoint Server 2013 and SharePoint Foundation 2013.


If your organization decides to use Excel Services instead of Excel Web App to view workbooks in the browser, you can use the Windows PowerShell New-SPWOPISuppressionSettings cmdlet to turn off Excel Web App for Excel workbooks. I have posted on that here.

Excel Services (part of SharePoint Server 2013) and Excel Web App (part of Office Web Apps Server) enable you and others to view and interact with a workbook in a browser window similar to how you would use Excel 2013 on your computer. In your environment, you might be using either Excel Services or Excel Web App, but not both at the same time. A SharePoint administrator determines which application is used to display workbooks in a browser window.

How to determine whether you are using Excel Web App or Excel Services.

One good way to determine whether Excel Services or Excel Web App is used to render a workbook is to examine the website address (URL) for the workbook.
1. Look for xlviewer in the URL
If the URL resembles               
“http://[servername]_layouts/15/xlviewer.aspx?id=/Documents/...” then Excel Services is used to render the workbook.

2. Look for WopiFrame in the URL
If the URL resembles 
“http://[servername]/_layouts/15/WopiFrame2.aspx?sourcedoc=/Documents/...”          
then Excel Web App is used to render the workbook.


 Reference: 1 , 2 




Wednesday, July 10, 2013

RBS FILESTREAM Installation for Sharepoint 2013

I am breaking down the RBS FILESTREAM Installation for SharePoint 2013 into 7 steps :

1. Enabling FILESTREAM on the database server
2. Provision a BLOB store for content database
3. Install the RBS client library on the on the first web server
4. Install the RBS client library on all additional web and application servers
5. Confirm the RBS client library installation
6. Enable RBS for the Content Database
7. Test the RBS Installation




1. Enabling FILESTREAM on the database server

Login to the SQL Server 2012 with the highest privileged account.

1. On the Start menu, point to All Programs, point to Microsoft SQL Server Code-Named 2012, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In the list of services, right-click SQL Server Services, and then click Open.
3. In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.
4. Right-click the instance, and then click Properties.
5. In the SQL Server Properties dialog box, click the FILESTREAM tab.
6. Select the Enable FILESTREAM for Transact-SQL access check box.
7. If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box [In this case it was BLOBSTORE].
8. Uncheck the Allow remote client’s access to FILESTREAM data. Click Apply.
9. In SQL Server Management Studio, click New Query to display the Query Editor.
10. In Query Editor, enter the following Transact-SQL code:

EXEC sp_configure filestream_access_level,2
RECONFIGURE

11. Click Execute.
12. Restart the SQL Server service.







2. Provision a BLOB store for content database

Note
·         The user account provisioning RBS stores must be a member of the db_owner fixed database role on each database that you are configuring RBS for.
·         For best performance, simplified troubleshooting, and as a general best practice, we recommend that you create the BLOB store on a volume that does not contain the operating system, paging files, database data, log files, or the tempdb file.

1. Click Start, click All Programs, click Microsoft SQL Server 2012, and then click SQL Server Management Studio.
2. Connect to the instance of SQL Server that hosts the content database.
3. Expand Databases.
4. Click the content database[DB1_RBSExp] for which you want to create a BLOB store, and then click New Query.
5. Paste the following SQL queries in Query pane, and then execute them in the sequence listed.

use [DB1_RBSExp]
if not exists (select * from sys.symmetric_keys where name = N'##MS_DatabaseMasterKey##')
begin
create master key encryption by password = N'1zqa2xws!'
Select 1
end

use [DB1_RBSExp]
if not exists (select groupname from sysfilegroups where groupname=N'RBSFilestreamProvider')
alter database [DB1_RBSExp] add filegroup RBSFilestreamProvider contains filestream


use [DB1_RBSExp]
alter database [DB1_RBSExp]
add file (name = RBSFilestreamFile, filename = 'E:\BlobStore') to filegroup RBSFilestreamProvider

6. Blob folder was created in E:\BlobStore.


Note
Download the file: ENU\x64\RBS.msi
  •  RBS client library should be installed on all servers in the SharePoint farm. The RBS client library is installed only one time per web server, but RBS is configured separately for each associated content database. The client library consists of a client-side dynamic link library (DLL) that is linked into a user application, and a set of stored procedures that are installed on SQL Server.
  • Do not install the RBS client library by running the RBS.msi file and starting the Install SQL Remote BLOB Storage wizard. The wizard sets certain default values that are not recommended for SharePoint 2013.







3. Install the RBS client library on the on the first web server

1. Login to WebServer1 as domain\sp_setup
2. Copy the RBS executable to C:\RBSClient\
3. Click Start, click Run, type cmd into the Run text box, and then click OK. Browse to C:\RBSClient\.
4. Run the below command
msiexec /qn /lvx* rbs_install_log.txt /i RBS.msi TRUSTSERVERCERTIFICATE=true FILEGROUP=PRIMARY DBNAME=" DB1_RBSExp" DBINSTANCE="DBSERVERNAME" FILESTREAMFILEGROUP=RBSFilestreamProvider FILESTREAMSTORENAME=FilestreamProvider_1

5. After this step verify that the RBS component is installed on the server by going to Control Panel.






4. Install the RBS client library on all additional web and application servers

Note - Confirm that the user account performing these steps is a member of the Administrators group on the computer where you are installing the library. [domain\sp_setup]

1. Login to the other servers (WebServers and AppServers) in the farm servers as domain\sp_setup .
2. Copy the RBS executable to C:\RBSClient\ 
3. Click Start, click Run, type cmd into the Run text box, and then click OK. 
4. Run the below command
msiexec /qn /lvx* rbs_install_log.txt /i RBS.msi DBNAME=" DB1_RBSExp" DBINSTANCE="DBSERVERNAME" ADDLOCAL=Client,Docs,Maintainer,ServerScript,FilestreamClient,FilestreamServer








5. Confirm the RBS client library installation

 1. The rbs_install_log.txt log file is created in the same location as the RBS.msi file. Open the rbs_install_log.txt log file by using a text editor and scroll toward the bottom of the file. Within the last 20 lines of the end of the file, an entry should read as follows:
Product: SQL Remote Blob Storage – Installation completed successfully.
2. On the computer that is running SQL Server 2012, verify that the RBS tables were created in the content database. Several tables should be listed under the content database[DB1_RBSExp] that have names that are preceded by the letters "mssqlrbs".
            








6. Enable RBS for the Content Database

1. Login to WebServer1 as domain\sp_setup

Note - The user account enabling RBS must have sufficient permissions to run Windows PowerShell.

2. Open SharePoint 2013 Management Shell as Administrator and perform the steps below:

$cdb = Get-SPContentDatabase DB1_RBSExp
$rbss = $cdb.RemoteBlobStorageSettings
$rbss.Installed()  [returns true]
$rbss.Enable()
$rbss.SetActiveProviderName($rbss.GetProviderNames()[0])


Executing $rbss.Installed() should return true, if false this may be due to the reason that RBS is not installed properly or RBS is not installed on other Web servers.


3. Type $rbss and verify that ActiveProviderName is not Null. See below.







7. Test the RBS Installation

1. Open the Site Collection where blob store is enabled.
2. Open a library or list where we can upload documents. Upload a document whose size limit is more than 100 KB.
3. Browse to Blob Store Folder on the SQL server and verify the latest modified folder. The document should be present in it.

Note

·         The SharePoint Set up account (domain\sp_setup) was made as sysadmin before running the RBS.msi.
·         Added SharePoint Set up account (domain\sp_setup) as dbowner for RBS content database
·         There were issues to upload the file to site after RBS Installation. To fix this issue, the Web Applications’ Application Pool Account (domain\sp_web) was made the dbowner for the content database where RBS was activated
·         The SharePoint Setup account (domain\sp_setup) was added to the local administrator group on the SQL DB Server
·         Average size of a BLOB is between 60-65 KB


After uploading the first document to your RBS Enabled Site collection, this is what you see in your content database tables.


Friday, July 5, 2013

PowerPivot Configuration for SharePoint 2013


Installing the Power PowerPivot for SharePoint Add-in

This post is continued from here - PowerPivot Installation for SharePoint 2013

1. PowerPivot for SharePoint Add-in (spPowerpivot.msi) - Install the spPowerpivot.msi on all Sharepoint servers in the farm.Copy the install file (\\SQL Server\Enterprise Edition\SQL Server 2012 EE with SP1\PCUSOURCE\1033_ENU_LP\x64\Setup) to all the servers in the farm.Click Next.


2. Click I accept. Click Next.

3. Click Next

4. Click Install.

5. Click Finish

6. These two icons appear after the install of the spPowerPivot.msi
PowerPivot Configuration Tool (only on the servers where POWERPIVOT was installed.)
PowerPivot for SharePoint 2013 Configuration (appears in all servers)

7. Click On Start ->PowerPivot for SharePoint 2013 Configuration. Click OK.



8. Click on all the options on the left to make sure that nothing is left blank.




9. For the secure Store Service master key also use the same passphrase as the farm (that is what I did .Easy to remember J).
Please note that the Target Application ID is PowerPivotUnattended Account [PowerPivotUnattendedAccount and domain\s_sp_sql]




10. Click Validate.

11. Click OK and Run

12. Click Yes.

13. Progress shown

14. Click OK .Don't activate at site collection level. Permission is needed for that. Uncheck that part .Close it and run the whole thing once more and manually uncheck the Activate PowerPivot Feature in a Site Collection and check all others.

15. Click OK and click Exit.

Note The reason why Activate PowerPivot Feature in a Site Collection fails is that the account [domain\s_sp_sql] is not having the permission on the site collection

16. Browse to Central Administration ->Services on the Server. PowerPivot is started

17. Browse to Central Administration ->Manage Service application. PowerPivot is listed there.
18. Activate the feature at site collection level


PowerPivot Installation for SharePoint 2013

Installing PowerPivot

1. Login to the server where you want install PowerPivot. In this case I initially installed on one of the Central Administration server . Login to the server as the Setup account (domain\s_sp_setup)

2. Copy the SQL Install files on to the server where you want to install PowerPivot in your farm.(\\SQL Server\Enterprise Edition\SQL Server 2012 EE with SP1)

3. Click on setup.exe

4. Select Installation on the left side of the SQL Server Installation Center.

5. Select New SQL Server stand-alone installation or add features to an existing installation.

6. The below screen comes up saying all passed. Click View detailed report to see the details. Click OK.

7. Click Next

8. Select I accept the license and Click Next.

9. Uncheck the Include SQL Server product updates. Click Next.






10. Click OK and proceed.

11. Click Next.

12. Choose SQL Server PowerPivot for SharePoint. Uncheck the below option and click Next.

13. Click Next

14. Click Next

15. Keep the instance as POWERPIVOT. Click Next


16. Click Next

17. Using domain\s_sp_sql as the account here. [I wanted to have separate account for PowerPivot services] Click Next. Use the same account for one more installation of Analysis server on the same farm.

18. Add the s_sp_setup by clicking Add Current User. Click Next. Both s_sp_sql and s_sp_setup has same level of permissions. But s_sp_sql will run the SSAS service.

19. Click Next.

20. Click Next

21. Click Install.




Progress is shown below

22. Click OK

23. Click Close.

24. Go to services.msc. You will see SQL Server Analysis Services (POWERPIVOT) running under SQL Account [domain\s_sp_sql]. Restart the machine.


This post is continued here - PowerPivot Configurationfor SharePoint 2013