Killing Active SQL Connections

So there are many times we are waiting to take a database offline or delete a database. I finally decided there had to be a better way than to wait … specially when this is my development environment. So here are two queries we can use.

First, find the active connection for all the databases:

  db_name(dbid) as [DB Name],
  count(dbid) as [Active Connections],
  loginame as [Login Name]
from sys.sysprocesses
where dbid > 0
group by dbid, loginame;

Then from that, we can determine which database needs the connections terminated. Or you can use this SQL directly if you already know which database you want to kill the active connections on:

set nocount on;
declare @databasename varchar(100);
declare @qry varchar(max);
set @qry = '';
set @databasename = '[Database name]';
if db_id(@databasename) < 4 begin print 'Hey, system database connections cannot be killed!!!'; return end select @qry=coalesce(@qry,',' )+'kill '+convert(varchar, spid)+ '; ' from master..sysprocesses where dbid=db_id(@databasename); if len(@qry) > 0
  print @qry;

Hope you all find this as useful as I have. Cheers!

Build a SP2013 Content Enrichment Service

Hi, yes it has been a while. Its been a brutal year.  I now have 4 girls, two recent twins. Probably doubled the gray hairs on my head by now.  Had to move to a bigger home.  Had to get a minivan to accommodate the bigger family. After moving into the house, I have been fixing things for 3 months now … an still not done.  There’s much more as well. Nonetheless, here I am … now to CEWS (Content Enrichment Web Service).

So there are many articles out there, between several I was able to get something working. Lets start with the basics:

  • Content Enrichment is a process by which you can add/modify/enhance metadata to the search index directly.
  • Content Enrichment does not add metadata to the actual document or list item.
  • Content Enrichment only works for on-premise farms.  Thus, not Office 365.
  • Content Enrichment is deployed as a web service.
  • Content Enrichment can be great if done properly … poor code will slow down search very significantly.

The basic steps for creating and deploying a CEWS are:

  1. Using Visual Studio 2012 or 2013, create a WCF Service Application project.
  2. Create your Content Enrichment Service
    1. Implement the IContentProcessingEnrichmentService interface from the Microsoft.Office.Server.Search.ContentProcessingEnrichment assembly which you must reference (located at c:\Program Files\Microsoft Office Servers\15.0\Search\Applications\External\ by default; unless you installed SharePoint somewhere else).
  3. Register your Content Enrichment Service with Powershell
  4. Test and Validate
  5. Once satisfied, package your solution
  6. Deploy your solution to Azure or IIS (or elsewhere as you need).

Here a couple other resources for more information:

  • How to: Use the Content Enrichment web service callout for SharePoint Server

  • Custom content processing with the Content Enrichment web service callout


Disable Loopback Check

Disclaimer: DO NOT DO THIS IN PRODUCTION … period.  Why? The loopback check is a security feature that is designed to help prevent reflection attacks on your computer/server.  As such, it is designed to fail fail authentication if the FQDN or the custom host header that you use does not match the local computer name.

So once upon a time (several times a year at least), you decide to build a new clean virtual machine to code on, you make it pristine with every patch, service pack, cumulative update, and the latest development tools.  It is pure awesomeness.  You build your web application with your dev site collection. You open a browser to it, and boom … you get prompted for credentials.  Alright!!!! It is alive.  You login … and you login again, and again.  Uh, what just happened; Nada … a white freaking page of nothingness.  If you use fiddler (or your tool of choice) to see the response, you will notice a “HTTP 401.1 – Unauthorized: Logon Failed”.  You can try again, but are logging in just fine.  You are being denied.

Long story short, you have been denied by the Loopback check feature which has been around since Windows 2003 (SP1) and Windows XP (SP2).  A very very long time.  The loopback check is a security feature that is designed to help prevent reflection attacks on your computer/server.  Unless you create web applications starting with the local computer name, you will be denied. That is why Central Administration works fine (unless you decided to give it a FQDN).  There are to solutions explained here (and all over the place on the web):

Here is a tool that implements the recommended approach (method 1):

or visit my buddy’s blog @

Here is the simple powershell command (method 2):

New-ItemProperty HKLM:\System\CurrentControlSet\Control\Lsa -Name “DisableLoopbackCheck” -Value “1” -PropertyType dword

The powershell approach is obviously the easy route.  I do encourage that you eventually look at the tool as this will be a better and safer long term approach.  For the curious, even in production, you shouldn’t have to do this in production at all.  You can diagnose just fine without it … log into to production for diagnostics should not be your first course of action.  If you have to do this, remove it after doing diagnostics … better yet, before you log off.

SharePoint Saturday Utah

SharePoint Saturday Utah has begun with a great crowd. I presented my session “Unlock your Big Data with Analytics and BI on Office 365” which is a Level 200 class. In my session I discuss how companies have huge amounts of data waiting to be explored. With Azure HDInsights (Microsoft’s Hadoop cluster solution in partnership with Nortonworks) you can realize the value of your data. With Microsoft Excel 2013 and Office 365, you have a complete platform for BI solutions and services. PowerPivot, Power View, Power Query, Power Map and Power BI Sites empowers users analyze and make decisions using structured and unstructured data.
Attendee Takeaways:
1. Learn to setup and configure HDInsights on Microsoft Azure.
2. Understand how to use Excel for BI capabilities.
3. Build a BI Dashboard in Office365.

Find the slide deck here:

and the code demo and sample here:

Finding duplicate values in a SQL table

Even though this is simple, I keep needing this every once in a while. This is for SQL Server.

To Find the list of fields for which there are duplicates (or multiple records), use:

SELECT [field1],[field2],[field3], count(*) as DUPE_COUNT INTO #DupeList FROM table_name GROUP BY [field1],[field2],[field3] HAVING count(*) > 1

Now to see the dupes:


Now, I prepare my delete … and verify I see only what I want to clean up:

SELECT * FROM table_name
WHERE [field1] in (SELECT [field1] FROM #DupeList)
ORDER BY [field1],[field2][field3]

Here we delete the dupes … be sure the query matches the select query to not delete unexpected data/values: (I commented this deliberated to avoid accidents)

–Clean up duplicate
–DELETE FROM table_name WHERE [field1] in (SELECT [field1] FROM #DupeList)

And we always clean up temp tables:

–Clean up the temp table

For more details: