Thursday, January 30, 2014

#PowerPivot refresh schedule disappears after creating new version of #excel file on #SP2013 #help

The challenge is as follows:
When you create a new version of your PowerPivot excel file and you load it to SharePoint 2013, the Data Refresh Schedule you had configured is now gone.. As soon as there is a new version (or a new file with versions disabled) the schedule is gone and you have to re-create it.
This problem only seems to happen on SharePoint 2013, SharePoint 2010 keeps a reference to the selected schedule after updating the excel document.

The problem visual:
A Power Pivot Excel document with a Schedule enabled:





Schedule is enabled:

Make a new version & save document:

Overwrite existing file:

Check PowerPivot Data Refresh:

Gone ...



No fix yet, will update this post when I find it !
(Suggestions are welcome ..) 

4 Feb Update:
I found the Microsoft KB that describes this issue (Sql Bug 1377755)  in the CU6 update for SQL Server 2012:
http://support.microsoft.com/kb/2874957/en-us

I installed the CU8 but so far no luck..the issue is still there!

5 Feb Update:
Posted question on the Microsoft PowerPivot for SharePoint forum

15 April Update:
It seems that "psiodmakFuture-Processing" found the cause of this bug:
We have this problem as well.

I think I found a possible cause. Try the following script:

SPWeb http://yoursharepoint.com).GetFile(path/to/file.xlsx).Properties['ppDRSchedule']

for a working file it should spew out an escaped XML. It's your schedule that was serialized and cached here probably to speed things up. When you save the document with Excel, this value is trimmed to 255 characters, rendering it broken, so the UI (and the timerjob) thinks that the schedule is missing. This might as well be a bug in Excel.

Now we just need a solution from Microsoft :)

23 April Update:
CU 9 for SQL Sever 2012 SP1 is now downloadable, let's give it a try and see if this resolves the outstanding issue...



Wednesday, January 22, 2014

Quick tool: Dev / Demo / Test environment in the cloud

When I got the question to create a proof-of-concept for exporting the number of emails in custom Microsoft-Outlook (exchange) folders, my thoughts went straight back to the period where I used a cloud solution for my team of developers because IT couldn't provide us with a Team Foundation Server.

On http://www.cloudshare.com you can create an environment with one or more VM's which you then can access via Remote Desktop (or your browser). The nice thing of this cloud solution is that they have several prepared VM's which you can "spin up" in a couple of minutes !
One of these VM's is a Microsoft Exchange 2010 SP3 environment (Exchange installed AND CONFIGURED, AD installed & configured etc.) but they have more ! :
(As per 22 January 2014)

CentOS 5 W/ MySQL
CentOS 5.4 Server
CentOS 5.8 Server
CentOS 6 Server
CentOS 6.3 Server
Dynamics CRM Server 2011
Exchange Server 2010 SP1
Exchange Server 2010 SP3
Exchange Server 2013 RTM
Microsoft Forefront TMG 2010
PostgreSQL 9.1
Ruby On Rails
SharePoint 2007
SharePoint 2007 Additional Server Bits Only
SharePoint 2010 Additional Server Bits Only
SharePoint 2010 Enterprise - No Configuration
SharePoint 2010 Enterprise SP1
SharePoint 2010 Enterprise SP1 W/ Duet
SharePoint 2010 Enterprise SP1 W/ SQL 2012 and BI Stack
SharePoint 2010 SP1 (Information Worker)
SharePoint 2010 SP1 Enterprise W/ FAST Search and SQL Server 2012
SharePoint 2010 SP1 Enterprise W/ SQL Server 2012
SharePoint 2010 SP1 Standard W/ SQL 2012
SharePoint 2010 SP1 W/ FAST Search Server
SharePoint 2010 SP1 W/ PowerPivot
SharePoint 2010 SP1 W/ Project Server
SharePoint 2010 Standard - No Configuration
SharePoint 2010 Standard SP1
SharePoint 2010 W/ Fast for SharePoint Additional Server Bits Only
SharePoint 2013 Additional Server Bits Only
SharePoint 2013 with October 2013 Update
SharePoint Foundation 2010 SP1
SharePoint Foundation 2010 SP1 W/ Dev Tools
SharePoint Server 2013
SQL Server 2008 R2 SP1 Standard
SQL Server 2012 Standard
SQL Server 2014 CTP2
Team Foundation Server 2010
Team Foundation Server 2012
Team Foundation Server 2013
Ubuntu 10.04 Desktop
Ubuntu 10.04 Server
Ubuntu 12.04.3 Desktop
Ubuntu 12.04.3 Server LTS 64 bit
Ubuntu 12.04.3 Server LTS 64 bit /W Docker 0.7
Ubuntu 13.10 Server 64 bit
Windows 2003 R2 W/ Oracle 11g
Windows Server 2003 R2 Enterprise
Windows Server 2008 R2 x64 Enterprise SP1
Windows Server 2008 R2 x64 Enterprise SP1 W/ Active Directory
Windows Server 2008 R2 x64 Enterprise SP1 W/ IIS Server
Windows Server 2008 R2 x64 Standard
Windows Server 2008 SP2 x86 Standard
Windows Server 2008 x64 W/ SQL Server 2012 SP1 BI Edition
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Standard W/ Visual Studio 2013
Windows Server 2012 x64 Standard
Windows Server 2012 x64 W/ Active Directory
Windows Server 2012 x64 W/ SQL Server 2012 SP1
Windows Server 2012 x64 W/ SQL Server 2012 SP1 BI Edition
Xubuntu 8

Each of these VM's ready to spin in a couple of minutes.
The nice thing of this solution is that you can actually invite people (developers or customers) to join-in on the environment and start testing or watching what you build.
You get a free and full-functional trial period which makes things even faster. Imagine you suddenly got that invite at the biggest customer ever, you need that PowerPivot demo area for yourself and ready NOW!!
You can do it, with cloudshare.com !!

Other than RDP'ing to this environment (You'll get a local-admin account & password), you can also use "Cloud Folders' to FTP from and to the VM. This way you can actually export your code / results from the cloud to your own machine.
SnapShot's are also available, so no more re-doing all that hard work :)

Some caveats:
- In the trial period, your environment will shut-down automatically when you don't use it. When you are using it, you can "extend" the period before it goes down. This makes sure that cloudshare is not using valuable resources for nothing :P When cloudshare terminates your environment you can "spin it up" at any time again, it's right where you left it !
- You are depending on your connection to the cloud.. no internet = no environment
- Make sure you look at all pricing options, depending what you are going to do with the tool