Luciano Evaristo Guerche

A brazilian geek interested in .NET technologies

About Me

February 2004 - Posts

Robert Scoble has mentioned my name in one of his postings

Today, just find out my name has been mentioned in Google vs. Yahoo, a posting written by Robert Scoble, the Microsoft employee who currently reads 1260+ feeds a day!

Thanks Robert.

The Road To Indigo

I have just read an excelent posting from Benjamin Mitchell concerning Indigo techology: The Road To Indigo

How to implement a work queue in SQL server

How to implement a work queue in SQL server script is attached just below. Any comment is welcome.

/******************************************************************************
 * Creates WorkQueue table                                                    *
 ******************************************************************************/
CREATE TABLE dbo.WorkQueue
 (
 WQID int NOT NULL,
 Field1 varchar(10) NULL,
 Field2 varchar(10) NULL,
 Field3 varchar(10) NULL,
 Field4 varchar(50) NULL,
 Field5 varchar(50) NULL,
 FieldN varchar(50) NULL,
 Stage1Flag tinyint NOT NULL,
 Stage2Flag tinyint NOT NULL,
 Stage3Flag tinyint NOT NULL
 )  ON [PRIMARY]
GO

ALTER TABLE dbo.WorkQueue ADD CONSTRAINT
 DF_WorkQueue_Stage1Flag DEFAULT 0 FOR Stage1Flag
GO

ALTER TABLE dbo.WorkQueue ADD CONSTRAINT
 DF_WorkQueue_Stage2Flag DEFAULT 0 FOR Stage2Flag
GO

ALTER TABLE dbo.WorkQueue ADD CONSTRAINT
 DF_WorkQueue_Stage3Flag DEFAULT 0 FOR Stage3Flag
GO

ALTER TABLE dbo.WorkQueue ADD CONSTRAINT
 PK_WorkQueue PRIMARY KEY CLUSTERED
 (
 WQID
 ) ON [PRIMARY]
GO

/******************************************************************************
 * Creates WorkQueue index which will be used by TOP procedures               *
 * ATTENTION: Do not delete this index, since it is masterkey to the process  *
 ******************************************************************************/
CREATE  INDEX [IX_WorkQueue_Stages] ON [dbo].[WorkQueue]([Stage1Flag], [Stage2Flag], [Stage3Flag]) ON [PRIMARY]
GO

/******************************************************************************
 * Creates WorkQueueErrors to hold errors which might occurs eventually       *
 ******************************************************************************/
CREATE TABLE dbo.WorkQueueErrors
 (
 WQEID int NOT NULL IDENTITY (1, 1),
 WQID int NOT NULL,
 Stage tinyint NOT NULL,
 Error varchar(255) NOT NULL
 )  ON [PRIMARY]
GO

ALTER TABLE dbo.WorkQueueErrors ADD CONSTRAINT
 PK_WorkQueueErrors PRIMARY KEY CLUSTERED
 (
 WQEID
 ) ON [PRIMARY]

GO

ALTER TABLE dbo.WorkQueueErrors ADD CONSTRAINT
 FK_WorkQueueErrors_WorkQueue FOREIGN KEY
 (
 WQID
 ) REFERENCES dbo.WorkQueue
 (
 WQID
 )
GO

/******************************************************************************
 * Creates procedure WorkQueueStage1Top                                       *
 ******************************************************************************/
CREATE PROCEDURE WorkQueueStage1Top  -- Last Input First Output (LIFO)
AS
SELECT TOP 1 dbo.WorkQueue.*
FROM dbo.WorkQueue WITH (READPAST, ROWLOCK, UPDLOCK, INDEX(IX_WorkQueue_Stages))
WHERE (Stage1Flag = 0) -- 0 = not processed
RETURN
GO

/******************************************************************************
 * Creates procedure WorkQueueStage1Pop                                       *
 ******************************************************************************/
CREATE PROCEDURE WorkQueueStage1Pop
(@WQID int,
 @Stage1Flag tinyint -- ATTENTION: parameter must be set to 1 (processed without error) or 2 (processed with error(s))
)
AS
UPDATE dbo.WorkQueue
SET Stage1Flag = @Stage1Flag
WHERE WQID = @WQID
RETURN
GO

/******************************************************************************
 * Creates procedure WorkQueueStage2Top                                       *
 ******************************************************************************/
CREATE PROCEDURE WorkQueueStage2Top  -- Last Input First Output (LIFO)
AS
SELECT TOP 1 dbo.WorkQueue.*
FROM dbo.WorkQueue WITH (READPAST, ROWLOCK, UPDLOCK, INDEX(IX_WorkQueue_Stages))
WHERE (Stage1Flag = 1) AND -- 1 = processed without error
      (Stage2Flag = 0)     -- 0 = not processed
RETURN
GO

/******************************************************************************
 * Creates procedure WorkQueueStage2Pop                                       *
 ******************************************************************************/
CREATE PROCEDURE WorkQueueStage2Pop
(@WQID int,
 @Stage2Flag tinyint -- ATTENTION: parameter must be set to 1 (processed without error) or 2 (processed with error(s))
)
AS
UPDATE dbo.WorkQueue
SET Stage2Flag = @Stage2Flag
WHERE WQID = @WQID
RETURN
GO

/******************************************************************************
 * Creates procedure WorkQueueStage3Top                                        *
 ******************************************************************************/
CREATE PROCEDURE WorkQueueStage3Top  -- Last Input First Output (LIFO)
AS
SELECT TOP 1 dbo.WorkQueue.*
FROM dbo.WorkQueue WITH (READPAST, ROWLOCK, UPDLOCK, INDEX(IX_WorkQueue_Stages))
WHERE (Stage1Flag = 1) AND -- 1 = processed without error
      (Stage2Flag = 1) AND -- 1 = processed without error
      (Stage3Flag = 0)     -- 0 = not processed
RETURN
GO

/******************************************************************************
 * Creates procedure WorkQueueStage3Pop                                       *
 ******************************************************************************/
CREATE PROCEDURE WorkQueueStage3Pop
(@WQID int,
 @Stage3Flag tinyint -- ATTENTION: parameter must be set to 1 (processed without error) or 2 (processed with error(s))
)
AS
UPDATE dbo.WorkQueue
SET Stage3Flag = @Stage3Flag
WHERE WQID = @WQID
RETURN
GO

/******************************************************************************
 * Creates procedure WorkQueueErrorsPush                                      *
 ******************************************************************************/
CREATE PROCEDURE WorkQueueErrorsPush
(@WQID int,
 @Stage tinyint,
 @Error varchar(255)
)
AS
INSERT INTO dbo.WorkQueueErrors
(WQID, Stage, Error)
VALUES
(@WQID, @Stage, @Error)
RETURN
GO

/******************************************************************************
 * Creates procedure WorkQueueErrorsGetStage1                                 *
 ******************************************************************************/
CREATE PROCEDURE WorkQueueErrorsGetStage1
AS
SELECT dbo.WorkQueue.*,
       dbo.WorkQueueErrors.*
FROM dbo.WorkQueue WITH (READPAST, ROWLOCK, UPDLOCK, INDEX(IX_WorkQueue_Stages)) INNER JOIN
     dbo.WorkQueueErrors ON dbo.WorkQueue.WQID = dbo.WorkQueueErrors.WQID
WHERE (dbo.WorkQueue.Stage1Flag = 2) AND -- 2 = processed with error(s)
      (dbo.WorkQueueErrors.Stage = 1)
RETURN
GO

/******************************************************************************
 * Creates procedure WorkQueueErrorsGetStage2                                 *
 ******************************************************************************/
CREATE PROCEDURE WorkQueueErrorsGetStage2
AS
SELECT dbo.WorkQueue.*,
       dbo.WorkQueueErrors.*
FROM dbo.WorkQueue WITH (READPAST, ROWLOCK, UPDLOCK, INDEX(IX_WorkQueue_Stages)) INNER JOIN
     dbo.WorkQueueErrors ON dbo.WorkQueue.WQID = dbo.WorkQueueErrors.WQID
WHERE (dbo.WorkQueue.Stage1Flag = 1) AND -- 1 = processed without error
      (dbo.WorkQueue.Stage2Flag = 2) AND -- 2 = processed with error(s)
      (dbo.WorkQueueErrors.Stage = 2)
RETURN
GO

/******************************************************************************
 * Creates procedure WorkQueueErrorsGetStage3                                 *
 ******************************************************************************/
CREATE PROCEDURE WorkQueueErrorsGetStage3
AS
SELECT dbo.WorkQueue.*,
       dbo.WorkQueueErrors.*
FROM dbo.WorkQueue WITH (READPAST, ROWLOCK, UPDLOCK, INDEX(IX_WorkQueue_Stages)) INNER JOIN
     dbo.WorkQueueErrors ON dbo.WorkQueue.WQID = dbo.WorkQueueErrors.WQID
WHERE (dbo.WorkQueue.Stage1Flag = 1) AND -- 1 = processed without error
      (dbo.WorkQueue.Stage2Flag = 1) AND -- 1 = processed without error
      (dbo.WorkQueue.Stage3Flag = 2) AND -- 2 = processed with error(s)
      (dbo.WorkQueueErrors.Stage = 3)
RETURN
GO

email received from Duncan Mackenzie

Tomorrow morning, when I opened Outlook Express and checked my email inbox at hotmail.com, I could not believe I have received an email from Duncan Mackenzie, MSDN Content Strategist for Visual Basic, commenting on the email I have sent to Robert Green, a Visual Basic .Net team member.

Amazing to know such valuable people take my notes into account.

Delimited List to Table

Peter Debetta published the article "Delimited List to Table" and stated in the article "I'm sure this could be optimized in a number of ways, and could also be made to trim extra spaces as well. If you have any suggestions, please add them as comments for one and all to see.".

I thought the function was too verbose for a function which just split a string into a table of values, so I optimized his function and I am posting here the result for appreciation. Any comments are welcome.

Regards,

Luciano Evaristo Guerche
Jacarei, SP, Brazil

 

CREATE FUNCTION dbo.Split2Table(@WhatToSplit varchar(8000), @Delimiter char(1))
RETURNS @ReturnTable TABLE (ListValue Sql_Variant)
AS
BEGIN
    DECLARE @StartAt int
    DECLARE @DelimiterPosition int

    SET @StartAt = 1
    SET @DelimiterPosition = COALESCE(NULLIF(CHARINDEX(@Delimiter, @WhatToSplit, @StartAt), 0), LEN(@WhatToSplit) + 1)
    WHILE @DelimiterPosition BETWEEN @StartAt AND LEN(@WhatToSplit) + 1
    BEGIN
        INSERT INTO @ReturnTable
        (ListValue)
        VALUES
        (SUBSTRING(@WhatToSplit, @StartAt, @DelimiterPosition - @StartAt))

        SET @StartAt = @DelimiterPosition + 1
        SET @DelimiterPosition = COALESCE(NULLIF(CHARINDEX(@Delimiter, @WhatToSplit, @StartAt), 0), LEN(@WhatToSplit) + 1)
    END
    RETURN
END
GO

/*
-- Some tests below

/*
-- Some tests below

/*
-- Some tests below

SELECT * FROM [dbo].[Split2Table]('', ',')
GO

SELECT * FROM [dbo].[Split2Table](',', ',')
GO

SELECT * FROM [dbo].[Split2Table]('a ,b ,c ,d ,e ', ',')
GO

SELECT * FROM [dbo].[Split2Table](' a, b, c, d, e', ',')
GO

SELECT * FROM [dbo].[Split2Table]('a,,b,,c,,d,,', ',')
GO
*/

 

Stored Procedure ForeignkeysAnalyze
/*
 
Because I usually deal with databases consisting of hundreds of tables, I developed the stored procedure attached to try to make my life easier. Hope you enjoy it. Any comments about it are welcome.
 
The stored procedure attached reads SQL server system tables and generates some valuable outputs, to name:
 
   1. List of tables, including their treeLevel (nodeLevel)
      treeLevel (nodeLevel): defined based on existing relationships between tables
      Example: table A relates to table B one to many; table B relates to table C one to many. Based on the relationships table A is on nodeLevel 1; table B on nodeLevel 2 and table C on nodeLevel 3
      How to use this information:
      a) Suppose DBA wants to delete all tables. He/she may soon face a lot of foreign key constraint errors. How to avoid it? Delete all tables from nodeLevel n, then delete all tables from nodeLevel n - 1, etc.
      b) Suppose DBA wants to bcp/bulk insert all/some the tables of a database. Soon, he/she may face a lot of foreign key constraint errors. How to avoid it? bcp/bulk insert all tables from nodeLevel 1, then all tables from nodeLevel 2, etc.
 
   2. List of relationships
 
   3. List of recursive relationships
      Recursive relationships: are the ones where one child table can achieve a parent table through another table(s).
      Example: table A relates to table B one to many; table B relates to table C one to many. table C can achieve table A through table B, so there is an recursive relationship.
 
   4. List of doubtful relationships
      Doubtful relationships: child table may achieve (direct or recursively) a parent table through only one way. If that does not happen, there are doubtful relationships between child and parent table. When a doubtful relationship exists it is hard to keep ACID rules on the tables involved in the relationship
      Example: table A relates to table B one to many; table B relates to table C one to many; table A relates to table C one to many. table C can achieve table A two ways. First, table C relating to table B, then table B relating to table A and second, table C relating to table A. How can I assure both ways I will get same record from table A? Relationships are redundant and ACID rules are hard to assure.
      How to use this information: Analyze the output and exclude the ones which are assured to be redundant
 
   5. List of circular references
      Circular references: When one table eventually depends on itself
      Example: table A relates to table B one to many; table B relates to table C one to many; table C relates to table A one to many.
      How to use this information: Analyze the output and exclude the relationship(s) which is/are causing circular reference
 
*/
 
/*************************************************************************
 * Drops procedure if it already exists                                  *
 *************************************************************************/
IF EXISTS(
          SELECT *
          FROM dbo.sysobjects
          WHERE id = OBJECT_ID(N'[dbo].[ForeignkeysAnalyze]') AND
                1 = OBJECTPROPERTY(id, N'IsProcedure')
         )
BEGIN
    DROP PROCEDURE [dbo].[ForeignkeysAnalyze]
END
GO
 
/*************************************************************************
 * Creates procedure                                                     *
 *************************************************************************/
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
 
CREATE PROCEDURE [dbo].[ForeignkeysAnalyze]
AS
BEGIN
    /*************************************************************************
     * Sets NOCOUNT ON                                                       *
     *************************************************************************/
    SET NOCOUNT ON
 
    /*************************************************************************
     * Selects table names                                                   *
     *************************************************************************/
    SELECT dbo.sysobjects.name, 1 AS treeLevel
    INTO #tables
    FROM dbo.sysobjects
    WHERE dbo.sysobjects.type = 'U' AND
          dbo.sysobjects.name NOT LIKE 'dt_%'
    ORDER BY dbo.sysobjects.name
 
    /*************************************************************************
     * Selects relations                                                     *
     *************************************************************************/
    SELECT sysobjectsParent.name AS parentTable,
           sysobjectsChild.name AS childTable
    INTO #relations
    FROM dbo.sysforeignkeys
      INNER JOIN
         dbo.sysobjects AS sysobjectsChild
         ON dbo.sysforeignkeys.fkeyid = sysobjectsChild.id
      INNER JOIN
         dbo.sysobjects AS sysobjectsParent
         ON dbo.sysforeignkeys.rkeyid = sysobjectsParent.id
    GROUP BY sysobjectsParent.name,
             sysobjectsChild.name
    ORDER BY sysobjectsParent.name,
             sysobjectsChild.name
 
    /*************************************************************************
     * Creates and fulfills recursive relations                              *
     *************************************************************************/
    DECLARE @Step AS smallint
    SET @Step = 1
 
    SELECT #relations.parentTable,
           #relations.childTable,
           CAST('\' + #relations.parentTable + '\' + #relations.childTable + '\' AS varchar(1024)) AS Path,
           #relations.childTable AS rightOfParent,
           #relations.parentTable AS leftOfChild,
           @Step AS Step
    INTO #relationsRecursive
    FROM #tables
      INNER JOIN
         #relations
         ON #tables.name = #relations.parentTable
    ORDER BY #tables.treeLevel,
             #tables.name
 
    WHILE EXISTS(
                 SELECT #relationsRecursive.parentTable
                 FROM #relationsRecursive
                   INNER JOIN
                      #relations
                      ON #relationsRecursive.childTable = #relations.parentTable
                 WHERE #relationsRecursive.Step = @Step AND
                       CHARINDEX('\' + #relations.childTable + '\', #relationsRecursive.Path) = 0
                )
    BEGIN
        INSERT INTO #relationsRecursive
        (parentTable,
         childTable,
         [Path],
         rightOfParent,
         leftOfChild,
         Step
        )
        SELECT #relationsRecursive.parentTable,
               #relations.childTable,
               #relationsRecursive.Path + #relations.childTable + '\',
               #relationsRecursive.rightOfParent,
               #relationsRecursive.childTable,
               @Step + 1
        FROM #relationsRecursive
          INNER JOIN
             #relations
             ON #relationsRecursive.childTable = #relations.parentTable
        WHERE #relationsRecursive.Step = @Step AND
              CHARINDEX('\' + #relations.childTable + '\', #relationsRecursive.Path) = 0
 
        SET @Step = @Step + 1
    END
 
    /*************************************************************************
     * Sets treeLevel field                                                  *
     *************************************************************************/
    WHILE EXISTS(
                 SELECT #tablesChild.treeLevel
                 FROM #tables AS #tablesChild
                   INNER JOIN
                      #relations AS #relationsChild
                      ON #tablesChild.name = #relationsChild.childTable
                   INNER JOIN
                      #tables AS #tablesParent
                      ON #relationsChild.parentTable = #tablesParent.name
                 WHERE #tablesChild.treeLevel < #tablesParent.treeLevel + 1 AND
                       NOT EXISTS(
                                  SELECT #relationsRecursive.parentTable
                                  FROM #relationsRecursive
                                  WHERE #relationsRecursive.childTable = #tablesParent.name AND
                                        #relationsRecursive.parentTable = #tablesChild.name
                                 )
                )
    BEGIN
        UPDATE #tablesChild
        SET #tablesChild.treeLevel = #tablesParent.treeLevel + 1
        FROM #tables AS #tablesChild
          INNER JOIN
             #relations AS #relationsChild
             ON #tablesChild.name = #relationsChild.childTable
          INNER JOIN
             #tables AS #tablesParent
             ON #relationsChild.parentTable = #tablesParent.name
        WHERE #tablesChild.treeLevel < #tablesParent.treeLevel + 1 AND
              NOT EXISTS(
                         SELECT #relationsRecursive.parentTable
                         FROM #relationsRecursive
                         WHERE #relationsRecursive.childTable = #tablesParent.name AND
                               #relationsRecursive.parentTable = #tablesChild.name
                        )
 
    END
 
    /*************************************************************************
     * Creates table #relationsDoubtful                                      *
     *************************************************************************/
    SELECT #relationsRecursive.parentTable,
           #relationsRecursive.childTable,
           COUNT(#relationsRecursive.childTable) AS Occurrences
    INTO #relationsDoubtful
    FROM #relationsRecursive
    WHERE (
           SELECT COUNT(#relationsRecursiveSQ.Path)
           FROM #relationsRecursive AS #relationsRecursiveSQ
           WHERE #relationsRecursiveSQ.childTable = #relationsRecursive.childTable AND
                 #relationsRecursiveSQ.parentTable = #relationsRecursive.parentTable AND
                 #relationsRecursiveSQ.rightOfParent = #relationsRecursive.rightOfParent
          ) = 1 AND
          (
           SELECT COUNT(#relationsRecursiveSQ.Path)
           FROM #relationsRecursive AS #relationsRecursiveSQ
           WHERE #relationsRecursiveSQ.childTable = #relationsRecursive.childTable AND
                 #relationsRecursiveSQ.parentTable = #relationsRecursive.parentTable AND
                 #relationsRecursiveSQ.leftOfChild = #relationsRecursive.leftOfChild
          ) = 1
    GROUP BY #relationsRecursive.parentTable,
             #relationsRecursive.childTable
    HAVING COUNT(#relationsRecursive.childTable) > 1
    ORDER BY #relationsRecursive.parentTable,
             #relationsRecursive.childTable
 
    /*************************************************************************
     * Sets NOCOUNT OFF                                                      *
     *************************************************************************/
    SET NOCOUNT OFF
 
    /*************************************************************************
     * Selects tables name (with treeLevel included)                         *
     *************************************************************************/
    SELECT '#tables' AS Source,
           #tables.name,
           #tables.treeLevel
    FROM #tables
    ORDER BY #tables.treeLevel,
             #tables.name
 
    /*************************************************************************
     * Selects relations                                                     *
     *************************************************************************/
    SELECT '#relations' AS Source,
           #relations.*
    FROM #relations
    ORDER BY parentTable,
             childTable
 
    /*************************************************************************
     * Selects recursive relations                                           *
     *************************************************************************/
    SELECT '#relationsRecursive' AS Source,
           #relationsRecursive.*
    FROM #relationsRecursive
    ORDER BY parentTable,
             Path
 
    /*************************************************************************
     * Selects doubtful relations                                            *
     *************************************************************************/
    SELECT '#relationsDoubtful' AS Source,
           #relationsDoubtful.parentTable,
           #relationsDoubtful.childTable,
           #relationsRecursive.Path
    FROM #relationsDoubtful
      INNER JOIN
         #relationsRecursive
         ON #relationsDoubtful.childTable = #relationsRecursive.childTable AND
            #relationsDoubtful.parentTable = #relationsRecursive.parentTable
    ORDER BY #relationsDoubtful.childTable,
             #relationsRecursive.parentTable,
             #relationsRecursive.Path
 
    /*************************************************************************
     * Selects circular relations                                            *
     *************************************************************************/
    SELECT '#relationsCircular' AS Source,
           #relationsRecursive.parentTable,
           #relationsRecursive.childTable,
           #relationsRecursive.Path
    FROM #relationsRecursive
    WHERE EXISTS(
                 SELECT #relationsRecursiveSQ.parentTable
                 FROM #relationsRecursive AS #relationsRecursiveSQ
                 WHERE #relationsRecursiveSQ.parentTable = #relationsRecursive.childTable AND
                       #relationsRecursiveSQ.childTable = #relationsRecursive.ParentTable
                )
    ORDER BY #relationsRecursive.childTable,
             #relationsRecursive.parentTable,
             #relationsRecursive.Path
 
    /*************************************************************************
     * Finishes procedure                                                    *
     *************************************************************************/
    RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
EXECUTE [dbo].[ForeignkeysAnalyze]
GO
 
/*
dbo.ForeignkeysAnalyze.storedprocedure.sql dbo.ForeignkeysAnalyze.storedprocedure.sql
*/ 
Who I am

Well, I am 31 year old, living in Jacarei, SP, Brazil and currently working for Visanet in São Paulo, SP, Brazil. Visanet is a company owned by 28 banks and partner to Visa International. Visa takes care of card holders and we take care of card processing and establishments relationship, supply and maintanance. I am senior system analyst in charge of some sensitive systems developed using tools/technologies like Microsoft Visual Basic 6, SQL server 6 and 2k, Microsoft Office automation and mainframe emulation (Attachment Extra!).

I graduated in Data Processing Technology in the Methodist University of Piracicaba in 1993 and post-graduated in system analysis in Universidade Mackenzie in 1995. I have been involved in projects since I graduated in 1993 and prior working for Visanet, I worked for American Express, Jornal Folha de São Paulo, Lloyds Bank PLC, Construções e Comércio Camargo Correa e Mineiração Rio do Norte, part of Vale do Rio Doce holding. In the beginning I used to use Clipper Summer 97, but when I left Construções e Comércio Camargo Correa and went to Lloyds Bank PLC, I never dared to use it again.

Recentely, I have attended a introductory Java course, which introduced me to the OOP world. To be honest, I did know much of was taught, but an important part of OOP I did not understand was presented in the course and I manage to learn it: INTERFACES. Java is an interesting technology/language, but the learning curve is longer, so I am postponing my entrance in the Java world...

As I am a VB 6 programmer I intend to learn VB .NET and due to the Java course I attended, I also want to learn C# and J#. Hope some day I be proficient in these tools.

More Posts