Sue Hernandez's SharePoint Blog

SharePoint and Related Stuff

Monthly Archives: May 2015

SharePoint 2013 – Recover deleted Default authentication Zone

Yes, yes, by the title you’re saying how in the world did you delete your default zone.  Well on my test platform I had installed a CU and it went horribly wrong, and in an effort to fix it, I mistakenly blew away the default zone.

Here's what I did to recover it:

$wa = Get-SPWebApplication "https://YourWebApplicationUrl.com"
$def = [Microsoft.SharePoint.Administration.SPIisSettings]::CreateFromExistingWebSite("IIS Displ Name")
$wa.IisSettings["Default"] = $def
$wa.Update()

So just go in to IIS Manager to make sure you have the right display name for the IIS web site related to that web application.

T-SQL query to get most recent 1 entry for each user

Hi, usually I write about SharePoint.  This week, I was attempting to get a list of all users on all the web applications on our Farm for the last 365 days.  Luckily one of our team members was already parsing through IIS Log files and writing the entries to a custom DB to be processed later.  These entries were written to the db basically exactly as they appear in the IIS Log – 1 entry for each and every page hit (I think he might have stripped out everything except .aspx pages).

So there’s currently 41,800,000 + rows in this custom DB, and really it’s still in a testing phase, because no indexes have yet been created on it (the concept is still being tested).  So I needed a way to say “Give me the Login Name and the URL of every unique user in the last 365 days, and the URL should be the most recent one from that unique Login Name, as long as it’s within 365 days”.

I haven’t used SQL in a while, and honestly I’m not a T-SQL guru, so I began to write a cursor to do this.  Yea.   Well 45 minutes later, when the query was still running and I had barely gotten a handful of anything, I thought there must be a better way.

I won’t go into the full explanations, because I don’t know if I’d be explaining it correctly, but here’s what I ended up with:

DECLARE @fromDate datetime
SET @fromDate = '5/5/2014'
SELECT RowNum, UserName, FullPageUrl, LastLogEntry
FROM (
   SELECT ROW_NUMBER() OVER(PARTITION BY logs.UserName ORDER BY logs.LogDateTime DESC) AS RowNum,
      logs.UserName, wa.Url + logs.Uri AS FullPageUrl, logs.LogDateTime AS LastLogEntry
   FROM IISLogs logs
      INNER JOIN WebApps wa ON logs.ApplicationId = wa.Id
) _
WHERE RowNum = 1
  AND LastLogEntry >= @fromDate
ORDER BY LastLogEntry ASC   -- or you could order by UserName or FullPageUrl

 

Basically this is saying Group By UserName, and only give me the first one, but take the first one in order of the Log Date in descending order.

This query took just over 5 minutes.  Also I have no idea why but the _ right before the last where clause absolutely has to be there.  I will look that up some other time.

SharePoint 2010 Error nativehr 0x80131904

We came across a strange error the other day, reported by a user in SharePoint 2010.   When the user attempted to add a new item to a Contacts list, the page threw an error.  When we debugged it on the server and showed the server error, it was basically as follows:

<nativehr>0x80131904<nativehr>
System.Runtime.InteropServices.COMException

with stack trace containing:

SPRequestInternalClass.AddOrUpdateItem
SPRequest.AddOrUpdateItem
SPListItem.AddOrUpdateItem
SPListItem.UpdateInternal

We looked in the ULS logs and found the following 2 errors:

  • No XsltListViewWebPart was found on this page[/path/siteColl/site/Lists/ListName/NewForm.aspx?IsDlg=1].  Hiding key filters and downgrading tree functionality
  • Cannot insert the value NULL into column ‘tp_DocId’, table ‘ContentDBName.dbo.AllUserData’; column does not allow nulls. INSERT fails.

From reading the blog that led me to the answer (below) there may also be an event log error, number 5586 (Unknown SQL Exception 515 occurred).

So I found a blog by Allen Wang about a similar problem he had with a survey.  SP2010 Survey List Error and Event ID 5586.  He explained in his post that when SharePoint owners add a certain number of columns to their List/Library, all of the same data type, that the SQL storage starts implementing what is called row wrapping.  So internally in the database, one of the columns is tp_RowOrdinal (NEVER EVER EVER EVER modify the DB directly), and this number gets increased once you go past the row wrapping limits (see below for PowerShell commands to see this).  Here’s a summary of the data types:

Single Line of Text:  Wraps after 64 columns
Mult Lines of Text:  Wraps after 32 columns
Choice:  Wraps after 64 columns
Number:  Wraps after 12 columns
Currency:  Wraps after 12 columns
Date/Time:  Wraps after 8 columns
Lookup:  Wraps after 16 columns
Boolean (Yes/No):  Wraps after 16 columns
Person/Group:  Wraps after 16 columns
Hyperlink/Picture:  Wraps after 32 columns
Calculated:  Wraps after 8 columns
GUID:  Wraps after each and every column
Int:  Wraps after 16 columns
Managed Metadata:  not sure, seems to be 4 or 6

Please reference this post from Microsoft (I know it’s 2013, but 2010 doesn’t seem to be around any more):  Software boundaries and limits.

So what had happened in our case was that there were over 78 columns in the list.  When the list Owner modified the column order to move the newly added columns to the top of the Edit/New form, that’s when it broke.  That was because RowOrdinal=1 was placed above RowOrdinal=0.

To see what I’m talking about without digging in the database, use PowerShell:

$web = Get-SPWeb https://yourURL.com/path/siteCollection/site
$list = $web.Lists["Name Of Your List"]
$field = $list.Fields.GetField("Field Display Name")
Write-Host $field.SchemaXml

This will output something like this

<Field Type="Number" DisplayName="Field Display Name" 
   ID="{89d9e81e-e31a-402d-a8e4-05935dbb29ac}" 
   SourceID="{9007df08-a644-461b-9af5-b39252f5bb93}" 
   StaticName="_x004e_um10" 
   Name="_x004e_um10" 
   ColName="float10" RowOrdinal="0" Version="1"/>

If you get the SchemaXml of the last few columns, you might see RowOrdinal=”1″ inside the SchemaXml.  If you see that, you cannot place that column above another column of the same type which has a 0 ordinal.  So for example this would be OK:

       NumberColumn10
       NumberColumn13

but this would break:

       NumberColumn13
       NumberColumn10

 

DISCLAIMER:

I just tried to replicate this in my VM of SP 2013 and I added over 500 number columns and over 100 user columns and was unable to get RowOrdinal to be anything but 0.  Not sure why.

Thanks to Allen Wang for providing solution.