Photos from Agile Austin March 6, 2012

Bob Wilson,the owner of Industrial Andons and Jidoka Consulting (www.industrialandons.com) presented a overview of the Lean process and conducted an interesting demonstration with Legos.




In the demonstration we had two teams with processing plants scattered throughout the world, or the room in our case. A runner had to move Lego pieces in batches between plants where the Legos were processed for 4, 7, or 10 seconds.









The game emphasized the lean principles of no overproduction and continuous flow. Bob showed a surprisingly simple solution at the end.

Afterwards I had a chance to talk with Bob and asked him what mistakes he sees most often with software companies. He said most software companies spend too much time upfront in requirements gathering and design, and not enough in simple prototypes and discussions with the users.

Converting all default values of GETDATE() to GETUTCDATE() in SqlServer

Since our business in getting more international, I needed to convert our database which has many default constraints of "GETDATE()" to "GETDATEUTC()". I wrote a little SQL script to locate all default constraints containing "GETDATE()" and replace them with "GETUTCDATE()". It also converts all the old date values to UTC time.

set nocount on
declare @tableName varchar(255)
declare @columnName varchar(255)
declare @constraintName varchar(255)
declare @newConstraintName varchar(255)
declare @myDropCmd varchar(512)
declare @myCreateCmd varchar(512)
declare @timeZoneCommand varchar(512)
declare @timeZoneOffset int
select @timeZoneOffset = Datediff(hh,GETDATE(),GETUTCDATE())

-- original query from MeanOldDBA at 
-- http://www.developmentnow.com/g/113_2004_9_0_0_433030/Search-for-defaults-in-table-DDL.htm
DECLARE mycursor CURSOR FOR 
   SELECT
   so1.name AS table_name,
   sc1.name AS column_name,
   so2.name AS default_name
   FROM
   sysobjects so1
   INNER JOIN syscolumns sc1 ON so1.id = sc1.id
   INNER JOIN sysobjects so2 ON sc1.cdefault = so2.id
   INNER JOIN syscomments sc2 ON so2.id = sc2.id
   WHERE
   sc2.text LIKE '%getdate()%'
OPEN mycursor
   FETCH NEXT FROM mycursor INTO @tableName, @columnName, @constraintName
   WHILE (@@fetch_status = 0)      -- while OK
     BEGIN
        print '-- Table: ' + @tableName + ', Constraint: ' + @constraintName+ ', Column: ' + @columnName
        SELECT @myDropCmd = '  ALTER TABLE ['+@tableName+'] DROP CONSTRAINT ['+@constraintName+']'
        PRINT @myDropCmd
        EXECUTE (@myDropCmd)
        SELECT @newConstraintName = 'DF_'+@tableName+'_dtime_utc'
        SELECT @myCreateCmd = '  ALTER TABLE ['+@tableName+'] ADD CONSTRAINT ['+@newConstraintName+'] DEFAULT (GETUTCDATE()) FOR ['+@columnName+']'
        PRINT @myCreateCmd 
        EXECUTE (@myCreateCmd)
        select @timeZoneCommand =  '  UPDATE ' + @tableName + ' SET ' + @columnName + ' = DATEADD(HOUR, '+convert(varchar(3),@timeZoneOffset)+', '+@columnName+')'
  PRINT @timeZoneCommand
  EXECUTE (@timeZoneCommand)

        FETCH NEXT FROM mycursor INTO @tableName, @columnName, @constraintName
     END
CLOSE mycursor
DEALLOCATE mycursor
GO