SQL Work

It's been a little while since the last post, no excuses, just been busy with other things.

The next step from the last post was to get some SQL work done so that I could pass the changes from the Exchange Event Sink through to SQL.

So I created a DB in SQL 2000 then created the table for the Time items I was going to be persisting. The following SQL script creates the basic table in which the Time items will sit.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Time]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Time]
GO

CREATE TABLE [dbo].[Time] (
[GUID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[URLName] [nvarchar] (256) COLLATE Latin1_General_CI_AS NOT NULL ,
[StoreEntryID] [nvarchar] (160) COLLATE Latin1_General_CI_AS NOT NULL ,
[EntryID] [nvarchar] (160) COLLATE Latin1_General_CI_AS NOT NULL ,
[ProjectTitle] [nvarchar] (256) COLLATE Latin1_General_CI_AS NULL ,
[Subject] [nvarchar] (256) COLLATE Latin1_General_CI_AS NOT NULL ,
[Contacts] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[Company] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[Employees] [nvarchar] (256) COLLATE Latin1_General_CI_AS NULL ,
[Start] [datetime] NULL ,
[End] [datetime] NULL ,
[Duration] [int] NULL ,
[RequestID] [nvarchar] (16) COLLATE Latin1_General_CI_AS NULL ,
[BillingCategory] [nvarchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[BillingDuration] [int] NULL ,
[Type] [nvarchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[Categories] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[Creator] [nvarchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[CreationTime] [datetime] NULL ,
[LastModifier] [nvarchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[LastModifcationTime] [datetime] NULL ,
[Body] [ntext] COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Time] WITH NOCHECK ADD
CONSTRAINT [PK_Time] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Time] ADD
CONSTRAINT [DF_Time_ID] DEFAULT (newid()) FOR [GUID]
GO

Theres not much special about the above table, it just replicates the fields from the Exchange Public Folder that I reckon I will need in the eventual solution. I suppose the only thing new for me is the use of UniqueIdentifier. I thought I'd better plan this in for replication purposes, how I'm going to use it is a matter I've not thought about yet!

The next thing for me to do was to create a Stored Procedure to update this table. I wanted a simple procedure that I could use to either create a new item or, given an ID, update an item. The following Stored Procedure is what I've come up with:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SaveTime]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SaveTime]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO


CREATE PROCEDURE SaveTime
(
@GUID uniqueidentifier=NULL,
@ID int=0,
@URLName nvarchar(256)='',
@StoreEntryID nvarchar (160),
@EntryID nvarchar (160),
@ProjectTitle nvarchar (256)=NULL,
@Subject nvarchar (256),
@Contacts nvarchar (128)=NULL,
@Company nvarchar (128)=NULL,
@Employees nvarchar (256)=NULL,
@Start datetime=NULL,
@End datetime=NULL,
@Duration int=NULL,
@RequestID nvarchar (16)=NULL,
@BillingCategory nvarchar (32)=NULL,
@BillingDuration int=NULL,
@Type nvarchar (32)=NULL,
@Categories nvarchar (128)=NULL,
@Creator nvarchar (32)=NULL,
@CreationTime datetime=NULL,
@LastModifier nvarchar (32)=NULL,
@LastModifcationTime datetime=NULL,
@Body ntext=NULL
)
AS

SET NOCOUNT ON

DECLARE
@trncnt int,
@ErrorNumber int

SELECT @ErrorNumber = -1

SELECT @trncnt = @@trancount

IF @trncnt = 0
BEGIN TRANSACTION T1
ELSE
SAVE TRANSACTION T1

IF @ID IS NULL
SET @ID = 0

IF @ID=0 --No ID passed so INSERT
BEGIN

SET @GUID=NEWID()

INSERT INTO Time
(GUID, URLName, StoreEntryID, EntryID, ProjectTitle, Subject, Contacts, Company, Employees, Start, [End], Duration, RequestID, BillingCategory, BillingDuration, Type, Categories, Creator, CreationTime, LastModifier, LastModifcationTime, Body)
VALUES
(@GUID, @URLName, @StoreEntryID, @EntryID, @ProjectTitle, @Subject, @Contacts, @Company, @Employees, @Start, @End, @Duration, @RequestID, @BillingCategory, @BillingDuration, @Type, @Categories, @Creator, @CreationTime, @LastModifier, @LastModifcationTime, @Body)
SET @ID=@@IDENTITY

END
ELSE --ID Passed so UPDATE
BEGIN
UPDATE Time
SET
URLName=@URLName,
StoreEntryID=@StoreEntryID,
EntryID=@EntryID,
ProjectTitle=@ProjectTitle,
Subject=@Subject,
Contacts=@Contacts,
Company=@Company,
Employees=@Employees,
Start=@Start,
[End]=@End,
Duration=@Duration,
RequestID=@RequestID,
BillingCategory=@BillingCategory,
BillingDuration=@BillingDuration,
Type=@Type,
Categories=@Categories,
Creator=@Creator,
CreationTime=@CreationTime,
LastModifier=@LastModifier,
LastModifcationTime=@LastModifcationTime,
Body=@Body
WHERE (ID=@ID)
END

SELECT @ID

IF @@Error <> 0 GOTO ErrorHandler

IF @trncnt = 0
COMMIT TRANSACTION

RETURN (0)

ErrorHandler:
ROLLBACK TRANSACTION
RETURN (100)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


Not the biggest or most comprenhsive of posts, but it's a move forward, so that's good. The next step I have to do is write the VB Script which calls the Stored Procedure when an item changes.

But considering I'm off to Murcia in Spain on Saturday, then it's going to be at least a couple of weeks until I revisit this.