Av rating:
Total votes: 16
Total comments: 16

 TheBuildScript
 send to a friend
 printer friendly version

/*
Here is a way of designing part of a SQL Server Database application so
it seems faster to the user. 

Often, when you are designing a database-intensive application or website,
you will often merely want the application layer to signal that a job should
be started, and return back immediately without blocking. How nice to have
a secure system that buffers up routines that aren't time-critical and
irons out peaks in demand, all without having to use Service Broker.

This is done by delegating processes that  aren't time-critical to 
another database connection. As a by-product,  you can then tighten up 
security, so that back-office functions are never done by user logins. 

The technique is to delegate as much processing as possible on to another
'back-office' process under a different connection login via a queue. This
is quite a commonly-used technique, and uses the Alerts and Jobs within
SQL Server Agent.

In this Workshop, Phil and I will show how it is done, using user-defined 
alerts in either SQL Server 2000 or SQL Server 2005. It is, of course, 
more convenient to use SSMS interactively to set up alerts and jobs but here
we'll script them so you can try it out more quickly and easily. 

For the technical background to User-Defined Alerts, you might like to read
SQL Server 2000: User Settable Object (Catchy title?) or The SQL 2005 version

Here is a skeletal system that is designed for the dispatch of email 
information from a database-driven website. It can be used for almost any 
time-intensive or security-sensitive process that does not require the 
immediate return of the results to the user, such as processing an invoice, 
printing out a dispatch note, doing a trade confirmation or trade settlement, 
resizing an uploaded image, or indexing text.

the first thing we must do is to create the log, queue and queue parameter
table. The log is necessary to tell us how the queue is getting on, and to
contain errors and warnings. we need a queue parmeter table because each
process that we add will have different parameters.


The steps in the process:
 . The user calls a function to send an email
 . The function puts an entry in the queue table to say he wants an email sent
 . the procedure puts the parameters into a paramter table.
 . The procedure sets a user-counter to 1 to fire an alert
 . the user returns
 . the alert fires and runs a job
 . The job step runs a procedure that empties the queue, running every task
       that has been put in the queue, including sending the email


Firstly, you must create a database called BackOfficeDemo


Then run this script....(should run in either SQL 2000 or SQL 2005

*/
--------------------------------Start of build script

USE BackOfficeDemo


SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, 
               ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects 
       WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error INT)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
PRINT N'Creating login ''TypicalUser'''
/* first off, we create our user with the minimal permissions*/
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins 
               WHERE loginname = N'TypicalUser')
   EXEC sp_addlogin N'TypicalUser', 'typical'
GO  --we'll use him to make the request in the demo
--and now we'll grant him access to the database
IF NOT EXISTS (SELECT sid FROM sysusers WHERE sid = 
   (SELECT sid FROM master.dbo.syslogins
       WHERE isntname = 0 
           AND loginname = 'TypicalUser') AND hasdbaccess = 1)
   EXEC sp_grantdbaccess N'TypicalUser', N'TypicalUser'
GO
BEGIN TRANSACTION
GO
PRINT N'Creating [dbo].[log]'
/* firrstly we create a log table that we will use to see the progress
of the procedure, and report errors etc. */
GO
IF EXISTS (SELECT * FROM sysobjects --delete it if it exists
       WHERE id=OBJECT_ID('log')) 
               DROP TABLE [Log]

CREATE TABLE [dbo].[log]
(/* this table is used to track what happens. we'll be able to see
what happens, when, using which login*/
[log_ID] [int] NOT NULL IDENTITY(1, 1),
[type] [int] NOT NULL,
[LogString] [varchar] (7000)  NOT NULL,
[InsertionDate] [datetime] NOT NULL 
   CONSTRAINT [DF_log_InsertionDate] DEFAULT (GETDATE()),
[User_ID] [int] NULL,
[CurrentUser] [nvarchar] (50)  NOT NULL 
   CONSTRAINT [DF_log_CurrentUser] 
           DEFAULT (CONVERT(NVARCHAR(50),USER_NAME())),
[SPid] INT  NOT NULL 
   CONSTRAINT [DF_log_SPid] 
           DEFAULT (@@SPid),
[LoginName] [nvarchar] (50)  NOT NULL 
   CONSTRAINT [DF_Log_LoginName] 
           DEFAULT (CONVERT(NVARCHAR(50),SUSER_SNAME())),
/* normally, you will also store the key to the user's session for audit 
purposes, particularly on a website where all users will be using the 
same login */
[Comment] [varchar] (30)  NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) 
                           SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_log] on [dbo].[log]'
GO
ALTER TABLE [dbo].[log] 
   ADD CONSTRAINT [PK_log] PRIMARY KEY CLUSTERED  ([log_ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) 
   SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[QueuedProcess]'
/*
Here we have a very simple queue, so we can pass requests to the 
'backoffice' process.
*/
GO
IF EXISTS (SELECT * FROM sysobjects 
       WHERE id=OBJECT_ID('QueuedProcess')) DROP TABLE [QueuedProcess]
CREATE TABLE [dbo].[QueuedProcess]
(
[QueuedProcess_ID] [int] NOT NULL IDENTITY(1, 1),
[insertionDate] [datetime] NOT NULL 
   CONSTRAINT [DF_QueuedProcess_insertionDate] 
       DEFAULT (GETDATE()),--the time the task was inserted
[CompletionDate] [datetime] NULL,--when the job was done
[Process] [int] NOT NULL --this is just an integer for now (normally FK)
   CONSTRAINT [DF_QueuedProcess_Process] DEFAULT (1),
[Comment/error] [varchar] (2000)  NULL--in case..(useful for debugging)
)

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 
   BEGIN 
   INSERT INTO #tmpErrors (Error) SELECT 1 
   BEGIN TRANSACTION 
   END
GO
PRINT N'Creating primary key [PK_QueuedProcess] on [dbo].[QueuedProcess]'
GO
ALTER TABLE [dbo].[QueuedProcess] 
   ADD CONSTRAINT [PK_QueuedProcess] 
       PRIMARY KEY CLUSTERED  ([QueuedProcess_ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 
   BEGIN 
   INSERT INTO #tmpErrors (Error) SELECT 1 
   BEGIN TRANSACTION 
   END
GO
PRINT N'Creating [dbo].[QueuedProcessParameter]'
GO
/*
Because processes can have different parameters, we'll place them in a 
separate table. This allows us to use the same queuing mechanism for a 
lot of different processes
*/
IF EXISTS (SELECT * FROM sysobjects 
       WHERE id=OBJECT_ID('QueuedProcessParameter')) 
               DROP TABLE [QueuedProcessParameter]
CREATE TABLE [dbo].[QueuedProcessParameter]
(
[QueuedProcessParameter_ID] [int] NOT NULL IDENTITY(1, 1),
[QueuedProcess_ID] [int] NOT NULL,
[parameter] [varchar] (50) NULL,--simple name of parameter
--this would normally be a FK and you'd be able to check... 
[value] [sql_variant] NULL--the actual value passed
)

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) 
                                   SELECT 1 BEGIN TRANSACTION END

PRINT N'Creating [dbo].[spEmailSomething]'
GO
IF EXISTS (SELECT * FROM sysobjects 
       WHERE id=OBJECT_ID('spEmailSomething')) 
               DROP PROCEDURE [spEmailSomething]

/* now we have the basic tables, we'' write a dummy procedure that
would ultimately run the task you wanted. It is dummy so as to 
allow us to debug and check the plumbing. This is later fleshed 
out to provide the functionality.*/
GO
CREATE PROCEDURE spEmailSomething (@queuedProcess_ID INT)
AS
DECLARE @Parameters VARCHAR(8000)
DECLARE @Rowcount INT

SELECT @parameters=COALESCE(@parameters,'')
                   +parameter+'='+CONVERT(VARCHAR(2000),value)+', '
FROM QueuedProcessParameter 
WHERE queuedProcess_ID=@QueuedProcess_ID
IF @rowcount=0
   BEGIN
   INSERT INTO [log] ([type], logstring)
       SELECT 23,'no parameters sent to spEmailSomething for process '
                               +CONVERT(VARCHAR(10),@QueuedProcess_ID)
   RETURN 1
   END
INSERT INTO [log] ([type], logstring)
   SELECT 21,'sending email: '+@parameters 
       +' for queued process '+CONVERT(VARCHAR(10),@QueuedProcess_ID)
/* Here we add the code to actually send the email */
INSERT INTO [log] ([type], logstring)
   SELECT 22,'Successful email sent for queued process '
       +CONVERT(VARCHAR(10),@QueuedProcess_ID)
RETURN 0

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) 
       SELECT 1 BEGIN TRANSACTION END
GO

PRINT N'Creating [dbo].[spExecuteQueuedActions]'
GO
IF EXISTS (SELECT * FROM sysobjects 
       WHERE id=OBJECT_ID('spExecuteQueuedActions')) 
               DROP PROCEDURE [spExecuteQueuedActions]
GO
/* the next job is the routine that services the queue. This will actually be
run by the agent and you will be able to assign a login to the task. It will
be allowed to do heap powerful magic. At the moment it will just write out
diagnostic information. Note that it will need to empty the entire queue as we
will prevent the alert triggering more than a certain number of times */
CREATE PROCEDURE spExecuteQueuedActions
AS
DECLARE @error INT
DECLARE @rc INT
DECLARE @nextprocess_ID INT
DECLARE @process INT
EXECUTE sp_user_counter1 0   --set the user counter to zero
--whatever we do, we want to prevent premature reentrancy. The agent task
--will only run once, and will not run again until the first one finishes
WHILE 1=1 --for ever until a break
   BEGIN
   SELECT TOP 1 @nextProcess_ID=queuedProcess_ID,@process=process
      FROM queuedProcess WHERE completionDate IS NULL
      SELECT @error = @@error, @rc = @@rowcount

   IF (@rc=0 OR @error<>0) BREAK    

   UPDATE queuedProcess SET completionDate=GETDATE() 
       WHERE queuedprocess_ID=@nextProcess_ID
   SELECT @error=@@Error
   IF @Error<>0 BREAK
   IF @process=1
       BEGIN
       EXECUTE @error=spEmailSomething @nextProcess_ID
       END
   --else if @process=2 execute spAnotherprocess
   --else if @process=3 execute spYetAnotherprocess
   ELSE
   INSERT INTO [log] ([type], logstring)
       SELECT 41,'Unknown process '+CONVERT(VARCHAR(10),@process)
   IF @error=0 SELECT @error=@@Error
       IF @Error<>0 BREAK
   END
IF @error<>0--if there has been an error it will appear in the
   BEGIN   --agent history but it is nice to log it too
   INSERT INTO [log] ([type], logstring)
       SELECT 41,'Error '+CONVERT(VARCHAR(10),@error)
   END
--because all the work is done
INSERT INTO [log] ([type], logstring)
       SELECT 43,'Completed all tasks'

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0
   BEGIN 
   INSERT INTO #tmpErrors (Error) 
       SELECT 1 BEGIN TRANSACTION 
   END
GO
PRINT N'Creating [dbo].[spSendAnEmail]'
GO
IF EXISTS (SELECT * FROM sysobjects 
       WHERE id=OBJECT_ID('spSendAnEmail')) 
               DROP PROCEDURE [spSendAnEmail]

GO
CREATE PROCEDURE spSendAnEmail
/* this is the test procedure to be run by the application using the
'typicalUser' login */
@To VARCHAR(30),
@From  VARCHAR(30),
@cc VARCHAR(30)='',
@bcc VARCHAR(30)='',
@Body VARCHAR(6000),
@isHTML INT=0,
@Comment VARCHAR(2000)='Dispatch a test email'
AS

DECLARE @queuedProcess_ID INT    
INSERT INTO Queuedprocess (process, [Comment/Error]) SELECT 1,@comment
SET @queuedProcess_ID=@@Identity
INSERT INTO QueuedProcessParameter(QueuedProcess_ID,[Parameter],Value) 
   SELECT @queuedProcess_ID, 'To',@To
INSERT INTO QueuedProcessParameter(QueuedProcess_ID,[Parameter],Value) 
   SELECT @queuedProcess_ID, 'From',@From
INSERT INTO QueuedProcessParameter(QueuedProcess_ID,[Parameter],Value) 
   SELECT @queuedProcess_ID, 'CC',@CC
INSERT INTO QueuedProcessParameter(QueuedProcess_ID,[Parameter],Value) 
   SELECT @queuedProcess_ID, 'BCC',@BCC
INSERT INTO QueuedProcessParameter(QueuedProcess_ID,[Parameter],Value) 
   SELECT @queuedProcess_ID, 'Body',@Body
INSERT INTO QueuedProcessParameter(QueuedProcess_ID,[Parameter],Value) 
   SELECT @queuedProcess_ID, 'IsHTML',@IsHTML
INSERT INTO [log] ([type], logstring)
   SELECT 23,'Inserted job into the queue to send an email'
EXECUTE sp_user_counter1 1   

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
GRANT EXECUTE ON [dbo].[spSendAnEmail] TO [TypicalUser]
IF @@TRANCOUNT=0
   BEGIN INSERT INTO #tmpErrors (Error) 
               SELECT 1 
   BEGIN TRANSACTION 
   END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database build succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database build failed'
GO
DROP TABLE #tmpErrors
GO
/* now we create an agent Job that merely executes the stored procedure
 that execute all the queued jobs */
BEGIN TRANSACTION   
PRINT 'creating the job'
         
  DECLARE @JobID BINARY(16)  
  DECLARE @ReturnCode INT    
  SELECT @ReturnCode = 0     
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories 
                       WHERE name = N'[Uncategorized (Local)]') < 1 
  EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
  -- Delete the job with the same name (if it exists)
  SELECT @JobID = job_id     
  FROM   msdb.dbo.sysjobs    
  WHERE (name = N'EmptyJobQueue')       
  IF (@JobID IS NOT NULL)    
  BEGIN  
  -- Check if the job is a multi-server job  
  IF (EXISTS (SELECT  * 
              FROM    msdb.dbo.sysjobservers 
              WHERE   (job_id = @JobID) AND (server_id <> 0))) 
  BEGIN 
    -- There is, so abort the script 
    RAISERROR (N'Unable to import job ''EmptyJobQueue'' since there is 
already a multi-server job with this name.', 16, 1) 
    GOTO QuitWithRollback  
  END 
  ELSE 
    -- Delete the [local] job 
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'EmptyJobQueue' 
    SELECT @JobID = NULL
  END 


BEGIN 
DECLARE @Username VARCHAR(100)
SELECT @username=SUSER_SNAME()
-- Add the job itself to the Agent. This job will be scheduled to
-- run whenever our alert hits its criterion (i.e. when the user 
-- counter 1 is greater than zero
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job 
       @job_id = @JobID OUTPUT , 
       @job_name = N'EmptyJobQueue', 
       @owner_login_name = @username, 
       @description = N'Test of a Job that clears a job queue', 
       @category_name = N'[Uncategorized (Local)]', 
       @enabled = 1, 
       @notify_level_email = 0, 
       @notify_level_page = 0, 
       @notify_level_netsend = 0, 
       @notify_level_eventlog = 2, @delete_level= 0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
PRINT 'creating the job step'
  -- Add the job steps
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep 
       @job_id = @JobID, 
       @step_id = 1, 
       @step_name = N'Execute Queued Actions', 
       @command = N'execute spExecuteQueuedActions', 
       @database_name = N'BackOfficeDemo', 
       @server = '', 
       @database_user_name = N'dbo', 
       @subsystem = N'TSQL', 
       @cmdexec_success_code = 0, 
       @flags = 0, 
       @retry_attempts = 0, @retry_interval = 1, @output_file_name = '',
       @on_success_step_id = 0, @on_success_action = 1, 
       @on_fail_step_id = 0, @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, 
                                                   @start_step_id = 1 

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the Target Servers
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver 
       @job_id = @JobID, 
       @server_name = N'(local)' 
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

END
COMMIT TRANSACTION          
GOTO   EndSave              
QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave: 
PRINT 'creating the alert'

/* Now we will create the alert called 'ThereAreJobsInTheQueue'*/
---- Delete the alert with the same name.
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts 
                           WHERE name = N'ThereAreJobsInTheQueue'))
  EXECUTE msdb.dbo.sp_delete_alert @name = N'ThereAreJobsInTheQueue' 
BEGIN 
EXECUTE msdb.dbo.sp_add_alert 
   @name = N'ThereAreJobsInTheQueue', 
   @message_id = 0, 
   @severity = 0, @enabled = 1, 
   @delay_between_responses = 3, 
   @performance_condition 
           = N'SQLServer:User Settable|Query|User counter 1|>|0', 
   @include_event_description_in = 0, 
   @job_name = N'EmptyJobQueue', 
   @category_name = N'[Uncategorized]'
END

GO
---------------------End of the build script


--we can enable or disable this alert very simply with this procedure
msdb.dbo.sp_update_alert @name = N'ThereAreJobsInTheQueue',@enabled =1

/* now we log out, log back in as TypicalUser and run this procedure to 
sent an email (it should just write to the log in this test mode */
EXECUTE spSendAnEmail
   @To='PhilipFactor@Simpler-talk.com', 
   @From='RobynPage@Simpler-talk.com',
   @CC='TheEditor@Simpler-talk.com',
   @Body='This is tremendous! It really works.'

-- if we then look at the log .

GRANT SELECT ON [dbo].[log] TO [TypicalUser]--sudt so 'TyplicalUser' can
--look at it
SELECT type,LogString,InsertionDate,CurrentUser  
   FROM LOG ORDER BY insertionDate

--we'll see something like this..



/*if all has gone well, the spSendAnEmail procedure will have put a 
request in the queue which will have been picked up and executed by
the agent after a short period. Notice that the execution of the task
is done by DBO (we set it that way) rather than the TypicalUser (who
will have very few privileges) 

You will notice a latency before the Agent picks up the job, up to 
twenty seconds. This is because, in SQL Server 2000, the performance 
condition counters are only polled every twenty seconds.
As a belt-and-braces approach, one might want to have a scheduled task 
the same stored procedure that fires every minute ofr so, as it will do
no harm.*/

/* if it doesn't work 
. Make sure that the SQLServerAgent is running. 
. Check that you are using the latest SQL Server service pack. 
. Check the account the SQLServerAgent services runs under 
. Make sure the event is firing. If not, is it enabled?
. Check the alert properties to see the last time that the alert worked. 
. Check the SQL Server error log, and job history log */

/*
You can have up to ten of these mechanisms working independently, but 
We've never thought of a use for more than one per database.

However, User-defined counters are great for application -based figures
that need to be monitored as part of performance logging (such as 
people logged onto a site simultaneously, or average views per minute,
so don't use them up unnecessarily: they are too valuable.*/