Archive for May, 2008

T-SQL Helper Tables… and generating dynamic email bodies… and lots more

While searching for a neat way to build the body of my emails for a particular application from database data I stumbled across a fantastic article by Robyn Page and Phil Factor (aka Database Mole). The article is entitled ‘The Helper Table Workbench’ and details how using a simple table filled with numbers from 1 to 10,000 can really help reduce the use of CURSOR and other slow T-SQL commands.

The section that interested me the most is by using this helper table it makes building the body of your email using T-SQL a piece of cake.

The function below that uses these principles (of the helper table) to do macro substitution of values into a string. It will work for replacing XHTML placeholders with a value, or producing error messages in a variety of languages. In fact, uses keep popping up for this sort of function. In this version, you are able to specify what strings are used for substitutions (the default is %1, %2, %3 etc,) and what you use as the delimiter of your list of macros and values.

CREATE FUNCTION [dbo].[ufsSubstitute]
(
@Template VARCHAR(8000),
@macroList VARCHAR(8000),
@valueList VARCHAR(8000),
@Delimiter VARCHAR(255)
)
RETURNS
VARCHAR(8000)
AS
BEGIN
DECLARE @macros
    TABLE
     (MyID INT IDENTITY(1,1),
    variable VARCHAR(80))
DECLARE @values
    TABLE
    (MyID INT IDENTITY(1,1),
    [value] VARCHAR(8000))
--get all the variables
INSERT INTO @macros (variable)
    SELECT
    SUBSTRING
    (@MacroList+@Delimiter,
     number,
    CHARINDEX(@Delimiter,
        @MacroList+@Delimiter,
             number) - number)
FROM Numbers
WHERE number <= LEN(@MacroList)
    AND SUBSTRING(@Delimiter + @MacroList,
         number, LEN(@delimiter))
             = @Delimiter
ORDER BY number 

INSERT INTO @values ([value])
    SELECT SUBSTRING(@ValueList+@Delimiter, number,
        CHARINDEX(@Delimiter,
         @ValueList+@Delimiter, number)
             - number)
FROM Numbers
WHERE number <= LEN(@ValueList)
AND SUBSTRING(@Delimiter + @ValueList, number,
        LEN(@delimiter)) = @Delimiter
ORDER BY number 

SELECT @Template=
    REPLACE(@Template,COALESCE(variable,
                '%'+CAST(v.MyID AS VARCHAR)),
                [value])
FROM @values v
LEFT OUTER JOIN @macros m ON v.MyID=m.MyID
    WHERE CHARINDEX(COALESCE(variable,'%'+
        CAST(v.MyID AS VARCHAR))
                    ,@Template)>0 

RETURN (@Template) 

END

It is possible to call this T-SQL function in many ways, using templates, macro lists, value lists and delimiters. See the examples below:

SELECT dbo.ufsSubstitute (NULL,NULL,'',',')
SELECT dbo.ufsSubstitute ('','','',',')
SELECT dbo.ufsSubstitute (
    ' views','','6',',') 

DECLARE @K2String VARCHAR(1000)
SET @K2String = 'Spitting,' + 'CAML,'
+ @ApproveDocument
+ ',' + @DestinationUser + ','
 + GETDATE() + ',' + @TaskListURL + ','
+ @ViewFlowURL + ',' + @MOSSUrl

SELECT dbo.ufsSubstitute ('
Dear $1 $2,
Your K2 task $3, has been actioned by $4
to on £$5.
Please check your K2 tasklist, $6, view flow $7 or
 MOSS Site $8 which shows all open tasks
that are available to you',
    '$1,$2,$3,$4,$5,$6,$7,$8',
    @K2String,
    ',')
SELECT dbo.ufsSubstitute ('To @Destination;
Report dated @Date
The @table table is now @rows long.
please @action'
,'@Destination|@Date|@Table|@rows|@action',
'Phil Factor|12 Apr 2007|Log|1273980|truncate','|')
SELECT dbo.ufsSubstitute (
'I thought that your present of a %1 was %2.
Thank you very much.
The %1 will come in handy for %3'
    ,''
    ,'trowel|absolutely wonderful|gardening','|') 

This is going to prove very valuable to allow me to build emails on the fly for many applications including hopefully the K2 [blackpearl] email bodies.

Enjoy

SpittingCAML


By SpittingCAML in SQL Server 2005, T-SQL  .::. (Add your comment)

Taking the user to a different habitat

My organisation runs some Linux based applications, and users are generally confused when it comes to the interface and usability.

An interesting experiment has been carried out… let an average user loose on Ubuntu (a particularly popular flavour of Linux…. possibly raspberry)… and compare what they can do in Windows based OS with what they can do with the Linux based OS. You can read the experiment in detail: here

The article shows that Ubuntu is harder to use by the average user, but using unfamiliar software will always be a challenge, for even a hardened IT professional… trust me, I’ve had to submit a time sheet and an expense claim in SAP!…

Well worth a read.

SpittingCAML


By SpittingCAML in Linux, SAP, Usability  .::. (Add your comment)

Discovering the status of a process instance after completing an activity instance with a decision making process

A good article on this has been produced by Bob @ K2 Underground and is available: here.

I’m going to investigate using the method he describes to help us better catch error statuses on our activity instances to remove the need for the operations and maintenance team to constantly check on running workflows.

You can now catch the error status, and be proactive in how you deal with it, either through notifications to the user, or to roll back to the activity instance that failed until an issue outside of the workflow is fixed e.g. networked database/AD issue.

SpittingCAML


By SpittingCAML in K2 [blackpearl]  .::. (Add your comment)

Getting through to the K2 Consultants

The place to look is the PitchBlack forums.

Right now is a really interesting post on how to create a custom security provider for K2 [blackpearl] with screenshots and code snippets. Well worth a read.

SpittingCAML


By SpittingCAML in Blogging, K2 [blackpearl]  .::. Read Comments (2)

Blog-tastic!

I’ve just discovered technorati…. yes I know I’m about 6 years behind the curve…. but now I’ve seen the light.

This nice Swiss army knife of blog reading allows you to search all blogs written by absolutely anyone in one hit.

See all the K2 blog posts here


A nifty automatically updated chart showing the number of K2 [blackpearl] postings over the last 30 days!

SpittingCAML


By SpittingCAML in Blogging, Random, Search Engines  .::. (Add your comment)

Need help with K2 [blackpearl]? Where do you turn?

Alas.. the answer is readily available. Visit the K2 Underground Definitive index of official help content.

SpittingCAML


By SpittingCAML in K2 [blackpearl], Training  .::. (Add your comment)

K2 [blackpearl] 0803 Released into the wild

I suppose it is just good manners to report this, but instead of repeating what others have said, here’s a few good links.

Sergio Del Piccolo’s Blog
Jason Apergis’ Blog
Daniel Gocsman’s Blog

Things to note about this release

  1. 64bit support - run on 64 bit hosts with no sleepless nights
  2. License Management - keep track of how many users you’ve got / track over use of licenses etc.
  3. Lots of performance tweaks
  4. Lots more documentation :-D

SpittingCAML


By SpittingCAML in K2 [blackpearl]  .::. Read Comment (1)

SharePoint Protocol Documentation… beta release

Microsoft have finally given up holding all the card and have released the documentation that describes the underlying communication protocols that SharePoint uses.

You can find it: here, and download the zip containing all the PDF documentation: here

It isn’t really what i’d call a scintillating read, but will stop me scratching my head the next time I come across a SharePoint ‘feature’ that wasn’t previously documented.

Microsoft have listed it in two sections, front end, and back end.

Enjoy,

SpittingCAML


By SpittingCAML in SharePoint  .::. Read Comment (1)

K2 [blackpearl] and the people impact

There are a multitude of benefits to having K2 [blackpearl] in your organisation.

To name my favourite four:

  1. Rapid workflow prototyping and development
  2. Non IT work force and be involved in the workflow design
  3. System synergies with SAP, BizTalk, SharePoint and many others
  4. Workflow configuration management

But, before you get too excited… have you considered

  1. Developer training? - after all, who’s going to implement your new workflows and integrations?
  2. Administration training? - who’s going to maintain the workflows and databases when the developers should be concentrating on the next release of your business processes?
  3. User training? - will the new business processes involve different steps and stages? Who will define the best practice, and perform user acceptance?

Most people cover points 1 and 3…. but it’s important to cover point 2! Ensure the operations/maintenance team in your organisation know what to do when workflows go wrong and when the CEO is ringing to tell you that his document is stuck in the lost workflow dimension.

The very helpful people at K2 are putting together an administration and maintenance course for my organisation. I will try to report on its success in the next few weeks.

SpittingCAML


By SpittingCAML in K2 [blackpearl], Training  .::. (Add your comment)

Open Source Blogging…. many upsides… but also plenty of downsides

You may have wondered why my site hasn’t been visible for a few days…. well, I got "Wordpressed"… It seems some security vulnerabilities were discovered, and lots of blogs were attacked.

Nothing was harmed, except my Google and other search engine listing… i.e. they all refuse to list my site now, as all it did was report silly spam and other rubbish that the hackers placed on the front page.

A little worrying this… perhaps I need to switch to ASP.NET and rid myself of open source….

SpittingCAML




You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.