Also available at

Also available at my website http://tosh.me/ and on Twitter @toshafanasiev

Tuesday, 10 June 2014

Loading "Vanilla" JavaScript files in Node

I'm writing more and more JavaScript these days and it's nice to have some sort of test infrastructure in place for all but the most trivial codebase.

If you're using a framework like AngularJS where modularity and dependency injection are first-class citizens then you're all set up. If, for whatever reason, you can't package things up in nice modules then testing can be tricky, particularly when there are dependencies involved.

I came up against this recently and my solution was to use Node.js and the excellent jasmine-node package
installing jasmine-node




I wrote a very simple Node module called include


Which lets me test simple JS files with no dependencies ...

maths.js
spec/maths.spec.js
... and more interestingly, those that rely on global objects such as window and document

ui.js
spec/ui.spec.js
So with a directory structure like this

directory structure

Tests can go from this ...
failing tests
... to this

Most of the code I write is more involved than this though :)

Tuesday, 4 February 2014

Managing CLR Assemblies in SQL Server

I've just employed a SQL Server CLR stored procedure in a project I'm working on (I was initially worried about performance, but having looked into it I've found that it can be quite good, provided you're mindful of marshalling boundaries etc.).

Having added a dedicated DLL project for SQL Server CLR artefacts (using .NET 2.0 as we target SQL Server 2005 in some instances) I extended our upgrade tool to generate and issue a CREATE/ALTER ASSEMBLY statement using a binary literal of this DLL. It was here I ran into one of Microsoft's weirder design decisions.

Running the following statement

ALTER ASSEMBLY SearchTools FROM 0xDEADBEEF...C001EE15

yields the following error if the new version of the assembly provided is identical to the existing one.

ALTER ASSEMBLY failed because the source assembly is, according to MVID, identical to an assembly that is already registered under the name "SearchTools".

This is a bit of a pain as an update that worked once will fail on all subsequent runs. Why would they choose to do this? You wouldn't expect a standard DML UPDATE statement to fail if the new row data matched the existing ones, would you?

Anyway, I considered dropping the assembly if it exists and then issuing a CREATE ASSEMBLY statement each time but then there's the problem of the UDFs and Stored Procedures that depend on the installed assembly - forcing the eventual owners of this project to manually manage all these CREATE/DROPs wouldn't be very friendly so I had to find another way.

In the end I found the built in ASSEMBLYPROPERTY(N'asm-name', 'property-name') function that returns metadata from an installed assembly. The 'MvID' property can be used to extract the stored module version identifier for comparison with the one provided by the candidate version of the assembly:


// using a previously created SqlCommand
cmd.CommandText = "SELECT ASSEMBLYPROPERTY(N'SearchTools', 'MvID')";

var currentId = (Guid)cmd.ExecuteScalar();
var moduleId = typeof(SearchProcs).Assembly.ManifestModule.ModuleVersionId;

if (moduleId != currentId)
{
  log.WriteLine("Updating assembly ...");
  var raw = File.ReadAllBytes(typeof(SearchProcs).Assembly.Location);
  var hex = String.Join("", raw.Select(b => b.ToString("x2")).ToArray());
  cmd.CommandText = String.Format("ALTER ASSEMBLY SearchTools FROM 0x{0}", hex);
  cmd.ExecuteNonQuery();
}
else
{
  log.WriteLine("Assembly already up to date");
}

As a bonus the ASSEMBLYPROPERTY function will return NULL for the MvID property if the assembly named does not exist - this allows for CREATE/ALTER selection.

Cool.

Wednesday, 22 January 2014

SQL Debugging Gotcha

While trying to debug a table trigger (shudder) in SQL Server Management Studio I encountered the following error message:

Unable to start T-SQL Debugging. Could not connect to computer '.'. This is usually a temporary error during hostname resolution and means that the local server did not receive a response from an authoritative server.

It's actually as simple as the fact that you used '.' to mean 'this machine'. Using your actual machine name, or even 'localhost' will have the same effect as the dot but will also let the debugger connect to the server instance.

This wasn't obvious from the error message and hopefully this will help someone else.