In SQL Server 2000 and 2005 there is no concept of a DATE data type, only a DATETIME. So, how do you get the DATE element only, i.e. with a 00:00 time element. One of my colleagues needed to do this, so after a bit of google searching he discovered the following.

-- Get today's date without the time element
SELECT CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE())))

I would probably have done it via a CONVERT/CAST operation, converting to a VARCHAR and then back to a DATETIME, but this is a much more efficient method.

In SQL Server 2008 there is a DATE data type so this will no longer be an issue.

Labels: SQL, SQLServer

// posted by Jane @ 5:38 PM   

Further transaction work revolved around the investigation of naming transactions, and naming savepoints and understanding the differences. Again I worked with the theory of nested transactions to try and understand what was going on.

So, stage one - Labelling the two transactions and committing explicit transactions works fine and as expected

  DELETE FROM Count -- outside of transaction - a known starting point

  IF @@TRANCOUNT > 0
    ROLLBACK TRAN -- in case there are any transactions still hanging around

  BEGIN TRAN OuterTransaction

  INSERT INTO COUNT VALUES (108)

    BEGIN TRAN InnerTransaction

    UPDATE COUNT SET Count = -108 WHERE Count = 108

    COMMIT TRAN InnerTransaction -- can commit transaction InnerTransaction - result will be -108

  IF @@TRANCOUNT > 0
    COMMIT TRAN OuterTransaction

  IF @@TRANCOUNT > 0
    SELECT 'Transactions left hanging.'

  SELECT * FROM COUNT

This results in one row, with the value -108 and no transactions left hanging.

However, if we don't explicitly commit the InnerTransaction, when we commit the OuterTransaction the InnerTransaction is left hanging, so the last SELECT @@TRANCOUNT returns 1 and not 0

  DELETE FROM Count -- outside of transaction - a known starting point

  IF @@TRANCOUNT > 0
    ROLLBACK TRAN -- in case there are any transactions still hanging around

  BEGIN TRAN OuterTransaction

  INSERT INTO COUNT VALUES (108)

    BEGIN TRAN InnerTransaction

    UPDATE COUNT SET Count = -108 WHERE Count = 108

    -- No inner commit / rollback

  IF @@TRANCOUNT > 0
    COMMIT TRAN OuterTransaction

  IF @@TRANCOUNT > 0
    SELECT 'Transactions left hanging.'

  SELECT * FROM COUNT

this results in one row, with the value of -108, but with a transaction left hanging - as we only issued an explicit COMMIT TRAN against the OuterTransaction

so we would probably be better to have the OuterTransaction statement which is currently
  IF @@TRANCOUNT > 0
    COMMIT TRAN OuterTransaction
as
  IF @@TRANCOUNT > 0
    COMMIT TRAN
i.e. with no explicit reference to the transaction

Stage Two, attempting to rollback to a named transaction

  DELETE FROM Count -- outside of transaction - a known starting point

  IF @@TRANCOUNT > 0
    ROLLBACK TRAN -- in case there are any transactions still hanging around

  BEGIN TRAN OuterTransaction

  INSERT INTO COUNT VALUES (108)

    BEGIN TRAN InnerTransaction

    UPDATE COUNT SET Count = -108 WHERE Count = 108

    ROLLBACK TRAN InnerTransaction

  IF @@TRANCOUNT > 0
    COMMIT TRAN OuterTransaction

  IF @@TRANCOUNT > 0
    SELECT 'Transactions left hanging.'

  SELECT * FROM COUNT

which results in the following error:
Msg 6401, Level 16, State 1, Line 77
Cannot roll back InnerTransaction. No transaction or savepoint of that name was found.

as well a single row, with the value of -108, and a transaction left open.

This behaviour confused me for quite a while until, after a bit of research and reading, I found the following definition for ROLLBACK TRAN [transaction_name]

transaction_name - Is the name assigned to the transaction on BEGIN TRANSACTION. transaction_name must conform to the rules for identifiers, but only the first 32 characters of the transaction name are used. When nesting transactions, transaction_name must be the name from the outermost BEGIN TRANSACTION statement.

So, instead, we can either rollback to the OuterTransaction explicitly

  DELETE FROM Count -- outside of transaction - a known starting point

  IF @@TRANCOUNT > 0
    ROLLBACK TRAN -- in case there are any transactions still hanging around

  BEGIN TRAN OuterTransaction

  INSERT INTO COUNT VALUES (108)

    BEGIN TRAN InnerTransaction

    UPDATE COUNT SET Count = -108 WHERE Count = 108

    ROLLBACK TRAN OuterTransaction

  IF @@TRANCOUNT > 0
    COMMIT TRAN OuterTransaction

  IF @@TRANCOUNT > 0
    SELECT 'Transactions left hanging.'

  SELECT * FROM COUNT

which now results in no rows - because everything is rolled back in the inner statement - and no hanging transactions

or, in this case, the same effect can be gained from issuing a simple ROLLBACK TRAN :

  DELETE FROM Count -- outside of transaction - a known starting point

  IF @@TRANCOUNT > 0
    ROLLBACK TRAN -- in case there are any transactions still hanging around

  BEGIN TRAN OuterTransaction

  INSERT INTO COUNT VALUES (108)

    BEGIN TRAN InnerTransaction

    UPDATE COUNT SET Count = -108 WHERE Count = 108

    ROLLBACK TRAN

  IF @@TRANCOUNT > 0
    COMMIT TRAN OuterTransaction

  IF @@TRANCOUNT > 0
    SELECT 'Transactions left hanging.'

  SELECT * FROM COUNT

which again results in no rows, and no hanging transactions

Step three, use a save point to restrict the amount of data to be rolled back instead of an inner transaction
  DELETE FROM Count -- outside of transaction - a known starting point

  IF @@TRANCOUNT > 0
    ROLLBACK TRAN -- in case there are any transactions still hanging around

  BEGIN TRAN OuterTransaction

  INSERT INTO COUNT VALUES (108)

    SAVE TRAN SavePoint

    UPDATE COUNT SET Count = -108 WHERE Count = 108

    ROLLBACK TRAN SavePoint

  IF @@TRANCOUNT > 0
    COMMIT TRAN

  IF @@TRANCOUNT > 0
    SELECT 'Transactions left hanging.'

  SELECT * FROM COUNT

This results in one row, with a value of 108, showing that the UPDATE statement has been rolled back leaving just the results of the INSERT statement

Labels: SQLServer

// posted by Jane @ 5:09 PM   

After Friday's experiment with transactions in SQL Server I got to wondering about what would happen to actions between the
  ROLLBACK TRAN
and the
  IF @@TRANCOUNT > 0
    COMMIT TRAN
so I amended the code to have an additional INSERT as follows (Note: I also changed the final COMMIT TRAN to be a ROLLBACK TRAN to help show the differenence)

  BEGIN TRAN
  INSERT INTO COUNT VALUES (1)
    BEGIN TRAN
    INSERT INTO COUNT VALUES (4)
    ROLLBACK TRAN
  INSERT INTO COUNT VALUES (99) -- New line
  IF @@TRANCOUNT > 0
    ROLLBACK TRAN

which results in the value 99 being inserted into the table, outside of the scope of any transactions - all transactions were rolled back in the inner ROLLBACK TRAN

To ensure that the second part of the statement, which might be unrelated to the first and therefore not dependent on the result of that transaction, is within a transaction, the following seems to work with the expected results:

  BEGIN TRAN
  INSERT INTO COUNT VALUES (1)
    BEGIN TRAN
    INSERT INTO COUNT VALUES (4)
  ROLLBACK TRAN

  IF @@TRANCOUNT = 0
    BEGIN TRAN
    INSERT INTO COUNT VALUES (99)
  IF @@TRANCOUNT > 0
    ROLLBACK TRAN

No new row is added as a result of this action as now all actions are covered within a transaction.

Labels: ilp, SQL

// posted by Jane @ 1:03 PM   

On Wednesday evening, after I left the Contemporary Photography : Lydia Yee talk at the Friends Meeting House I made my way along to the Black Horse for a Geek Dinner. The speaker was Mike Hadlow, who I worked with for a while at Madgex late last year.

He gave a talk about Inversion of Control, a talk that he'd given previously at DDD6 last November. He has posted some links over at his blog.

The audience was more technical than usual, and a few usual faces were missing, possibly put off by the content. However, some new faces were also there, probably attracted by a more technical talk. All in all, another good evening organised by Simon.

Labels: Brighton, event, Geek Dinner

// posted by Jane @ 6:33 PM   

After Bruce and I were looking at DBVerse earlier, we started specifiying how our tool would work, especially with regards to transaction. Our outer process will need to handle its own transactions, and will need to be aware of inner errors and inner transactions to ensure that errors don't ensue.

For example, in the following scenario
BEGIN TRAN
INSERT INTO COUNT VALUES (1)
  BEGIN TRAN
  INSERT INTO COUNT VALUES (4)
  ROLLBACK TRAN
COMMIT TRAN
the following error is produced
Msg 3902, Level 16, State 1, Line 17
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

This is because the inner ROLLBACK TRAN rollsback ALL transactions, not just the inner transaction. So, by the time the COMMIT TRAN is called there are no longer any transactions.

To get around this, the code can be changed to be:
BEGIN TRAN
INSERT INTO COUNT VALUES (1)
  BEGIN TRAN
  INSERT INTO COUNT VALUES (4)
  ROLLBACK TRAN

IF @@TRANCOUNT > 0
  COMMIT TRAN
which works happily and will not cause an error.

NOTE: Count is just a test table I created with a single column of Count which is defined as an int. Nothing very exciting.

Labels: ilp, SQL

// posted by Jane @ 6:01 PM   

A few weeks ago I found a reference to DBVerse in The Morning Brew #58 which seemed like a great starting point for a problem I've had time and time again - keeping databases in sync from a structure perspective making sure that all the dependencies are in place and there is little or no risk when running these changes into a live server.

Bruce and I spent an hour or so today looking into this tool as part of our Madgex ILP time, and the following are our observations.

DBVerse is a C# project, which you install as a template on your machine. In my case, I copied the entire DbVerseDatabaseProjectTemplate.zip file, still as a zip, into my My Documents\Visual Studio 2005\Templates\ProjectTemplates folder.

To add a new DBVerse project to a solution, it is pretty simple, just a case of Adding a new project of type DbVerse Database Project. This will then create a new project, consisting of a DbVerse folder (which keeps all the required DLL and EXE files), a Scripts folder (in which any SQL scripts can be kept) and three cs files - Methods.cs, Methods.Initialize.cs and Methods.Updates.cs

The cs file we were most interested in was Methods.Updates.cs as that is our prime requirement - record and manage updates. In DBVerse updates can be scripted as SQL and put into the Scripts folder and then executed via
Db.ExecuteScript("UpdateJaneTestTable.sql");
or by using the DB objects that are exposed from the Lunaverse.DbVerse.Core such as   Db.AddTable("JaneTest");
  Db.Table("JaneTest").AddColumn("ID", DataType.Int);
  Db.Table("JaneTest").AddColumn("Name", DataType.NVarChar(255));. I'm a big believer in scripting out changes in SQL, as these can be tested against the database and written in a none destructive manner (i.e. checking for existance of an object before updating it etc).

Each update method is given an attribute similar to
[Update(2, "Jane Dallaway", "25/04/2008")]
where 2 is the update number, "Jane Dallaway" is the author and "25/04/2008" is the date that the update was written.

The DBVerse project produces a dll, which is run via either a GUI or a console application. To run the updates via the console application I ran
Lunaverse.DbVerse.Console.exe ApplyAllUpdates

A new table is created - Database Updates - which records all the updates scripts which have run, storing the ID, Method Name, Author, Authored data and Application Date.

On subsequent runs I expected this table to be checked before any updates were applied, and to test this I created the following Update
[Update(3, "Jane Dallaway", "25/04/2008")]
  public void InsertData()
  {
    Db.ExecuteScript(("InsertData.sql"));
  }
I commented out all previous Updates to make it easy to check and ran
Lunaverse.DbVerse.Console.exe ApplyAllUpdates

The output was
Starting...
------------------------------------------------------------
Server=localhost Database=JaneTest Method count=-1
------------------------------------------------------------
Method starting [ApplyAllUpdates]
------------------------------------------------------------
Script [InsertData.sql] was run
Inserted row into table [DatabaseUpdates] ('InsertData', 'Jane Dallaway', '25/04/2008 00:00:00', '25/04/2008')
------------------------------------------------------------
Method finished [ApplyAllUpdates]
------------------------------------------------------------
Done: all methods completed
------------------------------------------------------------
When I checked the database I got the following output:
ID    Name          Description
1     Test          Test via DBVerse

(1 row(s) affected)

so all as I expected.

I then ran the console application again, and got the following output
Starting...
------------------------------------------------------------
Server=localhost Database=NewDesignPartners Method count=-1
------------------------------------------------------------
Method starting [ApplyAllUpdates]
------------------------------------------------------------
Script [InsertData.sql] was run
------------------------------------------------------------
Method finished [ApplyAllUpdates]
------------------------------------------------------------
Done: all methods completed
------------------------------------------------------------
which has differing output to the previous time in the Script [InsertData.sql] line as last time we were notified what was inserted and this time we weren't.
On examining the database, the insert was run again resulting in the following rowsID    Name          Description
1     Test          Test via DBVerse
1     Test         Test via DBVerse

(2 row(s) affected)

This indicates that the check that the update hasn't already been run is causing an issue and cannot be relied upon. This invalidates the tool for our use. All of our other observations were minor - such as not being able to work out how to run all the updates in one go in the GUI, I could only seem to work out how to run one update at a time.

This project is a work in progress, and is probably worth keeping an eye on. Maybe I'll revisit it at a later stage, but I think it might be time to write something that does exactly what we need for the Madgex environment.

Labels: ilp, SQLServer

// posted by Jane @ 1:09 PM   

Last night I went along to the SQL Server User Group meeting in Microsoft's London offices in Victoria (5 minutes walk from the station) to see what it was all about. The topics for the evening were:
• SQL Server 64 bit – advantages and choices
• Cursors – good or evil

The event was held at Microsoft London in one of the auditorium and was very professionally hosted.

It started at 6pm with an update on latest SQL Server related news – including the fact that SQL Server 2008 wouldn’t start on the 29th February 2008 - oops!

The next session was about SQL Server 64 bit, and when you should choose it – this was a bit too DBA focussed for me and was quite low level. But it was well presented and the presenter, Christian Bolton, obviously knew his stuff. His summary was that the question shouldn’t be "Why should I choose 64 bit?" but "Why wouldn’t I?".

There was then a break for pizza and drinks – no beer this time but apparently there normally is. I chatted to a few people and it would appear that LINQ has divided a community - with DBAs being suspicious of the load it was going to place on their databases without, necessarily, the ability to tune it as effectively as they feel they currently do. The developers liked the idea though.

The final session was about cursors and was presented by Eric Alsop – this talk’s summary was "Nothing divides the SQL community like a cursor, so why are they still around? In this session we are going to look at how to use cursors and when they are most applicable." This was an interesting talk, furnished with plenty of examples, to indicate at which points cursors will save you processing cost over using set based logic (mainly around aggregation, periodic optimisation (whatever that is) and matching issues.

All in all a good evening, and I'm planning to attend the one in June which is billed as a SQL Server 2008 UK Usergroup London Launch event. More details are available on the UK SQL Server Community website.

Update
I asked our Madgex DBA about his opinion on the 32 bit vs 64 bit question and his response was
"I think that the speaker was right to ask 'Why wouldn’t I?'. I think that the advantages for database applications far outweigh any additional cost. Considering that 64 bit is the future (or is that the present?), I think that rather than having to justify using a 64 bit system you would need to justify using a 32 bit system. Why would you want to use outdated technology? Obviously there is no point in upgrading to a 64 bit system just for the sake of it, but if your system has outgrown the capability of the current hardware and performance is suffering, or if the hardware has come to the end of it’s life and you were going to upgrade anyway, then I think that you would need a good reason not to upgrade to a 64 bit system.

The basic advantages are:

• Improved scalability through support of larger amounts of RAM and more processors.
• Improved performance through the ability to properly use all the available RAM (In a 32 bit system using AWE to increase the amount of memory, SQL only use the AWE memory for caching data pages. It cannot be used for other processes such as caching execution plans, sorting, hash tables, index creation etc.)

There is a good white paper that explains the issues."

Labels: event, london, SQLServer

// posted by Jane @ 10:02 AM   

Last week I attended another ILP workshop, this time about nUnit. This made me remember about the TSQLUnit testing framework, and the changes I had made to it whilst using it at my previous company and the fact that these changes were just sitting in a file somewhere and had never been shared.

The project which benefited from the most from TSQLUnit was one working with a large set of complicated calculations. Due to the nature of the data, this work was carried out in the database layer. These calculations were based on a well specified system, and so was a great candidate for thorough unit testing. Whilst building the calculation engine, I enhanced the base TSQLUnit installation to have some additional helper functions to save me coding the same thing over and over again.

I added a set of value checking helper procedures as follows
tsu_CheckValues_Decimal, tsu_CheckValues_Int, tsu_CheckValues_String, tsu_CheckValues_Date, tsu_CheckValues_Money all of which take 3 parameters - the first the expected data value, the second the actual data value, and the third an optional parameter expressing the message to be displayed to the developer if this assertion fails.

For example, EXEC tsu_CheckValues_Int 0,1,'Oops'
results in the following error being recorded 'Integer value mismatch Oops - Unexpected Result. Expected: 0. Actual: 1'

In additon I added some helper functions to assist in the generation of error messages as follows
tsu_GenerateErrorMessage_Date, tsu_GenerateErrorMessage_Int, tsu_GenerateErrorMessage_Real, tsu_GenerateErrorMessage_String all of which take 3 parameters, the first being the initial part of the message to display, the second the expected data value and the final the actual data value.

For example, SELECT dbo.tsu_GenerateErrorMessage_Int('Integer value mismatch',0,1) which results in the following text being generated 'Integer value mismatch - Unexpected Result. Expected: 0. Actual: 1'

The helper functions do not alter or change the basis under which TSQLUnit operates, and so they should install and work happily within existing TSQLUnit installations. Download the TSQLUnit Helper Functions.

I have also provided a script containing sample TSQLUnit tests - one coded to pass, one coded to fail, both making use of the tsu_CheckValues_Int.

In another post I will go into the other changes I made, and the recommendations for testing we came up with as a result of this exercise.

Labels: ilp, SQLServer2000, SQLServer2005, TSQLUnit

// posted by Jane @ 1:57 PM