A long time ago - February and March 2006 - Eugene Asahara has posted two very intersting articles about

• KPI Cause and Effect Visio Graph
• KPI Cause and Effect Graph - A View from the UDM into Relationships Between Information Workers

The "KPI Cause and Effect Visio Graph" is a very useful visualization tool to diskuss KPI´s with the prospective users whereas the "KPIRelationships sample application" is a very smart tool to control if all perspectives, KPI´s, etc are developed and have the right definitions.

This two articles are a really must-read and Eugene also provides the tools for download - so check it out!

If you would like to split values from a column which are divideable beacause they are using the same delimiter like an ',' for example you could use the function below. The function also brings up the postion, which sometimes could be very usefull, too.

To call the function just use,

select * from dbo.fn_split('Value 1,Value 2',',')

the first parameter is the column to split, the second contains the delimiter. You can not use different delimiters at one time at the moment, if you want to suggest some enhancements, please use the comments....
The statement will bring up the following result set:

CREATE FUNCTION [dbo].[fn_Split](@text nvarchar(max), @delimiter char(1) = ' ') 

RETURNS @Strings TABLE 
( 
position int IDENTITY PRIMARY KEY, 
value nvarchar(max) 
) 

AS 
BEGIN 

    DECLARE @index int 
    SET @index = -1 

    WHILE (LEN(@text) > 0) 
    BEGIN 
        SET @index = CHARINDEX(@delimiter , @text) 
        IF (@index = 0) AND (LEN(@text) > 0) 
        BEGIN 
            INSERT INTO @Strings VALUES (@text) 
            BREAK 
        END 
        IF (@index > 1) 
        BEGIN 
            INSERT INTO @Strings VALUES (LEFT(@text, @index - 1)) 
            SET @text = RIGHT(@text, (LEN(@text) - @index)) 
        END 
        ELSE 
            SET @text = RIGHT(@text, (LEN(@text) - @index)) 
    END 
    RETURN 
END

Are you also always looking in the BOL if you have to deal with datetime?

This stored procedure could bring some light in the dark...

You will have to call it with the following statement:

Select dbo.fn_formatdate(getdate(),'formated date: \TT\MM\YYYY \hh:\mi:\ss \ms Week: \wk Weekday: \DW Quarter: \qq'),getdate()

The formatstring can contain severall formatstrings and will bring up all datetimeparts you want, including some additional textparts...

CREATE  FUNCTION [dbo].[fn_formatdate](@dat datetime,@format varchar(255)) 
RETURNS varchar(255) 
AS 
BEGIN 
    declare @ret varchar(255) 
    declare @i int 
    -- year 4 digits
    set @ret=replace(@format,'\jjjj',cast(year(@dat) as varchar(4)))
    -- year 4 digits 
    set @ret=replace(@ret,'\yyyy',cast(year(@dat) as varchar(4)))
    -- day 2 digits 
    set @ret=replace(@ret,'\dd',right('0'+cast(day(@dat) as varchar(2)),2))
    -- weekday 1 digit 
    set @ret=replace(@ret,'\dw',cast(datepart(dw,@dat) as varchar(1)))
    -- day of year
    set @ret=replace(@ret,'\dy',right('00'+cast(day(@dat) as varchar(3)),3))
    -- hour 2 digits
    set @ret=replace(@ret,'\hh',right('0'+cast(datepart(hh,@dat) as varchar(2)),2))
    -- year 2 digits 
    set @ret=replace(@ret,'\jj',right(cast(year(@dat) as varchar(4)),2))
    -- minute 2 digits 
    set @ret=replace(@ret,'\mi',right('0'+cast(datepart(mi,@dat) as varchar(2)),2))
    -- month 2 digits
    set @ret=replace(@ret,'\mm',right('0'+cast(month(@dat) as varchar(2)),2))
    -- milliseconds 3 digits
    set @ret=replace(@ret,'\ms',right('00'+cast(datepart(ss,@dat) as varchar(3)),3))
    -- quarter 2 digits 
    set @ret=replace(@ret,'\qq',right('0'+cast(datepart(qq,@dat) as varchar(2)),2))
    -- seconds 2 digits
    set @ret=replace(@ret,'\ss',right('0'+cast(datepart(ss,@dat) as varchar(2)),2)) 
    -- day 2 digits
    set @ret=replace(@ret,'\tt',right('0'+cast(day(@dat) as varchar(2)),2)) 
    -- week 2 digits
    set @ret=replace(@ret,'\wk',right('0'+cast(datepart(wk,@dat) as varchar(2)),2)) 
    -- year 2 digits
    set @ret=replace(@ret,'\yy',right(cast(year(@dat) as varchar(4)),2))
    -- day 1-2 digits 
    set @ret=replace(@ret,'\d',cast(day(@dat) as varchar(2)))
    -- hour 1-2 digits 
    set @ret=replace(@ret,'\h',cast(datepart(hh,@dat) as varchar(2)))
    -- month 1-2 digits 
    set @ret=replace(@ret,'\m',cast(month(@dat) as varchar(2)))
    -- minute 2 digits
    set @ret=replace(@ret,'\n',right('0'+cast(datepart(mi,@dat) as varchar(2)),2)) 
    -- quarter 1 digit 
    set @ret=replace(@ret,'\q',cast(datepart(qq,@dat) as varchar(1)))
    -- seconds 1-2 digits
    set @ret=replace(@ret,'\s',cast(datepart(ss,@dat) as varchar(2)))
    -- day 1-2 digits 
    set @ret=replace(@ret,'\t',cast(day(@dat) as varchar(2)))
    -- week 1-2 digits 
    set @ret=replace(@ret,'\w',cast(datepart(wk,@dat) as varchar(2)))

    return @ret 
END 
GO

Do you want to create database snapshots? And do you want to do it programmatically? Then you could use this stored procedure...

Enjoy!

CREATE PROC [dbo].[spr_CreateSnapshot]
    (@Masterdb VARCHAR(255), @SnapshotName VARCHAR(255), @Execute BIT = 1) 
AS 
    SET NOCOUNT ON 
    DECLARE @NewLine CHAR(2) 
    DECLARE @Q CHAR(1) 
    DECLARE @fname VARCHAR(255) 
    DECLARE @extention VARCHAR(255) 
    DECLARE @Pfad VARCHAR(255) 
    DECLARE @DBname VARCHAR(255) 
    DECLARE @LogicName VARCHAR(255) 
    DECLARE @Command VARCHAR(MAX) 
    DECLARE @indexExt INT 
    DECLARE @indexPfad INT 
    DECLARE @lenFname INT 
    DECLARE @lenPfad INT 
    DECLARE @lenDB INT 
    DECLARE @lenExt INT 

    CREATE TABLE #Info
        (physical_name VARCHAR(255) not null, 
        logicname VARCHAR(255) not null) 

    SET @newLine = CHAR(13) + CHAR(10) 
    SET @Q = CHAR(39) 

    SET @command = 'INSERT INTO #info (physical_name, logicname) 
        SELECT s.physical_name,s.[name] AS LogicName 
        FROM '
            + Quotename(@masterdb) + '.sys.filegroups as g 
        INNER JOIN
        sys.master_files AS s ON 
            s.type = 0 
            AND s.database_id = db_id(' + @Q + @Masterdb + @Q + ') 
            AND s.drop_lsn is null 
            AND s.data_space_id = g.data_space_id 
        ORDER BY
            g.data_space_id' 
    EXECUTE (@Command) 

    SET @Command = 'CREATE DATABASE ' + @SnapshotName + @NewLine 
    SET @Command = @Command + 'ON' + @NewLine 

    DECLARE c CURSOR 
    READ_ONLY 
    FOR SELECT physical_name, logicname FROM #info 

    OPEN c 
    FETCH NEXT FROM c INTO @fname,@LogicName 
        WHILE (@@fetch_status <> -1) 
        BEGIN 
            IF (@@fetch_status <> -2) 
            BEGIN 
                SET @fname = REVERSE(@fname) 
                SET @lenFname = LEN(@fname) 
                SET @indexExt = CHARINDEX('.',@fname) -1 
                SET @indexPfad = CHARINDEX('\',@fname) - 1 
                SET @extention = REVERSE(SUBSTRING (@fname, 1, @indexExt)) 
                SET @lenExt = LEN(@extention) 
                SET @Pfad = LEFT (REVERSE(@fname), @lenFname - @indexPfad) 
                SET        @lenPfad = LEN(@Pfad) 
                SET @DBname = SUBSTRING(REVERSE(@fname), @lenPfad + 1, (@lenFname - @lenPfad - @lenExt) - 1) 
                SET @Command = @Command + '(Name = ' + @Q + @LogicName + @Q + ', Filename = ' + @Q 
                SET @Command = @Command + @Pfad + @SnapshotName + '_' + @DBname + '.' + 'ssh' + @Q + '),' + @NewLine 
            END 
        FETCH NEXT FROM c INTO @fname,@LogicName 
        END 
    CLOSE c 
    DEALLOCATE c 
    SET @Command = LEFT(@Command,LEN(@Command)-3) + @NewLine + 'AS SNAPSHOT OF ' + @masterdb 
        
    IF @Execute = 1 
    BEGIN 
        EXEC (@Command) 
    END 
    ELSE 
    BEGIN 
        SELECT @Command AS Command 
    END

You are interested in getting some informations about your SQL Server, for example to be able to do an auditing on your SQL Server via SSIS, this code will help:

SELECT
    SERVERPROPERTY ('MachineName') as MachineName,
    SERVERPROPERTY ('InstanceName') as InstanceName,
    SERVERPROPERTY ('ProductVersion') as ProductVersion,
    SERVERPROPERTY ('ProductLevel') As ProductLevel,
    SERVERPROPERTY ('Edition') as Edition,
    SERVERPROPERTY ('LicenseType') as LicenseType,
    SERVERPROPERTY ('NumLicenses') as  NumLicenses,
    SERVERPROPERTY ('IsClustered') as IsClustered,
    SERVERPROPERTY ('IsIntegratedSecurityOnly') as IsIntegratedSecurityOnly

I do not know the original author of this stored procedure...
If you are the one - please send me an email and I will publish the right credits!

After changing the structure of a table or delete one you will need to check and refresh your views also. This code will help...

DECLARE @Table_Name varchar(100)

DECLARE Refresh_Views CURSOR FOR
SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'VIEW' AND TABLE_SCHEMA = 'dbo'
OPEN Refresh_Views

FETCH NEXT FROM Refresh_Views INTO @Table_Name
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_refreshview @Table_Name
    FETCH NEXT FROM Refresh_Views INTO @Table_Name
END

CLOSE Refresh_Views
DEALLOCATE Refresh_Views

This one is an event for your 2008 calender...
PASS is coming back to Europe next year from April 14th to 16th with the European PASS Conference 2008!

You want to be part of it?