Reclaiming Stored Procedure Code

One of the largest problems people have with keeping stored procedures in source control is not a disagreement that they should be but the change to the process in which they are accustomed to. Normally with code, you create a physical file which is then piped to the compiler to output an assembly. With Microsoft’s SQL Server Management (SSMS) Studio the process of creating a file and then running it has changed this, because the database stores the SQL code for procedures in the database negating the need for a physical file. This is a convenient shortcut but makes it difficult to keep SQL source in sync, because inevitably people forget to create the file from Management Studio when they’re done. SSMS has a menu item for generating a file, but the creation options are limiting depending on the need, and it adds additional code around the procedure which most of the time isn’t necessary. Not to mention generating one file is simple enough, but having to do it for hundreds of changed procedures is tedious and time consuming.

Script Procedure

Fortunately, there is a convenient fix for this. Since MSSQL Server stores the code in the database, you can easily retrieve the code and save it yourself. Microsoft created the INFORMATION_SCHEMA.ROUTINES view which returns information such as it’s name, type, schema, and most importantly, the code. Using this approach you can also format the code the way you want when saving it to a file. I always make the scripts so they don’t fail so I prepend the necessary header code and change the create to an alter command before saving it.

File on Github at: https://github.com/kemiller2002/StructuredSight/blob/master/GettingProcedures/GetProcedures.ps1

Did You Forget to Create a Nuget Package Again?

The biggest problem with managing Nuget packages for internal applications is creating and publishing the correct versions for others to consume. It’s not hard, but it does require manual steps to update the Nuspec file, create the package, and publish it. For Nuget packages which are only occasionally updated, this probably isn’t a problem. There is adequate time to develop and test it, but when using packages to keep multiple solutions in sync by using the same libraries, and all of them are under active development, it can be easy to miss one of several steps.

Automating the process is relatively easy. The Nuget Package Explorer is great for creating packages through a GUI, but it doesn’t help with automating the process of creating packages after a build. For that, there is the Nuget Command Line.

When I install it, I put it in the following location:

The following scripts assume it will be located here. If not, simply modify them to point to the correct location.

Post Build Event

In the post build events text box under the Build Events tab in the project file place the following code. It needs to be all on one line with no carriage returns.

Create the CreateNugetPackage.ps1:

Modify the AssemblyInfo.cs file and change the AssemblyVersion to have a * to increment the build number (the last zero in the series). As future versions of the assembly are created in new branches, the other numbers should be changed to reflect the current version. (2.8.1.* for Major version 2, Minor Version 8, Patch Version 1)

Now everytime Visual Studio makes a successful build, a new Nuget package will appear in the appropriate folder next to the newly created assembly:

Nugets

A full example project with it can be found on GitHub at this repo.

Cut the Header Off and the Body will Die (Well not really)

Recently, I had to work on an audit item concerning removing the “X-Powered-By” header from the IIS websites.
http header

You can right click on the header and select remove, but this causes two problems. One if you have a lot of sites (I had about 30), it becomes rather time consuming. Two you’ll notice that the Entry Type of it is set to Inherited. With HTTP headers there are two entry types in IIS: Local and Inherited. Local means the current site or web application is the point of origin. If you delete it, the header will be removed from the current node and all it’s children. Inherited means just the opposite. It is not the point of origin and removing it does not permanently delete it from IIS. All web sites list this response header as inherited.

You can click on the machine which hosts the sites in IIS and remove it:

Delete From Computer

The problem with this being that someone has to manually delete it from each server. This is both time consuming and error prone (any manual task is), and with PowerShell there is a much easier way:

Http Header Removed

Consequently, if you want to check to see if it exists before running the delete command, you can add a few lines to check. By default the Remove-WebConfigurationProperty will output a warning if you try to delete something which doesn’t exist.

For any IIS Administration to work, you have to run PowerShell in Administrator mode. With a hardened server you’ll have to supply your credentials to do so.

PowerShell has the ability to add headers as well. Just use the Add-WebConfigurationProperty.

Language Comparison Euler Problem One

Question:

If we list all the natural numbers below 10 that are multiples of 3 or 5, we get 3, 5, 6 and 9. The sum of these multiples is 23.

Find the sum of all the multiples of 3 or 5 below 1000.

F#

F# Example One

or

F# Example Two

C#

C# Example One

or

C# Example Two

JavaScript

JavaScript Example One

SQL

or

PowerShell

or