Microsoft Comes Calling

I have been ignoring this blog...  I know...  I start to write something then I get busy and well...

Anyway, I get calls and emails from recruiters all of the time.  Most are for consulting jobs here and there, all require me to move somewhere, sometimes for a three month contract.  No thanks.  I get these contacts because I try to keep my resume and job information updated on the Internet sites.  It keeps the head-hunters informed on what I am doing, and the occasional call or email for the one off silly contract here and there is a price that I pay.

The reason I do such things is for the rare occasions that a good legitimate opportunity comes my way.  I found my last job this way, and now something very interesting has come my way....  A recruiter from Microsoft has given me a call.  I have to admit that I have always been in awe of the Redmond company.  Despite their shortcomings they have been one of the most innovative and influential companies in human history.  Getting a call from the recruiter was a big deal for me.

After the initial shock wore off, I reminded myself that Microsoft is just another company, and the opportunity, while lucrative, is just another opportunity.  I need to evaluate the company and the opportunity objectively and with an open mind.  I have to approach their interviews with the same confidence that I would any other position.

The big difference in approaching this interview process as opposed to most interviews that I do is that the interviewers will be as or more technically sound than I am with the SharePoint line of products.  Most interviews I do, the interviewer has a basic understanding of the product and thinks they know what it can do for them.  I come in and, during the interview, kind of give them a rundown of what SharePoint can actually do, and let them know if the product will work for them.
In this case, the people I will interview with will not only know the product, they will know where the company wants to take it, and will have had a hand in bringing the product to where it is today.

At any rate, I am simply in the phone screen stages of the interview process.  I have not been asked to Redmond, I have not talked with anyone other than the recruiter.  I will be speaking with the hiring manager soon, and from there, who knows?  It is an interesting opportunity, and I look forward to the process, IF the hiring manager likes me well enough to bring me in.  I have heard many horror stories about Microsoft interviews, so I hope that they will be gentle with me.  I will post my experiences...  that is if I get an interview...

Adding the SharePoint Snap In to PowerShell

What's the first step? If you are using the ISE, and you want to have the scripts excited from any PowerShell environment:
Add-PSSnapin Microsoft.SharePoint.PowerShell

This line adds the SharePoint snapin and makes any PowerShell console the same as the SharePoint Management Console.

If you want to get cute you can add the following code to see if the SharePoint snapin is available, and throw a friendly error message if it is not:

$snapin = Get-PSSnapin | Where-Object{$_.Name -eq 'Microsoft.SharePoint.PowerShell'}
if($snapin -eq $null)
{
Write-Output('SharePoint snapin does not exist on this server')
}else{
Add-PSSnapin Microsoft.SharePoint.PowerShell
}


As long as you know the namespace of the PowerShell snap in that you want to add, the above code will work to add it simply replace the SharePoint snapin namespace (Microsoft.SharePoint.PowerShell) with the namespace of your snap in.

Windows Server 2008 R2 High DNS Memory Usage

I was messing around with the Desktop Experience on my Windows Server 2008 R2 server and decided that I wanted to use one of the gadgets that I found useful with Windows 7. The gadget shows all of the core processor usage and the memory usage on the server.
This was a domain controller in my R&D farm, it is also my Hyper-V host so I kind of use it as a workstation as well. Don't judge me...

I installed it and found that my memory usage was abnormally high. Investigating, I opened Task Manager and found that DNS was eating up about 35MB per core. What the??? DNS should be nearly non existent. So I started to do some checking.

I hooked up my handy dandy Colasoft trace tool and found that I got a ton of errors when trying to connect to my ISP's DNS. Something about wrong format, more specifically "Additional Record: of type OPT on class Unknown DNSClass". Failure after failure after failure. It was literally like my DNS service was caught in some sort of infinite loop. What is this noise?

I found out that Microsoft was trying to be proactive with Server 2008 R2, and, by default, they enabled a new DNS format that hasn't quite yet been adopted by everybody yet. Like IP V6, this format, EDNS, is not yet everywhere. Bad news for me, because it was causing me pain.

I really don't have a whole lot of experience with DNS, other than adding records and whatnot, so how to disable this EDNS?
I found on TechNet there is a little tool, dnscmd, that will do the trick. So, all I did to disable it was to run dnscmd /config /EnableEDnsProbes 0

Immediately, my memory dropped from 35MB per core to about 2MB total. ColaSoft reported no new format errors. For now, it seems as if EDNS is not yet ready for the real world.

Lots of VMs To Create In Hyper V? Sysprep!

Unless you are only doing one or two virtual machines with Windows Server 2008 R2 in Hyper V, or any virtualization software or Windows OS for that mater, you will want to save yourself some configuration time by creating an image of the OS you will be using. Windows gives us a very easy utility to do this called sysprep. It is found in the Windows directory on your system drive under System32\sysprep.

Since I do a lot of work with SharePoint and all of its related systems, I create several images so that I can quickly create a server based on what I need at the time. First I create a "base" server image. This is the generic install of Windows, no Roles installed. This gives me an image that I can use for any custom purpose. One thing that you MUST do first is to run Windows Update. You do not want to waste time on each VM getting it up to date. Do this first, then use sysprep to create your image.

After making an image of this disk, I will use the image to create a new server and then add the specific roles that will make it in to a generic image of the server and role that I want to have on hand. I always create an image of a server with IIS, and the latest versions of the .NET framework.

For technical reference, Microsoft has a page for the command line options. You could use the UI, but... I don't trust it. The line that I use is the following:
sysprep /oobe /generalize /quiet /shutdown
This removes all of the security information that is created when the server is first installed, and will put the server state in to the welcome mode to prompt you for information when you start up the server the first time.

From here copy the .vhd file somewhere safe. This is the generic disk master that you will create all of your new VM with. Be sure to name the file well. I like to name it with the OS, the role, then the last time I ran Windows Update. When updating gets to be too much of a pain after the creation of a VM, I will create a new image.
When you need a VM you will make a copy of this file, move the new file in to your .vhd directory and attach your VM to this new .vhd file.

Of course, there are more and more things you can do with automated deployment. Microsoft even has a very interesting program and tool kit that you can download and use to create all sorts of answer files and what not for large unattended image installs.

Reset Domain Admin Password

I had to change my admin password on my development domain recently... Then promptly forgot it. How do you over come this? I thought that I would have to reinstall Windows on my DC, and redo all of my domain work. Turns out there is a hack.

First to make this solution work you need to have a local account that has admin rights to the OS. This can be the built in Administrator user that is created on install, but if you are like most people that account has been disabled. If you have no local accounts active with admin access, you are screwed. Go find some third party hackerware that will hack your domain and leave your vulnerable to any attack the hackerware wants to put on.

Anyway I always create an account that is a local admin on my servers for various reasons. This account is not named administrator and is set up according to best practices. So what do?

First you need to get out your Windows Server 2008 R2 DVD. You boot to this disk and select the repair option.
You are then given several options. You want to click on the Command Prompt option.

Things get slightly tricky here, because it is ambiguous what your drives are called. On a physical server, the C drive is most likely C, but if you are dealing with a HyperV or a VMWare server, C just might not be C. You need to find your OS dive and navigate to your Windows\System32 directory.

Here you will need to do some trickery. You need to be able to get to the command prompt from the log in screen when you get back to normal mode. So we have to do some fun stuff in order to get this to happen. So... When you look at your log on screen what do you have to work with? You have the two text boxes, the switch user button, the submit button, and (ta da!!) the Ease of Access button. We need to rename the command prompt application to the Ease of Access application, so that when we press the Ease of Access button on the log in screen the command prompt opens.

So from your command prompt in the recovery console rename the utilman.exe to utilman.exe.bak and then rename cmd.exe to utilman.exe. Reboot

When you get to the log on screen, simply click on the Ease of Access button. That will launch the command prompt. From the prompt type "user administrator NewPass123" That's it! Log in now with your new password.

Pivot Tables

For the last few weeks I have been building a web site that would display parent companies with the services they offered. The kicker is that I needed to differentiate if that service was provided by the parent company or some sub contracting company.

So I needed a variety of tables in the database to keep track of the data. First I needed a table to keep all of the various companies data in. This table also contained a flag to differentiate what was a parent company.
Next I needed a table to keep the services in. Then I needed a couple of look up tables that matched services to companies, and sub contracting companies with the parent companies. Lots of tables.

My requirement was to display the data in a grid that had the services run across the top, the parent companies run along the left, and a check box in cells where the company could provide the services.
At any time the services could be added to or removed, so I was left with the prospect of building the table from a set of four lists.
I first did this in C#, with several ForEach loops. Easy enough, and was fine for small amounts of data. Then word came down that another team saw the web site and wanted to use it for their parent company/sub contractors. This team had been in existence for nearly 10 years, so they had hundreds of services and thousands of companies. My C# ForEach loop solution would not scale well for this much data. The performance would be unacceptably slow. What to do? Use the power of SQL server and feed the roll up data to the data service. How?

My first problem was, how to take a table and use its rows as columns. There is no foreach loop in TSQL, so how to solve? With the release of SQL 2005 Microsoft gave us a neat little function for just such a problem. It is the PIVOT function:
SELECT <non-pivoted column>,
 [first pivoted column] AS <column name>,
 [second pivoted column] AS <column name>,
 ...
 [last pivoted column] AS <column name>
FROM
 (<SELECT query that produces the data>)
AS <alias for the source query>
PIVOT
(
 <aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column], [second pivoted column],
 ... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;

Looks worse than it is. So we start with my services table:

ServiceID	ServiceName
1	Engine
2	Fuel
3	Wheels
4	Wings

Company Table:

CompanyID	CompanyName	HoldingCompany
1	Bob's Holding Company	true
2	Jim's SubContractor	false
3	Matt's SubContractor	false
4	Bruce's Holding Company	true

Service Match Table:

ServiceMatchID	CompanyID	ServiceID
1	1	1
2	4	1
3	2	2
4	3	3

Company Match Table

CompanyMatchID	ParentCompanyID	ContractorCompanyID
1	1	2
2	4	3

Fun right? Anyway what we first need to do is craft the data table that the PIVOT function will use. What we want is to match up our Companies with our Services, while joining on the other tables to make the return human readable.

SELECT CompanyName, Company.CompanyID, ServiceName, Services.ServiceID, HoldingCompany, HoldingCompanySubContractMatch.SubContractID,
CellType =
CASE
WHEN HoldingCompany = true THEN 'M'
WHEN HoldingCompany = false THEN 'S'
END
FROM ServiceCompanyMatch
JOIN Services ON ServiceCompanyMatch.QualID = Service.ServiceID
JOIN Company ON ServiceCompanyMatch.CompanyID = Company.CompanyID
LEFT OUTER JOIN HoldingSubContractMatch ON company.CompanyID = HoldingSubContractMatch.MOAHolderID

This gives us:

CompanyName	CompanyID	ServiceName	ServiceID	HoldingCompany	SubContractID	CellType
Bob's Holding Company	1	Engine	1	true	2	M
Bruce's Holding Company	4	Engine	1	true	NULL	M
Jim's SubContractor	2	Fuel	2	false	NULL	S
Matt's SubContractor	3	Wheels	3	false	NULL	S

Now we have our data table. Now we have to make some decisions. First how do we want our pivot table to look? We want the columns to be the Services, and the rows to be the companies. So we want to pivot on the the ServiceID column, but display the CellType information.
We also want to be very careful with the Services table. These rows can change at any point, so we have to dynamically load these in to the PIVOT statement.
There is no ForEach loop in TSQL so we have to do something else to load all of the columns. To do that we need to use a little trick, called dynamic SQL. We load the function as a varchar in to an EXEC statement. Inside this varchar we load a flattened list of the rows. We use the COALESCE statement to do this. So, in pieces, here is our statement:

DECLARE @ColumnHeader varchar(MAX)
SELECT @ColumnHeader=
COALESCE(
@ColumnHeader + ',[' +Services.ServiceName+']','['+Services.ServiceName+']'
)
FROM Services

First, we declare a varchar to contain the list of the would be columns. Then we use COALESCE to put the rows in to a comma delimited string.

DECLARE @Pivot varchar(MAX)
SET @Pivot = N'
SELECT
*
FROM
(
SELECT CompanyName, Company.CompanyID, QualName, Quals.QualID, MOAHolder, MOAHolderSubContractMatch.SubContractID,
CellType = CASE
WHEN MOAHolder = 1 then ''M''
WHEN MOAHolder = 0 then ''S''
END
FROM QualCompanyMatch
JOIN Quals on QualCompanyMatch.QualID = quals.QualID
JOIN Company on QualCompanyMatch.CompanyID = Company.CompanyID
LEFT OUTER JOIN MOAHolderSubContractMatch on company.CompanyID = MOAHolderSubContractMatch.MOAHolderID
) DataTable

I then declare the varchar that will be executed in the EXEC statement, and set it to the string that follows, thus the opening tick mark after the N.
Then we grab the entire table from the SELECT statement that we figured out above. We call that result set DataTable.

PIVOT
(
MAX(CellType) FOR ServiceName
IN
(
'+@ColumnHeader+'
)
)as PivotTable'
exec (@Pivot)

Here we pivot the columns and load the column names from the varchar variable that we loaded earlier.
In the syntax of PIVOT we need to have an aggregation function over the column that contains the data we want to display in the rows. In my example, the aggregated column is a varchar, so we use the MAX aggregation function. This does nothing to a varchar, so the data remains untouched.
Next we have the FOR statement determining the column that we wish to pivot on. SQL takes the data in the rows of this column, and sets them as the columns in the new result set we are creating.
The IN statement then names the columns, we load in the @ColumnHeader vachar that we created in the first part of the statement.
Finally we name the new result set "PivotTable", and close the tick. We then run the dynamically crated command in the EXEC function.
The result is

CompanyName	CompanyID	ServiceID	HoldingCompany	SubContractID	Engine	Fuel	Wheels	Wings
Bob's Holding Company	1	1	true	2	M	NULL	NULL	NULL
Bruce's Holding Company	4	1	true	3	M	NULL	NULL	NULL
Jim's SubContractor	2	2	false	NULL	NULL	S	NULL	NULL
Matt's SubContractor	3	3	false	NULL	NULL	NULL	S	NULL

So we now have a data source that now can be called as a stored procedure or a view. It contains everything we need to display the information that we want it to.
I am still working on getting the result set to display with the data rolled up in to the parent companies. If I figure it out I will post!

Create a List Dynamically With Reflection

I wanted to do more with the Dynamic List that I put up a little while ago. I wanted a method that I could pass any type, and get a List of that type out of it. After a bit of playing around, I was able to get what I wanted using System.Reflection. Here is the code:

1:  private object GetList(object[] sourceArray, Type arrayType) {
2:        Type listType = typeof(List<>).MakeGenericType(new System.Type[] { arrayType });
3:        var list = Activator.CreateInstance(listType);
4:        MethodInfo mi = listType.GetMethod("Add");
5:        foreach (object item in sourceArray) {
6:          mi.Invoke(list, new object[] { item });
7:        }
8:        return list;
9:      }  

What I did was to first create an instance of the List. Then set the nested type to be the type of the calling type. This is all done using the Type class' MakeGenericType Method. All this does is create a List were T is the type that is passed as the parameter. I still need to instantiate this monstrosity. I do that in the next line of code, using the System.Activator class' CreateInstance method. After that I have an actual list. Now I need to populate it.
Because we don't know what types and classes we are dealing with at Design Time, we can't just call Class.Method() to make things work. I first need to find the specific method I need. To do that I call System.Reflection.Type's GetMethod method. This comes out as a System.Reflection.MethodInfo type, thus the creation of the MethodInfo variable.
From here I simply iterate through the objects in the array passed in to the method, and Add them to the List object.

There is one small issue... The return is not exactly what you would expect. It is not a List of the type you pass it, rather it is a single object, of type object. The good news is that this object can be casted as the proper List when you get back to your calling method.

For instance, here is my calling method:

1:  protected void butPeople_Click(object sender, EventArgs e) {
2:        Type peopleType = typeof(People);
3:        List<People> people = new List<People>();
4:        people = GetList(GetPeople(), peopleType) as List<People>;
5:        GridView1.DataSource = people;
6:        GridView1.DataBind();
7:        GridView1.Visible = true;
8:      }  

You can see in line 4 where I call the GetList method. I send it a custom type of People, along with an array of People. As I mentioned above, the return type of the GetList method is object, but this can be casted in to the type that we wanted it to be, as seen with the "as List" on line 4.