Click here to monitor SSC
  • Av rating:
  • Total votes: 30
  • Total comments: 3
Phil Factor

The Ultimate Excuse Database

15 January 2007

---Editor's Note---
This article, describing Phil's "ultimate excuse generator", was first published in December 2005. The plug-and-play version of the tool was recently removed from the Red-Gate.com website, but I couldn't stand by and let it disappear for good, so it's now hosted on Simple-Talk. This seemed like a good enough reason to give the article a polish and re-publish it. If you missed it the first time, you're in for a treat!
---End Editor's Note---

Failed Project? Need an excuse fast?

Recently, I received a desperate plea for help from a Simple-Talk reader, who cited the following dilemma:

Phil,

The project I'm working on is about to go belly-up. Can you come up with some plausible reasons for the failure that would not reflect poorly on my work?

Thanks in advance, Dave

This, of course, is not one of the classic SQL problems addressed in textbooks. It is quite difficult to give a solution that conforms to the SQL-92 standard.

There are many ways of approaching the problem, and I'll go over one solution – my challenge to other readers to se if they can come up with something better! If you want to test drive the excuse generator now, simply click on the link below:

TEST DRIVE EXCUSE GENERATOR

The versatility of T-SQL functions

Whatever its shortcomings my solution serves as a good way of highlighting the versatility of T-SQL functions. You just need to create a SQL Server database and into it load a few simple functions. These functions will provide facile and convincing arguments as to why a project has failed, thereby creating a smokescreen that will safely conceal the real culprits.

A T-SQL function for picking random data

We start with a function that will pick a random phrase or excuse from a list. This is a modification of a technique I use to populate a database with plausible data for checking indexing strategies, and for testing a fully populated database under load.

The first thing to remember about functions is that the use of the rand() operator is forbidden. This is for a very good reason: its use renders the function non-deterministic. We cheat by disguising it as a view.

NOTE:
This is not guaranteed to work in subsequent releases of SQL Server – though it works just fine on SQL 2000 and 2005.

CREATE VIEW vRandomNumber
AS
SELECT 
RAND() AS RandomNumber
GO

Now we provide the key function that picks a phrase from a list. This, by itself, is sufficient for simple tasks such as giving a weather forecast, doing a psychic reading, or selecting an appropriate IT architecture:

CREATE FUNCTION ufsOneOf
/*
Description:
Picks one of a delimited list. Here we have a version which has '|'
hard-wired as the list delimiter.

test:
select dbo.ufsOneOf('')
select dbo.ufsOneOf('|||')
select dbo.ufsOneOf(null)
select dbo.ufsOneOf('one|two|three')
*/

@String VARCHAR(8000)--input string of a list of alternatives

RETURNS VARCHAR(8000)--the list item selected
AS 
BEGIN 
DECLARE 
@ii INT
DECLARE 
@Substring VARCHAR(255)
DECLARE @which INT
DECLARE 
@Delimiter CHAR(1)

SELECT @Delimiter='|'

--select a random integer between 1 and the number of list items
SELECT @which=(SELECT RandomNumber FROM vRandomNumber)*
(
LEN(@String)-LEN(REPLACE(@String,@Delimiter,''))+1)+1

SELECT @ii=1@Substring=''
--And go to the item you want by iteration.
--This will please the procedural boys

WHILE @ii <= @which 
       
BEGIN
  --if the impossible has happened or he has passed a null string

       IF 
(@String IS NULL OR @Delimiter IS NULL )
               
BEGIN
               SELECT 
@Substring=''
                
BREAK 
               END
       IF CHARINDEX
(@Delimiter,@String
               
BEGIN 
               SELECT 
@subString @string
               
SELECT @String=''
               
END 
       ELSE
               BEGIN
               SELECT 
@subString =
SUBSTRING(
@String1,
CHARINDEX@Delimiter@String )-1
)
               
SELECT @String =
SUBSTRING(
@String,
CHARINDEX@Delimiter@String )+1,
LEN(@String))
               
END
       SELECT 
@ii=@ii+1
       
END 

RETURN 
(@subString
END 

GO  

Examples of use include this weather forecaster which seems to be at least as accurate as the Met Office or the National Weather Service:

Select dbo.ufsOneOf('Rain|Mist over the hills, Clearing later|
Dry in the east, Rain spreading from the west
later|Rain heavy at times, becoming clearer
later|Generally dry|Showers, more organized
rain spreading from the west|Scattered showers|
Rain spreading from the east|Dry interludes|
Becoming overcast later')

Picking your excuse

Now we build this function into a collection of word banks and phrase banks that we can then use to provide the basis of an IT strategy document; in this case, plausible reasons for the failure of a project.

NOTE:
In order to control the length of the listing, some of the inputs, contraints etc were omitted. The full source code can be obtained from the code download bundle.

CREATE FUNCTION dbo.ufsSelectRandomPhrase
/*
Description
So here we have a function that returns, at random,
one of a series of words or phrases.
Currently these are set to:


A--conceptual processes--
B--excuses--
C--inputs-- 
D--constraints--
E--types of projects--
F--pejorative words
G--things that must be good
H--misconceptions--
I--unpleasant results
J--unpleasant things
K--negatives
L--neutral modifiers
M--positive modifiers
N - S --the six main parts of a sentence
T-- process

Note that the function can pass back a string containing
a placeholder of the format %x where x is one of the
characters
A-T


Test
       Select dbo.ufsSelectRandomPhrase('A')
       Select dbo.ufsSelectRandomPhrase('I')

*/


@type CHAR(1


RETURNS VARCHAR(8000AS 
BEGIN 

RETURN
(
CASE @type --processes--
WHEN 'A' THEN 
dbo.ufsOneOf('process|organization|technology|planning
|methodology|documenting|design
|implementation|contingency planning
|change control systems'
)
--excuses--
WHEN 'B' THEN 
dbo.ufsOneOf('unclear objectives|changing objectives
|insufficient resources
|impossible schedules
|unrealistic expectations
|unclear roles and responsibilities
|corporate  politics|poor communication
|personnel turnover|changing technology
|constraining rules and regulations
|lack of sponsorship|poor planning
|unclear goals and objectives'
)
--inputs-- 
WHEN 'C' THEN
dbo.ufsOneOf('management support
|senior management buy-in
|resource allocation
|project control|pre-planning'
)
--constraints--
WHEN 'D' THEN
dbo.ufsOneOf('scope|resources|schedule
|targets|requirements'
)
--types of projects--
WHEN 'E' THEN
dbo.ufsOneOf('software packaging and distribution
|CMS|accounting|managing workflow'
)
--pejorative words
WHEN 'F' THEN
dbo.ufsOneOf('blindly|foolishly|shortsightedly
|unthinkingly|incompetently'
)
--things that must be good
WHEN 'G' THEN
dbo.ufsOneOf('core services
|comprehensive solutions
|industry best-practice'
)
--misconceptions--
WHEN 'H' THEN
dbo.ufsOneOf('the over-marketing of product features
by software vendors
|the belief that a particular technology
is a "silver bullet"
|the reluctance to invest heavily in
an area that is a "cost center"
|the overambitious goals
|a lack of understanding of IT processes
|the belief that distributed computing is
technology, not process driven
|the belief that technological change
can be rapidly absorbed by an organization'
)
--unpleasant results
WHEN 'I' THEN
dbo.ufsOneOf('An inability to properly support or manage
the technology, resulting in increasing costs
|Inefficiencies in the service provided to
customers
|An inability to meet service-level agreements
|Disconnect among the staff due to sudden
introduction of change to their environment,
resulting in %J and/or %j'

--unpleasant things
WHEN 'J' THEN
dbo.ufsOneOf('complexity|resistance|attrition|confusion
|apathy|anxiety'
)
--negatives
WHEN 'K' THEN
dbo.ufsOneOf('superfluous|inappropriate|flawed
|insufficient|harmful|vague|unhelpful
|inefficient|poor|inadequate|unfocused
|under-resourced'
)
--neutral modifiers
WHEN 'L' THEN
dbo.ufsOneOf('main|major|subtle|key|contributing
|influential|high level|ameliorating
|inherent'
)
--positive modifier
WHEN 'M' THEN
dbo.ufsOneOf('sufficient|adequate|focused|beneficial')
--sentence components
WHEN 'N' THEN 
dbo.ufsOneOf('|||As with many projects,
|Typically of projects of this scale,
|We are now realizing that|
|Surely|To be absolutely frank,
|Preliminary examinations reveal that
|An in-depth analysis suggests that
|I think it is fair to say that
|It is generally thought that|
|We must conclude that, finally'
)
WHEN 'O' THEN 
dbo.ufsOneOf('one of the %L causes
|a %L factor which was responsible
|the blame|one of the %L reasons
|a %L factor


|the %L difficulties'
)
WHEN 'P' THEN 
dbo.ufsOneOf('for the|for any of the
|for all the issues around the
|for what is usually termed the|
|for what is probably the
|of the avoidance of'
)
WHEN 'Q' THEN 
dbo.ufsOneOf('lack of %M Change Control Systems
|well-defined architectures
|overrun of initial cost estimations
|%K resolve to follow the plans
|inadequate planning|project slip
|budget overrun|%K productivity
|scope-creep
|lack of support from senior management'
)
WHEN 'R' THEN 
dbo.ufsOneOf('was because of|was due to|rests with
|cannot be attributed to
|may be linked with
|is undoubtedly due to
|should be seen in the context of'
)
WHEN 'S' THEN 
dbo.ufsOneOf('changing requirements
|personality conflicts
|%K upper management
|restricted budget|restricted time
|power struggles
|%K elicitation and validation
of requirements
|commitment|overambitious goals
|incompetent staff
|a culture dependent on maintaining

the status quo
|a %K transition strategy'
)
--processes
WHEN 'T' THEN
dbo.ufsOneOf('business|payroll|accounting|group
|management|sales|marketing'
)            
ELSE
   
'error -bad placeholder'
END
       

END 
GO  

Random excuses become plausible waffle

Now all we need to do is have a function that substitutes phrases in the right place. Normally, this would have a mundane use such as coming up with customer addresses for dummy data, but here we give it a more dignified purpose:

CREATE FUNCTION dbo.ufsWaffle

@formatString VARCHAR(8000

/*
Description:
Searches for the next placeholder repeatedly in a string,
and substitutes a random
phrase until there are no more.


Requires
dbo.ufsSelectRandomPhrase


Example
select dbo.ufsWaffle('%N %O %P %Q %R %S')

*/

RETURNS VARCHAR(8000AS 
BEGIN 
DECLARE 
@Where INT

WHILE 

       
BEGIN 
       SELECT 
@where=CHARINDEX'%'ISNULL(@formatString,'') )
--If we are out of % placeholders return the @formatString 
       
IF @where BREAK 
--If the delimiter is not in the Args list then do one last
--replacement 

       
SELECT @formatString 
               
STUFF@formatString@where2,
dbo.ufsSelectRandomPhrase
(SUBSTRING(@formatString,@where+1,1)))
       
END 
RETURN 
(@formatString
END 
GO  

So, invoking:

SELECT dbo.ufsWaffle('%N %O %P %Q %R %S')  

…repeatedly will supply as many plausible reasons as you wish.

A versatile bullshit generator

However, you will notice a certain repetitive sameness about the previous solution. A more subtle approach is to randomize the sentence structure.

CREATE  PROCEDURE spCreateEssay
/*
Description
This will produce a block of text based on picking the built-in
phrases at random and stringing them together.

Then the placeholders are substituted.
       
Example
 Execute spCreateEssay 'Red Face Software Ltd'


 

example output
Select dbo.ufsWaffle('%N %K %A %R %S')
*/
AS
SET 
nocount ON
CREATE TABLE 
#sentences (sentence VARCHAR(500),
theorder numeric(10,9))

INSERT INTO #Sentences (sentence,theOrder)
SELECT 'Without %M %A, a project will eventually fail!
One of the reasons why IT projects suffer from
this to a greater degree than other industry
projects lies in %B.'
,RAND()
INSERT INTO #Sentences (sentence,theOrder)
SELECT 'A %L consequence of  %H is either %I or %I.',RAND()
INSERT INTO #Sentences (sentence,theOrder)
SELECT 'An analogy of the link between %A, %A and %A is
the project management constraint triad --%D, %D and %D.
Changes to any one of these constraints affect
the others.'
,RAND() 
INSERT INTO #Sentences (sentence,theOrder)
SELECT 'If any organization %F implements a new tool for %T
without considering the underlying %T processes or the
organizational structure required to operate and support
the technology,then failures can and more than likely

will occur: ',RAND()
INSERT INTO #Sentences (sentence,theOrder)
SELECT 'Business environments these days are characterized by %B,

and acceleration of everything from %J to %J. 
IT has been one of the major drivers of this.'
,RAND()
INSERT INTO #Sentences (sentence,theOrder)
SELECT '%N %O %P %Q %R %S',RAND()
INSERT INTO #Sentences (sentence,theOrder)
SELECT '%N %K %A %R %S',RAND()

DECLARE @outputString VARCHAR(8000)


SELECT @OutputString=COALESCE(@OutPutString,'')
+
dbo.ufsOneOf(' | | | ')
dbo.ufsWaffle(sentence
FROM #sentences ORDER BY theorder
SELECT @OutputString
GO  

So, at the click of a mouse we generate random, dignified excuses that spread the blame evenly and safely, and away from any particular individual.

Start generating excuses…

Simple-Talk has provided a plug-an-play version of the bullshit…err…excuse generator. You can give it a whirl here:

http://www.simple-talk.com/apps/excuse

As with all code or prose, some editing and cleanup of the output is required, but it is easy to see how versatile and useful this tool can be.

The full source code is available from the CODE DOWNLOAD link (in the box below the star ratings at the top of this page).

Enjoy!

Phil Factor

Author profile:

Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications. Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career. See also :

Google + To translate this article...

Search for other articles by Phil Factor

Rate this article:   Avg rating: from a total of 30 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Please consider removing this from the site.
Posted by: Anonymous (not signed in)
Posted on: Thursday, March 01, 2007 at 3:04 PM
Message: Whilst this is indeed highly amusing, I'd strongly recommend taking it down (especially the source code), simply because while it demonstrates a useful technique, it also creates an easy way for spammers to generate large amounts of body text for inclusion in junk email.

Subject: Debatable
Posted by: Anonymous (not signed in)
Posted on: Friday, March 02, 2007 at 6:32 AM
Message: Security through obscurity? Spammers don't need examples of source code to generate junk mail; they already have it...

Subject: SQL Tools
Posted by: Patrick Index (view profile)
Posted on: Wednesday, April 04, 2007 at 8:35 AM
Message: I have found this application to be an invaluable addition to my full set of SQL Server tools. It has got me out numerous tricky situations like the time I failed to backup my data.

Thanks

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.