A method for generating transaction forecasts based on job scheduling (SQL Server)

Server generating transaction forecasts using job scheduling 2007-04-12 Baker 1. the question of the proposed : the laboratory project needs to do a transaction forecast module, uses this module to generate the forecast control information , if the database has the impending Acceptance project information , needs to prompt the user to prepare to carry on the acceptance to this project . and produces the following effects : and generate the Forecast information page , the user can also according to the requirements of the project list to query . If there are no items to forecast, the following window will be displayed 1. Solution : because events that need to be generated for forecast information are unusual , the easiest way is to determine whether there is information that needs to be predicted when each user logs on, and All forecast information is generated temporarily. If the master table is more informative , it has a great effect on the performance of the whole system, and the tasks with the same function are processed many times , resulting in great waste . there is a SQL Server Agent service in SQL Server that has a " job function ", much like a timer trigger , using a " job " to execute a specific T-SQL statement or process at a specific time is an efficient and effective method . 2. Generate forecast Control Table .  /* CREATE TABLE PYBKZ default inserts a fixed record (' 0000000000000000 ', ' 0 ', ' 0 ', null). */use KJXMGOIF exists (SELECT * from dbo.sysobjects where ID = object_id (N ' [dbo].[ PYBKZ] and OBJECTPROPERTY (ID, N ' istable ') = 1) drop table Pybkzgo CREATE TABLE pybkz (login name varchar () not NULL, test Collection forecast varchar (1) default (0), appropriation forecast varchar (1) default (0), Forecast month varchar (6) default (0)) insert into Pybkz select ' 00000000000 00000 ', ' 0 ', ' 0 ', Nullgo--set SQLServerAgent for self-starter exec msdb.dbo.sp_set_sqlagent_properties @auto_start =1go 3. Create Job --— 2007-04-11/21:47--—    by Baker--— Server: (local)  begin transaction               DECLARE @JobID BINARY (+)     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 alerts with the same name (if any).   SELECT @JobID = job_id       from   msdb.dbo.sysjobs      WHERE (name = N ' Kjxm_forecast ')          IF (@JobID is not NULL)   & nbsp   begin   --check if this job is a multiple server job   if EXISTS (select  *                from    msdb.dbo.sysjobservers                WHERE   (job_id = @JobID) and (server_id <> 0))   BEGIN    --already present, thus terminating the foot This     RAISERROR (N ' cannot import job ' kjxm_forecast ' because it already has multiple server jobs with the same name. ', 1     GOTO quitwithrollback    End   ELSE    --delete [local] job      EXECUTE msdb.dbo.sp_delete_job @job_name = N ' kjxm_forecast '     SELECT @JobID = null  End &nbsp ; BEGIN   --Add job   EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT, @job_name = N ' Kjxm_fo Recast ', @owner_login_name = n ' sa ', @description = N ' no description available. ', @category_name = N ' [Uncategorized (local)] ', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_lev El_netsend = 0, @notify_level_eventlog = 2, @delete_level = 0  IF (@ @ERROR <> 0 OR @ReturnCode <> 0) GOTO Quitwithrollback   --Add job steps   EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1 , @step_namE = n ' 1 ', @command = n ' use KJXM GO exec forecast ', @database_name = N ' kjxm ', @server = N ', @database_user_name = N ', @su bsystem = N ' TSQL ', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 5, @retry_interval = 1, @output_file_name = N ' ', @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, @s tart_step_id = 1    IF (@ @ERROR <> 0 OR @ReturnCode <> 0) GOTO quitwithrollback   --Add job Dispatch   EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N ' 2 ', @enabled = 1, @freq_type = 4, @ Active_start_date = 20070411, @active_start_time = @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959  IF (@ @ERROR <> 0OR @ReturnCode <> 0) GOTO quitwithrollback   --Add target server   EXECUTE @ReturnCode = Msdb.dbo.sp_add_jobse RVer @job_id = @JobID, @server_name = N ' (local) '   IF (@ @ERROR <> 0 OR @ReturnCode <> 0) GOTO Quitwithrol Lback  endcommit transaction          GOTO   EndSave               quitwithrollback:  IF (@@ Trancount > 0) ROLLBACK TRANSACTION endsave:    4. Generate forecast information Stored procedures ./*    creates a stored procedure     */use kjxmgoif exists for generating forecast information (SELECT * from dbo.sysobjects where id = obje ct_id (N ' [dbo].[ Forecast] and OBJECTPROPERTY (ID, N ' isprocedure ') = 1)   drop procedure [dbo]. [Forecast]go create procedure forecastas  begin transaction  begin      DECLARE @ have_forecast  int    DECLARE @time datetime    declare @nextmonth varchar (6)             --Generate next month date string, such as:20070501    set @time = DATEADD (Mm,datediff (Mm,0,getdate ()) +1,0)       if (month (@time) <10)         begin         Set @nextmonth =convert (varchar (4), Year (@time)) + ' 0 ' +convert (varchar (2), month (@time))        end    else       begin        Set @nextmonth =convert (varchar (4), Year (@time)) +convert (varchar (2), month (@time))       end          --Determine whether the current date is the first day of the month, and if the first day is updated, the forecast record in the Pybkz table .    if (not EXISTS (select predicts the date from Pybkz where login = ' 0000000000000000 ')) or (select forecast year from Pybkz where login = ' 0000000000000000 ')!= @nextmonth  )        begin        Delete from pybkz         Set @have_forecast =0        if (select COUNT (*) from pxmxx where finish date = @nextmonth)           begin      >0)        INSERT INTO Pybkz select ' 0000000000000000 ', ' 1 ', ' 1 ', @nextmonth            end        else           begin &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&Nbsp;  INSERT INTO Pybkz select ' 0000000000000000 ', ' 0 ', ' 0 ', @nextmonth            end      end  end  commit Transactiongo 5. Reading forecast Information ./*    determine whether a login needs to be prompted to forecast information, and set the corresponding forecast information. */use kjxmgo if exists (SELECT * from dbo.sysobjects where id = o bject_id (N ' [dbo].[ Is_forecast] and OBJECTPROPERTY (ID, N ' isprocedure ') = 1)   drop procedure [dbo]. [Is_forecast]go create procedure Is_forecast (  @user varchar ()) As  declare @nextmonth varchar ( 6  set @nextmonth = (select forecast year from Pybkz where login = ' 0000000000000000 ')  if (exists (SELECT * from Pybkz where logon name = ' 0000000000000000 ' and acceptance forecast = ' 1 ')    begin     if (exists (SELECT * from Pybkz where login name =@ User and acceptance forecast = ' 0 ')        begin         Select 0       end     if (exists (SELECT * from Pybkz where logon name = @user and Acceptance forecast = ' 1 ')        begin         Select 1        end     if (NOT exists(SELECT * from Pybkz where logon name = @user))        begin         INSERT INTO Pybkz Select @user, ' 1 ', ' 1 ', @nextmonth          select 1        end   end else   begin     Select 0   end