Monthly Archives: January 2009

T-SQL Foreign Key ON DELETE CASCADE and ON DELETE SET NULL – Creating an alternative INSTEAD OF DELETE TRIGGER that allows multiple cascading paths…

Posted on by
Reply

… to avoid “Error message 1785 occurs when you create a FOREIGN KEY constraint that may cause multiple cascade paths”

In most cases if you need to support linking of more than one ID from one table into another table, you would use an intermediary table, this allows any number of links to be established and details of each link can be supplemented with additional details.

However when maintaining and upgrading an existing database that has been around for many years, or even designing a new one, were there will only ever be a need to link in a specific number of IDs, it may be the case that the IDs are placed within the other table directly. 

Doing this prevents the ability for SQL to use the ON DELETE CASCADE and ON DELETE SET NULL from working.

Indeed even if there is only the one ID linked in, but there is more than one round table link you cannot have multiple ON DELETE CASCADE foreign keys established between the tables because it will also fail. Both these examples give the error “Error message 1785 occurs when you create a FOREIGN KEY constraint that may cause multiple cascade paths”.

I recently embarked on upgrading an existing database to include foreign keys relationships (previously controlled by software only), so that we could introduce full support for merge replication.

The database includes over 360 tables with around 860 relationships to be establish between tables, it is used by over 4500 users in a large number of organisations, with around 70 modules (or applications) accessing the data.

As the applications have been designed and written over the past 10 years by several developers there is no way to guarantee that all the relationships between tables are being enforced correctly… Leaving aside the creation of the FKs and the verification of existing data for another blog, I needed a way to maintain compatibility with all the applications currently using the database. The only way to do this while properly enforcing the foreign keys would be to delete or set to NULL the appropriate linked data on deletion of a row, rather than rely on the application to perform the action.

Having searched for quite a while I found that there was many examples of people recommending using an INSTEAD OF DELETE TRIGGER, to accomplish it, though I could not find any examples. After searching for some time the only examples I found contain hard coded references to linked tables. This is no way ideal and would be a nightmare to maintain with over 860 foreign key links between over 360 tables, which are added to and modified on a regular basis as the applications continue to grow.

Anyway to cut a long story short and get to the code I wrote a TRIGGER that would satisfy my needs, based on a few assumptions.

  1. If a Foreign Key column is nullable I am assuming it is not dependant on the Primary Key data and should be set to NULL rather than deleted.
  2. If the Foreign Key does not support NULLs then the data must be dependant on a valid link and will be deleted… obviously in this case I am assuming that all of the FKs that do not support NULLs are not to be set to a default value from the PK table.
  3. Within the TRIGGER I am will be checking if the FK constraint has a referential action, is disable, is trusted (enforced) and also isn’t a link back to the PK table itself.
  4. The Trigger is designed to fail if fired against a table that has a multi-column Primary Key.

It is worth noting when using a trigger it can be run on multiple rows and is not run for each row deleted, so must handle this

Now I am a bit short of time tonight so I am just going to just show the source of what I have done and assume you will be able to interpret it….

The trigger will cycle through all Foreign keys based on the tables name and the primary key column name. It then deletes or updates the dependant data as appropriate, as it does so (assuming the same trigger is on the dependant tables) if the dependant tables have an update or delete trigger on them it will in turn trigger that causing a cascade effect… As a result of this it could potentially result in a long loop so needs to be used with caution and the database’s foreign keys need to be established carefully.

You should not use this trigger if you are unsure, and I bear no responsibility for any data that is lost as a result of it’s usage! The trigger is based on T-SQL for MS SQL Server 2005, INSTEAD OF DELETE does not work in MS SQL 2000 and I have not tested it on MS SQL 2008.

The trigger looks something like the following; it is based on a table called [Table_1] with a Primary Key column that is either an integer or varchar called [ID], I will be adding it to all tables I’ll be writing a script that just runs through all the tables adding it: –

Code revised 31st Jan. 2009

 

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TRIG_PK_Table_1_Delete_FKs]’))

DROP TRIGGER [dbo].[TRIG_PK_Table_1_Delete_FKs]

GO

 

/**

CODED BY: Paul Bunting (http://www.paulbunting.com/)

Created: 23/01/2009

Modified: 31/01/2009

**/
CREATE TRIGGER TRIG_PK_Table_1_Delete_FKs ON dbo.Table_1

INSTEAD OF DELETE

AS

BEGIN

    SET NOCOUNT ON

      PRINT ‘***************************************************’

      PRINT ‘RUNNING – TRIG_PK_Table_1_Delete_FKs’
 
      — before doing anything lets check we
      — actually have something to delete!
      DECLARE @Counter_DELETED_ROWS INT
      SELECT @Counter_DELETED_ROWS = COUNT([EQUIPMENTID]) FROM DELETED

      IF (@Counter_DELETED_ROWS <= 0)
      BEGIN
          PRINT ”
          PRINT ‘0 records deleted’
          PRINT ”
      END
      ELSE
      BEGIN   

    — 1) we only wish to deal with tables that have one Primary Key…

    —    so lets check that this table has only one Primary Key

    DECLARE @Counter_PKs INT

    SELECT @Counter_PKs = COUNT(Col.Column_Name) FROM
            INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col

            INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab

            ON  Col.Constraint_Name = Tab.Constraint_Name AND
            Col.Table_Name = Tab.Table_Name

      WHERE Tab.Constraint_Type = ‘PRIMARY KEY’ AND
            Col.Table_Name = ‘Table_1’

     

      — 2) Verify only 1 found

      PRINT ‘  Found ‘ + CONVERT(VARCHAR(255), @Counter_PKs) +
            ‘ primary keys in table’

      IF (NOT (@Counter_PKs = 1))

      BEGIN

            — Lets throw an error if this trigger has been established 

            — against a table with more than one Primary Key, could

            — alter this later to handle them but no requirement to

            — do so at the moment

            DECLARE @tn varchar(4000)

            SELECT @tn = object_name(parent_obj)
            FROM sysobjects WHERE id = @@procid

            SET @tn = ‘Casscade deletes not allowed for this ‘ +
                   ‘table due to multiple primary key columns: ‘ + @tn

            PRINT @tn

            IF EXISTS(SELECT TOP(1) * from deleted) 

                  RAISERROR (@tn, 16, 1)   

      END

      ELSE

      BEGIN

            — 3) Lets get all the Foreign Keys assosiated with this table

            DECLARE @ForeignKeys TABLE (TableTempID bigint
               IDENTITY(1,1) PRIMARY KEY CLUSTERED,

               [Name] varchar(2000),[OBJECT_ID] int, [is_disabled] bit,
               [is_not_for_replication] bit,
               [delete_referential_action] int,
               [update_referential_action] int,
               [Fk_table_name] varchar(2000),
               [Fk_table_schema] varchar(2000),
               [Pk_table_name] varchar(2000),
               [Pk_table_schema] varchar(2000),
               [Fk_col_name] varchar(2000),
               [Pk_col_name] varchar(2000),
               [constraint_column_id] int,
               [is_not_trusted] bit)

           

            INSERT INTO @ForeignKeys
               ( [Name], [OBJECT_ID], [is_disabled],
                 [is_not_for_replication], [delete_referential_action],

                 [update_referential_action], [Fk_table_name],
                 [Fk_table_schema], [Pk_table_name], [Pk_table_schema],

                 [Fk_col_name], [Pk_col_name], [constraint_column_id],
                 [is_not_trusted] )

               ( select Fk.name, Fk.object_id, Fk.is_disabled, 
                  Fk.is_not_for_replication, Fk.delete_referential_action,

                  Fk.update_referential_action,
                  object_name(Fk.parent_object_id) as Fk_table_name,

                  schema_name(Fk.schema_id) as Fk_table_schema,
                  TbR.name as Pk_table_name,
                  schema_name(TbR.schema_id) Pk_table_schema,

                  col_name(Fk.parent_object_id,
                  Fk_Cl.parent_column_id) as Fk_col_name,

                  col_name(Fk.referenced_object_id,
                  Fk_Cl.referenced_column_id) as Pk_col_name,
                  Fk_Cl.constraint_column_id,

                  Fk.is_not_trusted

                 from sys.foreign_keys Fk
                  left outer join sys.tables TbR

                 on TbR.object_id = Fk.referenced_object_id
                  inner join sys.foreign_key_columns Fk_Cl

                 on Fk_Cl.constraint_object_id = Fk.object_id

                 where

                  ( ([TbR].[name] = ‘Table_1’) AND
                    (col_name(Fk.referenced_object_id, 
                        Fk_Cl.referenced_column_id) = ‘ID’) AND
                    ([Fk].[delete_referential_action] = 0) AND

                    ([Fk].[is_disabled] = 0) AND
                    ([Fk].[is_not_trusted] = 0) ) )

 

            — 4) Lets see how many Foreign Keys we are dealing with

            DECLARE @Counter bigint

            DECLARE @MaxTableCount BIGINT

           

            SELECT @MaxTableCount = max(TableTempID) from @ForeignKeys

            SET @Counter = 1

            PRINT ‘  Found ‘ + CONVERT(VARCHAR(255), @MaxTableCount) +
                 ‘ foreign keys links for this table’

 

           

            DECLARE @Fk_table_name VARCHAR(2000)

            DECLARE @Pk_table_name VARCHAR(2000)

            DECLARE @Fk_col_name VARCHAR(2000)

            DECLARE @Pk_col_name VARCHAR(2000)

            DECLARE @is_not_trusted BIT

            DECLARE @is_disabled BIT

            DECLARE @delete_referential_action INT

            DECLARE @Fk_col_IsNullable BIT

           

            DECLARE @sqlQuery nVarchar(4000)

           

            — 5) Lets cycle through all the Foreign Keys

            WHILE (@Counter <= @MaxTableCount)

            BEGIN

                 

                 SELECT @Fk_table_name = ltrim(rtrim(Fk_table_name))
                  FROM @ForeignKeys WHERE TableTempID = @Counter

                 SELECT @Pk_table_name = ltrim(rtrim(Pk_table_name))
                  FROM @ForeignKeys WHERE TableTempID = @Counter

                 SELECT @Fk_col_name = ltrim(rtrim(Fk_col_name))
                  FROM @ForeignKeys WHERE TableTempID = @Counter

                 SELECT @Pk_col_name = ltrim(rtrim(Pk_col_name))
                  FROM @ForeignKeys WHERE TableTempID = @Counter

                 SELECT @is_not_trusted = ltrim(rtrim(is_not_trusted))
                  FROM @ForeignKeys WHERE TableTempID = @Counter

                 SELECT @is_disabled = ltrim(rtrim(is_disabled))
                  FROM @ForeignKeys WHERE TableTempID = @Counter

                 SELECT @delete_referential_action =
                   ltrim(rtrim(delete_referential_action))
                  FROM @ForeignKeys WHERE TableTempID = @Counter

                 

                 SELECT @Fk_col_IsNullable = c.IsNullable
                  FROM syscolumns c WHERE c.id =
                   OBJECT_ID(@Fk_table_name) AND
                   c.NAME = @Fk_col_name

                 

                  PRINT ‘—————————————‘

                  PRINT ‘  Processing key ‘ +
                    CONVERT(VARCHAR(255), @Counter) + ‘ of ‘ +
                    CONVERT(VARCHAR(255), @MaxTableCount)

                  PRINT ‘  @Fk_table_name "’ + 
                    CONVERT(VARCHAR(255), @Fk_table_name) +
                    ‘" ~ @Fk_col_name – "’ +
                    CONVERT(VARCHAR(255), @Fk_col_name) + ‘"’

                  PRINT ”

 

                  — 6) Lets verify that there is no CASCADE or
                  —    UPDATE already on the Key link and that
                  —    it is not a system table… also

                  —    need to verify that the Foreign Key Table
                  —    is not the same as the current table and
                  —    that Key should be enforced.

                  IF (

                              (@delete_referential_action = 0) AND

                              (@is_not_trusted = 0) AND

                              (@is_disabled = 0) AND

                              (@Fk_table_name <> ‘dtproperties’) AND

                              (@Fk_table_name <> ‘sysdiagrams’) AND

                              (NOT (@Fk_table_name = @Pk_table_name))

                        )

                  BEGIN

                 

                        — 7) Because we need to execute deletes with
                        —    a "EXECUTE sp_executesql…" statement the

                        —    DELETED table is not available so we must
                        —    cycle through it in case it is a batch
                        —    delete.

                        —

                        —    We convert the values to Varchar here to
                        —   account for PKs that are text and integer,
                        —   plus we need it as text later

                        —

                        —    This is because we do not know what all
                        —    the FK column names will be so we need

                        —    to build the query

                        DECLARE @DELETED_ROWS TABLE
                          (TableTempID bigint IDENTITY(1,1)
                            PRIMARY KEY CLUSTERED,

                           [ValueINNER] varchar(4000))

                       

                        INSERT INTO @DELETED_ROWS

                          ( [ValueINNER] )

                          ( SELECT CONVERT(VARCHAR(4000), [ID])
                            from DELETED )

                        DECLARE @CounterINNER bigint

                        DECLARE @MaxTableCountINNER BIGINT

                        DECLARE @ValueINNER VARCHAR(4000)

           

                        SELECT @MaxTableCountINNER = max(TableTempID)
                          from @DELETED_ROWS

                        SET @CounterINNER = 1

           

                        — 8) Lets cycle through all the ROWS to be deleted

                        WHILE (@CounterINNER <= @MaxTableCountINNER)

                        BEGIN

                              SELECT @ValueINNER =
                                 ltrim(rtrim([ValueINNER])) 
                              FROM @DELETED_ROWS WHERE
                                TableTempID = @CounterINNER

                              — 9) Lets see if the Foreign Key
                              —    column allows nulls… we will
                              —    use this to guess if the linked
                              —    table is dependant on the
                              —    link (i.e. a link between

                              —    an email and it’s attachments)
                              —    or if it is only a lookup link
                              —    (i.e. a link between a staff 
                              —    member and the email they edited…
                              —    may want to delete the staff
                              —    member but don’t want their
                              —    emails deleted)

                              PRINT ‘====’

                              IF (@Fk_col_IsNullable = 1)

                              BEGIN

                                    — 10a) Set LINKED values to NULL
                                    —      (Using the like statement here
                                    —      should account for INT and
                                    —      VARCHAR PKs)

                                    PRINT ‘  UPDATE [‘ + @Fk_table_name
                                     + ‘] SET [‘ + @Fk_col_name 
                                     + ‘] = NULL WHERE [‘ + @Fk_col_name
                                     + ‘] LIKE ”’ + @ValueINNER + ””

                                    SET @sqlQuery = ‘

SET NOCOUNT OFF

UPDATE [‘ + @Fk_table_name + ‘] SET [‘ + @Fk_col_name + ‘] = NULL WHERE [‘ + @Fk_col_name + ‘] LIKE ”’ + @ValueINNER + ”’

SET NOCOUNT ON’

                                    –PRINT @sqlQuery

                                    EXECUTE sp_executesql @sqlQuery

                              END

                              ELSE

                              BEGIN

                                    — 10b) Delete DEPENDANT values
                                    —      (Using the like statement
                                    —      here should account for INT
                                    —      and VARCHAR PKs)

                                    PRINT ‘  DELETE FROM [‘
                                     + @Fk_table_name + ‘] WHERE [‘
                                     + @Fk_col_name + ‘] LIKE ”’
                                     + @ValueINNER + ””

                                    SET @sqlQuery = ‘

SET NOCOUNT OFF

DELETE FROM [‘ + @Fk_table_name + ‘] WHERE [‘ + @Fk_col_name + ‘] LIKE ”’ + @ValueINNER + ”’

SET NOCOUNT ON’

                                    –PRINT @sqlQuery

                                    EXECUTE sp_executesql @sqlQuery

                              END

                              PRINT ‘====’

                              SET @CounterINNER = @CounterINNER + 1

                        END 

                  END

 

                  SET @Counter = @Counter + 1

            END

            PRINT ‘—————————————‘

           

            — 11) Proceed with the delete…

            SET NOCOUNT OFF

            DELETE FROM [Table_1]

                  WHERE [ID] in (SELECT [ID] FROM DELETED)

            SET NOCOUNT ON

            –PRINT ‘DELETE FROM [Table_1]

            —    WHERE [ID] in (SELECT [ID] FROM DELETED)’

      END

 

      END 

     

      PRINT ‘COMPLETED – TRIG_PK_Table_1_Delete_FKs’

      PRINT ‘***************************************************’

      SET NOCOUNT OFF

END

GO

1972 MGB GT – Part 3 – Fresh Air Vent, Cabin Heater & Pipes, Water Jets, a Wiring problem found and Engine Test Run.

Posted on by
Reply

Before starting work proper, on my project car, I wanted to test run the car for a bit to check the engine out after replacing most of the ignition system.

Being winter, in Scotland, I wanted to check out a few things before I did this…

First the water jets did not work; on the heavily salted roads and driving into Glasgow, I would want them working. In addition the fresh air vent that is hidden behind the centre console was jammed open and there was no heat from the cabin heater to demist the windscreen.

During the journey, bring the car back from Heathrow, it was obvious from the smell that a connection in the manual water jets had came loose. No water would exit the jets but there was a distinct smell of screen wash from the centre console indicating that the manual pump was working, just not how it should.

I figured this would be a quick fix so one Wednesday night I removed the central air vents, which came out easy (and notably where not connected to their air pipes) and found that it was a simple disconnected connection.

From here I could also see that not only were the central vents disconnected, but the main window demist vents were also disconnected, on both ends!

Looking into the air pipes I also found the issue the jammed open fresh air vent (which can be seen in the background of the above picture, closed)… it was jammed open due to one of the windscreen demist pipes, having falling behind the leaver preventing it from closing.

While I fixed the air pipe connections I noticed a loose wire sparking off the stereo casing. Judging for the condition of the female bullet connectors condition, it looked like it was fairly old and must have been shorting there for some time!

I removed the radio that had been fitted somepoint fairly recently… it has MP3 support, a USB connector and SD Card slot. I taped up the loose connection with some insulating tape and will have to investigate further where exactly it should be connected to when I dismantle that area during the rebuild process.

While re-inserting the radio, due to the mess of the wiring behind it I inadvertently disconnected clock and cigarette lighter.

Once reconnected I found I was getting heat to the demist vents, and now the fan switch did not have the water jets squirting onto it, the fan worked (well most of the time, switch seems faulty)… the heater would only give heat to the windscreen and would not fully switch to the centre console vents that continued to release cold air through the driver side, though this would be more than fine for a few test drives.  

So I had water jets and de-mist ability… this would allow me to take the car for a few test runs…

The next morning I used the car for the school run taking Caelan to nursery.

This went fine, with the car starting each time first turnover, so I decided it should be fine to take into a work meeting in Glasgow on Friday. This would be a round trip journey of close to 100 miles and provides the opportunity to the use the motorway there and to use some B roads for 3/4 of the return journey.

The journey into and out of Glasgow went without a hitch! So all going well so far.

1972 MGB GT – Part 2 – Lights, Ignition System and Misfire

Posted on by
Reply

As mentioned in my previous post, on the journey back from Heathrow, the car had a slight misfire which was more noticeable the next day.

The MGB GT has a twin carburetor, where one carburetor feeds the first two cylinders and the other the second two. As the air filters looked quite old and the misfire sounded like it was on a couple of cylinders I thought I would check them first.

As it turned out the rubber seals around the inside of the K&N filters had completely split, partially blocking the air filters.

Before going down to pick up the car I had ordered a service kit from the MG Owners Club, as the previous owner had told me it would be due one soon. The service kit came with two standard air filters. Though they are designed to go in the standard air filter housing I briefly replaced the K&N filters with the new standard filters to see if it would make any difference. I also gave the carburetors a quick visual check and oiled the accelerator connections.

Replacing the filters did not correct the problem so I proceeded to check the ignition system.

As it turned out there was several problems, the points were corroded, the distributor cap had worn points and corroded lead connector points, the ignition leads were corroded and damaged, and the spark plugs looked like they could do with being replaced.


As the service kit I bought came with a new condenser, points and spark plugs, I fitted these first and ordered new ignition leads, distributor cap, rotor arm, ignition coil and air filters.

In addition to the misfire, when bringing  the car back up to Scotland, the window wipers had stopped working around half way back. Checking the handbook I quickly found which fuse had blown. The same fuse is used for the heater blower, which appears to be the problem.

As there was no spare fuses in the fuse box I had to repeatedly stop on the way back to clear the screen. As the fuses used in the MGB’s are no-longer available at any service stations I also order some some from the MG Owners Club (http://www.mgocspares.co.uk/).

While picking up the car the previous owner also noticed that one of the side lights had stopped working during their journey to the Airport. While checking this I also gave the car a quick check over to see if anything else was in need of immediate replacement prior to starting on the bodywork.

While checking the lights I found that the housing for the driver side headlamp was severely corroded, and some of the wires (namely the side light ones), had came away from their terminal connectors.


I added the headlamp housing and rubber seal to the list of parts I needed just now.

The delivery of the spares was very prompt, allowing me to replace the parts over the next few weekends, taking a few weekends off to visit relatives over Christmas and getting the car running again last weekend.


 

Having replaced all but the ignition coil and distributor itself, the engine appears to be running smoothly now, though I have only ran it for 5 to 10 miles so far, I intend run it a bit more before starting to strip out more major parts for inspection, and possible replacement.

Some of the things that are on my list to tackle next include: –

  • One of the jack points needs re-welded (will also get a mechanic to check and see if any other welding is required)
  • Some areas need to be retreated with under-seal
  • Driver side sill, to the rear, needs repaired
  • The major job… the various areas of bodywork that need attention and a re-spray
  • General engine bay tidy up (some water trap areas need touching up and repairing)
  • Source problem with de-mist pipes and manual water jets
  • Locate where cold air is entering the foot-well (cool air vent was shut)
  • Look into the heater and all associated piping (air and water), see what needs replaced and what is OK
  • Fitting electric water jets (still fitted with the old manual jets)
  • Window seals (front quarter windows in particular) need attention
  • Oil change and replace oil filter
  • Check header gasket and possibly replace
  • Re-oil SU carburetors
  • Replace timing belt (as I have no idea when it was last done) 

              … no doubt more will be added to the list as the project unfolds …

For it’s age and the money I parted with, I am quite pleased with the condition of the car so far… particularly given the previous owner had told me that the owner, prior to them, had left it sitting outdoors unused for many years (to the point it was moss covered), before they rescued it. They gave it a little clean up and continued using it for a couple of years as a general run around until they had to sell to it to make way for a VW Camper they were purchasing.

1972 MGB GT – Part 1 – A project to keep my sanity!

Posted on by
Reply

I had originally intended on keeping a separate blog or web-site to log my progress on this project, but have decided to just expand this blog, rather than manage yet another site.

In late October 2008 my dad informed me that he would be giving me, my brothers and my sister a little of his share of his inheritance. My dad, his brothers and sisters had split some inheritance that Granny had left after she had passed. She passed away earlier in 2008 after suffering for several years from Cancer. True to her nature and not wanting people to fuss, she had kept her suffering a secret almost to the end, just getting on with life and only telling the family when she started to get too ill.

Though it was not a lot of money I did not want to waste it on bills or similar… So I decided it was about time to renew an old hobby, classic cars… and possibly introduce my kids to some dirt and oil.

For many years now my main hobby has been has been my job and vice versa (software development and computer programming)… hobby since 1990 job since 1997, with any spare time between the two being taken up by the kids, parenthood and the occasional computer game or xbox distraction.

In order to keep the old brain fresh for coding I figure it is time to get my fingernails dirty again.

Prior to spending most of my time in front of a computer screen I used to spend a reasonable amount under a bonnet or re-wiring my old bangers. Admittedly most of the time it was out of necessity, rather than desire… spending most of my money on books, computer hardware, OU courses and the kids meant that all of my earlier cars were bangers run on a shoe string. Saying that there was always a lot of satisfaction in doing the repairs and maintenance myself or with the help of my dad…

First car I bought was a 1973 Opel Ascona, purchased for the pricey sum of £200, this was before owning a classic became more fashionable and the prices went up quite a bit.

Though it required a fair upkeep during it’s life, it went on for a surprising number of years passing between myself and my father a couple of times… There was then the 1988 Ford Sierra Estate, actually this is the car I learned to drive in before getting the Opel when I passed my test, I swapped the Opel with my Dad for this and took on the repayments for a while. Then there was a 1973 Volkswagen Beetle (that needed completely rewired, having melted all the wiring and somehow just got a MOT… always checked the electrics on a car I bought after this one and never trusted a MOT), then a Fiat 126 (my dad bought this but I used it for a while) and a 1982 Ford Capri.

 

I then moved onto more modern cars working my way through a Rover Mini Metro, a Ford Focus, a Citroen Picasso, a Volvo 940 Turbo, a Ford Mondeo and currently a Chrysler Voyager.

And that’s just the cars I have been through (my dad’s list is much longer and more impressive, including a bubble car, Ford Cortina, various customs, a Ford Anglia and many more).

So… I had a few pennies from my Granny and decided to get an old classic in need of some repairs as a way to entertain myself on weekends in my garage. I had always wanted to repair and restore an old classic rather than just keep it running out of necessity.

Having researched various types of classic cars I decided that I would go for an MGB GT. There was several reasons for this…

Firstly I had always wanted one when I was younger but they had always been out-with my budget and once I could have afforded one, well one word… "kids", so not really practical. Though the money I was getting would not normally be enough for a MGB GT; with the economic climate looking bleaker and bleaker it seemed it would be a buyers market for second-hand cars, bringing prices much closer to my budget.

Other factors came into play as well, such as the great availability of reasonably priced replacement parts for the MGB GT, through the various owners clubs… it was after all one of the most popular British small sports cars built. There is also an abundance of books and other materials on them.

Having settled on the idea and to my surprise getting instant approval from my wife, I set about trying to find one. It soon became obvious that the best place to get a good deal would be on eBay, buying a car through eBay is something I never thought I would do, but prices elsewhere still seemed high.

I was ideally looking for something 1972 or earlier (something that would be road tax exempt here in the UK, so I don’t have to worry about the yearly cost as much). It had to be something needing some work, but was currently running and have a MOT so that I could get it home.

Having browsed and monitored auctions for several weeks, it was looking like I would only be able to get something post 1973 for the money I had available. So I bid on a couple of cars from 1973. Though I was the highest bidder on both they never reached their reserve and the sellers were expecting more than double what the auctions reached and more than I was willing to pay.

I was in no rush and wasn’t about to be convinced to part with more money than I had budgeted.

I continued monitoring and caught sight of a red 1972 MGB GT, with no reserve that was in need of some restoration and maintenance… over the course of the week I emailed the seller numerous detailed questions, he was extremely helpful answering all my questions and sending me additional photos of various problem areas that would need repaired.

Come the end of the auction I luckily won it by one pence. The wife got a giggle that I won the auction by only one pence!

I made arrangements with the owner to pick it up from Heathrow airport and booked my flights down for the following weekend. Flying down on the first flight on 29th Nov, I spent the day (coldest of the year, in car with no working heater) driving back up.

The car did have a slight misfire on the journey back, but I just kept the revs low and took my time and it got me back home with only a few minor hiccups.

The next morning the misfire was much more noticeable and it was apparent that would be my first task, but I will leave that for my next post.

Our family pets (the Zoo!).

Posted on by
Reply

To go along with our 3 boys we have 2 dogs… Robbie a cross breed, and Sophie a Jack Russell Terrier, both of whom we adopted.

Robbie we adopted Nearly 8 years ago from the NCDL (now Dogs Trust), he was around 5 years old at the time so is getting on a bit now but despite recently going deaf he still has that puppy glint in his eyes. 
 

Sophie (who was previously called Honey), we adopted in spring 2008… my Mother had taken her in from someone living in Auchinleck when she moved to Melrose having lived abroad for a few years, but found her a bit much with work and other commitments, so she came to live with us and was renamed to Sophie.
 

Sophie was around 2 years old when we adopted her and her arrival brought a new lease of life to Robbie, who seems to have enjoyed teaching her how to play fight, eat carrots and many other things.
 

 

 

In addition to the two dogs, we have a hamster, Gilbert, and the wife got herself 4 baby Giant African snails a couple of months ago.

 

 

For Christmas this year, our family expanded again, with the addition of a further two pets…

To our surprise our youngest, Caelan, expressed a strong desire to have a snake several months back. Despite being only four and normally jumping from one thing to the next in a blink of an eye, he kept constant with this one (much to mummy’s delight since she had been dying for an excuse to get one).

So as a nice Christmas surprise, a 3 month old Miami Corn Snake would be his "big" present this year. This was much to his delight since, since he seemed to have expecting a toy one, given they are not common pets. He instantly loved it, was very eager to hold it (even attempting to give it a kiss) and he named it "Boy-snake"!

 

 

The second pet to join our family this Christmas, yet to be named, was a Red Knee Tarantula. 

Kieren, the middle boy, had mentioned sometime ago that he would like a Tarantula. Not to discourage something that would make most kids squirm we told him that he couldn’t just declare that he wanted one… They need looking after, can live for a long time and he doesn’t know what is required or what they need.

To his credit he went off on his own and researched it with impressive vigour. Researching which types are best for pets and indeed which are best as a beginner pet. He researched what they eat, how long they live, how to look after one and showed great enthusiasm giving us lots of information about them. This gave the wife the green light to go off and get a baby spiderling for his Christmas.

Yesterday the Spider shed it’s first skin and ‘Boy-snake’ shed it’s first skin (with us) on boxing day.