SQL Server CLR and Authorization

Over the past year I have worked on a few projects that have used SQL CLR procedures and functions.  SQL Server needs to be configured to run these CLRs.  A standard script to get this done would be:

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO
DECLARE @cmd NVARCHAR(MAX);
SET @cmd='ALTER DATABASE ' + QUOTENAME(DB_NAME()) + ' SET TRUSTWORTHY ON;';
EXEC(@cmd);
GO

This will generally get your CLR up and running on your server if it's the first time you have installed the CLR. What I have noticed though is that if you backup a database and restore it on another SQL Server in a different Domain with different security setting the CLR stops working with an error like this:

"An error occurred in the Microsoft .NET Framework while trying to load assembly id 65541. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: "

To get your CLRs working again you need to correct the Authorization on the database with the CLR.  Here is a script that will allow you to do this:

EXEC sp_configure 'clr enabled', 1;
-- To get the owner SID recorded in the master database for the current database
SELECT owner_sid FROM sys.databases WHERE database_id=DB_ID()

-- To get the owner SID recorded for the current database owner
SELECT sid FROM sys.database_principals WHERE name=N'dbo'

-- The sid's above should be the same
-- To fix:
/*
ALTER AUTHORIZATION ON Database::XXXXX TO [domain\user]
*/

 

Note the SELECT statements are merely going to show that the SID for the owner of the current database differs to what the master database has recorded as the SID for the current database.  To fix the SID on the current database run the ALTER AUTHORIZATION statement replacing the XXXXX with the current database name and the [domain\user] with the user you want to give authorization to e.g. [sa].  Once the authorization has been altered run the first script above to ensure CLR is enabled and TRUSTWORTHY is ON.

 

 

Tennis Kata - My Solution

Intentional practice is something that is strongly advocated at the company I work for.  What we do as intentional practice changes from time to time, but over the last few months we have spent a fair bit of time solving and resolving various kata's.

Here is what I eventually ended up with after numerous runs of the Tennis Kata.

Firstly, the code:

    
    public class Game
    {
        private readonly Point _servicePoint = new Point();
        private readonly Point _receiverPoint = new Point();

        public string Score
        {
            get
            {
                if (_servicePoint.ToString().Equals(Point.FORTY) 
                   && (_receiverPoint.ToString().Equals(Point.FORTY)))
                {
                    return "DEUCE";
                }
                return string.Format("{0}-{1}", _servicePoint, _receiverPoint);
            }
        }

        public void ScoreToService()
        {
            _servicePoint.Score(_receiverPoint);
        }

        public void ScoreToReceiver()
        {
            _receiverPoint.Score(_servicePoint);
        }
    }

 
    public class Point
    {
        public const string LOVE = "0";
        public const string FIFTEEN = "15";
        public const string THIRTY = "30";
        public const string FORTY = "40";
        public const string GAME = "GAME";
        public const string ADVANTAGE = "ADV";

        private string _score = LOVE;
        private Dictionary> _pointMap;

        public Point()
        {
            BuildPointMap();
        }

        public override string ToString()
        {
            return _score;
        }

        public void Score(Point opponentPoint)
        {
            _pointMap[_score].Invoke(opponentPoint);
        }

        private void BuildPointMap()
        {
            _pointMap = new Dictionary>
            {
               {Point.LOVE, o => { _score = Point.FIFTEEN; }},
               {Point.FIFTEEN, o => { _score = Point.THIRTY; }},
               {Point.THIRTY, o => { _score = Point.FORTY; }},
               {
                  Point.FORTY, o =>
                  {
                     if (o.ToString().Equals(Point.FORTY))
                     {
                        _score = ADVANTAGE;
                     }
                     else
                     {
                        _score = o.ToString().Equals(Point.ADVANTAGE) 
                                                             ? FORTY 
                                                             : GAME;
                     }
                  }
               },
               {Point.ADVANTAGE, o => { _score = GAME; }},
               {Point.GAME, o => 
                 { 
                  throw new ApplicationException("Game already completed!"); 
                 }
               }
            };
        }
    }

And the tests:

    
    [TestFixture]
    public class TestGame
    {
        [Test]
        public void NewGame_ShouldSetScore_0_0()
        {
            //---------------Set up test pack-------------------
            //---------------Assert Precondition----------------

            //---------------Execute Test ----------------------
            var game = new Game();

            //---------------Test Result -----------------------
            Assert.IsNotNull(game);
            Assert.AreEqual("0-0", game.Score);
        }

        [Test]
        public void ScoreToService_When_0_0_ShouldSetScore_15_0()
        {
            //---------------Set up test pack-------------------
            var game = new Game();
            //---------------Assert Precondition----------------
            Assert.AreEqual("0-0", game.Score);

            //---------------Execute Test ----------------------
            game.ScoreToService();
            //---------------Test Result -----------------------
            Assert.AreEqual("15-0", game.Score);
        }

        [Test]
        public void ScoreToReceiver_When_0_0_ShouldSetScore_0_15()
        {
            //---------------Set up test pack-------------------
            var game = new Game();
            //---------------Assert Precondition----------------
            Assert.AreEqual("0-0", game.Score);

            //---------------Execute Test ----------------------
            game.ScoreToReceiver();
            //---------------Test Result -----------------------
            Assert.AreEqual("0-15", game.Score);
        }

        [Test]
        public void ScoreToReceiver_When_15_15_ShouldSetScore_15_30()
        {
            //---------------Set up test pack-------------------
            var game = new Game();
            game.ScoreToService();
            game.ScoreToReceiver();
            //---------------Assert Precondition----------------
            Assert.AreEqual("15-15", game.Score);

            //---------------Execute Test ----------------------
            game.ScoreToReceiver();
            //---------------Test Result -----------------------
            Assert.AreEqual("15-30", game.Score);
        }

        [Test]
        public void ScoreToService_When_40_15_ShouldSetScore_GAME_15()
        {
            //---------------Set up test pack-------------------
            var game = new Game();
            game.ScoreToService();
            game.ScoreToService();
            game.ScoreToService();
            game.ScoreToReceiver();
            //---------------Assert Precondition----------------
            Assert.AreEqual("40-15", game.Score);

            //---------------Execute Test ----------------------
            game.ScoreToService();
            //---------------Test Result -----------------------
            Assert.AreEqual("GAME-15", game.Score);
        }

        [Test]
        public void ScoreToService_When_30_40_ShouldSetScore_DEUCE()
        {
            //---------------Set up test pack-------------------
            var game = new Game();
            game.ScoreToService();
            game.ScoreToService();
            game.ScoreToReceiver();
            game.ScoreToReceiver();
            game.ScoreToReceiver();
            //---------------Assert Precondition----------------
            Assert.AreEqual("30-40", game.Score);

            //---------------Execute Test ----------------------
            game.ScoreToService();
            //---------------Test Result -----------------------
            Assert.AreEqual("DEUCE", game.Score);
        }

        [Test]
        public void ScoreToService_When_DEUCE_ShouldSetScore_ADV_40()
        {
            //---------------Set up test pack-------------------
            var game = new Game();
            game.ScoreToService();
            game.ScoreToService();
            game.ScoreToService();

            game.ScoreToReceiver();
            game.ScoreToReceiver();
            game.ScoreToReceiver();
            //---------------Assert Precondition----------------
            Assert.AreEqual("DEUCE", game.Score);

            //---------------Execute Test ----------------------
            game.ScoreToService();
            //---------------Test Result -----------------------
            Assert.AreEqual("ADV-40", game.Score);
        }

        [Test]
        public void ScoreToReceiver_When_40_ADV_ShouldSetScore_40_GAME()
        {
            //---------------Set up test pack-------------------
            var game = new Game();
            game.ScoreToService();
            game.ScoreToService();
            game.ScoreToService();

            game.ScoreToReceiver();
            game.ScoreToReceiver();
            game.ScoreToReceiver();
            game.ScoreToReceiver();
            //---------------Assert Precondition----------------
            Assert.AreEqual("40-ADV", game.Score);

            //---------------Execute Test ----------------------
            game.ScoreToReceiver();
            //---------------Test Result -----------------------
            Assert.AreEqual("40-GAME", game.Score);
        }

        [Test]
        public void ScoreToReceiver_When_DEUCE_ShouldSetScore_40_ADV()
        {
            //---------------Set up test pack-------------------
            var game = new Game();
            game.ScoreToService();
            game.ScoreToService();
            game.ScoreToService();

            game.ScoreToReceiver();
            game.ScoreToReceiver();
            game.ScoreToReceiver();
            //---------------Assert Precondition----------------
            Assert.AreEqual("DEUCE", game.Score);

            //---------------Execute Test ----------------------
            game.ScoreToReceiver();
            //---------------Test Result -----------------------
            Assert.AreEqual("40-ADV", game.Score);
        }
    }

 
    [TestFixture]
    public class TestPoint
    {
        // ReSharper disable InconsistentNaming
        [Test]
        public void NewPoint_ShouldSetToString_0()
        {
            //---------------Set up test pack-------------------
            //---------------Assert Precondition----------------
            //---------------Execute Test ----------------------
            var point = new Point();
            //---------------Test Result -----------------------
            Assert.IsNotNull(point);
            Assert.AreEqual(Point.LOVE, point.ToString());
        }

        [Test]
        public void Score_When_0_ShouldSetToString_15()
        {
            //---------------Set up test pack-------------------
            var point = new Point();
            //---------------Assert Precondition----------------
            Assert.AreEqual(Point.LOVE, point.ToString(), "PRE CONDITION");
            //---------------Execute Test ----------------------
            point.Score(new Point());
            //---------------Test Result -----------------------
            Assert.AreEqual(Point.FIFTEEN, point.ToString());
        }

        [Test]
        public void Score_When_15_ShouldSetToString_30()
        {
            //---------------Set up test pack-------------------
            var point = new Point();
            point.Score(new Point());
            //---------------Assert Precondition----------------
            Assert.AreEqual(Point.FIFTEEN, point.ToString(), "PRE CONDITION");
            //---------------Execute Test ----------------------
            point.Score(new Point());
            //---------------Test Result -----------------------
            Assert.AreEqual(Point.THIRTY, point.ToString());
        }

        [Test]
        public void Score_When_30_ShouldSetToString_40()
        {
            //---------------Set up test pack-------------------
            var point = new Point();
            point.Score(new Point());
            point.Score(new Point());
            //---------------Assert Precondition----------------
            Assert.AreEqual(Point.THIRTY, point.ToString(), "PRE CONDITION");
            //---------------Execute Test ----------------------
            point.Score(new Point());
            //---------------Test Result -----------------------
            Assert.AreEqual(Point.FORTY, point.ToString());
        }

        [Test]
        public void Score_When_40_ShouldSetToString_GAME()
        {
            //---------------Set up test pack-------------------
            var point = new Point();
            point.Score(new Point());
            point.Score(new Point());
            point.Score(new Point());
            //---------------Assert Precondition----------------
            Assert.AreEqual(Point.FORTY, point.ToString(), "PRE CONDITION");
            //---------------Execute Test ----------------------
            point.Score(new Point());
            //---------------Test Result -----------------------
            Assert.AreEqual(Point.GAME, point.ToString());
        }

        [Test]
        public void Score_When_40_40_ShouldSetToString_ADV()
        {
            //---------------Set up test pack-------------------
            var point = new Point();
            var opponentPoint = new Point();
            point.Score(opponentPoint);
            point.Score(opponentPoint);
            point.Score(opponentPoint);
            opponentPoint.Score(point);
            opponentPoint.Score(point);
            opponentPoint.Score(point);
            //---------------Assert Precondition----------------
            Assert.AreEqual(Point.FORTY, point.ToString(), "PRE CONDITION");
            Assert.AreEqual(Point.FORTY, opponentPoint.ToString(), "PRE CONDITION");
            //---------------Execute Test ----------------------
            point.Score(opponentPoint);
            //---------------Test Result -----------------------
            Assert.AreEqual(Point.ADVANTAGE, point.ToString());
        }

        [Test]
        public void Score_When_40_ADV_ShouldSetToString_40()
        {
            //---------------Set up test pack-------------------
            var point = new Point();
            var opponentPoint = new Point();
            point.Score(opponentPoint);
            point.Score(opponentPoint);
            point.Score(opponentPoint);
            opponentPoint.Score(point);
            opponentPoint.Score(point);
            opponentPoint.Score(point);
            opponentPoint.Score(point);
            //---------------Assert Precondition----------------
            Assert.AreEqual(Point.FORTY, point.ToString(), "PRE CONDITION");
            Assert.AreEqual(Point.ADVANTAGE, opponentPoint.ToString(), "PRE CONDITION");
            //---------------Execute Test ----------------------
            point.Score(opponentPoint);
            //---------------Test Result -----------------------
            Assert.AreEqual(Point.FORTY, point.ToString());
        }

        [Test]
        public void Score_When_ADV_40_ShouldSetToString_GAME()
        {
            //---------------Set up test pack-------------------
            var point = new Point();
            var opponentPoint = new Point();
            point.Score(opponentPoint);
            point.Score(opponentPoint);
            point.Score(opponentPoint);
            opponentPoint.Score(point);
            opponentPoint.Score(point);
            opponentPoint.Score(point);
            point.Score(opponentPoint);
            //---------------Assert Precondition----------------
            Assert.AreEqual(Point.ADVANTAGE, point.ToString(), "PRE CONDITION");
            Assert.AreEqual(Point.FORTY, opponentPoint.ToString(), "PRE CONDITION");
            //---------------Execute Test ----------------------
            point.Score(opponentPoint);
            //---------------Test Result -----------------------
            Assert.AreEqual(Point.GAME, point.ToString());
        }

        [Test]
        public void Score_When_GAME_0_ShouldError()
        {
            //---------------Set up test pack-------------------
            var point = new Point();
            var opponentPoint = new Point();
            point.Score(opponentPoint);
            point.Score(opponentPoint);
            point.Score(opponentPoint);
            point.Score(opponentPoint);
            //---------------Assert Precondition----------------
            Assert.AreEqual(Point.GAME, point.ToString(), "PRE CONDITION");
            //---------------Execute Test ----------------------
            var ex = Assert.Throws(() => point.Score(opponentPoint));
            //---------------Test Result -----------------------
            StringAssert.Contains("Game already completed!", ex.Message);
        }
    }

Getting Started with Ruby

In my day job, we use ruby rake files for biulding our .net projects.  The rake files are run by our continuous integration server and build the projects, run unit tests, coverage reports and more.  Over the past year they have evolved and gained more and more in the way of functionality.  I have spent a fair amount of time hacking around in them to add and improve existing functionality picking up bits of ruby as I go along.  I am now at a point where I want to get to know ruby a little better and thought I might document some of my learning.

To get started, if you want to work with ruby you will need to download and install it on your pc.  Ruby can run on a number of operating systems, but in my case I am working on a Windows pc.  To download and install the latest version of ruby go to: www.rubyinstaller.org/downloads/ and select the latest ruby installer download.  Once the download is complete double click the exe to install.

Once ruby is installed you will need to add the path the the ruby.exe to your environment variables path.  Heres how to do it:
Windows XP : http://support.microsoft.com/kb/310519
Windows 7 : http://geekswithblogs.net/renso/archive/2009/10/21/how-to-set-the-windows-path-in-windows-7.aspx

Here is how you might create a hello world ruby script:

• Create a new folder for the scripts c:\rubyscripts
• Add a new text document in the folder called hello_world.rb
• Edit the script in your favourite text editor and add the following

puts "hello world"

• Open a command prompt in the c:\rubyscripts folder
• Run the following c:\rubyscripts\ruby hello_ruby.rb

Strange Web Services Exception

One of the application I work on started playing up recently.  The application communicates with one of our customers systems via web services.  Due to technical reasons on their side they are changing the platform that their web services run on.  This has meant that we have had to make some changes to accomodate their changes and get our application talking to the new web services correctly.  Once released into their testing environment some of the web services call started to throw a System.ServiceModel.CommunicationException:

System.ServiceModel.CommunicationException: There was an error in serializing body of message checkIssueDeliveryRequest: 
'Unable to generate a temporary class (result=1).  error CS2001: Source file '
C:\Windows\TEMP\rjsmbtjy.0.cs' could not be found  error CS2008: No inputs specified  '.  
Please see InnerException for more details. ---> System.InvalidOperationException: Unable to generate a temporary class (result=1).  
error CS2001: Source file 'C:\Windows\TEMP\rjsmbtjy.0.cs' could not be found  error 
CS2008: No inputs specified       
at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, XmlSerializerCompilerParameters xmlParameters, Evidence evidence)   
....
....

It turns out the problem is down to the fact that the User account that the Application Pool associated with the web services website does not have read and write permissions to the c:\windows\temp directory as described here http://support.microsoft.com/kb/322886.  Below is a more detailed step by step approach that I to solve the issue.

• Open IIS Manager
• Click on the web services application node of the problem web services website
• Click on Basic Settings... to see what Application Pool is being used (in my case it was ASP.NET v4.0 Integrated)
• Click the Application Pools node and select the Application Pool associated with your web services website
• Click on Advances Settings... and check the Process > Identity to get the account that is being used
• Open an explorer window and browse to c:\windows\temp
• Right click on the folder and click properties
• Select the Security tab and  click Edit:
• If the account already appears under "Group or user names:", highlight the account by clicking on it and ensure that the read and write checkboxes are checked
• If it is not already listed click Add... to add the account before ensuring that the read and write permission are checked for that account

That's it for now.

T-SQL: Scripting tables and Named constraints

In a number of projects that I am currently working on I have had to make schema changes.  When creating these schema changes we generally create changes scripts that get run as part of a version upgrade.  The change scripts will generally be applied either on application startup or by a command line application that will run any new upgrade scripts and update a version number in that database.  To get an idea of how this can be done have a look at how FluentMigrator works.

I don't particularly like creating new tables in SQL Server Management Studio and then using the script generator to create a create table script as I find the resulting scripts are always pretty verbose.
Writing a create table script is pretty straight forward.  Here is an example:

CREATE TABLE [dbo].[Employee]
(
  EmployeeID int not null PRIMARY KEY,
  FirstName varchar(100) not null,
  LastName varchar(100) not null
)

The problem with this script is that the primary key is not explicitly named.  So how do we go about explicitly naming the primary key constraint and for that matter any other constraint.  Here is an example that demonstrates how to do this for primary keys, foreign keys and unique constrains:

CREATE TABLE [dbo].[JobTitle]
(
 JobTitleID int not null 
  CONSTRAINT [PK_JobTitle] PRIMARY KEY (JobTitleID),
 Name varchar(100) not null,
 [Description] varchar(2000) null
)

CREATE TABLE [dbo].[Employee]
(
  EmployeeID int not null 
 CONSTRAINT [PK_Employee] PRIMARY KEY (EmployeeID),
  FirstName varchar(100) not null,
  LastName varchar(100) not null,
  IDNumber varchar(30) not null 
 CONSTRAINT [UC_IDNumber] UNIQUE (IDNumber),
  JobTitleID int null 
 CONSTRAINT [FK_Employee_JobTitle] REFERENCES [dbo].[JobTitle]
)

One thing that is not demonstrated in this code snippet is how to add a second named constraint onto a field.  It is pretty straight forward.  I have specifically layed out the example with the constraints indented on the line below the field they relate to.  Notice that there is no comma before the CONSTRAINT keyword.  To add another constraint that applies to the same field, you just add another constraint keyword and continue.  I have indented the constraints and put them on their own line so that the code is more readable in the case where there are multiple constraints on any one field.

SQLCMD and BACKUP - watch out for this

Yesterday, I was working creating a rake task for backing up a database.  As I am still pretty new to ruby I decided to see if I could get the backup code working with sqlcmd from the command line first.  I created a backupdb.sql file to do the backup and then called it from the command line.

This is what the backupdb.sql looked like:

BACKUP DATABASE $(database)  
    TO DISK = $(backupfilename)

Not much to it!
I then executed the following sqlcmd command:

sqlcmd -S  localhost -i BackupDb.sql -v database="mydb" -v backupfilename="mydb.bak"

When I just specified the database and backupfilename the backup worked fine with the backup file being written to the default SQL Server backup folder.  I then changed the backupfilename to include a path as I wanted to specify where the backup file should be saved. This is the command I executed:

sqlcmd -S  localhost -i BackupDb.sql -v database="mydb" -v backupfilename="E:\sqlbackups\mydb.bak"

This time I got the following error:
Msg 102, Level 15, State 1, Server ANDREW-PC, Line 3 Incorrect syntax near 'E:'.
After a lot of fiddling around I figured out what I needed to do to fix the problem. The problem was that the backupfilename needs to be unicode.  See below for the fixed backupdb.sql

BACKUP DATABASE $(database)  
    TO DISK = N'$(backupfilename)'

SQL Server - Query to get table sizes

Once again I am posting SQL that I have found on the internet (mainly so that I have can find this scriopt easily in the future).  This one is thanks to marc_s and can be found in this Stackoverflow post.  The script will give you a breakdown of the table size for each table in your database.  This includes a row count, disk space used and disk space available.  I have modified the original script slightly as a number of databases I work with have multiple schemas and the original script doesn't show the schema name.

SELECT
    s.NAME AS SchemaName,  
    t.NAME AS TableName, 
    p.rows AS RowCounts, 
    SUM(a.total_pages) * 8 AS TotalSpaceKB,  
    SUM(a.used_pages) * 8 AS UsedSpaceKB,  
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB 
FROM  sys.tables t 
INNER JOIN sys.Schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id 
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id 
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id 
WHERE  
    t.NAME NOT LIKE 'dt%'  
    AND t.is_ms_shipped = 0 
    AND i.OBJECT_ID > 255  
GROUP BY
 s.Name,  
    t.Name, 
    p.[Rows] 
ORDER BY  
 s.Name,
    t.Name 

T-SQL script for finding object dependencies

This morning I wanted to find out where a specific stored procedure was being used.  I wasn't sure how to do this using T-SQL so did a bit of googling and found this article.  I have made a slight enhancement to the scripts shown in the article. 

DECLARE @ObjectName VARCHAR(500)
SET @ObjectName = 'dbo.sp_test_Proc'

-- shows objects that the named object depends on
SELECT DISTINCT OBJECT_NAME(DEPID) DEPENDENT_ON_OBJECT, OBJECT_NAME (ID) OBJECTNAME
FROM SYS.SYSDEPENDS
WHERE ID = OBJECT_ID(@ObjectName)

-- shows objects that use the named object
SELECT DISTINCT  OBJECT_NAME(DEPID) OBJECT_NAME, OBJECT_NAME (ID) USED_IN_OBJECT
FROM SYS.SYSDEPENDS
WHERE DEPID = OBJECT_ID(@ObjectName)

I am sure I will get a lot of milage out of this in the future.

tSQLt - Assertions

In this post my aim is to introduce the basic tSQLt assertions.  The examples should be enough to get you going with assertions. My assumption is that you have at least some experience writing unit tests with any unit testing framework.

tSQLt.Fail

CREATE PROCEDURE [UnitTest_FirstGo].[test Fail]
AS
BEGIN
  EXEC tSQLt.Fail 'This test should fail and display this message';
END

 tSQLt.AssertEquals

CREATE PROCEDURE [UnitTest_FirstGo].[test AssertEquals]
AS
BEGIN
  --Assemble
  DECLARE @expected int
  DECLARE @actual int
 SET @expected = 1;
 SET @actual = 0;
  
  --Act
  
  --Assert
  EXEC tSQLt.AssertEquals @expected, @actual, 'The is a TEST FAILURE MESSAGE!'
END

 tSQLt.AssertEquals with System Under Test (SUT)

First we need to create something to represent the SUT

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_SUM]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_SUM]
 
GO

CREATE FUNCTION fn_SUM
(
 @a int,
 @b int
)
RETURNS int
BEGIN
 RETURN @a+@b;
END

and now the test

CREATE PROCEDURE [UnitTest_FirstGo].[test AssertEquals with SUT]
AS
BEGIN
  --Assemble
  DECLARE @expected int
  DECLARE @actual int
 SET @expected = 3;
  --Act
 SELECT @actual = dbo.fn_SUM(1,2);
  
  --Assert
  EXEC tSQLt.AssertEquals @expected, @actual
END

tSQLt.AssertEqualsString

CREATE PROCEDURE [UnitTest_FirstGo].[test AssertEqualsString]
AS
BEGIN
  --Assemble
  DECLARE @expected varchar(20)
  DECLARE @actual varchar(20)
 SET @expected = 'hello world';
 SET @actual = 'goodbye world';
  
  --Act
  
  --Assert
  EXEC tSQLt.AssertEqualsString @expected, @actual, 'The is a TEST FAILURE MESSAGE!'

END

tSQLt.AssertEqualsTable

This one takes a bit of time to get your head around, but once you get going and when used in conjunction with Fake tables it's really awesome.

CREATE PROCEDURE [UnitTest_FirstGo].[test AssertEqualsTable]
AS
BEGIN
  --Assemble
 if exists(select * 
 from information_schema.tables
 where table_schema='dbo' and table_name='tablea')
 begin
  drop table dbo.TableA;
  create table dbo.TableA (id int not null);
 end

 if exists(select * 
 from information_schema.tables
 where table_schema='dbo' and table_name='tableb')
 begin
  drop table dbo.TableB;
  create table dbo.TableB (id int not null);
 end

 insert tablea (id) values (1);
 insert tablea (id) values (2);
 insert tablea (id) values (3);


 insert tableb (id) values (1);
 --insert tableb (id) values (2);
 insert tableb (id) values (3);

  
  --Act
 -- nothing to do here
  
  --Assert
  exec tSQLt.AssertEqualsTable 'TableA', 'TableB'

END

Uncomment the commented out insert to make the test pass.

The failure message attempts to point you to what failed showing which rows matched and which failed to match.

tSQLt.AssertObjectEquals

For this test we once again need some setup (i.e. we need to create an object to test if it exists)

SELECT OBJECT_ID (N'dbo.fn_SUM', N'F')
IF OBJECT_ID (N'dbo.fn_SUM', N'F') IS NOT NULL
 DROP FUNCTION dbo.fn_sum
 
GO 
 
CREATE FUNCTION fn_SUM
(
 @a int,
 @b int
)
RETURNS int
BEGIN
 RETURN @a+@b;
END

And now the test

CREATE PROCEDURE [UnitTest_FirstGo].[test AssertObjectExists]
AS
BEGIN

  --Assert
  EXEC tSQLt.AssertObjectExists 'fn_SUM'

END

tSQLt.AssertResultSetsHaveSameMetaData

CREATE PROCEDURE [UnitTest_FirstGo].[test AssertObjectExists]
AS
BEGIN

  --Assemble
 if exists(select * 
 from information_schema.tables
 where table_schema='dbo' and table_name='tablea')
 begin
  drop table dbo.TableA;
  create table dbo.TableA (id int not null);
 end

 if exists(select * 
 from information_schema.tables
 where table_schema='dbo' and table_name='tablec')
 begin
  drop table dbo.TableC;
  create table dbo.TableC (id int not null, name varchar(50) not null);
 end

 insert tablea (id) values (1);
 insert tablea (id) values (2);
 insert tablea (id) values (3);


 insert tablec (id, name) values (5, 'andrew')
 
  --Act
 -- nothing to do here
  
  --Assert
  exec tSQLt.AssertResultSetsHaveSameMetaData 
   'select * from tablea',
   'select * from tablec'

END

That's it for now. I hope to get some time to look at how to isolate dependencies in the near future.

MSDTC Troubleshooting

Yesterday afternoon I was caught out by this error for a few hours.

A severe error occurred on the current command. The results, if any, should be discarded. OLE DB provider "SQLNCLI10" for linked server "LinkedServerName" returned message "No transaction is active.".

I had release the appliaction I am currently working on to testing.  The applcation has been runnin g perfectly on my development machine, but no such luck on our testers machine.  A new feature for this release is the use of distributed transactions to complete some of the business functionality.  This obviously requires that MSDTC is setup and a linked server is created on the local SQL Server. 

The MSDTC and linked server were setup on the test machine as I had described in my test document.

It took me a fair bit of googling to find the solution.  Most of the obvious setup problems were covered in my test document.  The problem in the end turned out to be the firewall on the test machine, something I should have checked much sooner.

Links for future reference:
http://stackoverflow.com/questions/673806/msdtc-how-many-ports-are-needed
http://support.microsoft.com/kb/306843
http://www.lewisroberts.com/2009/08/16/msdtc-through-a-firewall-to-an-sql-cluster-with-rpc/

Comparing Byte Arrays with Linq

This morning I ran into a small issue. I needed to extract a series of images from a database table. The problem I had was there were duplicate images in the database table and I needed a unique set of images written to disk. To filter the duplicates I have used the Enumerable.SequenceEqual linq operator. So here is how I did it.

using (var sqlConnection = new SqlConnection(connectionString))
{
 sqlConnection.Open();

 foreach (var deliveryNumber in deliveryNumbers)
 {
  
  var images = new List<byte[]>();

  // get all ewtphotos for this sapdeliverynumber 
  using (var sqlCommand = new SqlCommand())
  {
   sqlCommand.Connection = sqlConnection;
   sqlCommand.CommandType = CommandType.Text;
   sqlCommand.CommandText = "select imagedata " +
          "from delivery d " +
          "inner join image i on d.imageid=i.imageid " +
          "where d.deliverynumber='" + sapDeliveryNumber + "'";
   using (var sqlDataReader = sqlCommand.ExecuteReader())
   {
    while (sqlDataReader.Read())
    {
     var imageData = (byte[])sqlDataReader["ImageData"];

     var imageExists = images.Any(image => image.SequenceEqual(imageData));
     if (!imageExists) images.Add(imageData);

    }
   }

  }
  WriteImagesToDisk(sapDeliveryNumber, images);
 }

}

private static void WriteImagesToDisk(string deliveryNumber, List<byte[]> images)
{
 var counter = 1;
 foreach (var image in images)
 {
  var fileFullName = @"images\DeliveryNumber_" + deliveryNumber + "_" + counter + ".jpg";
  File.WriteAllBytes(fileFullName, image);
  counter++;
 }
}

tSQLt - Writing tests

I am not going to go into detail on how to download and install tSQLt here as it is pretty straight forward and all the instructions are on the tSQLt website. 
It's worth creating a test database in order to try out the examples in the code below.  Once you have created the test database follow the instructions on the tSQLt website to install tSQLt on this database.
Once tSQLt is installed the obvious starting point would be to write and run a test.


tSQLt introduces a concept of a TestClass.  This is a class that is used to group a set of unit tests. tSQLt uses schema's to accomplish this.  I am currently using the following naming convention UnitTests_<schemaName> where the schemaName is the name of the schema that contains the code that is being tested.
Creating a TestClass:

EXEC tSQLt.NewTestClass 'UnitTests_Processing'

Use this query to view your TestClasses:

SELECT * FROM tSQLt.TestClasses

And to delete a TestClass

EXEC tSQLt.DropClass 'UnitTests_Processing'

You actually don't need to create a TestClass as shown above.  You can just create a normal schema and start writing tests against that schema.  The downside of doing this is that the schema won't be correctly registered with tSQLt and commands like

EXEC tSQLt.RunAll

won't run these tests.

Writing a test is as simple as creating a stored procedure within a test schema (TestClass).  All test stored procedures need to start with the word "test". Test names can include spaces.

CREATE PROCEDURE [UnitTests_Processing].[test 1 should equal 1]
AS
BEGIN
-- this is the simplest example of an assert
    EXEC tSQLt.AssertEquals 1, 1, '1 should equal 1'
END

That is it for now.

Database unit testing with tSQLt

I am currently working on a project that has a fair amount of backend database processing happening.  I have got a fair amount of TDD experience in a C# environment and have found that once you get into TDD it becomes a way of life.  As such, I wanted to apply the same approach to my database development. 

After a bit of researching I stumbled across an Open Source unit testing framework for SQL Server called tSQLt.  I have been using it for a few weeks now and yesteday I past the 100 test mark in the project I am working on.  It took me a little while to get up and running with some of the concepts and idea's around tSQLt unit testing, but I am already feeling very comfortable writing database unit tests using tSQLt.

There is a very good series of blog posts on tSQLt already at http://datacentricity.net/tag/tsqlt/
Over the next few days (I hope).  I will try to put together a series of post's showing the basics working of each test type without any extra logic to get in the way of what is going on.

Welcome

It's Friday morning.  I got my headphones on with the sound track to Pulp Fiction playing... and I have just created my new blog.  My plan for this blog is to publish blog post's related to the programming problems that I encounter from day to day.

My aim is that this will act as a repository of sorts so that when I encounter the same issues in the future I know where to start looking.  If what I blog about manages to help anyone out there, that's just a bonus.