Wednesday, December 13, 2017

Python SQLite location, version, and navigating around

I am participating in a hackathon which made it necessary for me to learn some Python. In particular, I was documenting on how to get SQLite setup as nobody seemed to get the full details. As usual, I try to go first principles on this so that I can get a better understanding.

I am using Visual Studio 2017 Community and have the Python Development workload. This made Python available to me which is great, but I also needed SQLite. The setup for SQLite is very straightforward as it is simply a matter of copying a DLL. However, big unanswered questions remain that are not really answered anywhere for a newbie like me: Where do I copy the SQLite DLL? How does the Python code find the SQLite DLL?

Long story ... but it appears that SQLite is actually bundled into Python (at least in v3.6 that I am using).

import sqlite3

# WTH - that is not the right version???
# Aha - found this that explains it:


# Hmm... I wonder what other properties are available?

... and from the site above, I also upgraded to the latest version

Tuesday, July 18, 2017

SPEndPointAddressNotFoundException: There are no addresses available for this application

From time to time, I would receive support requests where some backend services was stopped for whatever reason. From an end user point of view, they only see that something is no longer working. However, looking at ULS, it becomes very obvious as you would see this kind of exception:

SPEndPointAddressNotFoundException: There are no addresses available for this application.

Typically, this is either a matter of restarting the service instances and / or running the Application Address Refresh Job

Here is also some powershell to see what the SharePoint Round Robin Load Balancer sees:

$proxyName = 'proxy name'
$saName = 'service application name'

$proxy = Get-SPServiceApplicationProxy | Where-Object {$_.TypeName -eq $proxyName}
$loadBalancer = $proxy.LoadBalancer
$loadBalancerContext = $loadBalancer.BeginOperation()


$sa = Get-SPServiceApplication -Name $saName
$sa.EndPoints | Format-List

One other common thing is to be specific about the SharePoint Round Robin Load Balancer. I often have to clarify that this is for backend services as often SharePoint admins get this mixed up with a WFE load balancer.

Also, here is a link to a very useful description about the SharePoint Topology Service:

Thursday, July 6, 2017

Add-Type : Could not load file or assembly 'Microsoft.SqlServer.Smo, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.

I am new to SQL Server Management Objects (SMO)

I tried

Add-Type -AssemblyName "Microsoft.SqlSever.Smo"

but got this error:

Add-Type : Could not load file or assembly 'Microsoft.SqlServer.Smo, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.

Knowing that my machine has had multiple versions of SQL and Visual Studio installed at some point, I decided to opt for the latest and provide the FullName of the assembly instead:

Add-Type -AssemblyName 'Microsoft.SqlServer.Smo, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91'

That seemed to work. Yes, I did get the assembly first to verify that all the referenced assemblies are there. Not sure why the loader would default to, but that is not my problem right now.

Wednesday, June 28, 2017

Grabbing EML files from drop folder before job-email-delivery gets them

Often when troubleshooting incoming email problems in SharePoint, we would temporarily disable the Microsoft SharePoint Foundation Incoming E-Mail (job-email-delivery) timer job, then send an email and watch the folder for an EML file to show up.

Alternatively, you can register a FileSystemWatcher to listen on a Created event. Then, this code can copy out the EML file to some safe place before job-email-delivery gets it.

To install the listener:

$destination = 'c:\Windows\Temp\maillogs' # Make sure your powershell user can write here
$dropFolder = 'C:\inetpub\mailroot\Drop' # Drop folder as configured in SharePoint

$filter = '*.eml'

$fsw = New-Object System.IO.FileSystemWatcher($dropFolder, $filter)
$fsw.IncludeSubdirectories = $false
$fsw.NotifyFilter = [System.IO.NotifyFilters]'FileName,LastWrite' # seems to need this to avoid some IOExceptions

$onCreated = Register-ObjectEvent -InputObject $fsw -EventName Created -Action {
  Write-Host "$($Event.TimeGenerated) : Incoming email file - $($Event.SourceEventArgs.Name)"
  Copy-Item $Event.SourceEventArgs.FullPath -Destination $destination -Force -Verbose

To remove the listener:

Unregister-Event -SourceIdentifier $onCreated.Name


  • Does not account for large incoming files that are temporarily locked while being copied
  • Not sure if this causes problems if job-email-delivery tries to access (or even remove) file while Copy-Item is being called.
  • Not sure if this is guaranteed to happen before job-email-delivery
  • Not sure of the performance impact

Thursday, April 27, 2017

SharePoint 2013 - Sign in as a different user

I keep forgetting this, so noting it down here:


Wednesday, April 19, 2017

Calling constructor of an internal class

I keep running into code that tries to "hide" from being used such as making classes internal or private. It is a royal pain in the ass when debugging.

Fortunately, reflection is very easy with powershell so we can do something like this:

$parameters = @(some-array-of-parameters)
$typeName = 'full-name-of-the-type'
Add-Type -AssemblyName $assemblyName
$assembly = [Reflection.Assembly]::Load($assemblyName)
$bindingFlags = [Reflection.BindingFlags]"Default,NonPublic" # also include Static if needed
$t = $assembly.GetType($typeName)
$m = $t.GetConstructors() | Where-Object {$_.GetParameters().Count -gt 0} # in my case, I happen to be looking for a non-default constructor
$myclassinstance = $m.Invoke($parameters)

Tuesday, April 4, 2017

SocialDataManager.SocialDataManager Proxy has no ServiceContext available

Just ran into this issue when trying to instantiate Microsoft.Office.Server.SocialData.SocialTagManager in powershell:

$siteUrl = 'some-site-url'
$site = Get-SPSite $siteUrl
$serviceContext = Get-SPServiceContext($site)
$msstm = New-Object Microsoft.Office.Server.SocialData.SocialTagManager($serviceContext)

New-Object : Exception calling ".ctor" with "1" argument(s): "UserProfileApplicationNotAvailableException_Logging :: SocialDataManager.SocialDataManager Proxy has no ServiceContext available."
At line:1 char:10
+ $msstm = New-Object Microsoft.Office.Server.SocialData.SocialTagManager($service ...
+          ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [New-Object], MethodInvocationException
    + FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand

Not much out there, but the UserProfileApplicationNotAvailableException provided a clue. I did not track down the specific cause, but it looks like I was not running powershell with an account that admin permissions in the UPA or MMS. I did not have time to dig further, but instead simply opened up my powershell as the farm account and did not run into the same problem.

Wednesday, March 22, 2017

Finding Alias for SharePoint Email Enabled Lists

As usual, there are several ways to do this that can be readily found via a search

Powershell: Loop through each Web Application, Site Collection (SPSite), Subweb (SPWeb), List. Then look in the EmailAlias property for what you are searching. In large environments, I suspect that this would be very slow and resource intensive. Here is a Foreach-Object pipeline that would do the trick:

Get-SPWebApplication | ForEach-Object {$_.Sites} | ForEach-Object {$_.AllWebs} | ForEach-Object {$_.Lists} | Where-Object {$_.CanReceiveEmail -and $_.EmailAlias} | Format-Table EmailAlias, Title, ParentWebUrl -AutoSize

SQL: One way is to essentially do the above but via the content databases, but this one also seems resource intensive:

USE [Some_Content_DB]
SELECT tp_Title, tp_EmailAlias, FullUrl
  FROM [dbo].[AllLists] lists
  LEFT JOIN [dbo].[AllWebs] webs ON lists.tp_WebId = webs.Id
  WHERE tp_EmailAlias IS NOT NULL

SQL: I decided to see what the Microsoft SharePoint Foundation Incoming E-Mail timer job does (job-email-delivery) since the timer job should be quite fast. Looking at the code, I can see that it calls the proc_getEmailEnabledListByAlias stored procedure. Diving into that, I see that this may be a better query:

SELECT * FROM [SharePoint_Config].[dbo].[EmailEnabledLists] 

However, I did also run across a post mentioning that it is possible the Config database and the Content database can be out of sync. So, it may still be necessary to choose the correct method depending on the problem one is trying to tackle.