Tuesday 09 November 2010

Google South Africa's Telephone Number

Are you tired of searching for Google South Africa's contact details? You are pretty sure that their office is on the corner of Main Road and Sloane Street, but more than that, you just don't know. Well, @StewartMoss managed to dig up their telephone number - after first having to call Google UK and Google Kenya! So, directly from the Kenyan horse's mouth, here it is:

Google South Africa's telephone number:
+27-11-575-6000
Apparently, according to the receptionist, they "don't accept calls."

So, there you go. You now have a telephone number for Google South Africa. I hope that you have better luck being put through to talk to someone than Stewart did.

Thank you for this information and for calling Google UK, Kenya and South Africa Stewart, you rock!

Monday 16 August 2010

HOWTO: Stay Motivated


How to Stay Motivated

It is a wonderful and surprising fact that programmers are highly motivated by the desire to create artifacts that are beautiful, useful, or nifty. This desire is not unique to programmers nor universal but it is so strong and common among programmers that it separates them from others in other roles

This has practical and important consequences. If programmers are asked to do something that is not beautiful, useful, or nifty, they will have low morale. There's a lot of money to be made doing ugly, stupid, and boring stuff; but in the end, fun will make the most money for the company

Obviously, there are entire industries organized around motivational techniques some of which apply here. The things that are specific to programming that I can identify are:
  • Use the best language for the job.
  • Look for opportunities to apply new techniques, languages, and technologies.
  • Try to either learn or teach something, however small, in each project.
Finally, if possible, measure the impact of your work in terms of something that will be personally motivating. For example, when fixing bugs, counting the number of bugs that I have fixed is not at all motivational to me, because it is independent of the number that may still exist, and is also affects the total value I'm adding to my company's customers in only the smallest possible way. Relating each bug to a happy customer, however, is personally motivating to me.

This has proven useful for me and I hope that it is useful for you too. Please check out the full page as indicated above.

Friday 09 July 2010

A Way to Handle Raised Errors in Both Dynamic and Static SQL Code

If you've needed a reliable way to handle errors that might be raised in both dynamic and static SQL code, here is what I've been doing:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Erland Sommarskog - http://www.sommarskog.se/error_handling_2005.html
-- Create date: 2009/11/29
-- Description: **Call this sp from within your
--              CATCH block** This
--              reraises/throws the most recent
--              error. Please see
--              http://www.sommarskog.se/error_handling_2005.html
--              for more details.
--
-- Modifications:
-- Author:      A. Jackson
-- Create date: 2010/03/11
-- Description: Renamed the sp from
--              "error_handler_sp" to
--              "dba_Error_Handler" to conform
--              to naming conventions.
--            : Documented this sp.
--            : Enhanced SQL readability.
-- Author:      A. Jackson
-- Create date: 2010/05/20
-- Description: Added the @isDynamicSQL BIT = 1
--              parameter. This now includes the
--              text '' only if
--              @isDynamicSQL is true.
-- =============================================
ALTER PROCEDURE [dbo].[dba_Error_Handler]
    @isDynamicSQL BIT = 1
AS
BEGIN
    DECLARE
        @errmsg NVARCHAR(2048),
        @severity TINYINT,
        @state TINYINT,
        @errno INT,
        @proc SYSNAME,
        @lineno INT,
        @typeOfSQL VARCHAR(15)
           
    SELECT
        @errmsg = ERROR_MESSAGE(),
        @severity = ERROR_SEVERITY(),
        @state = ERROR_STATE(),
        @errno = ERROR_NUMBER(),
        @proc = ERROR_PROCEDURE(),
        @proc = ERROR_PROCEDURE(),
        @lineno = ERROR_LINE()
       
    IF @errmsg NOT LIKE '***%'
    BEGIN
        SELECT @typeOfSQL = CASE WHEN @isDynamicSQL = 1 THEN ', '
                                 ELSE ', '
                            END
        SELECT @errmsg = '*** ' + COALESCE(QUOTENAME(@proc), @typeOfSQL) +
                         LTRIM(STR(@lineno)) + '. ErrNo ' +
                         LTRIM(STR(@errno)) + ': ' + @errmsg
        RAISERROR (@errmsg, @severity, @state)
    END
    ELSE
    BEGIN
        RAISERROR (@errmsg, @severity, @state)
    END
END


Thank you to Erland Sommarskog for allowing me to post this.

Wednesday 12 May 2010

HOWTO: Quickly and Efficiently Get An IEnumerable of Directories and Files

Have you ever wanted to get an IEnumerable of files and directories and had to either make do with or convert from an Array because the System.IO namespace didn't provide methods of doing this? In .NET 4 you can do this quickly and efficiently. Check out the MSDN documentation here.

Wednesday 21 April 2010

HOWTO: Find out the Sizes of All the User Tables in a Database in SQL

Do you need to know the sizes for all the user tables in your database? I found this script by Mitchel Sellers, an SQL Server MVP,  to be very handy. I've modified it slightly, but you can get the original on his blog.

Thank you Mitchel for allowing me to post this here.

Please excuse the poor formatting, I am working on finding and implementing a decent code formatted for both SQL and C#.


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:            Mitchel Sellers - mitchel.sellers@gmail.com
-- Create date: 2007/06/27
-- Description: Obtains spaced used data for
--                            ALL user tables in the database.
--                            This does not update the space
--                            usage information - implicity
--                            running DBCC UPDATEUSAGE on the
--                            database - as recalculation has
--                            the potential to be VERY costly
--                            if it is run on ALL tables at
--                            once inside a database.
--
-- Modifications:
-- Author:            A. Jackson
-- Date:                2010/03/10
-- Description: Renamed the stored procedure
--                            from
--                            GetAllTableSizes to
--                            dba_GetAllTableSizes to fit my
--                            DBA sp naming convention.
--                        : Changed the select to order the
--                            result by TableName with the
--                            option to order the results by
--                            descending ReservedSizeKB.
--                            In order to do this I had to
--                            strip out the " KB" out of the
--                            size strings and alter the
--                            ReservedSizeKB column definition
--                            after stripping out the " KB".
--                        : Neatened up the SQL.
--                        : Corrected a spelling error in
--                            the comments:
--                            ("reults" -> "results")
-- Author:            A. Jackson
-- Date:                2010/03/24
-- Description: Changed the type of
--                            @OrderByReservedSize from
--                            VARCHAR(5) to BIT.
-- =============================================

CREATE PROCEDURE [dbo].[dba_GetAllTableSizes]
        @OrderByReservedSize BIT = 0
AS  
BEGIN
        --For storing values in the cursor
        DECLARE @TableName VARCHAR(100)
        --Cursor to get the name of all user tables from the sysobjects listing
        DECLARE tableCursor CURSOR FAST_FORWARD READ_ONLY FOR
                SELECT
                        [name]
                FROM
                        dbo.sysobjects
                WHERE
                        OBJECTPROPERTY(id, N'IsUserTable') = 1

        --A procedure level temp table to store the results
        CREATE TABLE #TempTable
                (
                 TableName VARCHAR(100),
                 NumberOfRows VARCHAR(100),
                 ReservedSizeKB VARCHAR(50),
                 DataSizeKB VARCHAR(50),
                 IndexSizeKB VARCHAR(50),
                 UnusedSizeKB VARCHAR(50)
                )

        --Open the cursor
        OPEN tableCursor

        --Get the first table name from the cursor
        FETCH NEXT FROM tableCursor INTO @TableName

        --Loop until the cursor was not able to fetch
        WHILE (@@Fetch_Status >= 0)
        BEGIN
                --Dump the results of the sp_spaceused query to the temp table
                INSERT #TempTable EXEC sp_spaceused @TableName

                --Get the next table name
                FETCH NEXT FROM tableCursor INTO @TableName
        END

        --Get rid of the cursor
        CLOSE tableCursor
        DEALLOCATE tableCursor

        --Strip out the " KB" out of the size strings so that they can be ordered.
        UPDATE
                [#TempTable]
        SET  
                [#TempTable].[ReservedSizeKB] = LEFT([#TempTable].[ReservedSizeKB], LEN([#TempTable].[ReservedSizeKB]) - 3),
                [#TempTable].[DataSizeKB] = LEFT([#TempTable].[DataSizeKB], LEN([#TempTable].[DataSizeKB]) - 3),
                [#TempTable].[IndexSizeKB] = LEFT([#TempTable].[IndexSizeKB], LEN([#TempTable].[IndexSizeKB]) - 3),
                [#TempTable].[UnusedSizeKB] = LEFT([#TempTable].[UnusedSizeKB], LEN([#TempTable].[UnusedSizeKB]) - 3)
        --Alter the temp table so that the results can be ordered.
        ALTER TABLE [#TempTable] ALTER COLUMN [ReservedSizeKB] BIGINT
        
        --Select all records so we can use the results.
        --Optionally ORDER BY ReservedSizeKB
        IF (@OrderByReservedSize = 1)
        BEGIN
                SELECT
                        [#TempTable].[TableName],
                        [#TempTable].[NumberOfRows],
                        [#TempTable].[ReservedSizeKB],
                        [#TempTable].[DataSizeKB],
                        [#TempTable].[IndexSizeKB],
                        [#TempTable].[UnusedSizeKB]
                FROM
                        #TempTable
                ORDER BY
                        [#TempTable].[ReservedSizeKB] DESC
        END
        ELSE
        BEGIN
                SELECT
                        [#TempTable].[TableName],
                        [#TempTable].[NumberOfRows],
                        [#TempTable].[ReservedSizeKB],
                        [#TempTable].[DataSizeKB],
                        [#TempTable].[IndexSizeKB],
                        [#TempTable].[UnusedSizeKB]
                FROM
                        #TempTable
                ORDER BY
                        [#TempTable].[TableName]
        END
        
        --Final cleanup!
        DROP TABLE #TempTable
END
GO

HOWTO: Generate a Random String of Characters in SQL

Here's a stored procedure to generate a string of random characters. This is useful when, for example, one needs to generate a random password. Please excuse the poor formatting, I am working on finding and implementing a decent code formatted for both SQL and C#.

Thanks to Raymond Lewallen over at CodeBetter.com


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:            Raymond Lewallen
--                    http://codebetter.com/blogs/raymond.lewallen/archive/2005/05/17/63281.aspx
-- Create date: 2005/05/16
-- Description: Generate a random string of
--                            given length.
--                        : Everything is self-explanatory.
--                        : Right now max length is set to
--                            100. So anything between 1 and
--                            100 will work for a length.
--                        : If you specify a
--                            @charactersToUse, the bit flags
--                            get ignored.
--                        : All spaces are stripped from the
--                            @charactersToUse.
--                        : Characters can repeat. Will be
--                            handled in a future version.
--
-- Modifications:
-- Author:            A. Jackson
-- Create date: 2010/04/21
-- Description: Renamed the sp to fit into the
--                            naming convention of the
--                            database.
--                        : Formatted the header.
--                        : Reordered the parameters so that
--                            the @toReturn OUT parameter is
--                            first.
--                        : Added @useSpecialCharacters BIT.
--                        : Formatted the code.
--                        : Added default parameter values.
-- =============================================
CREATE PROCEDURE [dbo].[spGenerateRandomString]
        (
            @toReturn VARCHAR(100) OUT,
            @useSpecialCharacters BIT = 1,
            @useNumbers BIT = 1,
            @useLowerCase BIT = 1,
            @useUpperCase BIT = 1,
            @charactersToUse VARCHAR(100) = '',
            @stringLength INT = 100
        )
AS
BEGIN
        IF @stringLength <= 0  
        BEGIN
                RAISERROR ( 'Cannot generate a random string of zero length.', 16, 1 )
        END

        DECLARE @characters VARCHAR(110)
        DECLARE @count INT

        SET @characters = ''

        IF @useSpecialCharacters = 1
        BEGIN
        SET @count = 33
        WHILE @count <= 38
                BEGIN
                        SET @characters = @characters +
                                CAST(CHAR(@count) AS CHAR(1))
                        SET @count = @count + 1
                END
                
        SET @count = 40
        WHILE @count <= 47
                BEGIN
                        SET @characters = @characters +
                                CAST(CHAR(@count) AS CHAR(1))
                        SET @count = @count + 1
                END
                
        SET @count = 58
        WHILE @count <= 64
                BEGIN
                        SET @characters = @characters +
                                CAST(CHAR(@count) AS CHAR(1))
                        SET @count = @count + 1
                END
                
        SET @count = 91
        WHILE @count <= 95
                BEGIN
                        SET @characters = @characters +
                                CAST(CHAR(@count) AS CHAR(1))
                        SET @count = @count + 1
                END
                
        SET @count = 123
        WHILE @count <= 126
                BEGIN
                        SET @characters = @characters +
                                CAST(CHAR(@count) AS CHAR(1))
                        SET @count = @count + 1
                END
    END
    
        IF @useNumbers = 1  
        BEGIN
                -- load up numbers 0 - 9
                SET @count = 48
                WHILE @count <= 57
                BEGIN
                        SET @characters = @characters +
                                CAST(CHAR(@count) AS CHAR(1))
                        SET @count = @count + 1
                END
        END
        
        IF @useLowerCase = 1  
        BEGIN
        -- load up uppercase letters A - Z
        SET @count = 65
        WHILE @count <= 90
        BEGIN
                SET @characters = @characters +
                        CAST(CHAR(@count) AS CHAR(1))
                SET @count = @count + 1
        END
        END

        IF @useUpperCase = 1  
        BEGIN
                -- load up lowercase letters a - z
                SET @count = 97
                WHILE @count <= 122
                BEGIN
                        SET @characters = @characters +
                                CAST(CHAR(@count) AS CHAR(1))
                        SET @count = @count + 1
                END
        END

        SET @count = 0
        SET @toReturn = ''

        -- If you specify a character set to use, the bit flags get ignored.
        IF LEN(@charactersToUse) > 0  
        BEGIN
                WHILE CHARINDEX(@charactersToUse, ' ') > 0
                BEGIN
                        SET @charactersToUse = REPLACE(@charactersToUse, ' ', '')
                END

                IF LEN(@charactersToUse) = 0  
                BEGIN
                        RAISERROR ( 'Cannot use an empty character set.', 16, 1 )
                END

                WHILE @count <= @stringLength
                BEGIN
                        SET @toReturn = @toReturn + SUBSTRING(@charactersToUse,
                                                                                                    CAST(ABS(CHECKSUM(NEWID())) *
                                                                                                    RAND(@count) AS INT) %
                                                                                                    LEN(@charactersToUse) +
                                                                                                    1, 1)
                        SET @count = @count + 1
                END
        END
        ELSE  
        BEGIN
                WHILE @count <= @stringLength
                BEGIN
                        SET @toReturn = @toReturn + SUBSTRING(@characters,
                                                                                                    CAST(ABS(CHECKSUM(NEWID())) *
                                                                                                    RAND(@count) AS INT) %
                                                                                                    LEN(@characters) + 1,
                                                                                                    1)
                        SET @count = @count + 1
                END
        END
END

GO