Entity Framework 6: Taming the Open Connection Conundrum

Here’s an Entity Framework scenario for you:

You’re using Entity Framework 6.

Check.

You’re using a custom database initializer to init your DEV and TEST databases. Specifically the DropCreateDatabaseAlways initializer.

Yup, that's me.

Your project contains 1 or more Unit Test projects that utilize the above.

Do you have a unit test project? Say yes.

You have a connection open to the database that the initializer is going to drop.

Say, via SSMS.

You run your unit tests. And it runs, and runs, and runs … and then : 51485cb2b3ab8.preview-620

System.Data.SqlClient.SqlException: Cannot drop database "Foo" because it is currently in use.. Aborting test execution.
Result StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.b__0(DbCommand t, DbCommandInterceptionContext`1 c)
at

blahdy blah blah blah ...

Ok, so what’s happening? The initializer cannot drop the database because there is currently an open connection. The most simple way of getting rid of open connections is either to delete the DB in your SSMS (with the close all connections checkbox checked), or run some SQL in a query window that accomplishes the same task.

How annoyed are you after the 10th time of doing this? The correct answer is: very.

Give Those Connections a Dirt Nap

There is a fairly simple way to combat this problem by embedding the code necessary to kill open connections directly into the database initializer. Here’s what you do:

In your custom initializer that inherits from DropCreateDatabaseAlways, add the following method (you can rename it to something a little less Class A felony if you wish):

private void MurderAllConnections(HumanResourceContext context)
{

try
{

// FIRST: Build a connection using the DB Context's current connection.
SqlConnectionStringBuilder sqlConnBuilder = new SqlConnectionStringBuilder(context.Database.Connection.ConnectionString);

// Set the catalog to master so that the DB can be dropped
sqlConnBuilder.InitialCatalog = "master";
using (SqlConnection sqlConnection = new SqlConnection(sqlConnBuilder.ConnectionString))
{

   sqlConnection.Open();
   string dbName = context.Database.Connection.Database;

   // Build up the SQL string necessary for dropping database connections. This statement is doing a couple of things:
   // 1) Tests to see if the DB exists in the first place.
   // 2) If it does, sets single user mode, which kills all connections.
   string sql = @"IF EXISTS(SELECT NULL FROM sys.databases WHERE name = '" + dbName + "') BEGIN ALTER DATABASE [" + dbName + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE END";

   using (SqlCommand sqlCmd = new SqlCommand(sql, sqlConnection))
   {
       // Run and done.
       sqlCmd.CommandType = System.Data.CommandType.Text;
       sqlCmd.ExecuteNonQuery();
   }
}
}
catch
{
   // Something bad happened.
   throw new Exception("Hey, boss, the UnitTestInitializer failed. You want I should fix it?");
}

}

Now with the above method in place, the add it to the InitializeDatabase() method in your custom initializer:

public override void InitializeDatabase(FooContext context)
{
   this.MurderAllConnections(context);
   base.InitializeDatabase(context);
}

Now all those connections are sleepin’ with the fishes.

Some (hopefully obvious) Caveats

  • Make sure the MurderAllConnections() call runs before the base.
  • Do not name your method MurderAllConnections(). That’s just bad form.
  • Your connection will need to be in windows auth mode.
  • Highly recommend NOT using this in a PROD or STAGING environment. Just sayin’.

Happy Entity Frameworking …

Cheers,
Jim

Advertisements
Tagged , ,

Deployment Error: Failed to extract the cab file in the solution.

Deployment woes? Par for the course.

I ran into an odd issue today while deploying a SharePoint solution via Visual Studio 2013 to a test SharePoint instance today:

Error occurred in deployment step 'Add Solution': Failed to extract the cab file in the solution.

The only change made prior to deployment was the addition of a new image file to the Style Library folder. Seems like a fairly simple, non-deployment-killing change, right?

It’s never that simple.

After some digging, I realized the image file name had an ‘@’ symbol in it:

ProjectDashboard_-_Microsoft_Visual_Studio__Administrator_

Removing the symbol fixed the problem. Incidentally, Visual Studio will warn you when your files have invalid characters, but not when pasting a file into the folder. Only when creating a new file with the ‘@’ in the name will it warn you that it’s not allowed.

In doing some research, I also found parenthesis are not allowed. See this blog post.

Are there any other file name characters not allowed within the CAB file? Comment up!

Cheers,
Jim

Tagged ,

Sharepoint NCSS: Replace your content to save your sanity.

imagesThat’s right, SharePoint and The Shining. This is a combo that just works.

The insanity starts here …

NCSS solutions (No Code Sandbox Solutions, for the uninitiated) are extremely useful for laying in custom SharePoint groundwork. Via these solutions you can easily deploy custom Content Types, Site Columns, List Definitions, even Master Pages, Page Layouts, etc. I highly recommend using them. There’s no better way that I know of to deploy this stuff into SharePoint (and SharePoint Online, incidentally). I fully intend to blog more about these solutions in the future … including the good, the bad, and the unbearably ugly.

That being said, before I myself forget, allow me to impart upon you, the unsuspecting NCSS newbies, this one key piece of advice:

When developing your master pages, page layouts and auto-deployed pages in your modules, be sure to include the following element (in RED below): 

<File Url=mypage.aspx Path=default.aspx Type=GhostableInLibrary ReplaceContent=TRUE >

All failure and no success can make [your name here] a dull [boy|girl].

If you enjoy your sanity (or whatever semblance of sanity you claim to possess), you’ll add that key. Without it, changes you make in your NCSS  content will not be deployed, even if you deactivate your solution, upload a replacement, and re-activate the solution and the feature. The retraction of the feature/solution from SharePoint DOES NOT RETRACT THE CONTENT.

I personally was fighting deployment issues for well over a day trying to get one auto-deployed page to display properly. Incessant “file not found” errors were plaguing me. Turns out I had some invalid file references in the page layout, which as all of us light to moderately seasoned SharePoint devs know, means something doesn’t exist within SharePoint at the referenced location. (Dear Bill and team: If you could give us some indication as to what file is not found ON the page, that would be useful.)

Let this be a lesson to you. My hope is that you do not make the same mistake as I made.

Here’s to white puffy clouds, 70 degree afternoons and boat drinks in your future. Cheers.
Jim

SharePoint 2013: Navigation Without all the Audience Trimmings

Most SharePoint devs out there know about trimming navigation items based on audience trimming settings. This has been around since I’ve been using SharePoint, back in the dark-dark times of SharePoint 2007.

Site_Navigation_SettingsIt usually just works. Add a link or folder to the site’s navigation, set which audience should see the link, and done. People in the “Analytics” group see the link or folder, lesser privileged individuals do not. It’s like magic, without all the magical mumbo-jumbo.

In fact, it’s so dead simple, it’s the last thing I expected to have a problem. So … what’s this crazy exception message I’m seeing when setting audience trimming??

This entry cannot be validated. Exception “Object reference not set to an instance of an object.” occurred.

Cannot be validated object reference what-what?

First of all, thank you for the amazingly useless exception message. It’s nothing new to developers in most any stack, but I thank you anyway.

So why is it not working? Who’s fault is it? Is SharePoint bugged? Is Mars in retrograde? Did I configure something wrong? If I did, can I blame my parents because they never gave me a RC car for Christmas? (Turns out I can, and have. Next stop: therapy.)

As it turns out, it was mostly my fault. In order for audience trimming to work the User Profile Service has to be activated and fully configured.

How do I activate and configure the User Profile Service?

This is the easy-peasy part. First of all:

  1. Go to Central Administration
  2. Go to System Settings
  3. Go to Services on Server
  4. Look for the following:BSD__TS13-APP1
  5. Start that turkey

Now that the service is started on the farm, provision the User Profile Service (seems backwards, but whatever). Rather than re-typing this next part all out, I direct you to this stellar post on configuring the service: http://jaxsharepoint.blogspot.com/2013/08/setup-and-start-sharepoint-2013-user.html

Unless you need it for other reasons, stop before provisioning the User Profile Synchronization Service. This is not needed to get audience trimming running.

Conclusion?

If I could draw one conclusion for you, it would look like this:

 

 

 

 

 

 

Keep going …

 

 

 

 

Untitled drawing (1)

Happy SharePointing,
Jim

 

 

SharePoint 2013: Configuration Wizard Issues When Installing Local Development Instance

search

Issue #1:

Ever try to install a local development instance of SharePoint 2013, only to have the configuration wizard conk out around step #4 indicating a timeout error when starting services?

Me, too. I cried like an infant with a wet diaper in a childish attempt to project utter discomfort and anguish to anyone or anything within a 3 mile radius. I’m pretty sure not a single bit was shed by the VM. My co-workers, however, have scheduled an intervention.

You, being of sound mentality and professionalism, checked the installation log file for more information, and found something similar to the following:

06/03/2014 14:56:00 6 ERR Failed to register SharePoint services.
An exception of type System.ServiceProcess.TimeoutException was thrown. Additional exception information: Time out has expired and the operation has not been completed.
System.ServiceProcess.TimeoutException: Time out has expired and the operation has not been completed.
 at System.ServiceProcess.ServiceController.WaitForStatus(ServiceControllerStatus desiredStatus, TimeSpan timeout)
 at Microsoft.SharePoint.Win32.SPAdvApi32.StartService(String strServiceName)
 at Microsoft.SharePoint.Administration.SPWindowsServiceInstance.Start()
 at Microsoft.SharePoint.Administration.SPWindowsServiceInstance.Provision(Boolean start)
 at Microsoft.Office.Server.Conversions.LauncherServiceInstance.Provision()
 at Microsoft.SharePoint.PostSetupConfiguration.ServicesTask.InstallServiceInstanceInConfigDB(Boolean provisionTheServiceInstanceToo, String serviceInstanceRegistryKeyName, Object sharepointServiceObject)
 at Microsoft.SharePoint.PostSetupConfiguration.ServicesTask.InstallServiceInstances(Boolean provisionTheServiceInstancesToo, String serviceRegistryKeyName, Object sharepointServiceObject)
 at Microsoft.SharePoint.PostSetupConfiguration.ServicesTask.InstallServices(Boolean provisionTheServicesToo)
 at Microsoft.SharePoint.PostSetupConfiguration.ServicesTask.Run()
 at Microsoft.SharePoint.PostSetupConfiguration.TaskThread.ExecuteTask()

The Fix:

To the registry! Follow this post, add the key and DWORDs as instructed, and re-run the SharePoint configuration wizard.

 

bit_noIssue #2. No No No No … !

Yes, BIT. Another issue has been detected.

We made it to step 8 in the SharePoint Configuration Wizard, at least! But it failed all over the place again. Go to your log file and you’ll likely find this:

06/03/2014 15:05:19 16 ERR An exception of type System.ArgumentException was thrown. Additional exception information: The SDDL string contains an invalid sid or a sid that cannot be translated.
Parameter name: sddlForm
System.ArgumentException: The SDDL string contains an invalid sid or a sid that cannot be translated.
Parameter name: sddlForm
 at System.Security.AccessControl.RawSecurityDescriptor.BinaryFormFromSddlForm(String sddlForm)
 at System.Security.AccessControl.RawSecurityDescriptor..ctor(String sddlForm)
 at Microsoft.SharePoint.Win32.SPNetApi32.CreateShareSecurityDescriptor(String[] readNames, String[] changeNames, String[] fullControlNames, String& sddl)
 at Microsoft.SharePoint.Win32.SPNetApi32.CreateFileShare(String name, String description, String path)
 at Microsoft.SharePoint.Administration.SPServer.CreateFileShare(String name, String description, String path)
 at Microsoft.Office.Server.Search.Administration.AnalyticsAdministration.CreateAnalyticsUNCShare(String dirParentLocation, String shareName)
 at Microsoft.Office.Server.Search.Administration.AnalyticsAdministration.ProvisionAnalyticsShare(SearchServiceApplication serviceApplication)
 at Microsoft.Office.Server.Search.Administration.AnalyticsAdministration.CreateDefaultStoreLocation(SearchServiceApplication serviceApplication)
 at Microsoft.Office.Server.Search.Administration.AnalyticsAdministration.ProvisionRawEventStore(SearchServiceApplication serviceApplication)
 at Microsoft.Office.Server.Search.Administration.AnalyticsServiceAdministration.Provision()
 at Microsoft.Office.Server.Search.Administration.SearchServiceApplication.Provision()
 at Microsoft.SharePoint.PostSetupConfiguration.EvalModeProvisionTask.ProvisionServiceApplicationsAndProxies()
 at Microsoft.SharePoint.PostSetupConfiguration.EvalModeProvisionTask.Run()
 at Microsoft.SharePoint.PostSetupConfiguration.TaskThread.ExecuteTask()

Isn’t this magical?

And the fix is … ?

Share your Analytics, my friend. Your Analytics_{GUID} folder, that is.

  1. Open explorer and go to: C:\Program Files\Microsoft Office Servers\15.0\Data\Office Server.
  2. Right click the Analytics_{GUID} folder -> Sharing -> Advanced Sharing.
  3. Click “Share This Folder”, leave the Share name defaulted, then click Permissions.
  4. Because this is a dev instance, just give Everyone Full Control access (and feel OK about it).
  5. Re-run the wizard.

NOTE: For full disclosure, I found this here. I did not need to check for DB security permissions, nor did I have to add the WSS_ADMIN_WPG account specifically.

Is that the end?

Yes. Enjoy your development instance of SharePoint 2013. Preferably with a decent pint of ale.

Cheers,
Jim

Tagged

SharePoint 2013: Sorry, this site hasn’t been shared with you.

HAL, I think you’re malfunctioning again … 

So here’s the setup: after a complete, stand-alone installation of SharePoint 2013 on a development VM and creation of a new site (let’s call it http://foocompellsyou:8080) via central admin, browsing the configured site returns this lovely message:

"Sorry [Dave], this site hasn't been shared with you."

SharePoint 2013 was installed and setup using the setup and configuration wizard. This had it’s own set of challenges, to be blogged about later.

I went through all of the normal checks post-creation of the new site:

  • Does the account I am logged in as have site administration access? Yes.
  • Are the app pools functioning? If not it would be a different message, but checking it can’t hurt.
  • Are the user policies configured correctly? Yes. In fact, the account I am logging in as has full control.
  • Can I get to http://foocompellsyou:8080/_layouts/settings.aspx? No. Same message.
  • Check the space weather index — anything out of the ordinary? No, it seems normal. For space.
  • What about the default authentication provider?

Ah … here’s something. A default install and creation of a new site will get you a claims-based application, but the default auth provider is set to Windows Auth/NTLM.

Changing this to Kerberos fixed the problem:Image

Hopefully this helps someone!

Cheers,
Jim

Tagged

SharePoint 2013: Search Host Controller Service stuck in ‘starting’ state.

An odd issue came up recently where the Search Host Controller Service on a single web front end was stuck in the ‘starting’ state (as shown in the CA -> System Settings -> Services on Server). Couldn’t locate anything in the event log that showed why it didn’t start. And the final oddity: the service in Windows Services was started and running normally on all machines in the farm.

There are a bunch of posts out there (this one and others) that suggest un-provisioning and modifying registry settings and re-provisioning … oh myyyyy!

Before going down that path:

Start simple

When stuck in the starting state, there’s no ability via Central Admin to stop, start or restart. You might think rebooting your WFE might do the trick … no, think again. So the first step: enumerate the host services on your farm in powershell (note you will need to run as admin):

Get-SPServiceInstance | ? {$_.TypeName -eq "Search Host Controller Service"}

This will produce something similar to the following, though you will see differences based on the number of servers in your farm, and hopefully different GUIDs. If our GUIDs match … run for cover because the world is ending.

Server : SPServer Name=WFE1
HostControllerURL : net.tcp://WFE1/ceres/hostcontroller/nettcp
RepositoryVersion : 0
PrimaryHostController : True
SearchServiceInstanceId : 43d4837c-b0c6-42e1-93ff-c8b872854be7
Server : SPServer Name=WFE2
HostControllerURL : net.tcp://WFE2/ceres/hostcontroller/nettcp
RepositoryVersion : 0
PrimaryHostController : False
SearchServiceInstanceId : 6d02e4ab-bfb9-442f-8a29-705274ec8262
Server : SPServer Name=WFE3
HostControllerURL : net.tcp://WFE3/ceres/hostcontroller/nettcp
RepositoryVersion : 0
PrimaryHostController : False
SearchServiceInstanceId : 98d9cc05-92d6-4e2a-b872-3bd7d22f33a4

Super. I see services and one of them is dead. What’s next?

In this instance, it happens to be an issue on the 3rd server. I stopped and started it by running these two commands:

Stop-SPServiceInstance -Identity 98d9cc05-92d6-4e2a-b872-3bd7d22f33a4

And finally:

Start-SPServiceInstance -Identity 98d9cc05-92d6-4e2a-b872-3bd7d22f33a4

After refreshing CA -> Services on Server, the service was listed in the ‘started’ state.

 

 

Why did this happen?!?

No. Idea. If I had to make an semi-educated guess, it wasn’t really an issue other than it was displaying as “starting” in CA. The service was clearly running in windows services. Perhaps during startup the messaging got stuck. That being said, it looked very angry, and might have lead to false-positives when chasing down other issues on the farm.

 

Happy SharePointy-ing.
Jim

.NET 4: GAC’ing Assemblies Made Simple

Installing assemblies into the GAC is no longer straight forward. In the good ol’ days it used to be as simple as dragging a file into the Assembly folder. Or using the gacutil.exe. Neither is available any longer.

According to various posts out in the wild wild web, the updated .NET 4 version of gacutil.exe is for dev purposes only and should not be used in a production environment. It says so here, and I believe everything posted on the internet. The recommended approach of using a Setup and Deployment InstallSheild (Limited Edition) installation project also has it’s issues; the out of the box version installed with VS 2012 is not usable without downloading a new version, downloading a new version requires you to register, registration is a hassle, and the dude doesn’t abide hassles.

So … is there another option?

You betcha.

Are you going to tell me what it is?

If you ask nicely.

Okay, what’s the other option?

Check out PowerShellGac on CodePlex. It contains several useful ps scripts for getting assemblies into and out of the GAC. This was the best option I found.

The documentation on CodePlex is good enough to get you going, however when running the Install-Module command, I had to do: Install-Module .\Gac instead of Install-Module Gac. No big whoop.

Is there a catch?

Not really. If I had one wish for Christmas this year, just one wish, this set of scripts would allow for deployments of assemblies to various staging and production servers remotely. In order to use these powershells they need to be deployed to each server and run individually.

Are there other options?

Great question. Do you know of a better method or better scripts? Is there an easier way to install remotely? Comment up, people.

*EDIT*: I’ve finally had a moment to check into powershell remoting. It’s fairly straight forward. The one catch is remoting must be enabled on your various machines. Take a look at this TechNet post for more informaiton.

Cheers,
Jim

MSTR 9 + Win Svr 2012: A match made for hotfixin’

I recently spun up a MicroStrategy VM to be used as a demo/training box. In doing so it seemed natural to throw it on a Windows Server 2012. MicroStrategy 9 is shiny and new, Windows Server 2012 is shiny and new, what could possibly go wrong?

Well, I’m posting about it. There’s the first clue.

1st Issue: Configure IIS Properly

The first issue was that IIS was not fully configured for MSTR to run properly. The installation was complaining about missing IIS features. To head this off, configure 2012’s IIS as such:

1st page iis config

2nd page iis config

First time configuring the server I missed the following IIS components: “ASP” and “IIS Management Scripts and Tools”.

2nd Issue: She’s installed, captain, but she won’t start.

Yes, Scotty, we need more power. I get it.

After MSTR installation, several MSTR services will not start. You’ll see something like this in the event viewer:

The MicroStrategy Intelligence Server service depends on the following service: 
ProtectedStorage. This service might not be installed.

This service is no longer available Windows Server 2012 (or Windows 8).  See this post for some additional information. I guess Protected Storage is anything but.

Okay, that’s great. Now what?

The Fix:

There are a lot of posts on the inter-tubez describing several unsavory solutions, including running MSTR as an application, or hacking the registry, etc etc. You can skip all of that. MSTR HotFix #3 will take care of all of this for you. Simply install it, and you’re good to go.

One thing to note is that the hotfix #3 IS commutative. You can skip installation of hotfixes #1 and #2. Don’t believe me? Read the readme.

Now I’m  rockin’ a Windows Server 2012 VM with SQL 2012 and MSTR 9.3. Do you smell that new software smell? I do.

Cheers!
Jim

Windows Explorer and CMD

Here’s the setup: you’re browsing around in Windows Explorer, finding that command line tool or whatever. You find the correct location. Now you need to open up a command line. So you start/run/cmd, change drives if needed, and either copy/paste the folder path from Windows Explorer into CMD or manually navigate using a series of CDs and/or tabs.

Annoying, right?

A Trick

Here’s a trick I just learned today. I’m SURE this has been around forever, and is about as new as sliced bread. That being said, it’s a wonderful little shortcut and it deserves to be shared with everyone. It may only save a few seconds of real-time, but it saves a great many units of frustration-time. Here it is:

1) Find your location in Windows Explorer. In this case I’m browsing to the location of a NANT build script:

2013-03-22_1123

2) Put your cursor in the location bar, like so:
2013-03-22_1128

3) Type “cmd” and hit enter:

2013-03-22_1132

Boom. Nice, huh? There are a million different ways to skin this same cat, including installing power toys, scripts, shortcuts, etc. It’s nice, though, to utilize built-in shortcuts because they can be counted on across various Windows installations, specifically on production servers where you don’t necessarily want to install 3rd party tools or scripts.

Incidentally, this also works for “powershell”. And in both PS and CMD, there’s a way to reverse it. Type “explorer .” and Windows Explorer opens to your current PS/CMD location. My frustration level literally just went down a notch.

Earth shattering? Nope. Helpful? Duh.

Rejoice!
Jim