Inventory Project Link

Press Me

Ready only view in sql server

create view view1 as
select id,name,sal from emp union all
select 0,'x',1 where 1=0

use of PopupControlExtender in asp.net

Design the page like this

<%@ Control Language="C#" AutoEventWireup="true" CodeFile="WebUserControl.ascx.cs"
    Inherits="WebUserControl" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>
<script language="javascript" type="text/javascript">
    function x()
     {
        var m = document.getElementById('<%=cbl.ClientID%>').getElementsByTagName('input');
        var p = document.getElementById('<%=cbl.ClientID%>').getElementsByTagName("label");
        var n = "";
        for (i = 0; i < m.length; i++)
            if (m[i].checked)
                n = n + p[i].innerHTML + ',';
        document.getElementById('<%=tb.ClientID%>').value = n.substring(0,(parseInt(n.length)-parseInt(1)) );

    }
</script>
<table>
    <tr>
        <td>
            <asp:TextBox ID="tb" runat="server" Width="200"></asp:TextBox><asp:ImageButton ID="imb"
                runat="server" />
            <asp:PopupControlExtender Position="Bottom" PopupControlID="pn" TargetControlID="tb"
                ID="PopupControlExtender1" runat="server">
            </asp:PopupControlExtender>
        </td>
    </tr>
    <tr>
        <td>
            <asp:Panel ID="pn" runat="server" Width="204" BackColor="Silver" BorderWidth="1"
                BorderColor="Gray">
                <asp:CheckBoxList ID="cbl" runat="server" Width="204" Height="150" onclick="x()">
                </asp:CheckBoxList>
            </asp:Panel>
        </td>
    </tr>
</table>

C# code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class WebUserControl : System.Web.UI.UserControl
{
    DataClassesDataContext o = new DataClassesDataContext();
    protected void Page_Load(object sender, EventArgs e)
    {
        var m = from x in o.BRANs select x;
        cbl.DataSource = m;
        cbl.DataTextField = "name";
        cbl.DataValueField = "bid";
        cbl.DataBind();
    }
}

how to find text of a check box list in aps.net using javascript

<asp:CheckBoxList ID="CheckBoxList1" runat="server" >

    <asp:ListItem Text = "One" Value = "1"></asp:ListItem>

    <asp:ListItem Text = "Two" Value = "2"></asp:ListItem>

    <asp:ListItem Text = "Three" Value = "3"></asp:ListItem>

</asp:CheckBoxList>

Concept

The ASP.Net CheckBoxList Control when rendered Client Side page it is rendered as HTML Table with CheckBoxes in it. Refer the figure below.

Hence we will need to write a script which will loop through all the controls (CheckBoxes) in the generated HTML table in order to validate or get the selected Item

Now when you do a research of the HTML Source you get the following

You can see above in the yellow mark that the Text is rendered between <label> tags.

        

Validating CheckBoxList

Below is the JavaScript function to perform validations in an ASP.Net CheckBoxList control.

<script type = "text/javascript">

var atLeast = 1

function Validate()

{

   var CHK = document.getElementById("<%=CheckBoxList1.ClientID%>");

   var checkbox = CHK.getElementsByTagName("input");

   var counter=0;

   for (var i=0;i<checkbox.length;i++)

   {

       if (checkbox[i].checked)

       {

            counter++;

       }

   }

   if(atLeast>counter)

   {

        alert("Please select atleast " + atLeast +  " item(s)");

        return false;

   }

   return true;

}

</script>

You will notice I am simply looping through all the input controls (CheckBoxes) and checking whether it is checked and incrementing the counter variable. Finally I match its value with the atLeast variable. If the atLeast value is greater than the counter variable it prompts the user to select the required number of items when user selects an item the page is submitted

The above JavaScript method is called on the click of a button given below

<asp:Button ID="Button1" runat="server" Text="Validate" OnClientClick =

"return Validate()" />

Getting SelectedText and SelectedValue

The following JavaScript function is used to get the Selected Item of the ASP.Net CheckBoxList Control.

           

<script type = "text/javascript">

function GetSelectedItem()

{

   var CHK = document.getElementById("<%=CheckBoxList1.ClientID%>");

   var checkbox = CHK.getElementsByTagName("input");

   var label = CHK.getElementsByTagName("label");

   for (var i=0;i<checkbox.length;i++)

   {

       if (checkbox[i].checked)

       {

           alert("Selected = " + label[i].innerHTML);

       }

   }

   return false;

}

</script> 

As you can see above in this case I am also looking for label tag along with input since the label tag has the Text part enclosed in it.

The above JavaScript function is called on the click event of button given below

<asp:Button ID="Button2" runat="server" Text="SelectedItem" OnClientClick

 = "return GetSelectedItem()" />

FREE PROJECT DOCUMENT DOWNLOAD SITE

Press Me

Create Scheduling Jobs in SQL Server Express

ScheduledJobs Table

This holds information about our scheduled jobs such as job name, enabled status, etc...

CREATE TABLE ScheduledJobs
(
    ID INT IDENTITY(1,1), 
    JobScheduleId INT NOT NULL,
    ConversationHandle UNIQUEIDENTIFIER NULL,
    JobName NVARCHAR(256) NOT NULL DEFAULT (''),
    ValidFrom DATETIME NOT NULL,
    LastRunOn DATETIME, 
    NextRunOn DATETIME, 
    IsEnabled BIT NOT NULL DEFAULT (0),
    CreatedOn DATETIME NOT NULL DEFAULT GETUTCDATE()
)

ScheduledJobSteps Table

This holds the job step name, the SQL statement to run in the step, whether to retry the step on failure and how many times, step duration, etc...

CREATE TABLE ScheduledJobSteps
(
    ID INT IDENTITY(1,1),
    ScheduledJobId INT NOT NULL,    
    StepName NVARCHAR(256) NOT NULL DEFAULT (''), 
    SqlToRun NVARCHAR(MAX) NOT NULL, -- sql statement to run
    RetryOnFail BIT NOT NULL DEFAULT (0), -- do we wish to retry the job step on failure
    RetryOnFailTimes INT NOT NULL DEFAULT (0), -- if we do how many times do we wish to retry it
    DurationInSeconds DECIMAL(14,4) DEFAULT (0), -- duration of the step with all retries 
    CreatedOn DATETIME NOT NULL DEFAULT GETUTCDATE(),
    LastExecutedOn DATETIME
)

JobSchedules Table

This holds the job's schedule. Multiple jobs can have the same schedule. Here we specify an absolute or relative scheduling frequency. This is mimicking most of the SQL Server Agent scheduling options. For weekly scheduling number 1 is Monday and number 7 is Sunday. This is because the ISO standard says that a week starts on Monday.

CREATE TABLE JobSchedules
(
    ID INT IDENTITY(1, 1) PRIMARY KEY,
    FrequencyType INT NOT NULL CHECK (FrequencyType IN (1, 2, 3)), 
        -- daily = 1, weekly = 2, monthly = 3. "Run once" jobs don't have a job schedule 
    Frequency INT NOT NULL DEFAULT(1) CHECK (Frequency BETWEEN 1 AND 100),
    AbsoluteSubFrequency VARCHAR(100), 
        -- '' if daily, '1,2,3,4,5,6,7' day of week if weekly, '1,2,3,...,28,29,30,31' if montly    
    MontlyRelativeSubFrequencyWhich INT, 
    MontlyRelativeSubFrequencyWhat INT,
    RunAtInSecondsFromMidnight INT NOT NULL DEFAULT(0) 
        CHECK (RunAtInSecondsFromMidnight BETWEEN 0 AND 84599), -- 0-84599 = 1 day in seconds
    CONSTRAINT CK_AbsoluteSubFrequency CHECK 
        ((FrequencyType = 1 AND ISNULL(AbsoluteSubFrequency, '') = '') OR -- daily check
         (FrequencyType = 2 AND LEN(AbsoluteSubFrequency) > 0) OR -- weekly check (days of week CSV)
         (FrequencyType = 3 AND (LEN(AbsoluteSubFrequency) > 0 -- monthly absolute option (days of month CSV)
             AND MontlyRelativeSubFrequencyWhich IS NULL 
             AND MontlyRelativeSubFrequencyWhat IS NULL)
             OR ISNULL(AbsoluteSubFrequency, '') = '') -- monthly relative option
                ), 
    CONSTRAINT MontlyRelativeSubFrequencyWhich CHECK -- only allow values if frequency type is monthly
         (MontlyRelativeSubFrequencyWhich IS NULL OR 
           (FrequencyType = 3 AND 
            AbsoluteSubFrequency IS NULL AND 
            MontlyRelativeSubFrequencyWhich IN (1,2,3,4,5)) -- 1st-4th, 5=Last
                                              ), 
    CONSTRAINT MontlyRelativeSubFrequencyWhat CHECK  -- only allow values if frequency type is monthly
      (MontlyRelativeSubFrequencyWhich IS NULL OR 
       (FrequencyType = 3 AND 
        AbsoluteSubFrequency IS NULL AND
        MontlyRelativeSubFrequencyWhich IN (1,2,3,4,5,6,7,-1)) -- 1=Mon to 7=Sun, -1=Day
                                              )
)

For monthly relative scheduling you can easily set the first/second/third/fourth/last day of the week or of the month.

SchedulingErrors Table

This table contains information about any errors that have happened in our job execution. Once a job errors out it will stop without any further execution.

CREATE TABLE SchedulingErrors
(
    Id INT IDENTITY(1, 1) PRIMARY KEY,
    ScheduledJobId INT, 
    ScheduledJobStepId INT,    
    ErrorLine INT,
    ErrorNumber INT,
    ErrorMessage NVARCHAR(MAX),
    ErrorSeverity INT,
    ErrorState INT,    
    ErrorDate DATETIME NOT NULL DEFAULT GETUTCDATE()
)

Required User-Defined Functions

dbo.GetNextRunTime

This calculates the next time our job will be run based on the schedule we created and the last run time or last ValidFrom time of the job. In the function we use an excellent calendar table F_TABLE_DATE and a user defined function called dbo.F_ISO_WEEK_OF_YEAR that gets the ISO Week number for a date. Both were created by Michael Valentine Jones, a regular on the SQLTeam.com forums. The GetNextRunTime function is quite complex but the comments in code should provide enough information for complete understanding. If they don't, ask questions in the comments.
The following code shows the part of the UDF that calculates the next date to run for the daily scheduling type:

-- ...  
-- DAILY SCHEDULE TYPE
IF @FrequencyType = 1
BEGIN        
    SELECT TOP 1 @NextRunTime = DATE
    FROM (
            SELECT  DATEADD(s, @RunAtInSecondsFromMidnight, DATE) 
                AS DATE, ROW_NUMBER() OVER(ORDER BY DATE) - 1 AS CorrectDaySelector
            FROM    dbo.F_TABLE_DATE(@LastRunTime, DATEADD(d, 2*@Frequency, @LastRunTime))
         ) t
    WHERE   DATE > @LastRunTime
            AND CorrectDaySelector % @Frequency = 0
    ORDER BY DATE
END
-- ...

Required Stored Procedures

These stored procedures make a nice and friendly interface to the scheduling functionality.

• usp_AddJobSchedule: Adds a new job schedule.
• usp_RemoveJobSchedule: Removes an existing job schedule.
• usp_AddScheduledJob: Adds a new scheduled job. For "Run once" job types we don't need a Job Schedule so for @JobScheduleId parameter we have to pass -1 and for @NextRunOn we have to set a date in the future in UTC time.
• usp_RemoveScheduledJob: Removes an existing scheduled job.
• usp_AddScheduledJobStep: Adds a new scheduled job step for a job. It also calculates the next run time of the job.
• usp_RemoveScheduledJobStep: Removes an existing scheduled job step from a job.
• usp_StartScheduledJob: Used when starting a new or job or re-enabling an old disabled job by passing a new ValidFrom date and in the activation stored procedure to start the job anew for the next scheduled run.
• usp_StopScheduledJob: Stops the scheduled job run by ending the conversation for it and setting it to disabled.
• usp_RunScheduledJobSteps: Runs every job step and repeats it the set number of times if that option is enabled. After each successful job step execution the step duration time and last run on time is set.
• usp_RunScheduledJob:  Activation stored procedure that is NOT meant to be run by hand. You should run it by hand only for debugging, when your messages are left in the queue. The stored procedure receives the dialog timer message from the queue and finds the scheduled job that corresponds with its conversation handle. After it gets the Scheduled job it runs its Job steps, sets the dialog timer for the next scheduled value and updates the last run time of the job.

Scheduling Code Examples

DECLARE @JobScheduleId INT, 
 @ScheduledJobId INT, 
 @validFrom DATETIME, 
 @ScheduledJobStepId INT, 
 @secondsOffset INT, 
 @NextRunOn DATETIME

SELECT    @validFrom = GETUTCDATE(), -- the job is valid from current UTC time
         -- run the job 2 minutes after the validFrom time. 
         -- we need the offset in seconds from midnight of that day for all jobs
        @secondsOffset = 28800, -- set the job time time to 8 in the morning of the selected day
        @NextRunOn = DATEADD(n, 1, @validFrom) -- set next run for once only job to 1 minute from now

-- SIMPLE RUN ONCE SCHEDULING EXAMPLE
-- add new "run once" scheduled job 
EXEC usp_AddScheduledJob @ScheduledJobId OUT, -1, 'test job', @validFrom, @NextRunOn
-- add just one simple step for our job
EXEC usp_AddScheduledJobStep @ScheduledJobStepId OUT, @ScheduledJobId, 'EXEC sp_updatestats', 'step 1'
-- start the scheduled job
EXEC usp_StartScheduledJob @ScheduledJobId 

-- SIMPLE DAILY SCHEDULING EXAMPLE
-- run the job daily
EXEC usp_AddJobSchedule @JobScheduleId OUT,
                        @RunAtInSecondsFromMidnight = @secondsOffset,
                        @FrequencyType = 1,
                        @Frequency = 1 -- run every day                      
-- add new scheduled job 
EXEC usp_AddScheduledJob @ScheduledJobId OUT, @JobScheduleId, 'test job', @validFrom
DECLARE @backupSQL NVARCHAR(MAX)
SELECT  @backupSQL = N'DECLARE @backupTime DATETIME, @backupFile NVARCHAR(512); 
    SELECT @backupTime = GETDATE(), 
    @backupFile = ''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorks_'' + 
         replace(replace(CONVERT(NVARCHAR(25), @backupTime, 120), '' '', ''_''), '':'', ''_'') + 
            N''.bak''; 
    BACKUP DATABASE AdventureWorks TO DISK = @backupFile;'

EXEC usp_AddScheduledJobStep @ScheduledJobStepId OUT, @ScheduledJobId, @backupSQL, 'step 1'
-- start the scheduled job
EXEC usp_StartScheduledJob @ScheduledJobId 

-- COMPLEX WEEKLY ABSOLUTE SCHEDULING EXAMPLE
-- run the job on every tuesday, wednesday, friday and sunday of every second week
EXEC usp_AddJobSchedule @JobScheduleId OUT,
                        @RunAtInSecondsFromMidnight = @secondsOffset,
                        @FrequencyType = 2, -- weekly frequency type
                        @Frequency = 2, -- run every every 2 weeks,
                        @AbsoluteSubFrequency = '2,3,5,7' 
     -- run every Tuesday(2), Wednesday(3), Friday(5) and Sunday(7)    
-- add new scheduled job 
EXEC usp_AddScheduledJob @ScheduledJobId OUT, @JobScheduleId, 'test job', @validFrom
-- add three steps for our job
EXEC usp_AddScheduledJobStep @ScheduledJobStepId OUT, @ScheduledJobId, 'EXEC sp_updatestats', 'step 1'
EXEC usp_AddScheduledJobStep @ScheduledJobStepId OUT, @ScheduledJobId, 'DBCC CHECKDB', 'step 2'
EXEC usp_AddScheduledJobStep @ScheduledJobStepId OUT, @ScheduledJobId, 
  'select 1,', 'step 3 will fail', 1, 2 -- retry on fail 2 times
-- start the scheduled job
EXEC usp_StartScheduledJob @ScheduledJobId 

-- COMPLEX RELATIVE SCHEDULING SCHEDULING EXAMPLE
DECLARE @relativeWhichDay INT, @relativeWhatDay INT
SELECT    @relativeWhichDay = 4, -- 1 = First, 2 = Second, 3 = Third, 4 = Fourth, 5 = Last
        @relativeWhatDay = 3 -- 1 = Monday, 2 = Tuesday, ..., 7 = Sunday, -1 = Day
-- run the job on the 4th monday of every month 
EXEC usp_AddJobSchedule @JobScheduleId OUT,
                        @RunAtInSecondsFromMidnight = @secondsOffset, -- int
                        @FrequencyType = 3, -- monthly frequency type
                        @Frequency = 1, -- run every month,
                        @AbsoluteSubFrequency = NULL, -- no aboslute frequence if relative is set
                        @MontlyRelativeSubFrequencyWhich = @relativeWhichDay,
                        @MontlyRelativeSubFrequencyWhat = @relativeWhatDay
/*
some more relative monthly scheduling examples
run on:
the first day of the month:
  - @MontlyRelativeSubFrequencyWhich = 1, @MontlyRelativeSubFrequencyWhat = -1
the third thursday of the month:
  - @MontlyRelativeSubFrequencyWhich = 3, @MontlyRelativeSubFrequencyWhat = 4
the last sunday of the month:
  - @MontlyRelativeSubFrequencyWhich = 5, @MontlyRelativeSubFrequencyWhat = 7
the second wedensday of the month:
  - @MontlyRelativeSubFrequencyWhich = 2, @MontlyRelativeSubFrequencyWhat = 3
*/
-- add new scheduled job 
EXEC usp_AddScheduledJob @ScheduledJobId OUT, @JobScheduleId, 'test job', @validFrom
-- add just one simple step for our job
EXEC usp_AddScheduledJobStep @ScheduledJobStepId OUT, @ScheduledJobId, 'EXEC sp_updatestats', 'step 1'
-- start the scheduled job
EXEC usp_StartScheduledJob @ScheduledJobId 

-- SEE WHAT GOING ON WITH OUR JOBS
-- show the currently active conversations
-- look at dialog_timer column (in UTC time) to see when will the job be run next
SELECT GETUTCDATE(), dialog_timer, * FROM sys.conversation_endpoints
-- shows the number of currently executing activation procedures
SELECT * FROM sys.dm_broker_activated_tasks
-- see how many unreceived messages are still in the queue. should be 0 when no jobs are running
SELECT * FROM ScheduledJobQueue WITH (NOLOCK)
-- view our scheduled jobs' statuses
SELECT * FROM ScheduledJobs  WITH (NOLOCK)
SELECT * FROM ScheduledJobSteps WITH (NOLOCK)
SELECT * FROM JobSchedules  WITH (NOLOCK)
SELECT * FROM SchedulingErrors WITH (NOLOCK)

how to create Scheduling Jobs in SQL Server Express

As we all know SQL Server 2005 Express is a very powerful free edition of SQL Server 2005. However it does not contain SQL Server Agent service. Because of this scheduling jobs is not possible. So if we want to do this we have to install a free or commercial 3rd party product. This usually isn't allowed due to the security policies of many hosting companies and thus presents a problem. Maybe we want to schedule daily backups, database reindexing, statistics updating, etc. This is why I wanted to have a solution based only on SQL Server 2005 Express and not dependent on the hosting company. And of course there is one based on our old friend the Service Broker.

New terminology

To achieve scheduling we will use SQL Server Service Broker. If you're not familiar with this great addition to the storage engine go read my previous three articles about it. There you'll get familiarized with the terminology and database objects used in this article. Done? OK, let's move on.
So you're familiar with services, queues, activation procedures, messages, contracts, conversations, etc... The new member we have to take a look at is the
Conversation Timer:

BEGIN CONVERSATION TIMER ( conversation_handle )
   TIMEOUT = timeoutInSeconds 
[ ; ]

When the conversation timer is set it waits the number of seconds specified in the timeout and then it sends the http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer message to the local queue that is the part of a conversation. It never sends the message to the remote queue. As the DialogTimer message comes to the queue, the activation stored procedure associated with the queue fires, receives the message from the queue and executes whatever logic we have programmed it to.
Don't mistake the conversation timer for the conversation lifetime! Each part of the conversation can have a different conversation timer set while the conversation lifetime is the time from the beginning to the end of the conversation.

How it works

Let's see how this scheduling infrastructure is built from start in simple bullet points:
1. Create the needed tables for our scheduled jobs information
2. Create the needed stored procedures that handle scheduled jobs
3. Create the needed contract, queue and service

1. Needed tables

We need two tables:
- ScheduledJobs stores information about our scheduled jobs
- ScheduledJobsErrors stores possible errors when manipulating scheduled jobs

CREATE TABLE ScheduledJobs
(
    ID INT IDENTITY(1,1), 
    ScheduledSql nvarchar(max) NOT NULL, 
    FirstRunOn datetime NOT NULL, 
    LastRunOn datetime, 
    LastRunOK BIT NOT NULL DEFAULT (0), 
    IsRepeatable BIT NOT NULL DEFAULT (0), 
    IsEnabled BIT NOT NULL DEFAULT (0), 
    ConversationHandle uniqueidentifier NULL

)

CREATE TABLE ScheduledJobsErrors
(
    Id BIGINT IDENTITY(1, 1) PRIMARY KEY,
    ErrorLine INT,
    ErrorNumber INT,
    ErrorMessage NVARCHAR(MAX),
    ErrorSeverity INT,
    ErrorState INT,
    ScheduledJobId INT,
    ErrorDate DATETIME NOT NULL DEFAULT GETUTCDATE()
)

2. Needed stored procedures

For our simple scheduling we need three stored procedures. Only the pieces of code are shown here so look at the accompanying script for full code.
First two expose the scheduling functionality we use. The third one isn't supposed to be used directly but it can be if it is needed.
- usp_AddScheduledJob adds a row for our job to the ScheduledJobs table, starts a new conversation on it and set a timer on it. Adding and conversation starting is done in a transaction since we want this to be an atomic operation.

INSERT INTO ScheduledJobs(ScheduledSql, FirstRunOn, IsRepeatable, ConversationHandle)
VALUES (@ScheduledSql, @FirstRunOn, @IsRepeatable, NULL)

SELECT @ScheduledJobId = SCOPE_IDENTITY()

...

BEGIN DIALOG CONVERSATION @ConversationHandle
    FROM SERVICE   [//ScheduledJobService]

    TO SERVICE      '//ScheduledJobService', 
                    'CURRENT DATABASE'
    ON CONTRACT     [//ScheduledJobContract]

    WITH ENCRYPTION = OFF;

BEGIN CONVERSATION TIMER (@ConversationHandle)
TIMEOUT = @TimeoutInSeconds;

- usp_RemoveScheduledJob performs cleanup. It accepts the id of the scheduled job we wish to remove. It ends the conversation that the inputted scheduled job lives on, and it deletes the row from the ScheduledJobs table. Removing the job and ending the conversation is also done in a transaction as an atomic operation.

IF EXISTS (SELECT * 
        FROM sys.conversation_endpoints 
        WHERE conversation_handle = @ConversationHandle)
    END CONVERSATION @ConversationHandle

DELETE ScheduledJobs WHERE Id = @ScheduledJobId 

- usp_RunScheduledJob is the activation stored procedure on the queue and it receives the dialog timer messages put there by our conversation timer from the queue. Depending on the IsRepeatable setting it either sets the daily interval or ends the conversation. After that it runs our scheduled job and updates the ScheduledJobs table with the status of the finished scheduled job. This stored procedure isn't transactional since any errors are stored in the error table and we don't want to return the DialogTimer message back to the queue, which would cause problems with looping and poison messages which we'd have to again handle separately. We want to keep things simple.

RECEIVE TOP(1) 
        @ConversationHandle = conversation_handle,
        @message_type_name = message_type_name
FROM ScheduledJobQueue

... 


SELECT  @ScheduledJobId = ID, 
       @ScheduledSql = ScheduledSql, 
       @IsRepeatable = IsRepeatable

FROM    ScheduledJobs 
WHERE   ConversationHandle = @ConversationHandle AND IsEnabled = 1

...

-- run our job
EXEC (@ScheduledSql)

3. Needed Service Broker objects

For everything to work we need to make a simple setup used by the Service Broker:
- [//ScheduledJobContract] is the contract that allows only sending of the "http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer" message type.
- ScheduledJobQueue is the queue we use to post our DialogTimer messages to and run the usp_RunScheduledJob activation procedure that runs the scheduled job.
- [//ScheduledJobService] is a service set on top of the ScheduledJobQueue and bound by the [//ScheduledJobContract] contract.

CREATE CONTRACT [//ScheduledJobContract]

    ([http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer] SENT BY INITIATOR)

CREATE QUEUE ScheduledJobQueue 
    WITH STATUS = ON, 
    ACTIVATION (    
        PROCEDURE_NAME = usp_RunScheduledJob,
        MAX_QUEUE_READERS = 20, -- we expect max 20 jobs to start simultaneously

        EXECUTE AS 'dbo' );

CREATE SERVICE [//ScheduledJobService] 
    AUTHORIZATION dbo
    ON QUEUE ScheduledJobQueue ([//ScheduledJobContract])

4.Tying it all together

Now that we have created all our objects let's see how they all work together.
First we have to have a valid SQL statement that we'll run as a scheduled job either daily or only once. We can add it to or remove it from the ScheduldJobs table by using our usp_AddScheduledJob stored procedure. This procedure starts a new conversation and links it to our scheduled job. After that it sets the conversation timer to elapse at the date and time we want our job to run.
At this point we have our scheduled job lying nicely in a table and a timer that will run it at our time. When the scheduled time comes the dialog timer fires and service broker puts a DialogTimer message into the ScheduledJobQueue. The queue has an activation stored procedure usp_RunScheduledJob associated with it which runs every time a new message arrives to the queue.
This activation stored procedure then receives our DialogTimer message from the queue, uses the conversation handle that comes with the message and looks up the job associated with that conversation handle. If our job is a run only once type it ends the conversation else it resets the timer to fire again in 24 hours. After that it runs our job. When the job finishes (either succeeds or fails) the status is written back to the ScheduledJobs table. And that's it.
We can also manually remove the job at any time with the usp_RemoveScheduledJob stored procedure that ends the conversation and its timer from our job and then deletes a row from the ScheduledJobs table.
The whole infrastructure is quite simple and low maintenance.

5. How to Schedule Jobs - Example

Here is an example with three scheduled jobs: a daily backup job of our test database, a faulty script and a one time update of statistics. All are run 30 seconds after you add them with the usp_AddScheduledJob stored procedure.

GO
DECLARE @ScheduledSql nvarchar(max), @RunOn datetime, @IsRepeatable BIT

SELECT  @ScheduledSql = N'DECLARE @backupTime DATETIME, @backupFile NVARCHAR(512); 
    SELECT @backupTime = GETDATE(), 
           @backupFile = ''C:\TestScheduledJobs_'' + 
                replace(replace(CONVERT(NVARCHAR(25), @backupTime, 120), 
                '' '', ''_''), '':'', ''_'') + N''.bak''; 
           BACKUP DATABASE TestScheduledJobs TO DISK = @backupFile;',
        @RunOn = dateadd(s, 30, getdate()), 
        @IsRepeatable = 0

EXEC usp_AddScheduledJob @ScheduledSql, @RunOn, @IsRepeatable
GO

DECLARE @ScheduledSql nvarchar(max), @RunOn datetime, @IsRepeatable BIT

SELECT  @ScheduledSql = N'select 1, where 1=1',
        @RunOn = dateadd(s, 30, getdate()), 
        @IsRepeatable = 1

EXEC usp_AddScheduledJob @ScheduledSql, @RunOn, @IsRepeatable
GO

DECLARE @ScheduledSql nvarchar(max), @RunOn datetime, @IsRepeatable BIT

SELECT  @ScheduledSql = N'EXEC sp_updatestats;', 
        @RunOn = dateadd(s, 30, getdate()), 
        @IsRepeatable = 0

EXEC usp_AddScheduledJob @ScheduledSql, @RunOn, @IsRepeatable
GO

6. Monitoring

We can monitor our scheduled jobs' conversations currently being processed and their success by running these queries:

-- show the currently active conversations. 

-- Look at dialog_timer column (in UTC time) to see when will the job be run next
SELECT * FROM sys.conversation_endpoints
-- shows the number of currently executing activation procedures
SELECT * FROM sys.dm_broker_activated_tasks

-- see how many unreceived messages are still in the queue. 
-- should be 0 when no jobs are running
SELECT * FROM ScheduledJobQueue with (nolock)

-- view our scheduled jobs' statuses
SELECT * FROM ScheduledJobs  with (nolock)
-- view any scheduled jobs errors that might have happend

SELECT * FROM ScheduledJobsErrors  with (nolock)