CREATE   PROCEDURE [dbo].[registerSite] (
  @SiteID uniqueidentifier = NULL OUTPUT,
  @MachineName nvarchar(15),
  @SiteIndex smallint
) AS
BEGIN TRAN
DECLARE @tmpID uniqueidentifier
DECLARE @TableName nvarchar(25)
DECLARE @SQLStatement nvarchar(2000)

IF (ISNULL(@MachineName, N'') = N'')
  RAISERROR(N'The machine name ''%s'' is invalid.', 16, 1, @MachineName)
IF (ISNULL(@SiteIndex, 0) < 1)
  RAISERROR(N'The site index ''%hd'' is invalid.', 16, 1, @SiteIndex)

IF (@@ERROR <> 0) GOTO EXIT_BATCH

IF EXISTS(SELECT [ID] FROM LogSite WHERE [MachineName] = @MachineName 
  AND [SiteIndex] = @SiteIndex)
  BEGIN
    SELECT @tmpID = [ID] FROM LogSite WHERE [MachineName] = @MachineName 
      AND [SiteIndex] = @SiteIndex
    GOTO EXIT_BATCH
  END

IF (@SiteID IS NULL)
  SET @tmpID = NEWID()
ELSE
  SET @tmpID = @SiteID

INSERT INTO [LogSite] ([ID], [MachineName], [SiteIndex])
  VALUES (@tmpID, @MachineName, @SiteIndex)

SET @TableName = @MachineName + N'W3SVC' + CONVERT(nvarchar(5), 
  @SiteIndex)

-- This will dynamically create the table
-- used to hold individual log entries.
IF EXISTS(SELECT * FROM dbo.sysobjects WHERE [ID] = OBJECT_ID(@TableName)
  AND OBJECTPROPERTY([ID], N'IsUserTable') = 1)
  BEGIN
    SET @SQLStatement = N'DROP TABLE ' + @TableName
    EXECUTE sp_executesql @SQLStatement
    SET @SQLStatement = N'DROP VIEW vw_' + @TableName
    EXECUTE sp_executesql @SQLStatement
  END

-- Dynamically construct our SQL statement
SET @SQLStatement = N'CREATE TABLE [' + @TableName + '] (
  [ID] uniqueidentifier ROWGUIDCOL NOT NULL PRIMARY KEY CLUSTERED
  DEFAULT (NEWID()), [Source] [uniqueidentifier] NOT NULL FOREIGN
  KEY REFERENCES LogSource ([ID]) ON DELETE CASCADE, [date-time]
  datetime NULL, [c-ip] int NULL, [cs-username] nvarchar(36) NULL,
  [s-sitename] varchar(12) NULL, [s-computername] nvarchar(15) NULL,
  [s-ip] int NULL, [s-port] int NULL, [cs-method] varchar(10) NULL,
  [cs-uri-stem] uniqueidentifier NULL FOREIGN KEY REFERENCES URIStem
  ([ID]), [cs-uri-query] uniqueidentifier NULL FOREIGN KEY REFERENCES
  URIQuery ([ID]), [sc-status] smallint NULL, [sc-win32-status] int NULL,
  [sc-bytes] int NULL, [cs-bytes] int NULL, [time-taken] int NULL,
  [cs-version] varchar(20) NULL, [cs-host] uniqueidentifier NULL FOREIGN
  KEY REFERENCES Host ([ID]), [cs(User-Agent)] uniqueidentifier NULL
  FOREIGN KEY REFERENCES UserAgent ([ID]), [cs(Cookie)]
  uniqueidentifier NULL FOREIGN KEY REFERENCES Cookie ([ID]),
  [cs(Referer)] uniqueidentifier NULL FOREIGN KEY REFERENCES Referer
  ([ID])) ON [PRIMARY]'

EXECUTE sp_executesql @SQLStatement

SET @SQLStatement = 'CREATE VIEW vw_' + @TableName + ' AS SELECT
  [ID], [date-time], CONVERT(varchar(15), CONVERT(varchar(3), [c-ip]
  & 255) + ''.'' + CONVERT(varchar(3), (([c-ip] & 65280) / 256)) + 
  ''.'' + CONVERT(varchar(3), (([c-ip] & 16711680) / 65536)) + ''.'' + 
  CONVERT(varchar(3), (([c-ip] & 2130706432) / 16777216) + ((CONVERT(
  smallint, SIGN([c-ip])) & -32768) / -32768) * 128)) AS [c-ip], 
  [cs-username], [s-sitename], [s-computername], CONVERT(varchar(15), 
  CONVERT(varchar(3), [s-ip] & 255) + ''.'' + CONVERT(varchar(3), ((
  [s-ip] & 65280) / 256)) + ''.'' + CONVERT(varchar(3), (([s-ip] & 
  16711680) / 65536)) + ''.'' + CONVERT(varchar(3), (([s-ip] & 
  2130706432) / 16777216) + ((CONVERT(smallint, SIGN([s-ip])) & 
  -32768) / -32768) * 128)) AS [s-ip], [s-port], [cs-method], 
  [cs-uri-stem], [cs-uri-query], [sc-status], [sc-win32-status], 
  [sc-bytes], [cs-bytes], [time-taken], [cs-version], [cs-host], 
  [cs(User-Agent)], [cs(Cookie)], [cs(Referer)] FROM ' + @TableName

EXECUTE sp_executesql @SQLStatement

EXIT_BATCH:
IF (@@ERROR <> 0)
  BEGIN
    ROLLBACK TRAN
    SET @SiteID = NULL
  END
ELSE
  BEGIN
    COMMIT TRAN
    SET @SiteID = @tmpID
  END
