Tagged: SQL Server

In SQL Null is not a value… not a value!

I have been spending a lot of time fixing SQL Server database errors caused by stored procedures attempting to compare null. If you don’t know, in SQL:

NULL = NULL is false

NULL <> NULL is false

Null is not a value. Null is nothing. You can’t compare nothing to nothing because there is nothing to compare. I know you can do a select and see the word NULL in the results in SQL Management Studio, but that is just a marker so you don’t confuse empty strings with NULL or something.

If you need to do a comparison on a nullable value please check that shit for null first:

t2.column2 is null or t2.column2 = t1.column2

t2.column2 is not null

Also, if you try to be smart and turn ANSI_NULLS off you are going to be hurt when you have to upgrade your SQL Server to a version that forces ANSI_NULLS on (it’s coming).

I have been guilty of comparing NULL and saying, “it has a NULL value.” Now that I am having to fix scripts written by someone who did think about NULL, I wanted to rant and hammer this point home for myself so I don’t cause anyone the pain I am feeling right now. Null is not a value… not a value!


Running SQL Files in C# with SMO

I have used SMO, older versions of SMO, to run SQL in C#, but I wanted to do it a new application I’m writing to help with seeding database for tests. Actually, it was pretty easy and you may ask why not just use ADO or sqlcmd. Well the SQL I want to run has TSQL statements that ADO can’t work with. The sqlcmd tool is awesome from the command line, but I wanted a C# solution and SMO allowed me to have less ceremony to get everything up and running.

First you have to get the SMO DLLs necessary to connect with SQL Server and execute the scripts. I am using the files for SQL Server 2012 and I found the DLLs in C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\. You will need 3 of them:

  • Microsoft.SqlServer.ConnectionInfo.dll
  • Microsoft.SqlServer.Management.Sdk.Sfc.dll
  • Microsoft.SqlServer.Smo.dll

You can copy them to a common folder in your solution and reference them in the project you will use to code up your SQL file runner. If you are into all the Ninja code stuff, you will probably host them on a private NuGet server. Next, all you need is a little code:

using System;
using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

public class ExcuteSqlScript
	public void FromFile(string filePath, string connectionString)
		FileInfo file = new FileInfo(filePath);
		string script = file.OpenText().ReadToEnd();
		this.FromString(script, connectionString);

	public void FromString(string script, string connectionString)
		SqlConnection connection = new SqlConnection(connectionString);
		Server server = new Server(new ServerConnection(connection));

	//Finally figured out how to display formatted code, yay!

I basically have two methods. One loads the SQL from a file path to the SQL file and the other accepts a string with the SQL you want to run. They are both pretty self explanatory. You just need to supply the file path or SQL string and a connection string and it will execute. You should add some error handling and perhaps tweak the file read security and performance for your situation (see refs below). One thing I will be adding is a method to run all SQL files in a directory or iterate over a config file containing the paths to SQL files that need to be ran.

Anyway, this gives a basis to create a more robust solution. If you need more advanced interaction, like transactions, take a closer look at the API for ServerConnection and read the docs, it wasn’t too hard to get through it as the API is simple.


SQL Server Management Objects (SMO) Programming Guide – http://technet.microsoft.com/en-us/library/ms162169.aspx

C# .Net: Fastest Way to Read Text Files – http://blogs.davelozinski.com/curiousconsultant/csharp-net-fastest-way-to-read-text-files

Happy Coding!