Job scheduler in SQL Server 2000

Job scheduler in SQL Server 2000

To create a SQL job scheduler in Sql Server 2000 and run it automatically in a particular interval. For this, we need to follow the steps given below:

1. First we create one test database called 'KannanOrganization' in Sql Server 2000.
2. Then we create one test table namely 'Employee' with the following table schema.
   

   Table Name : Employee
   
   -----------------------------------------------------------------------
   
   Column Name                  DataType         Width    Dec   Allow Null
   
   ------------------------------------------------------------------------
   Id                           INT              4                 No, PK , Identity
   
   FirstName                    VARCHAR          50                Yes
   LastName                     VARCHAR          50                Yes
   Salary                       DECIMAL          8          0      Yes
   ------------------------------------------------------------------------
   See the below snaps for more information.
   
   
   

3. Then we need to create one store procedure for auto Sql Jjob test. For a sample, here I created one stored procedure called, 'InsertEmplyee' for testing SQL job.
   

   CREATE PROCEDURE [dbo].[InsertEmployee]
   AS
             INSERT Employee
                       (FirstName, LastName, Salary)
             VALUES
                       ('Kannan', 'Arjun', 17658.50)
   GO
   
   Now stored procedure is ready to use.
   
   
   

4. Next we need to create a job scheduler. First we select Management ---> SQL Server Agent --> Job from Sql server you connected currently. See the snaps for clear picture.
   
   
5. Right click and select the New Job from the from Job options. It will open New Job Property window. Fill the relavent information in this window. Like this snaps,
   
   


6. Next, we goto Step tab and select New button from it. It will open New Job Step window. Fill the query type, database, stored procedure information like the below snaps.
   
   
   Step Name - Name of the Step. We may use multiple step in single job.
   Type - Select the type of the query to be executed. In out case, 'TSQL'
   Database - Select your database. Here for testing select 'KannanOrganization'.
   Commend - fields is going to execute our stored procedure.
   
   
7. Once we finished our Step tab works then we go for Scheduler setting in Schduler tab.
   Here it providing 4 verities of scheduler for our job running. We can select the type which you want. For my case, I need to run my job on every 1 mins. So that I can select option number 4 Recurring.
   
   For date and time setting, select Change button in the same tab. It will open another window for our date and time settings. See the snaps below.
   
   


8. Once we fixed our date and time setting, click ok button. It will create our job successfully. Now the time to start our job manully by right click the job we created and select Start Job option from that. See the snaps.
   


9. After running our job, we see the job result by open out Employee table and see the records inserted. See the snaps.
   
   
   It will run and insert the record at every 1 mins. In the same way we can do our requirement.

Use the technology!!!