SharePoint Experts, Information Architects, Expert Witness

We provide consulting in a broad array of business and technology from architecture to design to deployment of global systems with a focus on surfacing data in the enterprise. Specialists in Microsoft, we are a premier provider of SharePoint Expertise (including 2016 and Office 365). We also provide Expert Witness/Legal Expert in eDiscovery, source discovery, patent infringement, piracy and more! We also have established SICG DLDS s.a. - our counterpart in Costa Rica that specializes in water systems (http://www.crwatersolutions.com) - Contact me direct: david_sterling@sterling-consulting.com or call 704-873-8846 x704.

Search This Blog

Saturday, February 17, 2018

Status of DBCC Shrink or Shrink through the GUI SQL Server

A note so I don't forget - if while doing a Database Shrink, you may want to know the status - the TSQL command is this:

SELECT percent_complete, start_time, status, command, estimated_completion_time, cpu_time, total_elapsed_time
FROM sys.dm_exec_requests
WHERE command = 'DbccFilesCompact'

Here's the detail on the table:

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-requests-transact-sql

The key field you may be interested in is estimated_completion_time - to get the hours/minutes, use this:

Select dateadd(s, convert(bigint, <value>) / 1000, convert(datetime, ''))

For example:

Select dateadd(s, convert(bigint, 162114) / 1000, convert(datetime, ''))

will return '1900-01-01 00:02:42.000', i.e. 2 minutes, 42 seconds.

Be aware that the estimated_completion_time is just that, estimated but it will give you a rough idea on if you should grab another cup of coffee or go out to lunch.


Sunday, February 11, 2018

The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator

You may catch this error when working with lists:

The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator

There are several fixes to this, most notably by updating the thresholds in the web application settings in Central Administration (on premise) but you can't do it on Online.

However, even working with both on-premise and online developing a 'recent file/folder/doc set' view tool, it became apparent that changing the view limits didn't make a difference anyway. 

For little queries (a single list with a few thousand items) you don't usually see this. However, if you are doing web wide, SiteColllection or Recursive queries, this becomes a real problem in a live site setup.

It turns out that it's all in the CAML query - yes, yet another anomaly we find that's never documented. So the issue that in the CAML query, we have to be careful what we use in terms of nesting - a query can be really long (for example, filtering out using <Neq> to eliminate specific file types - for example, xml, xsl, css using the DocIcon field) or short HOWEVER, if your CAML query includes both <And> and <Or> viola, you get the error.

My assumption here is that the OR query portion means there's too much hashing going on to properly calculate the number of rows it will return (FYI Row Limit doesn't fix this). 

The fix can be a pain but it works - make sure that you ONLY use <And>. This can complicate your queries but if you think them through, you can always work it out.

UPDATE: This MAY not fix it either. It turns out that if the database is exceedingly large (like over the limit), the error still occurs.

One other tidbit: 

If you are looking for recently created or modified files (and folders, etc.), be sure to only use the Modified date in the query (if you use Created then Or Modified, the query blows up as above). Remember that the Created/Modified dates are always filled when created so you do have add some logic to split them (i.e. If Created = Modified the status is "created" - if Modified greater than Created, status = modified).

Thursday, February 1, 2018

Add a page title to your master page (SharePoint & SharePoint Online)

Ever get a bit annoyed with the ribbon in SharePoint blocking the page/list/whatever title? Can't tell what folder (gasp you use folders?) you are in? It can be quite difficult for new users. So quick fix - simply edit your Master to add a little snip it as follows.

Edit your master using Designer. Make a copy of the master you are using now and create a new one. In the code, find the line below and paste in the code snippet - modify the style as you see fit:

RIGHT AFTER THIS LINE:
<SharePoint:AjaxDelta runat="server" id="DeltaSuiteLinks" BlockElement="true" CssClass="ms-core-deltaSuiteLinks">

PASTE THIS IN:
<span id="pageDisplayName" style='text-align:left;'></span>
<style type="text/css">
.TopPageLinkStyle { font-weight:bold; text-decoration:none; }
a.TopPageLinkStyle { color:#ffffff; }
a.TopPageLinkStyle:visited { color:#ffffff; } 
a.TopPageLinkStyle:hover { color:#00FFFF; } 
a.TopPageLinkStyle:active { color:#00FFFF; } 
</style>
<script type='text/javascript'>
var trimIt = document.title;
if (trimIt.length > 0)
{
var pgUrl = window.location.href;
var dispIt = trimIt;
document.getElementById("pageDisplayName").innerHTML = "Current Location: <a href='" + pgUrl + "' class='TopPageLinkStyle'>" + dispIt + "</a>" + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";;
}
</script>

AND IT MUST BE BEFORE THIS LINE:
<div id="suiteLinksBox">

Save your new master and publish it. Navigate to your site and try it out on a sub site - the result should be something like this:


Notice that the name is a link - it allows one click away from going back instead of having to click the ribbon. Also, check it out in a List! Enables getting back to the main view quickly (and includes it in the title):


Tuesday, January 23, 2018

Using SharePoint Web Services from Anonymous Mode

NOTE: This is only applicable to on premise.

You might have a need to access the SharePoint Web Services (in my case it was the UserProfileService.asmx) from an anonymous site. The issue is authentication - through both CSOM and Server Side code, there are problems with authentication.

A test with elevated, impersonation using a token and impersonation in .NET failed to produce results. In all cases, the permissions were blocked (user not part of the site, etc.). The actual fix was from a post I stumbled on wherein you can add a section to the Web.config to make it work.

Look for the end tag </system.web.extensions> and add the following just below it:

<!-- Disable anonymous access to _vti_bin -->
<location path="_vti_bin">
    <system.web>                 
        <authorization>
            <deny users="?" />
        </authorization>
    </system.web>
</location>

This effectively blocks anon-users from accessing _vti_bin (a good thing anyway) but permits the web services to work (you still need to set network credentials).

FYI: You should be using SPWebConfigModification to make the change - while I've documented this myself, here's another quick guide: https://blogs.technet.microsoft.com/heyscriptingguy/2010/09/14/use-powershell-to-script-changes-to-the-sharepoint-web-config-file/


Kudos to: http://www.sharepointdiary.com/2012/06/sharepoint-web-services-exposed-to-anonymous-users.html#ixzz551Cj8h3j



Wednesday, January 17, 2018

Getting the Public Key Token for a DLL (and where the heck is sn.exe?)

An oldie but a goodie...(and for my own memory). To get the Public Key Token for a DLL is to simply use the "Strong Name Tool" (more from Microsoft here: https://docs.microsoft.com/en-us/dotnet/framework/tools/sn-exe-strong-name-tool).

The SN.exe tool can be found under the SDK's folder, under C:\Program Files (x86)\Microsoft SDKs\Windows. You can simply open the folder and search for sn.exe and pick the latest based on what version of .NET you have. For example, it's C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.6.1 Tools.

Note: If you use the Developer Command Window, the path will already be loaded.

Simply navigate to the folder where the DLL is and enter:

sn  -T <ddl name>.dll

Example:

sn -T MyNewDLL.dll

* If you don't know where the DLL is, you can find it under the bin\debug or bin\release folders in your Visual Studio project.