Openning excel doc in SharePoint errors - Read only document or security certificate problem.

This was very tricky but figured out eventually.

SharePoint 2010 and 2013, excel 2010.

Microsoft update KB3054886 will brake the ability to open excel documents with content type.

Just go to your installed updates remove "KB3054886" No need to restart computer just Explorer.

I hope this helps. It took me several hours to fix it and there are no other post online about this.

Management Reporter 2012 format error - FRX migration

I had this error since I have migrated from FRX.

The error was a general error "An unknown error has occurred"
Went to configuration console the I have found "System.OverflowException: Value was either too large or too small for a Decimal."

Solution:

It was the format. I had a custom format for the percent, at the column level. Change any custom format to their standard, in this case percent. Then like magic everything worked just fine.

I hope this helps because I looked all over the net for a solution and it looks like nobody have had this issue before.

Find records where your column has numbers or not SQL Server

Well, after looking for a while online I was kind of surprised nobody have had blogged about this. Maybe I was looking for the wrong keywords I don’t know

I was able to come up with this simple way.

Find records where your column has numbers :

WHERE PATINDEX ( '%[^0-9]%' , [Field Name]) > 1

 

Find only records with number on the column
WHERE PATINDEX ( '%[^0-9]%' , [Field Name]) = 0

Find records where your column does not have numbers

WHERE PATINDEX ( '%[^0-9]%' , [Field Name]) = 1

 

I hope you enjoy this.

Mass add link to Navigation Pane SharePoint 2010 Power shell

--add a link to navigation pane mass sites

$sites = Get-SPWeb "Site Here"
foreach ($site in $sites.webs)
{
$web = Get-SPWeb $Site.URL
$qlNav = $web.Navigation.QuickLaunch
$qlHeading = $qlNav | where { $_.Title -eq "Libraries" }
$linkNode = New-Object Microsoft.SharePoint.Navigation.SPNavigationNode("Name", "URL", $true)
$qlHeading.Children.AddAsLast($linkNode)
$qlHeading.Update
$web.Update
$web.Dispose
}

enjoy

Hierarchy Query

This I Took from another Blog but is so cool I have to re post it,

This is to make a level hierarchy query

Only SQL server 2008 and up

with MyCTE AS
(
SELECT      
TYPEID,
ParentTypeId,
TYPE,
1 as level,
CAST(CAST(TYPEID AS BINARY(4)) AS VARBINARY(8000)) AS SortPath
FROM          
TASKTYPE
WHERE      
(ParentTypeId  IS NULL)
UNION ALL

SELECT      
TY.TYPEID,
TY.ParentTypeId ,
TY.TYPE,
EL.level + 1,
CAST(el.SortPath + CAST(TY.TYPEID AS BINARY(4)) AS VARBINARY(8000)) AS SortPath
FROM            TASKTYPE as TY
INNER JOIN MyCTE AS el on TY.ParentTypeId  = el.TypeID
WHERE        (TY.ParentTypeId  IS NOT NULL)

)
Select Typeid, parenttypeid, type = SPACE((level-1)*4)+type , level
from MyCTE
ORDER BY SortPath

Enjoy