tag:blogger.com,1999:blog-367427932024-03-12T17:49:39.227-07:00schoennieschoennies blogjeShuwihttp://www.blogger.com/profile/18225167405815820927noreply@blogger.comBlogger59125tag:blogger.com,1999:blog-36742793.post-52700761472550705612019-06-20T04:22:00.002-07:002019-06-20T04:22:20.498-07:00Ouch.. turns out that moving the ManagementReporterDM DB to SSD disks wasn't such a great idea..I looked left and right to solve this error I got the morning after migrating all SQL Databases to their new fast shiny SSD location:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjKGnvALUs51KkT4ilyA4j_pVL7ji8Wgp8PBVMYS9L1E3tBBh1ImbUxYpzOLORmwODZGRmqC54xr1inwBEJDGDTGq66AZID1-NifXVwOV9umP4-_pR1gTh49F9iuXzb8DTqPENmoA/s1600/Annotation+2019-06-20+131019.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="337" data-original-width="1023" height="131" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjKGnvALUs51KkT4ilyA4j_pVL7ji8Wgp8PBVMYS9L1E3tBBh1ImbUxYpzOLORmwODZGRmqC54xr1inwBEJDGDTGq66AZID1-NifXVwOV9umP4-_pR1gTh49F9iuXzb8DTqPENmoA/s400/Annotation+2019-06-20+131019.jpg" width="400" /></a></div>
After starting the "Management
reporter 2012 process service" and receiving the "Service started successfully" message it took about 4 seconds for the other 4 errors to occur. "An error occurred while executing the command definition. see the inner exception for details."<br />
<br />Googling on that in combination with AX2012 R2 didn't give any meaningful results. Even the <a href="https://support.microsoft.com/en-us/help/2862020/error-message-when-you-start-microsoft-management-reporter-2012-can-t?wa=wsignin1.0" target="_blank">mega AX MR solutions page</a> didn't have an answer for this one...<br />
<br />
I checked all permissions <a href="https://cdn2.hubspot.net/hubfs/3077395/Documents/GP%20-%20Management%20Reporter/MRforDynERPInstGuide.pdf" target="_blank">(and permission documentation)</a>, everything was 100% same as a working acceptance environment. No details in any logging... Permissions should be the same as I only updated the .LDF & .MDF locations using:<br />
<br />ALTER DATABASE ManagementReporterDM_PROD MODIFY FILE ( NAME = '<filename>', FILENAME = '<newdrive&path&filename>.mdf' );<br />
<br />While de DB was offline off course (ALTER DATABASE ManagementReporterDM_PROD SET OFFLINE;. )<br />
<br />
I did not change any permissions here..!<br />
<div>
<br /></div>
<div>
Until... the google query "management reporter 2012 detach" brought me to this interesting article:</div>
<div>
<a href="https://support.microsoft.com/en-gb/help/2744330/error-messages-when-you-restore-a-microsoft-management-reporter-2012-d">https://support.microsoft.com/en-gb/help/2744330/error-messages-when-you-restore-a-microsoft-management-reporter-2012-d</a></div>
<div>
<br /></div>
<div>
After removing (detaching & renaming the MDF & LDF) I asked the AX Admin to reinstall the RM DB from AX and... Everything worked again. </div>
<div>
<br /></div>
<div>
Hope the exact error and tags in this blog can help others that might face this issue in the future!</div>
Shuwihttp://www.blogger.com/profile/18225167405815820927noreply@blogger.com0tag:blogger.com,1999:blog-36742793.post-48110400398879155602018-11-22T00:30:00.003-08:002018-11-22T00:48:22.767-08:00Using Office 365 Groups PowerShell to manage Power BI Workspace members Imagine the following scenario:<br />
As the IT admin for an organization Office 365 management is part of your job.<br />
You're introducing Microsoft Teams in the organization and people are getting enthusiastic, requesting loads and loads of new Microsoft Teams sites.<br />
<br />
Power BI workspaces are used to create and share Power BI reports. Every Power BI workspace is creating an Office 365 group and every Office 365 group is creating a Power BI workspace. What a fantastic happy connected Microsoft Cloud world we live in... When everything stays connected and works that is..<br />
<br />
I'm not sure how the Office 365 group was initiated.. via the Power BI workspace or via the Teams site. I do know I didn't want to be a team owner anymore. As an admin I guided the team, provisioned their team one-note, plan etc. Now the team started communicating and @mention the group name. I'm receiving team related as an administrator, the time had come to 'leave the team'. (Teams function in member management). This action triggers some Microsoft internal workflows and eventually it will remove my full membership from the Office 365 Group. This was no problem for me until one (Monday) morning I receive an incident: "The Power BI dataset is not refreshing".<br />
<br />
No worries mate! I'll quickly login to the Power BI workspace and investigate the issue, maybe even trigger the refresh manually. Not! Because I left the Teams Team and lost the Office 365 Groups membership I was no longer part of the Power BI workspace members, result: Unable to find or manage the workspace. Of course there is the Office 365 groups management from the Azure AD portal. Quickly find the group, add myself as owner and wait. Wait a bit longer, wait several hours and surely after 2.5 hours I can see the teams team again. Unfortunately I still can't see the Power BI workspace. Trying to add/ remove myself again (as owner of the group), nothing works. My colleague who still is a member of the workspace sees my account listed as 'admin'! But I can't see/ access the workspace. When he sends me the link I get an access denied!<br />
<br />
24 hours later, still no access and I need to think of something. Something clearly doesn't function in the inner workings of the Office 365 Groups Permission management, updating the Groups connected objects like Teams, PBI workspaces etc. etc.<br />
I decide to use the Groups PowerShell module to execute the same permission change as I tried to execute in the Azure AD portal. Groups management is done via the Exchange Online PS module so I create a connection:<br />
<br />
<div lang="en-GB" style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div lang="en-GB" style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
1: $UserCredential
= Get-Credential</div>
<div lang="en-GB" style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div lang="en-GB" style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
2: $Session
= New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri <a href="https://outlook.office365.com/powershell-liveid/">https://outlook.office365.com/powershell-liveid/</a>
-Credential $UserCredential -Authentication Basic -AllowRedirection</div>
<div lang="en-GB" style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div lang="en-GB" style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
3: Import-PSSession
$Session -DisableNameChecking</div>
<div lang="en-GB" style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div lang="en-GB" style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Then using the Add-UnifiedGroupLinks command I try to add myself as an Owner of the group, I receive a error: </div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEggeAA2_GtBlor46csO-Rfp1NBjB8YSn8YQMq3Hc1ZZl7cVf1xiHre53jibtKT-KlnEMyoHAg3yq_8JPU2hz2mOXZvZBKRt5PcQ1enCNRnNvkmqMvsJrsWZU_YD3VxccHB6mpQjBA/s1600/groups-blog-1.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="184" data-original-width="1050" height="112" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEggeAA2_GtBlor46csO-Rfp1NBjB8YSn8YQMq3Hc1ZZl7cVf1xiHre53jibtKT-KlnEMyoHAg3yq_8JPU2hz2mOXZvZBKRt5PcQ1enCNRnNvkmqMvsJrsWZU_YD3VxccHB6mpQjBA/s640/groups-blog-1.jpg" width="640" /></a></div>
<div lang="en-GB" style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<br />
<br />
PowerShell indicates I'm not listed in the group membership at all! AND I first need to add myself as a member before I can be added as an owner. I know this works the same way in Teams but the Azure AD Portal does not warn about this at all. It allows you to immediately add an account as owner of the group. This might be the reason why it's not functional from the Azure AD Portal?!<br />
<br />
Surely, a few minutes after executing the PowerShell comands above I gained full access to the Power BI workspace and am able to help them out again! (Unfortunately this also means I'll be receiving the Teams @mention group-name again.. Need to figure out how I can fix that :)<br />
<br />
<br />Shuwihttp://www.blogger.com/profile/18225167405815820927noreply@blogger.com2tag:blogger.com,1999:blog-36742793.post-56083030111062635812018-11-01T01:41:00.001-07:002018-11-22T00:39:45.368-08:00Thousand Separator and Decimal Separator in Power BI Desktop, Chrome, IE & EdgeRecently I got the question from one of the Power BI report users why the currency formatting was 'wrong' in all reports.<br />
Not sure what changed, I went investigating and discovered it was about the way millions of euros were shown in the report. We in the Netherlands <a href="https://en.wikipedia.org/wiki/Decimal_separator" target="_blank">are used </a>to separate thousands using a "dot" or "."<br />
E.g.:<br />
<br />
But Power BI keeps showing comma's for example - 1,234,567.89 thousand separator is a comma and decimal separator is a comma).<br />
<br />
Very soon I found <a href="https://community.powerbi.com/t5/Desktop/Thousand-Separator-and-Decimal-Separator/m-p/555216/highlight/false#M261507" target="_blank">this post </a>on the great Power BI Community stating to change the Locale setting in Power BI Desktop. This had no effect for me, even after restarting Power BI Desktop (a couple of times); No matter what Locale I choose, the display was containing commas.<br />
<br />
Then I found a post stating to change the Locale setting in Windows 10 - Control Panel - Clock and Region - Region settings e.g:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgamdTy109yDXQAZcJ6ceJBbTDcE5AXmXAf9mo8nEkGB4NdQQuA3QysUrmisqGq-TdWcTHK_qm_UnSZoRV6sA3fa0YFmjP1YzR3iTUtomoUnTz1tzs07__xuIhHsoDdY8vfspjzVw/s1600/tmp_pbi2.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="758" data-original-width="898" height="337" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgamdTy109yDXQAZcJ6ceJBbTDcE5AXmXAf9mo8nEkGB4NdQQuA3QysUrmisqGq-TdWcTHK_qm_UnSZoRV6sA3fa0YFmjP1YzR3iTUtomoUnTz1tzs07__xuIhHsoDdY8vfspjzVw/s400/tmp_pbi2.JPG" width="400" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
As you can see, somewhere during the installation of Windows 10 I choose the format "English (Netherlands)". The preview screen doesn't show the currency format, only the information around dates. So I clicked the "Additional settings" button in the bottom. Here you can change/ customize the Decimal symbol for both 'Numbers' and 'Currency'. In Currency you can even choose the 'Digit grouping symbol':<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhn8p-UGlMjKJTG0jqrvlo9i4DmW0ix2UZZYdzOv2g8c5BkaLIZ7SzzGA5VgTsSjlg3jr9eCD4mLdVAFIEBvrM10rZ9rD-mH0UDPobqhcXB6HMslZrFuEPODJmiWykfNFPjFPpcdg/s1600/tmp_pbi3.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="664" data-original-width="513" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhn8p-UGlMjKJTG0jqrvlo9i4DmW0ix2UZZYdzOv2g8c5BkaLIZ7SzzGA5VgTsSjlg3jr9eCD4mLdVAFIEBvrM10rZ9rD-mH0UDPobqhcXB6HMslZrFuEPODJmiWykfNFPjFPpcdg/s400/tmp_pbi3.JPG" width="308" /></a></div>
<br />
<br />
The problem is ... when you start customizing this.. these changes are not reflected in Power BI Desktop reports. Only the selected 'Format' is reflecting in Power BI.<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
E.g. when I change the 'Decimal Symbol' value from a comma to a dot in above screen, Power BI desktop will still show both comma's E 4,100,235 (Using the English (Netherlands) format):<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg8sw7WnnBe5F-jmw4ZCHEDU2xgaDOaiUexWCwEgag_vWkAzAr-aiaMMl0pF4QG0V3adwyGy-z7crh1fPLy6MRAvRUSDDGd1bBdx6SUF6oWhKP3dgnY7nQs2yYjAUQk21BXs3rYFw/s1600/tmp_pbi4.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="216" data-original-width="485" height="177" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg8sw7WnnBe5F-jmw4ZCHEDU2xgaDOaiUexWCwEgag_vWkAzAr-aiaMMl0pF4QG0V3adwyGy-z7crh1fPLy6MRAvRUSDDGd1bBdx6SUF6oWhKP3dgnY7nQs2yYjAUQk21BXs3rYFw/s400/tmp_pbi4.JPG" width="400" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
But when I change the Region -> Format Locale to "Dutch (Netherlands)", Power BI Desktop (after restart) updates the changes immediately:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJQCI5XLenkHRGVQ8WeG5mA__94VHAH2p2S0tEcp7Js9jWTNqN8eEiskpOz-0Q_Q3DfTT-mKVg2kwa6CFc5vixA62eLypQjDapX3xLQWNGRNV6W6RxnDmGFQtS1xVm8N3NMuFCkw/s1600/tmp_pbi5.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="55" data-original-width="178" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJQCI5XLenkHRGVQ8WeG5mA__94VHAH2p2S0tEcp7Js9jWTNqN8eEiskpOz-0Q_Q3DfTT-mKVg2kwa6CFc5vixA62eLypQjDapX3xLQWNGRNV6W6RxnDmGFQtS1xVm8N3NMuFCkw/s1600/tmp_pbi5.JPG" /></a></div>
<br />
<br />
<br />
<br />
So my tip is to stay away from the "Additional settings" configuration to change decimal symbol information; these changes won't reflect into your Power BI Desktop reports. Use the desired "Format" to use the desired format options:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhEK9raLNDmlQHj-fCFNK4HVEEcNcqNPU9VUhCsoaM1XKXNawsV6xo04-LSOGM5L6tXstDubl5ryd1G-BI-ssiiJ1a0nnQIJBfbBLglKCV4Vz60P3oxFH8_M7UwE2WSWHKRED_fGA/s1600/tmp_pbi.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="545" data-original-width="498" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhEK9raLNDmlQHj-fCFNK4HVEEcNcqNPU9VUhCsoaM1XKXNawsV6xo04-LSOGM5L6tXstDubl5ryd1G-BI-ssiiJ1a0nnQIJBfbBLglKCV4Vz60P3oxFH8_M7UwE2WSWHKRED_fGA/s640/tmp_pbi.JPG" width="584" /></a></div>
<br />
<br />
<br />
PS: I'm using the Windows 10 control panel -> regional settings to change this. There is also a regional settings configuration in Power BI Desktop. Changing settings here doesn't seem to be having any effect..<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhPFJfecRLY9Hzx7PC4JDx_xiPUcXmRNxgxtMFZHH42Hi1cVAUaR_O2z1Es-tp7Zc6kJ135Nwhdyn4hMjofNGiC7vBxtWJjPhg1XJjNz1xdF0YxQ6o38uV_IT2zbEniqnOOHjyOJw/s1600/tmp_pbi7.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="609" data-original-width="614" height="317" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhPFJfecRLY9Hzx7PC4JDx_xiPUcXmRNxgxtMFZHH42Hi1cVAUaR_O2z1Es-tp7Zc6kJ135Nwhdyn4hMjofNGiC7vBxtWJjPhg1XJjNz1xdF0YxQ6o38uV_IT2zbEniqnOOHjyOJw/s320/tmp_pbi7.JPG" width="320" /></a></div>
<br />
Update: Above results are all based on Power BI Desktop. When viewing the (published) report in the browser, the results are different again. It seems that Internet Explorer & Edge are taking the Windows 10 Local in account. Chrome uses it's own language settings which you can change using the url: "chrome://settings/languages"<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRXFDXVYzo52IOmY1x7v300aUr2mbN7lW4dtNrup2DOohs9O5fXE0Z6ei_j2hGyApDWtr84_wcEGF_V2k8amJU5kmxFpsvIPqKkRFzVmz_9xRuNejTNhPwQfZyMBnNRpxgTaB5Pw/s1600/tmp_pbi10.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="472" data-original-width="700" height="268" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRXFDXVYzo52IOmY1x7v300aUr2mbN7lW4dtNrup2DOohs9O5fXE0Z6ei_j2hGyApDWtr84_wcEGF_V2k8amJU5kmxFpsvIPqKkRFzVmz_9xRuNejTNhPwQfZyMBnNRpxgTaB5Pw/s400/tmp_pbi10.JPG" width="400" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
When Dutch is at the top: E 4.100.235<br />
When English is at the top: E 4,100,235<br />
<br />
Thanks<br />
Jeroen<br />
<br />
<b>UPDATE 22-11-2018</b><br />
Yesterday I had to export some SQL tables into another database (creating an export for the auditors). I had done this before and thought it would be a nice and easy job until it bombed out with the error:<br />
<br />
Culture is not supported:<br />
**tablename**<br />
3072 (0x0c00) is an invalid culture identifier<br />
<div>
<br /></div>
<div>
A quick google revealed: Win 10 region settings and a little light bulb went on. I remembered creating this blogpost and changing the region settings to experiment with the thousands indicator. After setting the location back to US and language to US the SSIS job (which executes the export data wizard in the SQL Management tool) ran perfectly again without any problems!</div>
<div>
<br /></div>
<br />Shuwihttp://www.blogger.com/profile/18225167405815820927noreply@blogger.com5tag:blogger.com,1999:blog-36742793.post-68577841691135580402016-10-12T04:35:00.001-07:002016-10-12T04:58:51.353-07:00SharePoint ResourcesOverview of my SharePoint resources to keep up to speed with latest technology. (v1: 12-10-2016)<br />
<br />
1) Using the https://www.changedetection.com/ website to track sites like:<br />
<a href="https://technet.microsoft.com/en-us/library/sharepoint-online-it-professional-service-description.aspx">https://technet.microsoft.com/en-us/library/sharepoint-online-it-professional-service-description.aspx</a><br />
<br />
<a href="http://www.toddklindt.com/blog/Lists/Posts/Post.aspx?ID=346">http://www.toddklindt.com/blog/Lists/Posts/Post.aspx?ID=346</a><br />
<br />
<a href="https://support.office.com/en-us/article/SharePoint-Online-software-boundaries-and-limits-8f34ff47-b749-408b-abc0-b605e1f6d498">https://support.office.com/en-us/article/SharePoint-Online-software-boundaries-and-limits-8f34ff47-b749-408b-abc0-b605e1f6d498</a><br />
<br />
<a href="https://www.changedetection.com/log/office/support/what-s-new-in-microsoft-sharepoint-online-02449ef0-027e-4089-8717-f0ae7ea58029_log.html">https://www.changedetection.com/log/office/support/what-s-new-in-microsoft-sharepoint-online-02449ef0-027e-4089-8717-f0ae7ea58029_log.html</a><br />
<br />
<a href="https://blogs.office.com/sharepoint/">https://blogs.office.com/sharepoint/</a><br />
<br />
<br />
<br />
1b) Twitter<br />
#SP2010<br />
#SqlHelp<br />
#LazyWeb<br />
#Office<br />
@lucienengelen<br />
@Kjonge<br />
..<br />
2) Listen to the latest news @ The Microsoft CloudShow: <a href="http://www.microsoftcloudshow.com/podcast">http://www.microsoftcloudshow.com/podcast</a><br />
<br />
3) Follow online events & webcasts:<br />
<a href="https://ignite.microsoft.com/#fbid=5sJ5o7G556X">https://ignite.microsoft.com/#fbid=5sJ5o7G556X</a><br />
<br />
<a href="https://www.sharepointeurope.com/">https://www.sharepointeurope.com/</a><br />
<br />
(Potential) partners:<br />
<a href="http://en.share-gate.com/?_ga=1.229530332.1930697738.1473866799">http://en.share-gate.com/?_ga=1.229530332.1930697738.1473866799</a><br />
<a href="http://www.avepoint.com/">http://www.avepoint.com/</a><br />
<a href="http://www.metalogix.com/">http://www.metalogix.com/</a><br />
<a href="https://harmon.ie/">https://harmon.ie/</a><br />
<br />
4) Microsoft Mechanics<br />
<a href="https://www.youtube.com/user/OfficeGarageSeries">https://www.youtube.com/user/OfficeGarageSeries</a><br />
<br />
5) Microsoft Virtual Academy<br />
<a href="https://mva.microsoft.com/">https://mva.microsoft.com/</a><br />
<br />
6) Pluralsight training<br />
<a href="https://www.pluralsight.com/">https://www.pluralsight.com/</a><br />
<br />
7) Linked In Special Interest Groups<br />
BI: <a href="https://www.linkedin.com/groups/59185">https://www.linkedin.com/groups/59185</a><br />
SharePoint Community: <a href="https://www.linkedin.com/groups/43166">https://www.linkedin.com/groups/43166</a><br />
HealthCare: <a href="https://www.linkedin.com/groups/98243">https://www.linkedin.com/groups/98243</a><br />
SharePoint 2013: <a href="https://www.linkedin.com/groups/3839320">https://www.linkedin.com/groups/3839320</a><br />
SharePoint Enterprise Solutions architects: <a href="https://www.linkedin.com/groups/93094">https://www.linkedin.com/groups/93094</a><br />
<br />
8) The SharePoint network (previously SPYammer)<br />
<a href="https://techcommunity.microsoft.com/">https://techcommunity.microsoft.com</a><br />
<br />Shuwihttp://www.blogger.com/profile/18225167405815820927noreply@blogger.com0tag:blogger.com,1999:blog-36742793.post-61521335465738651822015-06-11T06:03:00.002-07:002015-06-11T06:03:49.884-07:00Error removing SQL SnapShots<div class="MsoNormal">
When trying
to delete some Microsoft SQL Server 2012 (SP1) snapshots, that I created as part of a recovery procedure before
upgrading a SharePoint farm to Service Pack 1, I ran against this error:</div>
<div class="MsoNormal">
<span lang="EN-GB">Drop
database [snapshotDbName]<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB" style="font-family: Consolas; font-size: 9.0pt; mso-ansi-language: EN-GB;">Msg 21, Level 21, State 1, Line 1<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB" style="font-family: Consolas; font-size: 9.0pt; mso-ansi-language: EN-GB;">Warning: Fatal error 615 occurred at Jun 11 2015 2:51PM. Note the error and time, and contact
your system administrator.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span lang="EN-GB">Or via the
interface: <o:p></o:p></span></div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhZ17vRkBK1Bd3QimiBZjfdt3_YhgN3iqk_f5kODukEGQVTUMyEUcPxMXH6YVcEOzcEC6KpRoPZRZVkryo8hyphenhyphenvQnNdzzSk8LT8yAgGPpMDJStH1ZPBpbRROVA7mGPr-SFbFVUwkDw/s1600/blogPostPic1.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="133" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhZ17vRkBK1Bd3QimiBZjfdt3_YhgN3iqk_f5kODukEGQVTUMyEUcPxMXH6YVcEOzcEC6KpRoPZRZVkryo8hyphenhyphenvQnNdzzSk8LT8yAgGPpMDJStH1ZPBpbRROVA7mGPr-SFbFVUwkDw/s400/blogPostPic1.PNG" width="400" /></a></div>
<div class="MsoNormal">
<br /></div>
<br /><div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span lang="EN-GB">I knew I
had enough rights because I was using the same account that created the snapshots.
After trying setting the snapshot-parent-db to single user, etc. etc. I ran
into this solution:<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span lang="EN-GB"> </span><span lang="EN-GB" style="color: blue; font-family: Consolas; font-size: 9.5pt; mso-ansi-language: EN-GB;">alter</span><span lang="EN-GB" style="font-family: Consolas; font-size: 9.5pt; mso-ansi-language: EN-GB;">
<span style="color: blue;">database</span> <span style="color: teal;">[YourDBName]</span>
<span style="color: blue;">set</span> <span style="color: blue;">emergency<o:p></o:p></span></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span lang="EN-GB">The result
will be:<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB" style="font-family: Consolas; font-size: 9.0pt; mso-ansi-language: EN-GB;">Msg 5093, Level 16, State 1, Line 1<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB" style="font-family: Consolas; font-size: 9.0pt; mso-ansi-language: EN-GB;">The operation cannot be performed on a database
snapshot.<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB" style="font-family: Consolas; font-size: 9.0pt; mso-ansi-language: EN-GB;">Msg 5069, Level 16, State 1, Line 1<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB" style="font-family: Consolas; font-size: 9.0pt; mso-ansi-language: EN-GB;">ALTER DATABASE statement failed.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span lang="EN-GB">BUT, now
you ARE able to delete the snapshot using the UI or:<br />
</span><span lang="EN-GB" style="color: blue; font-family: Consolas; font-size: 9.5pt; mso-ansi-language: EN-GB;">drop</span><span lang="EN-GB" style="font-family: Consolas; font-size: 9.5pt; mso-ansi-language: EN-GB;"> <span style="color: blue;">database</span>
<span style="color: teal;">[bwdba_SS]<o:p></o:p></span></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span lang="EN-GB">Don’t know
why but .. it works because of the (emergency) herbs :)<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<br />
<div class="MsoNormal">
<br /></div>
Shuwihttp://www.blogger.com/profile/18225167405815820927noreply@blogger.com2tag:blogger.com,1999:blog-36742793.post-49184577659430722082014-08-01T02:08:00.000-07:002014-08-01T04:56:32.301-07:00Display page properties in SharePoint page, like “last modified date” & “modified by” using SPServices (& jQuery)<div class="MsoNormal">
<span lang="EN-GB">A colleague got the request to add the latest modification date & modifier name to a SharePoint page. <br />
This is convenient for users visiting the page, to see when the page is changed and by who.<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><br />
I didn’t have much time (as usual) for this assignment and different options ran through my mind. At first I played a little bit with a custom page-layout where I would stuck the Modify data & modifier properties into the aspx; I quickly ran into all kinds of trouble using this option (publishing features must be on, the test O365 SharePoint site began throwing errors at me, (even after activating built-in page layouts), this was out of scope for my assignment so I quickly went out looking for other technology that could help me out!<br />
<br />
<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><b>SPServices</b><br />
The SharePoint 2013 Client Side Object Model (CSOM) is one of those technology options. Another option is jQuery, SPServices is such a jQuery library. But how do you implement this? Lots of examples but little of them are in context / complete and therefore not very useable for beginners. This is one of the reasons I wanted to blog about my solution, to create a full working example that can get you started. Visit <a href="https://www.nothingbutsharepoint.com/sites/devwiki/articles/Pages/When-to-Choose-SPServices-vs-the-Client-Side-Object-Model-CSOM.aspx" target="_blank">this </a>blog on when to use the CSOM / when to use the jQuery libraries.<br />
SPServices is an initiative from Marc. D. Anderson. Visit his <a href="http://sympmarc.com/" target="_blank">site </a>for loads of resources !<br /><br />
I started my journey with the SPServices “framework” which you can find here: </span><a href="http://spservices.codeplex.com/documentation"><span lang="EN-GB">http://spservices.codeplex.com/documentation</span></a><span lang="EN-GB"> <o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB">This codeplex project is full of usable resources and is providing most of the content of my final script. To start off you will need to reference 2 links; one to include the JQuery classes and the other one to include the SPServices classes. SPServices provides you with easy to use code that in the back talks to the SharePoint object model. This way you can create effective functions that are not available in SharePoint 2013 out of the box. For example: (all examples from the documentation part of the codeplex project with detailed info available!) <o:p></o:p></span></div>
<div class="MsoNormal">
<b><span lang="EN-GB"><br />
Form Enhancements/Assistance<br />
</span></b><span lang="EN-GB">SPCascadeDropdowns; It allows you to easily set up cascading dropdowns on a list form. (What we mean by cascading dropdowns is the situation where the available options for one column depend on the value you select in another column.) This is not possible with SharePoint OOB components!<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><br />
SPDisplayRelatedInfo; This function lets you display related information on forms when an option in a dropdown is chosen.<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><br />
SPLookupAddNew; This function allows you to provide a link in forms for Lookup columns so that the user can add new values to the Lookup list easily.<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><br />
SPRedirectWithID; This function allows you to redirect to a another page from a new item form with the new item's ID. This allows chaining of forms from item creation onward.<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><br />
SPSetMultiSelectSizes; Sets the size of the boxes in a multi-select picker based on the values they contain.<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><br />
SPArrangeChoices; Rearranges radio buttons or checkboxes in a form from vertical to horizontal display to save page real estate.<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><br />
SPAutocomplete; The SPAutocomplete lets you provide values for a Single line of text column from values in a SharePoint list. The function is highly configurable and can enhance the user experience with forms.<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><br />
SPUpdateMultipleListItems; SPUpdateMultipleListItems allows you to update multiple items in a list based upon some common characteristic or metadata criteria.<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><br />
SPFilterDropdown; The SPFilterDropdown function allows you to filter the values available in a Lookup column using CAML against the Lookup column's source list.<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><br />
SPComplexToSimpleDropdown; Converts a "complex" dropdown (which SharePoint displays if there are 20+ options) to a "simple" dropdown (select).<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><br />
SPFindPeoplePicker; The SPFindPeoplePicker function helps you find and set People Picker column values.<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><br />
SPFindMMSPicker; The SPFindMMSPicker function helps you find an MMS Picker's values.<o:p></o:p></span></div>
<div class="MsoNormal">
<b><span lang="EN-GB"><br />
Utilities<br />
</span></b><span lang="EN-GB">SPGetCurrentSite; This utility function, which is also publicly available, simply returns the current site's URL. It mirrors the functionality of the WebUrlFromPageUrl operation.<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><br />
SPGetCurrentUser; This function returns information about the current user. It is based on an insightful trick from Einar Otto Stangvik.<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><br />
SPGetLastItemId; Function to return the ID of the last item created on a list by a specific user. Useful for maintaining parent/child relationships.<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><br />
SPGetDisplayFromStatic; This function returns the DisplayName for a column based on the StaticName. <o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><br />
SPGetStaticFromDisplay; This function returns the StaticName for a column based on the DisplayName.<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><br />
SPScriptAudit; The SPScriptAudit function allows you to run an auditing report showing where scripting is in use in a site.<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><br />
SPGetQueryString; The SPGetQueryString function returns an array containing the Query String parameters and their values.<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><br />
SPListNameFromUrl; Returns the current list's GUID *if* called in the context of a list, meaning that the URL is within the list, like /DocLib or /Lists/ListName.<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><br />
SPFilterNode; Can be used to find namespaced elements in returned XML, such as rs:data or z:row from GetListItems. <o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><br />
SPXmlToJson; SPXslToJson is a function to convert XML data into JSON for client-side processing.<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><br />
SPConvertDateToISO; Convert a JavaScript date to the ISO 8601 format required by SharePoint to update list items.<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><br />
SPGetListItemsJson; SPGetListItemsJson combines several SPServices capabilities into one powerful function. By calling GetListItemChangesSinceToken, parsing the list schema, and passing the resulting mapping and data to SPXmlToJson automagically, we have a one-stop shop for retrieving SharePoint list data in JSON format. No manual mapping required!<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><br />
SPDropdownCtl; The function finds a dropdown in a form based on the name of the column (either the DisplayName or the StaticName) and returns an object you can use in your own functions.<o:p></o:p></span></div>
<span lang="EN-GB" style="font-family: "Calibri","sans-serif"; font-size: 11.0pt; line-height: 107%; mso-ansi-language: EN-GB; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"><br clear="all" style="mso-special-character: line-break; page-break-before: always;" /> </span> <br />
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b><span lang="EN-GB">The solution:<br />
</span></b><span lang="EN-GB">First of all creating a script; Then adding the script to a page where you want to display the page’s properties. This way, users can still edit the page content without messing with the script code. The code can be inserted into a script editor webpart or in a content editor webpart, depending on your situation you can also upload the script to a resource library and link the content editor webpart to your script. This way users won’t see the script at all, you can modify the script on 1 location and without the need to edit your page. <o:p></o:p></span></div>
<div class="MsoNormal">
<b><span lang="EN-GB"><br />
The script:</span></b><span lang="EN-GB"><br />
The script I put together looks like this; I’ve added some comments in there to explain what’s going on:<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><!-- Reference jQuery on the Google CDN (content delivery network), if you want to make an external link to the (for this script correct) jquery library --><o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script><o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><!-- Alternatively Reference jQuery on the locale Site Assets , uncomment to activate and comment out the above one to deactivate the CDN method--><o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><!--<script type="text/javascript" src="https://<yoursite>.sharepoint.com/SiteAssets/jquery.min.js"></script>--><o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><!-- Reference SPServices on cdnjs (Cloudflare) if you want to make an external link to the (for this script correct) SPServices library --><o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><script type="text/javascript" src="//cdnjs.cloudflare.com/ajax/libs/jquery.SPServices/2014.01/jquery.SPServices.min.js"></script><o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><!-- Alternatively Reference jQuery on the locale Site Assets, uncomment to activate and comment out the above one to deactivate the CDN method --><br />
<!--<script type="text/javascript" src="https://michindusinc.sharepoint.com/SiteAssets/jquery.SPServices.min.js"></script>--><o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><script language="javascript" type="text/javascript"><o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><!—A little helper to format the date like you would like it, in this case.. Dutch formatting--><o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB">function SPConvertDate(t)<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB">{<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"> var d = new Date(t.substr(0,4), parseInt(t.substr(5,2)) - 1, t.substr(8,2), t.substr(11,2), t.substr(14,2), t.substr(17,2));<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span lang="EN-GB"> var month = ((d.getMonth()+1) < 10) ? "0" + (d.getMonth()+1) : (d.getMonth()+1);<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"> var date = (d.getDate() < 10) ? "0" + d.getDate() : d.getDate();<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"> var minutes = (d.getMinutes() < 10) ? "0" + d.getMinutes() : d.getMinutes();<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"> var amPm = (d.getHours() < 12) ? "am" : "pm";<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span lang="EN-GB"> return date + "-" + month + "-" + d.getFullYear() + " " + d.getHours() + ":" + minutes+amPm;<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB">}<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><!—here starts the main program</span><span lang="EN-GB" style="font-family: Wingdings; mso-ansi-language: EN-GB; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-char-type: symbol; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin; mso-symbol-font-family: Wingdings;">à</span><span lang="EN-GB"><o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB">$(document).ready(function() {<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><!—here you can populate the only 2 parameters; the list-name where the page resides and the page ID of the page you want to show the modified date for; I’m using the pageID to prevent disfunction of the script if someone decides to rename a page ..</span><span lang="EN-GB" style="font-family: Wingdings; mso-ansi-language: EN-GB; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-char-type: symbol; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin; mso-symbol-font-family: Wingdings;">à</span><span lang="EN-GB"> <br />
<br />
var ListName = "2AAC79F6-98B3-45D4-BD77-EDE404B99C39"<br />
var PageID = "3"<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><br />
<!—then we define our query that will filter all pages in the list to the one we would like to show. Of course you can modify this query and even show multiple pages information. Via these methods we can access all properties / metadata from a SharePoint page, you just need to know the column names and ways to extract it; another nice thing is that it’s a query, which is familiar to a lot of people!</span><span lang="EN-GB" style="font-family: Wingdings; mso-ansi-language: EN-GB; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-char-type: symbol; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin; mso-symbol-font-family: Wingdings;">à</span><span lang="EN-GB"> <o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><br />
var CamlQuery = "<Query><Where><Eq><FieldRef Name='ID'/><Value Type='Number'>" + PageID + "</Value></Eq></Where></Query>";<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><!—Then we define which fields (metadata) we would like to extract and show in our page </span><span lang="EN-GB" style="font-family: Wingdings; mso-ansi-language: EN-GB; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-char-type: symbol; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin; mso-symbol-font-family: Wingdings;">à</span><span lang="EN-GB"> <br />
var CamlViewFields = "<ViewFields><FieldRef Name='Title' /><FieldRef Name='Modified' /><FieldRef Name='Author' /></ViewFields>";<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><!—now let’s build and execute our query </span><span lang="EN-GB" style="font-family: Wingdings; mso-ansi-language: EN-GB; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-char-type: symbol; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin; mso-symbol-font-family: Wingdings;">à</span><span lang="EN-GB"><br />
$().SPServices({<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"> operation: "GetListItems",<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"> async: false,<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"> listName: ListName,<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"> CAMLViewFields: CamlViewFields,<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"> CAMLQuery: CamlQuery,<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"> completefunc: function (xData, Status) {<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span lang="EN-GB"><!—we’re getting a XML back, let’s get our data out of it and fill some variables with the data</span><span lang="EN-GB" style="font-family: Wingdings; mso-ansi-language: EN-GB; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-char-type: symbol; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin; mso-symbol-font-family: Wingdings;">à</span><span lang="EN-GB"><o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"> $(xData.responseXML).SPFilterNode("z:row").each(function() {<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"> var Author = new String($(this).attr("ows_Author"));<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"> Author = Author.split(";#")[1]<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"> ModifiedDate = $(this).attr("ows_Modified")<br />
<br />
<!—making a liHtml variable which in this specific case I turned into a DIV type later, if you want to display more than one pages’ info, you might want to construct an <li>info</li> kind of html </span><span lang="EN-GB" style="font-family: Wingdings; mso-ansi-language: EN-GB; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-char-type: symbol; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin; mso-symbol-font-family: Wingdings;">à</span><span lang="EN-GB"><o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"> var liHtml = "Latest update:&nbsp;" + SPConvertDate(ModifiedDate) + "&nbsp;<br>By:&nbsp;" + Author;<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"> $("#tasksUL").append(liHtml); <o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"> });<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"> }<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"> });<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB">});<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"></script><o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><!—and then display your html </span><span lang="EN-GB" style="font-family: Wingdings; mso-ansi-language: EN-GB; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-char-type: symbol; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin; mso-symbol-font-family: Wingdings;">à</span><span lang="EN-GB"> <o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><h1>Page properties:</h1><br/><o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><div id="tasksUL"/><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<br />
<div class="MsoNormal">
<span lang="EN-GB">Ok, we’ve got our script.. Now what? Let’s insert it into a web part and see if it works, the end result:</span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgMl9P2QO9bV7JERpwrPS3lFJs0X2FQkb_pt8kIrNTWY8JSzrRbkOhkKLiuGa337oz1IS6xhz8jhKgWUbw-5tP7GKYfyF7AM61A6nDj7NHMj0Ae1YEg9iasGKMYExRN097I1_TGpg/s1600/Screen1.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgMl9P2QO9bV7JERpwrPS3lFJs0X2FQkb_pt8kIrNTWY8JSzrRbkOhkKLiuGa337oz1IS6xhz8jhKgWUbw-5tP7GKYfyF7AM61A6nDj7NHMj0Ae1YEg9iasGKMYExRN097I1_TGpg/s1600/Screen1.PNG" height="313" width="640" /></a></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span lang="EN-GB"></span></div>
<div class="MsoNormal">
<span lang="EN-GB">First, we add a script editor or content editor web part to the page;</span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjWjNr7yW6dZv6EwQyewB41t4VAeTsc9a7rkQZvUYIt96LypBG5svlbf82FC3Mz1HJZ3spjltM_0MCJbZD_B6BT1eCIb_PS1QgKnIHsbVH-9zU3y5pNyRPSxpUybyijq37iURQ20w/s1600/Screen2.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjWjNr7yW6dZv6EwQyewB41t4VAeTsc9a7rkQZvUYIt96LypBG5svlbf82FC3Mz1HJZ3spjltM_0MCJbZD_B6BT1eCIb_PS1QgKnIHsbVH-9zU3y5pNyRPSxpUybyijq37iURQ20w/s1600/Screen2.PNG" height="302" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="MsoNormal">
<span lang="EN-GB"> <o:p></o:p></span>Then add the script into it and save it.</div>
<div class="MsoNormal">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhCeBw3S7KbQpgAQC7VBaCLzWcptyG2ruUMvjNYliVvwZwqz6i3DOt0IJc3-nJSQUg7eHUdOBY8-W1UnkL1LdJXkcatrHn0STFPsPcgdo53u5NHQhu2Le3n3wKCuEo2fFHkCz76FA/s1600/Screen3.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhCeBw3S7KbQpgAQC7VBaCLzWcptyG2ruUMvjNYliVvwZwqz6i3DOt0IJc3-nJSQUg7eHUdOBY8-W1UnkL1LdJXkcatrHn0STFPsPcgdo53u5NHQhu2Le3n3wKCuEo2fFHkCz76FA/s1600/Screen3.PNG" height="342" width="640" /></a></div>
<br />
<o:p></o:p><br />
<div class="MsoNormal">
<span lang="EN-GB"></span></div>
<div class="MsoNormal">
<span lang="EN-GB">Now we can add other content to the page, when the page is saved the info updates automatically!<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><br />
</span></div>
<div class="MsoNormal">
<span lang="EN-GB">You can download the script here:<br />
<iframe frameborder="0" height="120" scrolling="no" src="https://onedrive.live.com/embed?cid=0A0B3C17DC291412&resid=A0B3C17DC291412%21985&authkey=AA9YB1BQur8GjE4" width="98"></iframe><br />
<br />
</span></div>
<div class="MsoNormal">
<span lang="EN-GB"><br />
</span></div>
Shuwihttp://www.blogger.com/profile/18225167405815820927noreply@blogger.com0tag:blogger.com,1999:blog-36742793.post-91872126685048279012014-06-25T05:09:00.000-07:002014-06-25T05:09:05.335-07:00TomTom Issue: Connecting to your computer<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzgVyRgZxMu-u48gH4aZ7RPDEE8sASe6xQFYLCRrKsidNAIuqStgbSFrMpADpw5oJa_TnlOUee8yHxjVDxK8E-V81ioaD0mxNTqX2jf6Y-usyE8Qc3GhP5zBoXk7Ld_pO-ChfEDQ/s1600/1.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzgVyRgZxMu-u48gH4aZ7RPDEE8sASe6xQFYLCRrKsidNAIuqStgbSFrMpADpw5oJa_TnlOUee8yHxjVDxK8E-V81ioaD0mxNTqX2jf6Y-usyE8Qc3GhP5zBoXk7Ld_pO-ChfEDQ/s1600/1.jpg" height="400" width="300" /></a>A bit of a different post, not about SharePoint this time !<br /><br />I finally resolved an issue with my TomTom today and thought to share this solution with the world :)<br /><br />My problem was that after a few seconds of starting my navigation in the car, it would display this message:<br />"Connecting to your computer".<br /><br />And this message would stay there forever, until you click OK. Because this message would pop up after 15 / 40 seconds, you almost always are driving. So it was very annoying to pay attention to it and press the OK button while driving.<br /><br />I logged a ticket with TomTom support, they suggested to reload the software on it, this didn't help. I decided to do some serious debugging and after a few moments I found the answer... The error / message was caused by plugging the TomTom into the Peugeot's own USB connection and not using the TomTom provided 12v ->USB connector:<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVQJzos2qa3qW7qGrWGyav4l1N1VeuVPkpRg0jfDT3Qr19-lM1jSPHUDO0RXx94tSffQfKozR6FRRxysNBHofkFKRR9Jji_Ki8SuzXvsYRKDMhxcUUpdht6hF5pDzG0Y7IwJtzag/s1600/2.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVQJzos2qa3qW7qGrWGyav4l1N1VeuVPkpRg0jfDT3Qr19-lM1jSPHUDO0RXx94tSffQfKozR6FRRxysNBHofkFKRR9Jji_Ki8SuzXvsYRKDMhxcUUpdht6hF5pDzG0Y7IwJtzag/s1600/2.JPG" /></a><br />
<br />
<br />
<br />
<br />
<br />
<br /><br />
<br />
<br />
<br />
<br />
Because I was using the Peugeot powered USB, TomTom thought it was connected to a computer; showing me the annoying message ! Using the normal 12v connection and TomTom USB converter that comes with the navigation device, no message; problemo solved :)<br />
<br />
Cheers,<br />
JeroenShuwihttp://www.blogger.com/profile/18225167405815820927noreply@blogger.com1tag:blogger.com,1999:blog-36742793.post-82205624540087737122014-06-18T05:33:00.002-07:002014-06-24T02:29:18.845-07:00No Managed Metadata columns in Power Query When using Power Query to retrieve data from an O365 SharePoint list, you run into trouble when that list data contains columns connected to the Term Store (managed metadata).<br />
<br />
These columns are not retrieved by Power Query, not using the "SharePoint List" connection and also not using the "oData" connection.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhwKL-yvVMQf7df3ue5dVvRC2XtUNs0oWx3SOxib14nuoHv8ut_kqeY-9F3pj1ky58AqGWMsZ-kiJa2c-Evui7IQgFzvXwPjbkecwGqq6AQ9fParmb5JIO_W6ijS8ctm3J3aYVlRQ/s1600/1.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhwKL-yvVMQf7df3ue5dVvRC2XtUNs0oWx3SOxib14nuoHv8ut_kqeY-9F3pj1ky58AqGWMsZ-kiJa2c-Evui7IQgFzvXwPjbkecwGqq6AQ9fParmb5JIO_W6ijS8ctm3J3aYVlRQ/s1600/1.png" height="142" width="320" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
So, the managed metadata column "organisatie eenheid" is in this list:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7uDCoiHvGfhnV7lf34i0HqmO5JcfZcqx69jyeti4TgJh4fJqmxqxnrLst-T_a1-bo2OQWfI51V0SPKBtdyOhNDn2fDYT2UkqT7J9UNtulFuKWBg8YlsKsx6usNGhjUdyEFe_LnA/s1600/3.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7uDCoiHvGfhnV7lf34i0HqmO5JcfZcqx69jyeti4TgJh4fJqmxqxnrLst-T_a1-bo2OQWfI51V0SPKBtdyOhNDn2fDYT2UkqT7J9UNtulFuKWBg8YlsKsx6usNGhjUdyEFe_LnA/s1600/3.png" height="248" width="640" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
But not in the Power Query result sets (oData):<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEga3u52ZerHm84xvMI8xMDRH722tqM9pTRr8UJxYjDWOucbbjscpjU-Y7cr_ry9jHnqNx72XcIC0UBQTej7g4EomJK1SWvNO8wUK78tGpEp0mZ8Tfcmz5_oWAvIoJoZkiUZb71yhA/s1600/4.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEga3u52ZerHm84xvMI8xMDRH722tqM9pTRr8UJxYjDWOucbbjscpjU-Y7cr_ry9jHnqNx72XcIC0UBQTej7g4EomJK1SWvNO8wUK78tGpEp0mZ8Tfcmz5_oWAvIoJoZkiUZb71yhA/s1600/4.png" height="75" width="640" /></a></div>
<div class="" style="clear: both; text-align: left;">
Or in the "From SharePoint List" resultset:</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjon2dRMx3Khv6qV5BTwjBz8A8r40WdsW71JcsStwE8rfTR_B06HNa8qCY337Kdinx48DNy8ZInlTizZqhtqsGoEiyWLPm31lrkBgWfUXDjDGNunUFKXUGuznx1Bs8D_A86T72pLA/s1600/5.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjon2dRMx3Khv6qV5BTwjBz8A8r40WdsW71JcsStwE8rfTR_B06HNa8qCY337Kdinx48DNy8ZInlTizZqhtqsGoEiyWLPm31lrkBgWfUXDjDGNunUFKXUGuznx1Bs8D_A86T72pLA/s1600/5.png" height="66" width="640" /></a></div>
<br />
<br />
<br />
<br />
<br />
I've notified Microsoft of this issue <a href="http://social.technet.microsoft.com/Forums/en-US/68f1418a-8a5b-4f7e-a575-3aaf532992aa/no-managed-metadata-columns-in-power-query-sharepoint-list-queries?forum=powerquery" target="_blank">here</a>, let's wait and see if there are any workarounds or fixes !<br />
Will keep you posted...<br />
<br /><b>Update 24 June 2014:</b><br /><br />Ben Martens from Microsoft searched around and asked the product team about this issue and unfortunately the conclusion is that right now, it's not possible to get MMD data extracted from SharePoint.<br />Both the ListData.mvc as OData connections won't bring back these columns from your SharePoint lists.<br />Sorry for you ...<br />Let's hope these services get modified by the SharePoint team and start exposing this data, there are many <a href="http://social.msdn.microsoft.com/Forums/vstudio/en-US/bba6f165-b012-4c5b-8e1b-a6e0093bb333/sharepoint-2013-managed-metadata-field-support?forum=lightswitch" target="_blank">scenarios </a>where people really want to work with the Managed Metadata ! (why use it if it's not supported !?!). Thanks Ben for this update !Shuwihttp://www.blogger.com/profile/18225167405815820927noreply@blogger.com0tag:blogger.com,1999:blog-36742793.post-3750648747025176592014-06-12T22:59:00.000-07:002014-06-12T22:59:08.106-07:00Use English as your #0365 (sub) site's default language or else...(use oData)Hi there,<br />
a quick post on something I discovered yesterday during the creation of a proof of concept a #0365 site that provides it's list data to an Excel Power Query & Power Pivot report. (Which will be published on a Power BI site, that will be described on it's own blog post :) )<br />
<br />
I was having trouble getting the data from the O365 SharePoint list into Excel. After making the connection with Power Query:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbd1xmssV7GSQ-79r7cmq3QGe2-Z4bGneMbJjhDyoNVHM41NWd7sGxBcIj_BU2uSlgWwY_8XcQjMKus2bevH1EFWYx7GD-Bmw3LpZ0Ppo0qzwasnVZ2wqNEBZ9eV7etvFODtBK4w/s1600/1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbd1xmssV7GSQ-79r7cmq3QGe2-Z4bGneMbJjhDyoNVHM41NWd7sGxBcIj_BU2uSlgWwY_8XcQjMKus2bevH1EFWYx7GD-Bmw3LpZ0Ppo0qzwasnVZ2wqNEBZ9eV7etvFODtBK4w/s1600/1.png" height="208" width="640" /></a></div>
<br />
The "navigator" which should show me all available lists on a given SharePoint URL was empty:<br />
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjxBJ9Cy5F9hIaDjgIBtlh2i7v_9vsgme771lI3xqmW2bh4EDaYhnYN9LWxF_EdciBUCjw8oIYv2wH_BAvmXAOuS0M9zApCr_dOwoY2WipIWAlzxKvgagLSiCnibCiTxCZRIJ6y6w/s1600/2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjxBJ9Cy5F9hIaDjgIBtlh2i7v_9vsgme771lI3xqmW2bh4EDaYhnYN9LWxF_EdciBUCjw8oIYv2wH_BAvmXAOuS0M9zApCr_dOwoY2WipIWAlzxKvgagLSiCnibCiTxCZRIJ6y6w/s1600/2.png" height="233" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
I tried different security settings, checking the data connections, removing all data connections and adding them again, blaming the latest Power BI update, googling, nothing worked. And the strange thing was that I'd seen it work in an earlier POC I did...<br />Eventually I tried another site URL (on the same O365 web application), that one worked !<br />Now I had to find out what the difference was between the working and none working sites. Soon I realized it was the regional and language settings. I made the regions the same, no difference. But when I created a new site and gave English as the default language upon creation, Power BI could instantly connect to it. </div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
I created a new site to test because changing the default language in O365 SharePoint is not as easy, I think I'll need Power Shell to do this but of course I need to focus on my original POC first :) </div>
<div class="separator" style="clear: both; text-align: left;">
<br />I quickly asked the community for feedback<a href="http://community.office365.com/en-us/f/172/p/226946/756295.aspx#756295" target="_blank"> here: http://community.office365.com/en-us/f/172/p/226946/756295.aspx#756295</a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
and here: <a href="http://community.office365.com/en-us/f/172/p/244657/756386.aspx#756386">http://community.office365.com/en-us/f/172/p/244657/756386.aspx#756386</a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
I also found a post from Kasper de Jonge, a senior program manager at the Microsoft SQL Server BI division and a good friend; He said he'll take this issue up with the PQ team and just posted an update on this:</div>
<div class="separator" style="clear: both; text-align: left;">
"<span style="background-color: #006fb0; color: white; font-family: 'Segoe UI', Helvetica, Garuda, Arial, sans-serif; font-size: 14px;">Update: Yes it is a known issue which they are working on with SP, the workaround is to connect to the sharepoint API using OData, rather than using the From SharePoint option in PQ" </span></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="background-color: #006fb0; color: white; font-family: 'Segoe UI', Helvetica, Garuda, Arial, sans-serif; font-size: 14px;"><br /></span></div>
<div class="separator" style="clear: both; text-align: left;">
Thanks Kasper !</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
Shuwihttp://www.blogger.com/profile/18225167405815820927noreply@blogger.com0tag:blogger.com,1999:blog-36742793.post-27487620859112971732014-05-19T04:45:00.004-07:002014-05-19T05:00:42.517-07:00SharePoint performance presentationI've uploaded the presentation I gave at the Information Worker Community evening Microsoft Johannesburg in October 2013: (download the PPT for fun animations)<br />
<br />
<iframe allowfullscreen="" frameborder="0" height="356" marginheight="0" marginwidth="0" scrolling="no" src="http://www.slideshare.net/slideshow/embed_code/34849541" style="border-width: 1px 1px 0; border: 1px solid #CCC; margin-bottom: 5px; max-width: 100%;" width="427"> </iframe> <br />
<div style="margin-bottom: 5px;">
<strong> <a href="https://www.slideshare.net/jeroenschoenmakers5/sharepoint-performance-public" target="_blank" title="SharePoint Performance">SharePoint Performance</a> </strong> from <strong><a href="http://www.slideshare.net/jeroenschoenmakers5" target="_blank">Jeroen Schoenmakers</a></strong> </div>
Shuwihttp://www.blogger.com/profile/18225167405815820927noreply@blogger.com0tag:blogger.com,1999:blog-36742793.post-86450026012112843942014-01-30T05:06:00.001-08:002014-04-23T07:19:19.439-07:00#PowerPivot refresh schedule disappears after creating new version of #excel file on #SP2013 #help The challenge is as follows:<br />
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.<br />
This problem only seems to happen on SharePoint 2013, SharePoint 2010 keeps a reference to the selected schedule after updating the excel document.<br />
<br />
The problem visual:<br />
A Power Pivot Excel document with a Schedule enabled:<br />
<br />
<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlpmflaYSfRO8z7b17wqY-vsex34xko2OgJNIwPKcbZD_nmbbBv5iApHq6ehuDncfrbQbCEpSi0EaADJDyc0ty70B8mN-aCnv_KECcdEe93gTRPEzWN3I5L0XxbidoL_XD1OaQMw/s1600/screen0.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlpmflaYSfRO8z7b17wqY-vsex34xko2OgJNIwPKcbZD_nmbbBv5iApHq6ehuDncfrbQbCEpSi0EaADJDyc0ty70B8mN-aCnv_KECcdEe93gTRPEzWN3I5L0XxbidoL_XD1OaQMw/s1600/screen0.PNG" height="89" width="320" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg9uIbryYsCDdTt3a4L2tENYgb30WCnYAzcVl1ZUOZQYVlzS3v7Qdsm5JDwwmx8vJ1_Qygeczl7W-3csldrgIPgQrabnS-7MjC_Cl9phNc3XzwpLZxxTdAJDlmLDTCD906VKCvhWw/s1600/screen1.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg9uIbryYsCDdTt3a4L2tENYgb30WCnYAzcVl1ZUOZQYVlzS3v7Qdsm5JDwwmx8vJ1_Qygeczl7W-3csldrgIPgQrabnS-7MjC_Cl9phNc3XzwpLZxxTdAJDlmLDTCD906VKCvhWw/s1600/screen1.PNG" height="179" width="320" /></a></div>
Schedule is enabled:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4cwnLge0iWskd1TNFQXgEHJf_rLzIa6szPh9kmFE0J-MoOw50QhB3Y-Y8MsgYf71UgtuaDIwQg9XRibhydsHLFaSDz2GMGVZ2EJgE_65oq0QJK1TmB0XL_rdJBRjyqZmorKt9-g/s1600/screen2.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4cwnLge0iWskd1TNFQXgEHJf_rLzIa6szPh9kmFE0J-MoOw50QhB3Y-Y8MsgYf71UgtuaDIwQg9XRibhydsHLFaSDz2GMGVZ2EJgE_65oq0QJK1TmB0XL_rdJBRjyqZmorKt9-g/s1600/screen2.PNG" height="175" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
Make a new version & save document:</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi6H03Zc-rnxd_Wxc4yAXXv-HOqu_160-fd9iSPyJHgjhwnDeTZ1_lCTMGFedzdH7SYM77OC6UOTIMwBVW4j-h0Wr6jT-RMuLAmDeq6ngG6DQxB_0n2idTCiRPKqmgHFQQWcGliPA/s1600/screen3.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi6H03Zc-rnxd_Wxc4yAXXv-HOqu_160-fd9iSPyJHgjhwnDeTZ1_lCTMGFedzdH7SYM77OC6UOTIMwBVW4j-h0Wr6jT-RMuLAmDeq6ngG6DQxB_0n2idTCiRPKqmgHFQQWcGliPA/s1600/screen3.PNG" height="170" width="320" /></a></div>
Overwrite existing file:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhCu6U9miQ4xf_JHf4KMPnxQMBja9NxvIol6_N93PMFqWBMqaTNaWAiQWXMQLBohf2uPKV0FxC8BQBANbwamavSnM1H7Ad_ZcU-oyj7r7JuC5LAqJZdXglqInKb5T-KQ-Pvh88yrg/s1600/screen4.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhCu6U9miQ4xf_JHf4KMPnxQMBja9NxvIol6_N93PMFqWBMqaTNaWAiQWXMQLBohf2uPKV0FxC8BQBANbwamavSnM1H7Ad_ZcU-oyj7r7JuC5LAqJZdXglqInKb5T-KQ-Pvh88yrg/s1600/screen4.PNG" height="95" width="320" /></a></div>
Check PowerPivot Data Refresh:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhCOrNRI-akiiZ8SDmVdoYTLlSiwTkDNvYzFROXL0r8wDROI7fY8SjQ62HjwpxZsIjeVLURuulAghoTnp-__eDIbziCVq6xx0P2umkpwVuDIJpbTfU7IUF0fBqoqPvwW-SzAuoauA/s1600/screen5.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhCOrNRI-akiiZ8SDmVdoYTLlSiwTkDNvYzFROXL0r8wDROI7fY8SjQ62HjwpxZsIjeVLURuulAghoTnp-__eDIbziCVq6xx0P2umkpwVuDIJpbTfU7IUF0fBqoqPvwW-SzAuoauA/s1600/screen5.PNG" height="187" width="320" /></a></div>
Gone ...<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgAeipDC1QF2pgzF-bUNH1bnaznMGMRdcpy9G59iWXB3jbjV6hLIRItiWRV2_LF3PR_V2rbn5NwxZu6e0yUCv0HpRWEY1DtM0V4SVo73uQa-u-e1zgo5CMlN0_aaQKKJ_J89mcRHA/s1600/screen6.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgAeipDC1QF2pgzF-bUNH1bnaznMGMRdcpy9G59iWXB3jbjV6hLIRItiWRV2_LF3PR_V2rbn5NwxZu6e0yUCv0HpRWEY1DtM0V4SVo73uQa-u-e1zgo5CMlN0_aaQKKJ_J89mcRHA/s1600/screen6.PNG" height="130" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
No fix yet, will update this post when I find it !</div>
<div class="separator" style="clear: both; text-align: left;">
(Suggestions are welcome ..) </div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
4 Feb Update:</div>
<div class="separator" style="clear: both; text-align: left;">
I found the Microsoft KB that describes this issue (Sql Bug <span style="background-color: white; color: #221111; font-family: Verdana, Arial, Helvetica, Geneva, sans-serif; font-size: 12px; line-height: 15.807999610900879px;">1377755) </span> in the CU6 update for SQL Server 2012:</div>
<div class="separator" style="clear: both; text-align: left;">
http://support.microsoft.com/kb/2874957/en-us</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
I installed the CU8 but so far no luck..the issue is still there!</div>
<br />
5 Feb Update:<br />
Posted question on the <a href="http://social.msdn.microsoft.com/Forums/sqlserver/en-US/ac73c02f-97b4-47ab-8a1e-28a57ac2b58b/not-resolved-with-cu6-or8-kb-2874957-scheduled-data-refresh-disabled-after-you-make-some?forum=sqlkjpowerpointforsharepoint" target="_blank">Microsoft PowerPivot for SharePoint forum</a><br />
<br />
15 April Update:<br />
It seems that "psiodmakFuture-Processing" found the cause of this bug:<br />
We have this problem as well.<br />
<br />
I think I found a possible cause. Try the following script:<br />
<br />
SPWeb http://yoursharepoint.com).GetFile(path/to/file.xlsx).Properties['ppDRSchedule']<br />
<br />
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.<br />
<br />
Now we just need a <b>solution </b>from Microsoft :)<br />
<br />
23 April Update:<br />CU 9 for SQL Sever 2012 SP1 is now <a href="http://support.microsoft.com/kb/2931078" target="_blank">downloadable</a>, let's give it a try and see if this resolves the outstanding <a href="http://social.msdn.microsoft.com/Forums/sqlserver/en-US/ac73c02f-97b4-47ab-8a1e-28a57ac2b58b/not-resolved-with-cu6-or8-kb-2874957-scheduled-data-refresh-disabled-after-you-make-some?forum=sqlkjpowerpointforsharepoint" target="_blank">issue</a>...<br />
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<br />Shuwihttp://www.blogger.com/profile/18225167405815820927noreply@blogger.com0tag:blogger.com,1999:blog-36742793.post-91407387112301411422014-01-22T05:17:00.003-08:002014-01-22T05:21:07.947-08:00Quick tool: Dev / Demo / Test environment in the cloudWhen 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.<br />
<br />
On <a href="http://www.cloudshare.com/">http://www.cloudshare.com</a> 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 !<br />
One of these VM's is a Microsoft Exchange 2010 SP3 environment (Exchange installed AND CONFIGURED, AD installed & configured etc.) but they have more ! :<br />
(As per 22 January 2014)<br />
<br />
CentOS 5 W/ MySQL<br />
CentOS 5.4 Server<br />
CentOS 5.8 Server<br />
CentOS 6 Server<br />
CentOS 6.3 Server<br />
Dynamics CRM Server 2011<br />
Exchange Server 2010 SP1<br />
Exchange Server 2010 SP3<br />
Exchange Server 2013 RTM<br />
Microsoft Forefront TMG 2010<br />
PostgreSQL 9.1<br />
Ruby On Rails<br />
SharePoint 2007<br />
SharePoint 2007 Additional Server Bits Only<br />
SharePoint 2010 Additional Server Bits Only<br />
SharePoint 2010 Enterprise - No Configuration<br />
SharePoint 2010 Enterprise SP1<br />
SharePoint 2010 Enterprise SP1 W/ Duet<br />
SharePoint 2010 Enterprise SP1 W/ SQL 2012 and BI Stack<br />
SharePoint 2010 SP1 (Information Worker)<br />
SharePoint 2010 SP1 Enterprise W/ FAST Search and SQL Server 2012<br />
SharePoint 2010 SP1 Enterprise W/ SQL Server 2012<br />
SharePoint 2010 SP1 Standard W/ SQL 2012<br />
SharePoint 2010 SP1 W/ FAST Search Server<br />
SharePoint 2010 SP1 W/ PowerPivot<br />
SharePoint 2010 SP1 W/ Project Server<br />
SharePoint 2010 Standard - No Configuration<br />
SharePoint 2010 Standard SP1<br />
SharePoint 2010 W/ Fast for SharePoint Additional Server Bits Only<br />
SharePoint 2013 Additional Server Bits Only<br />
SharePoint 2013 with October 2013 Update<br />
SharePoint Foundation 2010 SP1<br />
SharePoint Foundation 2010 SP1 W/ Dev Tools<br />
SharePoint Server 2013<br />
SQL Server 2008 R2 SP1 Standard<br />
SQL Server 2012 Standard<br />
SQL Server 2014 CTP2<br />
Team Foundation Server 2010<br />
Team Foundation Server 2012<br />
Team Foundation Server 2013<br />
Ubuntu 10.04 Desktop<br />
Ubuntu 10.04 Server<br />
Ubuntu 12.04.3 Desktop<br />
Ubuntu 12.04.3 Server LTS 64 bit<br />
Ubuntu 12.04.3 Server LTS 64 bit /W Docker 0.7<br />
Ubuntu 13.10 Server 64 bit<br />
Windows 2003 R2 W/ Oracle 11g<br />
Windows Server 2003 R2 Enterprise<br />
Windows Server 2008 R2 x64 Enterprise SP1<br />
Windows Server 2008 R2 x64 Enterprise SP1 W/ Active Directory<br />
Windows Server 2008 R2 x64 Enterprise SP1 W/ IIS Server<br />
Windows Server 2008 R2 x64 Standard<br />
Windows Server 2008 SP2 x86 Standard<br />
Windows Server 2008 x64 W/ SQL Server 2012 SP1 BI Edition<br />
Windows Server 2012 R2 Standard<br />
Windows Server 2012 R2 Standard W/ Visual Studio 2013<br />
Windows Server 2012 x64 Standard<br />
Windows Server 2012 x64 W/ Active Directory<br />
Windows Server 2012 x64 W/ SQL Server 2012 SP1<br />
Windows Server 2012 x64 W/ SQL Server 2012 SP1 BI Edition<br />
Xubuntu 8<br />
<br />
Each of these VM's ready to spin in a couple of minutes.<br />
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.<br />
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!!<br />
You can do it, with cloudshare.com !!<br />
<br />
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.<br />
SnapShot's are also available, so no more re-doing all that hard work :)<br />
<br />
Some caveats:<br />
- 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 !<br />
- You are depending on your connection to the cloud.. no internet = no environment<br />
- Make sure you look at all pricing options, depending what you are going to do with the toolShuwihttp://www.blogger.com/profile/18225167405815820927noreply@blogger.com0tag:blogger.com,1999:blog-36742793.post-21203786527725625712013-10-19T07:27:00.000-07:002013-10-19T07:28:51.645-07:00O365 Snag, Grouped view in public site: Working On It......(for anonymous users)So,<br />
I'm helping out a small business by creating an online exposure for them via O365. Just showing the products, with a product-code in a category and an image.<br />
There are about 3000 products so showing them all at once sounds like a bad idea.. Let's just group them on the category, users can open a category and see all product with a little product image (thumbnail) and in the future select them to get a quote for them.<br />
Of course, all of this happens in the "public" = "anonymous" part of the 0365 site.<br />
<br />
I'm sorry .. but that's not going to work !<br />
Microsoft support (SRX1219692554ID) suggests you better use an alternative view without the grouping functionality. (That's the final "work around" they gave me after some extensive testing).<br />
<br />
As it turns out, the anonymous users don't have sufficient permissions to access this information, resulting in a "working on it" status as soon as these anonymous users hit the + sign before a group. This status won't change.. no matter how long you wait for it; in the background O365 is trying to redirect these users to the login page which in not going to work for these type of users.<br />
<br />
Authenticated users see the products in a category:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj2_0xFBa1Vw35H_Md0CsUozi7LpB25xcR2cOhLNJgfejLsRpMlQzPaejScCUrEPstLJbAKhyphenhyphenCpqcP34KNX97fCgLsKpEL-587obH36Ga-2tUPEOkOH2dl-R9Ek0aeetidvOHwbaQ/s1600/workingOnIt2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="190" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj2_0xFBa1Vw35H_Md0CsUozi7LpB25xcR2cOhLNJgfejLsRpMlQzPaejScCUrEPstLJbAKhyphenhyphenCpqcP34KNX97fCgLsKpEL-587obH36Ga-2tUPEOkOH2dl-R9Ek0aeetidvOHwbaQ/s320/workingOnIt2.png" width="320" /></a></div>
<br />
Anonymous users just see "Working on it..."<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjj3ql2etN7c4tYtDM8q6LPv3t1N9jIT2i60APiXaCiS2wrr2NVsL0K59Kylk6q0x6XgdO1zYGJryAH9-MlfN76ldyayhQT1ejtjtLa2XcTatibtc-S4uxR83XnB4mHVoQCAJOOZQ/s1600/workingOnIt.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjj3ql2etN7c4tYtDM8q6LPv3t1N9jIT2i60APiXaCiS2wrr2NVsL0K59Kylk6q0x6XgdO1zYGJryAH9-MlfN76ldyayhQT1ejtjtLa2XcTatibtc-S4uxR83XnB4mHVoQCAJOOZQ/s1600/workingOnIt.png" /></a></div>
<br />
The "work around" is of course not helping at-all, there are no other views that have this functionality.. I'm not even daring to think to create a Access APP (view) that can host this functionality for anonymous users...<br />
<br />
Big snag !! Maybe some JavaScript + XSLT to the rescue.. I'll post a real work-around as soon as I find one !Shuwihttp://www.blogger.com/profile/18225167405815820927noreply@blogger.com0tag:blogger.com,1999:blog-36742793.post-1681585480071714572013-10-02T07:59:00.002-07:002013-10-02T07:59:33.123-07:003rd Party Apps that leave their Sh#!T behind...(or crash your farm in the removal process) You know the situation, lots of time pressure on the project, the client needs a little [treeview] [search optimizer] [birthday calendar] [pdf / cad / xxx viewer] [etc] (select or choose your own) addition to the SharePoint implementation.<br />
There is no budget, because .. why should we by this ??! Doesn't it come with SharePoint ? We'll think about it when we see it working...<br />
<br />
And you know you should use the dev / test environment to run these trials on .. but hey... sometimes there is only 1 environment...<br />
<br />
STOP... Don't DO IT !!<br />
<br />
Chances are that the 3rd party tool you are installing is NEVER going to leave the farm / web app / site collection, or you going to spend ALLOT of time cleaning up after you try to remove it.<br />
Maybe you don't care what's left behind.. until you see the warnings appear in the health-analyzer.. Maybe you don't look in the health analyzer ? You will... once you install a CU or want to upgrade to SharePoint 2013.. Which won't work.. because there are some missing components ! ALERT ALERT ALERT !!! Of course you did a trial upgrade .. right !?!<br />
<br />
The above scenarios I consider "lucky'", in one occasion I saw the whole farm die when I uninstalled a 3rd party product; giving a nice correlation ID message to all visitors until YOU the admin who broke everything (euh.. I clicked uninstall... wtf ?!?! ) will FIX IT ! (For free of course.. )<br />
<br />
Things that can get left behind (also after the neat uninstall says "finished":<br />
- Event receivers (in every single site (collection) !<br />
<a href="http://speventreceiverman.codeplex.com/">http://speventreceiverman.codeplex.com/</a><br />
<br />
- Web Parts<br />
(web part gallery)<br />
<a href="http://etienne-sharepoint.blogspot.com/2011/10/solving-missingwebpart-errors-from.html">http://etienne-sharepoint.blogspot.com/2011/10/solving-missingwebpart-errors-from.html</a><br />
<br />
- Assemblies<br />
<a href="http://etienne-sharepoint.blogspot.com/2011/10/solving-missingassembly-errors-from.html?showComment=1380725089569#c3924490290473490722">http://etienne-sharepoint.blogspot.com/2011/10/solving-missingassembly-errors-from.html?showComment=1380725089569#c3924490290473490722</a><br />
<br />
- Setup Files<br />
<a href="http://etienne-sharepoint.blogspot.com/2011/10/solving-missingsetupfile-errors-from.html">http://etienne-sharepoint.blogspot.com/2011/10/solving-missingsetupfile-errors-from.html</a><br />
<br />
-Features<br />
<a href="http://featureadmin.codeplex.com/">http://featureadmin.codeplex.com/</a><br />
<br />
<br />
Shuwihttp://www.blogger.com/profile/18225167405815820927noreply@blogger.com0tag:blogger.com,1999:blog-36742793.post-67139759896594720862013-10-02T03:06:00.003-07:002013-10-02T03:07:46.521-07:00Add DB server IP to localhost when using SQL Alias So, when you implement the "best practice" of using SQL Server ALIAS names (to be flexible at all times of where your SharePoint databases will be hosted), apparently it's also a best practice to add the IP & ALIAS name to (all) your WFE & APP's that are using this name.<br />
<br />
This came to my attention when I started receiving ERROR logs in the windows APP log of a WFE:<br />
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br />
Log Name: Application</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Source:
Microsoft-SharePoint Products-SharePoint Foundation</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Date: 2013/10/02
07:24:00 AM</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Event ID: 6398</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Task Category: Timer</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Level: Critical</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Keywords: </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
User: **</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Computer: **</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Description:</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
The Execute method
of job definition
Microsoft.SharePoint.Diagnostics.SPDatabaseServerDiagnosticsPerformanceCounterProvider
(ID 5bce4422-820a-465f-aee4-5ce0f31640ce) threw an exception. More information
is included below.</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
SharePointDbServer:The
network path was not found.</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
;</div>
<br />
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Please ensure that
the database server is available</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br />
Eh.. duh.. of course the database server is available.. I personally know the DBA ! :P :P </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
(Yeah, he's one of my personalities)...</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Ok; so .. apparently when you use the SPDiag toolkit from MS, this toolkit starts monitoring stuff and will trigger above ERROR in your eventlog, as being described here:</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<a href="http://social.technet.microsoft.com/Forums/sharepoint/en-US/4b397f6c-249a-43f0-aa91-ff210e4e132b/timer-event-6398-spdatabaseserverdiagnosticsperformancecounter">http://social.technet.microsoft.com/Forums/sharepoint/en-US/4b397f6c-249a-43f0-aa91-ff210e4e132b/timer-event-6398-spdatabaseserverdiagnosticsperformancecounter</a></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Thank you so much <a href="http://social.technet.microsoft.com/profile/mohammed%20al-basri/?ws=usercard-mini" style="background-color: white; border: none; color: #6633b3; cursor: pointer; font-family: inherit; font-size: inherit; font-style: inherit; font-variant: inherit; line-height: inherit; list-style-type: none; margin: 0px; padding: 0px; text-decoration: none; vertical-align: baseline;" target="_blank">Mohammed Al-Basri</a> for pointing this out :) </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
I added the HOME ip (in this case it's a single server install) 127.0.0.1 + DB ALIAS name and voila, another problemo solved !</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
Shuwihttp://www.blogger.com/profile/18225167405815820927noreply@blogger.com0tag:blogger.com,1999:blog-36742793.post-13334127477207486272013-09-19T04:31:00.000-07:002013-10-14T00:30:28.040-07:00Slow StatMan query issue with #SP2010 farm<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
2 days ago I've emailed help@brentozar.com the mail below, unfortunately I haven't heard anything and I haven't managed to resolve this issue yet. That's why I decided to blog about it, so I can also keep you posted on the progress !</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Hi Help team, </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
I'm Jeroen (Dutch)
all the way from South Africa, running into a Microsoft SQL Server 2008 R2 (SP2) -
10.50.4279.0 (X64) Mar 26 2013
17:33:13 Copyright (c) Microsoft
Corporation Enterprise Edition (64-bit)
on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
challenge. </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
This server is also
running SharePoint 2010 14.0.6117.5002 (Feb 2012 CU); The problem is that
'suddenly' (used to work without this problem), when the first person uploads a
new document to a SharePoint document library, this upload takes forever. </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Thanks to your free
'Triage' tool, I managed to pinpoint the query that is taking forever to
finish:</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
EXEC
master.dbo.sp_whoisactive</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
@get_plans=1,@get_transaction_info
=1</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
results in:</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<b>SELECT
StatMan([SC0], [LC0]) FROM (SELECT TOP 100 PERCENT CONVERT([varbinary](200),
SUBSTRING ([Content], 1, 100)++substring([Content], case when
LEN([Content])<=200 then 101 else LEN([Content])-99 end, 100)) AS [SC0],
datalength([Content]) AS [LC0] FROM [dbo].[AllDocStreams] WITH
(READUNCOMMITTED) ORDER BY [SC0] ) AS
_MS_UPDSTATS_TBL</b></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
This seems to be
related with the statistics.. Probably the insert query for the document
triggers the query optimizer to create statistics.. which causes a lot of disk
i/o and takes more than 20 minutes to complete... </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
The
auto_create_statistics & auto_update_statics settings are FALSE / off on
the content-database. </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br />
I have manually created the statistics (not trusting the SharePoint timer job
that supposed to do that but often fails, as explained here: </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<a href="http://blogs.msdn.com/b/erica/archive/2012/10/31/sharepoint-and-database-statistics-why-are-they-out-of-date-and-what-to-do-about-it.aspx">http://blogs.msdn.com/b/erica/archive/2012/10/31/sharepoint-and-database-statistics-why-are-they-out-of-date-and-what-to-do-about-it.aspx</a></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
I used this query to
create the statistics: </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
EXECUTE
sp_msforeachdb </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
'USE [?];</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
USE
WSS_Content_DiscoverPortal </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
IF DB_NAME() NOT
IN(''master'',''msdb'',''tempdb'',''model'')</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
begin </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
print ''updating statistics in
database ==> '' + db_name()</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
if exists (select 1 from sys.objects
where name = ''proc_updatestatistics'')</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
begin</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
print ''updating statistics
via proc_updatestatistics''</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
exec proc_updatestatistics </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
end</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
else </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
begin </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
print ''updating statistics
via sp_updatestats''</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
exec sp_updatestats</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
end</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
end'</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
But unfortunately,
that doesn't help.. It still hangs on the same "SELECT StatMan…."
query </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
I have also looked
at the indexes in the database / AllDocStreams table (by the way, the
AllDocStreams table seems to hold all BLOB data for the documents in a
SharePoint 2010 document library). </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
SELECT a.index_id,
name, avg_fragmentation_in_percent</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
FROM
sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'dbo.AllDocStreams'),</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
NULL, NULL, NULL) AS a</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
JOIN sys.indexes AS b ON a.object_id =
b.object_id AND a.index_id = b.index_id;</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
GO</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
ALTER INDEX
AllDocStreams_CI ON dbo.AllDocStreams</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
REBUILD;</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
GO</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
--1%</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
ALTER INDEX
AllDocStreams_RbsId ON dbo.AllDocStreams</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
REBUILD;</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
GO</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
--3.5%</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
To rebuild the
indexes, resulting in 0% fragmentation. </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
I found this
blogpost that seems to describe the same type of issue.. With no solution
described.. </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<a href="http://connect.microsoft.com/SQLServer/feedback/details/683303/string-summary-slow-statistics-creation-on-varbinary-max-column">http://connect.microsoft.com/SQLServer/feedback/details/683303/string-summary-slow-statistics-creation-on-varbinary-max-column</a></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
And also these
posts.. </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
SELECT query is
timing out due to SELECT STATMAN process</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<a href="http://www.sqlservercentral.com/Forums/Topic1002494-391-1.aspx#bm1002504">http://www.sqlservercentral.com/Forums/Topic1002494-391-1.aspx#bm1002504</a></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
-->this is
exactly it, but no answer..</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
BINGO: STATSMAN
QUERY – KNOWN ISSUE WITH UPDATE STATISTICS</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<a href="http://cleversoft.wordpress.com/2010/12/12/statsman-query-known-issue-with-update-statistics/">http://cleversoft.wordpress.com/2010/12/12/statsman-query-known-issue-with-update-statistics/</a></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
--> no answer..</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Does the BrentO
help-team have any other pointers for me to look at ?? </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
It's massively
appreciated and I think maybe even challenging enough to take a look at ? </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br />
Thanks very much in advance,</div>
<br />
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Jeroen<br />
<br />
Update: 14 October 2013<br />
After posting this issue here: <a href="http://dba.stackexchange.com/questions/51303/extremely-slow-statman-query-issue-with-sp2010-farm?noredirect=1#comment90988_51303" style="font-size: 11pt;">http://dba.stackexchange.com/questions/51303/extremely-slow-statman-query-issue-with-sp2010-farm?noredirect=1#comment90988_51303</a><br />
<br />
I got some nice feedback and started re-checking the settings on the SharePoint content database. Somehow (probably during early testing and trying to resolve this issue) the Database Setting "Auto Create Statistics) was turned on... This is not best practice for the SharePoint content database (SharePoint's timer job is taking care of creating statistics). After turning off this setting (resetting it to default setting), the issue seems to be resolved. Will keep a close monitor on this for the rest of this week and let you know next week if this resolved this painful issue !<br />
<br /></div>
Shuwihttp://www.blogger.com/profile/18225167405815820927noreply@blogger.com0tag:blogger.com,1999:blog-36742793.post-14321818949424605442013-09-19T00:41:00.000-07:002013-09-19T00:41:19.360-07:00Brent Ozar - How To Prove Your SQL Server Needs More MemoryI Really enjoyed watching this short 30 minute webcast from Brent Ozar that shows you how to check if your SQL Server needs more memory. Of course the relationship between SQL & SharePoint [xxxx] is clear and will directly impact the performance of any SharePoint implementation !<br />
<br />
Thanks so much for sharing this info Brent !<br /><br />
The link to the webcast:<br />
<a href="http://www.brentozar.com/archive/2013/09/how-to-prove-your-sql-server-needs-more-memory-video/">http://www.brentozar.com/archive/2013/09/how-to-prove-your-sql-server-needs-more-memory-video/</a><br />
<br />
My personal notes from this webcast:<br />(I've put in the time frames of the video when Brent talks about that specific performance counter).<br />
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
64 GB of memory
costs less than your laptop. </div>
<ul style="direction: ltr; margin-bottom: 0in; margin-left: .375in; margin-top: 0in; unicode-bidi: embed;" type="disc">
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;">Create a business case to
rectify the purchase of more memory for your SQL </span></li>
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;">Less than one weekday of your
salary. </span></li>
</ul>
<div style="font-family: Calibri; font-size: 11.0pt; margin-left: .375in; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<span style="font-weight: bold;">Cost of your time tuning + changing VS Cost to upgrade
RAM to the next level</span> </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="direction: ltr;">
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse; border-color: #A3A3A3; border-style: solid; border-width: 1pt; direction: ltr;" valign="top">
<tbody>
<tr>
<td style="border-color: #A3A3A3; border-style: solid; border-width: 1pt; padding: 4pt 4pt 4pt 4pt; vertical-align: top; width: 2.0958in;">
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Memory - Available
Mbytes</div>
</td>
<td style="border-color: #A3A3A3; border-style: solid; border-width: 1pt; padding: 4pt 4pt 4pt 4pt; vertical-align: top; width: 5.5784in;">
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
</td>
</tr>
<tr>
<td style="border-color: #A3A3A3; border-style: solid; border-width: 1pt; padding: 4pt 4pt 4pt 4pt; vertical-align: top; width: 2.0958in;">
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
SQLServer: Buffer
Manager - Page life expectancy<br />
(18:41)</div>
</td>
<td style="border-color: #A3A3A3; border-style: solid; border-width: 1pt; padding: 4pt 4pt 4pt 4pt; vertical-align: top; width: 5.5784in;">
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
SQL keeps the
grapes, but has to start making wine every time from scratch. </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
How long can SQL
keep the page-date in memory (cache), before turning to disk to get the data.
(Measured in seconds). The higher this value, the better (how long can the
data be kept in-memory). Some say, 300
seconds is absolute lowest. </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Even if you are
above 300..if the cache get's cleared very often you still have a
problem! (and putting more memory in
won't solve this problem). </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Also see
"Buffer pool questions" for details. </div>
</td>
</tr>
<tr>
<td style="border-color: #A3A3A3; border-style: solid; border-width: 1pt; padding: 4pt 4pt 4pt 4pt; vertical-align: top; width: 2.0958in;">
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
SQLServer: Memory
Manager - Memory grants pending<br />
(13:10)</div>
</td>
<td style="border-color: #A3A3A3; border-style: solid; border-width: 1pt; padding: 4pt 4pt 4pt 4pt; vertical-align: top; width: 5.5784in;">
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
How many queries
are waiting for memory before they can start. Should never be above 0. If
above 0 , you should just add memory !</div>
</td>
</tr>
<tr>
<td style="border-color: #A3A3A3; border-style: solid; border-width: 1pt; padding: 4pt 4pt 4pt 4pt; vertical-align: top; width: 2.0958in;">
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
SQLServer: Memory
Manager - Target Server Memory</div>
</td>
<td style="border-color: #A3A3A3; border-style: solid; border-width: 1pt; padding: 4pt 4pt 4pt 4pt; vertical-align: top; width: 5.5784in;">
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Target = size of
the barrel</div>
</td>
</tr>
<tr>
<td style="border-color: #A3A3A3; border-style: solid; border-width: 1pt; padding: 4pt 4pt 4pt 4pt; vertical-align: top; width: 2.0958in;">
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
SQLServer: Memory
Manager Total Server Memory</div>
</td>
<td style="border-color: #A3A3A3; border-style: solid; border-width: 1pt; padding: 4pt 4pt 4pt 4pt; vertical-align: top; width: 5.5784in;">
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Total = how full
is it right now </div>
</td>
</tr>
<tr>
<td style="border-color: #A3A3A3; border-style: solid; border-width: 1pt; padding: 4pt 4pt 4pt 4pt; vertical-align: top; width: 2.0958in;">
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
SQLServer: SQL
Statistics - Batch Requests / sec</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
(14:27) </div>
</td>
<td style="border-color: #A3A3A3; border-style: solid; border-width: 1pt; padding: 4pt 4pt 4pt 4pt; vertical-align: top; width: 5.5784in;">
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
How busy is my sql
server due to incoming queries. </div>
</td>
</tr>
<tr>
<td style="border-color: #A3A3A3; border-style: solid; border-width: 1pt; padding: 4pt 4pt 4pt 4pt; vertical-align: top; width: 2.0958in;">
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
SQLServer: SQL
Statistics - Compilations / sec<br />
(14:27) </div>
</td>
<td style="border-color: #A3A3A3; border-style: solid; border-width: 1pt; padding: 4pt 4pt 4pt 4pt; vertical-align: top; width: 5.5784in;">
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
(related to above)
Building the execution plans #sec. There should already be execution plan
ready, which lowers the CPU performance. <br />
If SQL Server is forced to compile >10% of queries [because it can't
cache them] then we need more memory.</div>
</td>
</tr>
</tbody></table>
</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Avoiding buying RAM </div>
<ul style="direction: ltr; margin-bottom: 0in; margin-left: .375in; margin-top: 0in; unicode-bidi: embed;" type="disc">
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;">Parameterize queries </span></li>
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;">Keep your ORM up to date
(Nhibernate, LINQ, EF, etc)</span></li>
<ul style="direction: ltr; margin-bottom: 0in; margin-left: .375in; margin-top: 0in; unicode-bidi: embed;" type="circle">
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;">Keep these tools up to date
! (developers)</span></li>
</ul>
</ul>
<ul style="direction: ltr; margin-bottom: 0in; margin-left: .375in; margin-top: 0in; unicode-bidi: embed;" type="disc">
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;">Transition to stored
procedures</span></li>
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;">Consider OPTIMIZE FOR AD HOC</span></li>
<ul style="direction: ltr; margin-bottom: 0in; margin-left: .375in; margin-top: 0in; unicode-bidi: embed;" type="circle">
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;">Don't keep these in the
cache !</span></li>
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;">Only change this is this is
causing the problem !</span></li>
</ul>
</ul>
<div style="font-family: Calibri; font-size: 11.0pt; margin-left: .75in; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Buffer pool
questions</div>
<ul style="direction: ltr; margin-bottom: 0in; margin-left: .375in; margin-top: 0in; unicode-bidi: embed;" type="disc">
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;">What part of our database is
"active" ?</span></li>
<ul style="direction: ltr; margin-bottom: 0in; margin-left: .375in; margin-top: 0in; unicode-bidi: embed;" type="circle">
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;">Percentage ?</span></li>
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;">Cache the active part; you
have to figure this out</span></li>
<ul style="direction: ltr; margin-bottom: 0in; margin-left: .375in; margin-top: 0in; unicode-bidi: embed;" type="disc">
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;">Look at what data-pages are
cached in memory ?</span></li>
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;">Which databases are cached
in memory ?</span></li>
<ul style="direction: ltr; margin-bottom: 0in; margin-left: .375in; margin-top: 0in; unicode-bidi: embed;" type="square">
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;">Which tables ? </span></li>
</ul>
</ul>
</ul>
</ul>
<ul style="direction: ltr; margin-bottom: 0in; margin-left: .375in; margin-top: 0in; unicode-bidi: embed;" type="disc">
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;">What data pages are being
cached in memory ?</span></li>
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;">What queries are constantly
running, thereby forcing those pages to be cached</span></li>
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;">Analyse using:
Sys.dm_exec_query_stats (BrentOzar.com/go/plans)</span></li>
<ul style="direction: ltr; margin-bottom: 0in; margin-left: .375in; margin-top: 0in; unicode-bidi: embed;" type="circle">
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;">Pulls out the top 10 / 20
queries & how much resources they use</span></li>
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;">Make sure to order by
TotalReads sec</span></li>
</ul>
</ul>
<ul style="direction: ltr; margin-bottom: 0in; margin-left: .375in; margin-top: 0in; unicode-bidi: embed;" type="disc">
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;">Analyse which pages are in
memory (brentozar.com/go/pool)</span></li>
<ul style="direction: ltr; margin-bottom: 0in; margin-left: .375in; margin-top: 0in; unicode-bidi: embed;" type="circle">
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;">Which database & tables</span></li>
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;">This is a one-time snapshot
! </span></li>
</ul>
</ul>
<ul style="direction: ltr; margin-bottom: 0in; margin-left: .375in; margin-top: 0in; unicode-bidi: embed;" type="disc">
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;">It make a long time to run
these queries.. Run them during a course of time. </span></li>
</ul>
<div style="font-family: Calibri; font-size: 11.0pt; margin-left: .375in; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
From easy to hard
(aka, cheap to expensive)</div>
<ul style="direction: ltr; margin-bottom: 0in; margin-left: .375in; margin-top: 0in; unicode-bidi: embed;" type="disc">
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;">Slow, and less than 64 GB ?
Learn how to explain business costs: your time and risks vs $500 RAM. </span></li>
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;">Memory Grants Pending >0 ?
Queries can't start, buy more
memory</span></li>
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;">Compiles / sec over 10% of
Batch Requests / Sec ? </span></li>
<ul style="direction: ltr; margin-bottom: 0in; margin-left: .375in; margin-top: 0in; unicode-bidi: embed;" type="circle">
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;">SQL Server May not be able
to cache plans. </span></li>
</ul>
</ul>
<br />
<ul style="direction: ltr; margin-bottom: 0in; margin-left: .375in; margin-top: 0in; unicode-bidi: embed;" type="disc">
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;">Buffer Page Life Expectancy
> 300 ? You still may need more memory, but start with tuning indexes
& queries. </span></li>
<ul style="direction: ltr; margin-bottom: 0in; margin-left: .375in; margin-top: 0in; unicode-bidi: embed;" type="circle">
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;"><a href="http://brentozar.com/blitzindex">http://BrentOzar.com/blitzindex</a> -
sp_BlitzIndex</span></li>
<li style="margin-bottom: 0; margin-top: 0; vertical-align: middle;"><span style="font-family: Calibri; font-size: 11.0pt;"><a href="http://brentozar.com/go/indextuning">http://BrentOzar.com/go/indextuning</a> </span></li>
</ul>
</ul>
Shuwihttp://www.blogger.com/profile/18225167405815820927noreply@blogger.com0tag:blogger.com,1999:blog-36742793.post-23258555291990749672013-09-12T08:25:00.002-07:002013-09-12T08:49:39.575-07:00Script to download documents from SharePoint that match a certain view<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Today I got a
request to put some files from SharePoint on a USB stick. <br />
My boss is currently in some jungle and although they have (satellite)
SharePoint connection which is fine to conduct searches and "define which
files they need", it's almost impossible to download 11 GB on. </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
That's why he told
me which document-sets he wanted on a USB stick, for HIS boss to take with .</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Our situation is
that we have a few document libraries with about 20.000 files in them. Using
views and document-sets to make sense of it all. Who needs folders (which are
just a piece of metadata to group some files together… right )?! </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
That's all cewl..
But when you want to export a few document-sets and a few hundred other files
grouped together via a value in a column which obviously doesn't exist when you
click the "open in explorer" button. </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
After some thinking
I decided to see if I can export the files to the file-system via PowerShell. </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Pretty soon I found
this article: </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<a href="http://blogs.dawnworld.org/2012/12/09/export-documents-from-sharepoint-document-libraries/">http://blogs.dawnworld.org/2012/12/09/export-documents-from-sharepoint-document-libraries/</a></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
This article from
Jack shows the basics for downloading items from a SP list via PowerShell. My
only problem was that I don't need ALL files, just certain files. <br />
So I created a view in SharePoint that matches my criteria. Basically a view
per destination folder so that it makes a bit sense after exporting the files.
(Folders.. You know.. The old-school stuff before SharePoint libraries !). </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Quite soon I found
out about the SPList Class & .GetItems method (sample script I had uses
SpList.Items to iterate through each item in the list.</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
This method doesn't
support filtering on a view or query, so I changed it to the
SpList.GetItems method. </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<a href="http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.splist.getitems(v=office.14).aspx">http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.splist.getitems(v=office.14).aspx</a></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
You can provide a
query or … VIEW to this method ! YAY… </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Unfortunately I hit
a snag.. In this part:</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
$filelocation =
$item["ows_ServerUrl"]</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
This property
"ows_ServerUrl" in not in the set of properties returned by the
.GetItems method; so you can't use it to provide it to the download component. </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
After some research
I came across this post:</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<a href="http://sharepoint.stackexchange.com/questions/44288/copying-items-from-one-list-in-one-site-to-another-in-another-site">http://sharepoint.stackexchange.com/questions/44288/copying-items-from-one-list-in-one-site-to-another-in-another-site</a></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
And there I saw they
provided the file (item) location using the following command:</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
SPFile file = item.File;</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
So, I tried the same
method in my script and … voilla ! It was working !! </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Downloading the
items that match the view !</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br />
Of course you can just change the filtering in the view, matching whatever
files you wish to export from SharePoint and run the PowerShell… put them in a
folder that matches the view and you're done !</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br />
I didn't come across any other example on how to do this, so I thought let's
contribute and make a blog about it. </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
The final script:</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
#Script to download
documents from SharePoint that match a certain view</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
#site name</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
$sourceurl="http://**/sites/newbusiness"</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
#view name</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
$viewname="tempj"</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
#destination
location</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
$destinationfolder="e:\trial"</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
#view-name to filter
the export</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
$spview =
$list.views[$viewname]</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
$sourceweb =
get-spweb $sourceurl</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
$sourcelist =
"BRGM"</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
$list =
$sourceweb.lists[$sourcelist];</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<span style="font-size: 11pt;">$count =
$list.items.Count</span></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
write-host
"iterating $count items in list"</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
$listItems =
$list.GetItems($spview)</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br />
write-host $listItems.Count</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Write-host
"Items in view"</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
foreach ($item in
$listItems)</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin-left: .375in; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
{</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin-left: .375in; margin: 0in;">
Write-host
"Processing:"</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin-left: .375in; margin: 0in;">
Write-host
$item.File.Name</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin-left: .375in; margin: 0in;">
if
($item.FileSystemObjectType -eq "File")</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin-left: .75in; margin: 0in;">
{</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin-left: 1.125in; margin: 0in;">
$filename
= $item.File.Name</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin-left: .375in; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin-left: 1.125in; margin: 0in;">
#downloadfile</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin-left: 1.125in; margin: 0in;">
$spFile
= $item.File;</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin-left: 1.125in; margin: 0in;">
[string]$targetname
= $filename</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin-left: 1.125in; margin: 0in;">
[string]$target
= $destinationfolder + "\" + $targetname</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin-left: 1.125in; margin: 0in;">
[System.IO.File]::WriteAllBytes($target,$spFile.OpenBinary())</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin-left: .75in; margin: 0in;">
}</div>
<br />
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
}</div>
Shuwihttp://www.blogger.com/profile/18225167405815820927noreply@blogger.com0tag:blogger.com,1999:blog-36742793.post-20674537631799964112013-09-05T00:33:00.001-07:002013-09-05T00:33:15.093-07:00Latest .NET security update breaks SharePoint 2010 datasheet view<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Latest Microsoft
Update Patches <span style="font-weight: bold;">breaks</span> the Datasheet View
on #SP2010 Confirmed on 2 clients now.. (so it was working but after running
windows update, it was not working anymore). It now returns "The selected
cells are read-only" when trying to edit a field in the Datasheet view. (Any
field, all which were previously editable). </div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
DataSheet view gives
"column is read-only" after one of these updates is installed on win7
+ office 2010 client:</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
KB2840628v2
(Security Update for Microsoft .NET framework 4 Client Profile)</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
KB2835393 (Security
Update for Microsoft .NET framework 4 Client Profile)</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
KB2804576 (Security
Update for Microsoft .NET framework 4 Client Profile)</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
KB2789642 (Security
Update for Microsoft .NET framework 4 Client Profile)</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
KB2742595 (Security
Update for Microsoft .NET framework 4 Client Profile)</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
KB2737019 (Security
Update for Microsoft .NET framework 4 Client Profile)</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
KB2736428 (Security
Update for Microsoft .NET framework 4 Client Profile)</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
KB2729449 (Security
Update for Microsoft .NET framework 4 Client Profile)</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
KB2656351 (Security
Update for Microsoft .NET framework 4 Client Profile)</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
KB2604121 (Security
Update for Microsoft .NET framework 4 Client Profile)</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
KB2600217 (Update
for Microsoft .NET Framework 4 client Profile)</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
KB2533523 (Update
for Microsoft .NET Framework 4 client Profile)</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
KB2468871 (Update
for Microsoft .NET Framework 4 client Profile)</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
KB2742595 (Security
Update for Microsoft .NET Framework 4 Extended)</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
Please let me know
if you run into similar problems or how to notify Microsoft about this. I did had to install the 2007 Office System
Drives: Data Connectivity components to regain the Datasheet functionality in
the first place:</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<a href="http://www.microsoft.com/en-us/download/details.aspx?id=23734">http://www.microsoft.com/en-us/download/details.aspx?id=23734</a>
</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
(re-install of this
tool does not regain functionality)</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
<br /></div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
#SharePoint2010</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
#SP2010</div>
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
#Datasheetview</div>
<br />
<div style="font-family: Calibri; font-size: 11.0pt; margin: 0in;">
#Office2010</div>
Shuwihttp://www.blogger.com/profile/18225167405815820927noreply@blogger.com0tag:blogger.com,1999:blog-36742793.post-56152887497790795512013-07-30T01:34:00.002-07:002013-07-30T01:41:38.429-07:00SP2010 Datasheet view not working (but different)Ok, running into a bit of trouble here..<br />
We 'discovered' we actually need a publication date piece of metadata. No worries, updating the Content type hub with the additional metadata, re-publish the content type, activate some timer jobs, wait for it... yeah ! It's been pushed down to all site collections and subscribing farms :)<br />
<br />
Wonderful.<br />
<br />
Now we just need to populate a few thousand documents with the publication date.<br />
Ok, .. let's create a view that filters all files, removes all folder structure (annoying folders anyway!) and filter with [year] in the document name.<br />
<br />
Alright ! Let's use the free batch-editor I got from <a href="http://sp2010batchedit.codeplex.com/" target="_blank">somewhere</a>, check all files out (100 at the time), put in the date, check them all in and ... wait for it ... damn.. not working !<br />
Probably a bug in the batch-edit tool.<br />
<br />
Ok, let's try datasheet view then.. because then, you can just drag the the date down for all items. Datasheet view doesn't work for all kinds of 64 bit reasons. Google is full of potential answers.. but just don't have time to fix this right now / install all kinds of Office stuff / re-install office into 64 bit etc etc etc etc.<br />
<br />
So... let's fire-up the win8 machine inside an Oracle Virtual Machine (free).<br />
Connect to the test-library, datasheet view.. works ! yay ! Ok, connect to production library.. doesn't work ! Nay !!!<br />
Wtf is going on ... ???! Same OS, same browser, same farm.. different libraries.. different views .<br />
<br />
Googling this problem is a challenge, it's a case of .. you've got a certain problem which results in certain key-words (datasheet view + share point 2010 + failure) but you're actually NOT having the problem 90% of the people with these keywords have....I just hate it when this happens .. but also like it, because it's a challenge :)<br />
<br />
So.. after some killing of search results, come across this one: <a href="http://social.msdn.microsoft.com/Forums/sharepoint/en-US/59b06220-3016-4c3f-819c-adb0fa16f7f0/issues-with-the-custom-filter-in-datasheet-view">http://social.msdn.microsoft.com/Forums/sharepoint/en-US/59b06220-3016-4c3f-819c-adb0fa16f7f0/issues-with-the-custom-filter-in-datasheet-view</a><br />
<br />
Where dlanders1 shares his finding around the datasheet view and some influence on filtering ! (Thank You!)<br />
So, I adjust the filter in the view, try datasheet again, works :)<br />
<br />
<b>Conclusion:</b><br />
If you want to use datasheet view and you get the error: The
Standard View of your list is being displayed because your browser does not
support running ActiveX controls.<br />
Don't pay to much attention to the ActiveX controls part of this error... You probably just need to make a simpler filter in the view !<br />
<br />
Because there is so much irrelevant information out there, I'd thought to create a blog about this.. just add a possible relevant drop to the ocean!<br />
<br />
<br />Shuwihttp://www.blogger.com/profile/18225167405815820927noreply@blogger.com0tag:blogger.com,1999:blog-36742793.post-28899176006376653212013-06-25T06:07:00.000-07:002013-06-25T06:07:56.175-07:00Quick fix: Opening images by using the document ID service <b>"Why is this not working ?!!" </b><br />
<br />
<b>What ?</b><br />
Well, I copy the document ID link (copy shortcut) and use this shortcut to create a link in a PDF document, but when I click on it, SharePoint (2010) gives me an overview of the properties, not showing me the image !<br />
<br />
<b>Ok, I'll look at it..</b><br />
Turns out that the document ID retrieval page (/_layouts/DocIdRedir.aspx?ID=XXXXXX) is working for PDF or Office documents but a simple JPG file is not being shown. In stead, it shows the properties of the jpg file. So this:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi0QTWDD89hmNwMCvoCBHG_z36jr6C8bH6VJWuaV9kTXGa6Z7KdprpaTkmGPzcasggqmKGtIeLh-4DLbIrzfUGgeLG_QWCxansSoSciZLwiv8VuSMH_1zF06jHxqE-De9ktCsHOdw/s1600/tmpblog1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi0QTWDD89hmNwMCvoCBHG_z36jr6C8bH6VJWuaV9kTXGa6Z7KdprpaTkmGPzcasggqmKGtIeLh-4DLbIrzfUGgeLG_QWCxansSoSciZLwiv8VuSMH_1zF06jHxqE-De9ktCsHOdw/s320/tmpblog1.png" width="281" /></a></div>
<br />
<br />
<br />
<br />
And not this:<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjD6JvKm0B81noyd1KWdwUMZKxTNOwfg-hCI-M8mXAQh5H-LrXNbIlRo3TCnVQBVOVkylJwhRlQG5YKJRKSJmAMh7-FAkmYeUrfCsit-wvMs1KObIfnPewcD6iUjhJUHzFIMP314A/s1600/tmpblog2.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="173" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjD6JvKm0B81noyd1KWdwUMZKxTNOwfg-hCI-M8mXAQh5H-LrXNbIlRo3TCnVQBVOVkylJwhRlQG5YKJRKSJmAMh7-FAkmYeUrfCsit-wvMs1KObIfnPewcD6iUjhJUHzFIMP314A/s320/tmpblog2.png" width="320" /></a><br />
<b>How to fix this ?</b><br />
The document ID retrieval mechanism is connected to the enterprise search configuration.<br />
To fix this issue, (and other document types that have the same behavior), go to your Central Admin -> Search Service Application Properties -> File Types configuration.<br />
Make sure that the file type you are trying to display is registered in this list.<br />
<br />As a next step, you will need to clear your search index "Index Reset" link on same location in Central Admin. And execute a full crawl.<br />
Immediatly after the full crawl I tried again, unfortunately it still wasn't working. But after an aditional hour of googling I tried again, this time it worked ! (Maybe the crawl said it was finished but it actually wasn't yet !).<br />
<br />
Problemo Solved.<br />
Because there is not much info on this out there, I decided to create a blog-post.<br />
Thanks to this <a href="http://sharepoint.stackexchange.com/questions/33224/why-does-document-id-link-open-property-page-instead-of-document" target="_blank">site </a>for pointing me in the right direction !<br />
<br />Shuwihttp://www.blogger.com/profile/18225167405815820927noreply@blogger.com0tag:blogger.com,1999:blog-36742793.post-85347444206798912132013-05-08T23:24:00.000-07:002013-05-08T23:36:06.498-07:00Using fun and interactive training to create better SharePoint user adaptation <br />
This morning, I found a interesting link in my daily Yammer overview email:<br />
<br />
<br />
<a href="http://blogs.msdn.com/b/kaevans/archive/2013/05/06/create-the-best-app-for-office-2013-in-5-minutes.aspx" target="_blank">Kirk Evans: I'm bringing Clippy back.</a><br />
<br />
<br />
Create the Best App for Office 2013 in 5 Minutes - Kirk Evans Blog - Site Home - MSDN Blogs<br />
<br />
Visiting this MSDN blog post, I soon saw the old clippy back on my screen ! This time, inside a browser window, using a JavaScript Libraries. Instantly my creative mind was going crazy ...<br />
<br />
I'm currently battling to get end-users to work with the beautiful SharePoint 2010 intranet & document management system. I know there are may different reasons why users won't go to the intranet, lack of relevant content; fear for the new; no user training; no clear internal policies of what process (or document) goes where and who to share it with, etc. etc.<br />
<br />
But what if..<br />
<br />
The relatively inexperienced users (in a "work in a digital way kind of way") would be welcomed to the "new" intranet by a cute little puss in boots, showing them around, give a little animation while they execute their first search query. Giving tips & tricks, latest news (link) updates and maybe guide them trough the intranet's possibilities and procedures.<br />
<br />
Since the "agents" are now completely accessible via JavaScript, it must be possible to integrate them into SharePoint (in this case 2010).<br />
Let's do a proof of concept, and see what they users say !<br />
<br />
Demo site: <a href="https://www.smore.com/clippy-js" target="_blank">https://www.smore.com/clippy-js</a><br />
<br />
Thanks Kirk, for bringing Clippy back !Shuwihttp://www.blogger.com/profile/18225167405815820927noreply@blogger.com0tag:blogger.com,1999:blog-36742793.post-89277748866482225052013-04-25T22:27:00.001-07:002013-04-25T22:33:44.294-07:00Manage deletion of index items (SharePoint Server 2010)While re-configuring the search configuration and checking / validating the search functionality I discovered around 15.000 errors in the crawl-log.<br />
The reason for this was that I changed the architecture for a specific site collection. In my first setup, I had a site collection with about 55 sub-sites. Each sub-site had it's own document library and security profile.<br />
<br />
I already migrated about 15.000 documents in some of these sub-sites when I realized this structure wasn't going to work for me.<br />
The maintenance and template creation of these sub-sites was so high, together with some problem in SharePoint with these sub-sites (I could not get the import-server to see the sub-sites to import the data in it, and some users complained they could not see the sub-sites even though they had access to it).<br />
<br />
So, I decided to delete all sub-sites (thank god for powershell) and created 55 document libraries under the main site-collection.<br />
Of course, the search crawlers already crawled the previous content (with sub-sites) and when executing the full crawl schedule now, it resulted in it not finding the 15K documents (and 15K errors!).<br />
<br />
Thanks to <a href="http://technet.microsoft.com/en-us/library/hh127009.aspx" target="_blank">this </a>great TechNet article I found the correct properties to change in the SharePoint 2010 Search Service application. It was quite a challenge because of the many documented other problems with SP2010 Search (404 not found ->Loopback check, etc. etc.) but after a while I found the relevant information :<br />
<br />
<br />
<b>Delete policy for access denied or file not found</b><br />
<div>
<br /></div>
<div>
ErrorDeleteCountAllowed: Default value: 30<br />
ErrorDeleteIntervalAllowed: 720 Hours (30 days)</div>
<div>
<br />
So it would have taken SharePoint 30 + 30 days (2 months !) before it would have removed the reference and resolve the error automatically...<br />
I made the numbers a bit lower and saw this morning that the crawler has deleted 15K references :)<br />
<br />
<br />
Next: to find out why the SearchContentAccess account can't access 2 site collections in a web-application that allows this account FULL READ policy access (and works for the other 50+ site collections..)<br />
Cheers, Jeroen<br />
<br />
Command to change the policy-parameter values:<br />
<br />
<pre style="overflow: auto; padding: 5px;">$SearchApplication = Get-SPEnterpriseSearchServiceApplication -Identity "<SearchServiceApplicationName>"
$SearchApplication.SetProperty("<PropertyName>", <NewValue>)
<pre style="font-family: Consolas, Courier, monospace; font-size: 13px; line-height: 17px; overflow: auto; padding: 5px;"></pre>
</pre>
<br />
<br /></div>
Shuwihttp://www.blogger.com/profile/18225167405815820927noreply@blogger.com0tag:blogger.com,1999:blog-36742793.post-30801491301849860152013-04-16T23:39:00.001-07:002013-04-16T23:39:04.568-07:00SharePoint 2010 "File already exists" backup errorYesterday I asked the community to help me with a #wickedProblem regarding the SharePoint production farm backup. Basically it gave me 8 errors, all caused by one main error:<br />
<br />
<span style="color: #2a2a2a; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 20px;">InvalidOperationException: Job "Search Backup and Restore (first phase backup) application b46e25e7-2307-4...restofid..-query-0 server [servername]" failed with error: The file '\\server\share$\spbr0000\b46e25e7-2307-4..sameasabove-query-0\projects\portal_content\indexer\cifiles\00010002.ci' already exists.</span><br />
<span style="color: #2a2a2a; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 20px;"><br /></span>
I created a blog post for this:<br />
<a href="http://social.technet.microsoft.com/Forums/en-US/sharepointadminprevious/thread/5fa9c4cb-b121-4c95-b01f-01153291ec1c/#0cfce1bb-11c9-4839-b570-dbca48dec704">http://social.technet.microsoft.com/Forums/en-US/sharepointadminprevious/thread/5fa9c4cb-b121-4c95-b01f-01153291ec1c/#0cfce1bb-11c9-4839-b570-dbca48dec704</a><br />
<br />
and thanks to <a href="http://social.technet.microsoft.com/profile/cornelius%20j.%20van%20dyk/?ws=usercard-mini" style="border: none; color: rgb(102, 51, 179) !important; font-family: inherit; font-size: inherit; font-style: inherit; font-variant: inherit; line-height: inherit; list-style-type: none; margin: 0px; padding: 0px; text-decoration: none !important; vertical-align: baseline;" target="_blank">Cornelius J. van Dyk</a> I found out that my 'emptying the backup folder' before starting the backup, wasn't really effective because of the default "Don't show hidden files, folders, or drives" windows explorer setting.. Yup, changed the setting to actually 'see' all files made me able to 'delete' all files on the share :)<br />
<br />
Ran the backup again and this time it finished:<br />
<br />
<div style="margin: 0in 0in 0in 0.375in;">
</div>
<div style="margin: 0in 0in 0in 0.375in;">
<span style="font-family: Calibri;"><span style="font-size: 15px;">Finished with 0 warnings.</span></span></div>
<div style="margin: 0in 0in 0in 0.375in;">
<span style="font-family: Calibri;"><span style="font-size: 15px;">Finished with 0 errors.</span></span></div>
<div style="margin: 0in 0in 0in 0.375in;">
<span style="font-family: Calibri;"><span style="font-size: 15px;"><br /></span></span></div>
<div style="margin: 0in 0in 0in 0.375in;">
<span style="font-family: Calibri;"><span style="font-size: 15px;">Another problem solved :)</span></span></div>
<br />
Shuwihttp://www.blogger.com/profile/18225167405815820927noreply@blogger.com0tag:blogger.com,1999:blog-36742793.post-65478735323945004662013-04-09T22:36:00.001-07:002013-04-09T22:41:14.247-07:00How I added 250.000+ documents and 3.5 Million pieces of metadata to 152 #SP2010 document libraries <br />
The approach I took was the following:<br />
<br />
First, created a separate site collection to hold the 8000+ documents, just from a content database point of view this made most sense. <br />
<br />
After enabling all branding features on this site collection, I created a template document library which had the appropriate content type enabled, correct view and managed metadata navigation-configuration. <br />
<br />
Next, I used a tool called DocKIT from VYAPIN software to create a basic-metadata-excel document from a file share which contained the documents that need to go to SharePoint. <br />
<a href="http://www.vyapin.com/products/sharepoint-migration/dockit/sharepoint-migration.htm">http://www.vyapin.com/products/sharepoint-migration/dockit/sharepoint-migration.htm</a><br />
<br />
This basic .XLS contains all file references (path) and per record (file), you can add the metadata you need to fill the content type in the destination location (library). Because it's all in excel (the tool reads from this document later), it's easy to copy and paste the metadata in the document / complete the metadata in bulk and offline.<br />
<br />
Next, I created a document library per 'company-documents'; this was done mainly for security reasons. (Persons who have rights to company-A's document, don't necessarily have rights to company-B's documents. To manage these rights in bulk, separate document-libraries are used. <br />
<br />
I started off by creating separate sites (sub-sites) per company (152 in total), using a powershell script to create the sub-sites, but I left this idea after some sites seemed to be inaccessible (just did not exist) for some users and for the import tool. Very strange behaviour from SP2010 but I didn't have time to do huge research on why this was happening. <br />
<br />
The metadata.xls document contained the 'destination library location' for each document (again, a copy & paste within excel makes this definition very fast) and after completing all metadata for the 8000+ documents it was ready to use the file as import data for the DocKIT tool. <br />
This tool reads the file from it's "Path" location, uploads it to the destination library on SharePoint, attaches the right content type to the file and applies the configured metadata, all automatically. <br />
<br />
After completion, 8000+ documents were uploaded in 152 document libraries, mostly automated :) <br />
Each document contains 14 pieces of metadata, in total more then 112000 metadata additions in SharePoint 2010. <br />
<br />
I created the document libraries automatically, based on the data in the excel document (company name is a piece of metadata that was available as the document library name) , using the library template as one of the parameters. <br />
<br />
I'm happy to share more details if need-be on the configuration of DocKIT or any of the other items described here :) <br />
<br />
<b>Update:</b> using the same principle, I also created a "Public Domain Documents Silo" for this client, uploading 220.000 documents (mostly OCR'd PDF's), all with around 15 pieces of metadata attached. (I wrote a little C# program that got most of the existing information like folder structure into the metadata.xls document). <br />
This site collection is fully search able (SharePoint 2010 search) and VERY fast, under 3 seconds. The nice thing is that because all documents have so much metadata, the search if fully refine able. Creating a great end-user experience.<br />
<br />
<br />Shuwihttp://www.blogger.com/profile/18225167405815820927noreply@blogger.com0