Job to Check for Dates in a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Job to Check for Dates in a table

Author

Topic

RHarrison
Starting Member

4 Posts

Posted - 2011-06-03 : 09:56:03

There is a Notices Processing job on our system that creates email notices to notify customers when items they have checked out are due. Currently the job runs every day, but we would like it to run only on days we are open. The DB already contains a table "DatesClosed". It seems like it should be easy to write a step for the existing job that precedes the first step in the job that will check the "DatesClosed" table to see if the current day's Date exists. If today's date exists, then quit the job. If today's date is not in the table, then go on and complete the job.

Randy Harrison
City of Wichita IT/IS
Application Analyst-Library
www.wichita.gov
(316) 261-8504 (office)
(316) 219-6220 (fax)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-03 : 10:06:43

A query such as this should do it


if not exists (select * from DatesClosed where dateColumnInTheTable = cast(current_timestamp as date))
begin
-- continue with what you need to do
end

RHarrison
Starting Member

4 Posts

Posted - 2011-06-03 : 13:27:48

Here is the Job Code I'm working with and when I tried to add a step with the query you suggested, I get an error but can't seem to figure out what I'm doing wrong.

Thanks for your assistance.

****************************************************************************************************

USE [msdb]
GO

/****** Object: Job [Notices Processing] Script Date: 06/03/2011 12:26:22 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Polaris Support] Script Date: 06/03/2011 12:26:22 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Polaris Support' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Polaris Support'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Notices Processing',
@enabled=0,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'This massive job is doing the following tasks:

-- Process Fine Notices (step 1 & 2);
-- Process Overdue and Bill Notices (step 3 & 4);
-- Process Hold Notices for emails only (step 5);
-- Process Reminders - almost overdue, expiration and inactive (step 6);
-- Send out email notices by calling PolEmailManager.exe (step 7);
-- Export Overdue, Bill, Hold and Fine notices by calling ExportNotices.exe if Export notices is enabled',
@category_name=N'Polaris Support',
@owner_login_name=N'ICTLAN\PolarisExec', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Process Fine Notices] Script Date: 06/03/2011 12:26:22 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Process Fine Notices',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=4,
@on_fail_step_id=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC Notices_FineNoticeProcessing',
@database_name=N'Polaris',
@output_file_name=N'C:\ProgramData\Polaris\4.0\Logs\SQLJobs\Notices_Processing.log',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Populate Fine Notices for Reporting] Script Date: 06/03/2011 12:26:22 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Populate Fine Notices for Reporting',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=4,
@on_fail_step_id=3,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC Notices_GenerateFineNotice',
@database_name=N'Polaris',
@output_file_name=N'C:\ProgramData\Polaris\4.0\Logs\SQLJobs\Notices_Processing.log',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Process Overdue and Bill Notices] Script Date: 06/03/2011 12:26:22 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Process Overdue and Bill Notices',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=4,
@on_fail_step_id=4,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC ProcessOverdueNotices',
@database_name=N'Polaris',
@output_file_name=N'C:\ProgramData\Polaris\4.0\Logs\SQLJobs\Notices_Processing.log',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Populate Overdue and Bill Notices for Reporting] Script Date: 06/03/2011 12:26:22 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Populate Overdue and Bill Notices for Reporting',
@step_id=4,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=4,
@on_fail_step_id=5,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC GenerateOverdueNotices ''''',
@database_name=N'Polaris',
@output_file_name=N'C:\ProgramData\Polaris\4.0\Logs\SQLJobs\Notices_Processing.log',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Populate Hold Notices for Email] Script Date: 06/03/2011 12:26:22 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Populate Hold Notices for Email',
@step_id=5,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=4,
@on_fail_step_id=6,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC GenerateHoldNotices 2',
@database_name=N'Polaris',
@output_file_name=N'C:\ProgramData\Polaris\4.0\Logs\SQLJobs\Notices_Processing.log',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Process Almost Overdue, Expiration and Inactive Reminders] Script Date: 06/03/2011 12:26:22 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Process Almost Overdue, Expiration and Inactive Reminders',
@step_id=6,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=4,
@on_fail_step_id=7,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC Notices_ProcessReminders',
@database_name=N'Polaris',
@output_file_name=N'C:\ProgramData\Polaris\4.0\Logs\SQLJobs\Notices_Processing.log',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Execute Polaris Email Manager to send out email notices] Script Date: 06/03/2011 12:26:22 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Execute Polaris Email Manager to send out email notices',
@step_id=7,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=4,
@on_fail_step_id=8,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'declare @szSaVal varchar(255)
declare @szCmd varchar(1000)
declare @szPolarisPath varchar(200)
declare @nRetVal int
declare @szAppServer varchar(100)

--
-- try to send out all types of email notices and a summary report
-- after email notices are sent successfully
--
select @szAppServer = AppServer FROM Polaris.PolarisDBVersion
exec Polaris.SA_GetPolarisPath NULL, @szPolarisPath OUT
select @szCmd = ''"'' + @szPolarisPath + ''bin\PolEmailManager.exe" /a SERVERNAME='' + @szAppServer + '' PATH=['' + @szPolarisPath + '']''
exec @nRetVal = master..xp_cmdshell @szCmd, no_output
if @nRetVal <> 0
begin
select @szCmd = @szCmd + '' returned '' + cast(@nRetVal as varchar)
exec Polaris.Logit @szCmd
end

',
@database_name=N'Polaris',
@output_file_name=N'C:\ProgramData\Polaris\4.0\Logs\SQLJobs\Notices_Processing.log',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Execute ExportNotices to export notices data] Script Date: 06/03/2011 12:26:22 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Execute ExportNotices to export notices data',
@step_id=8,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'declare @saVal1 varchar(255)
declare @saVal2 varchar(255)
declare @saVal3 varchar(255)
declare @saVal4 varchar(255)

declare @szCmd varchar(1000)
declare @szPolarisPath varchar(200)
declare @nRetVal int
declare @szAppServer varchar(100)

-- check to see if any notice needs to be exported
SETUSER ''Polaris''
exec Polaris.SA_GetValueByOrg ''NSPARM_OVRDUE_EXPORT'', 1, 1, @saVal1 output
exec Polaris.SA_GetValueByOrg ''NSPARM_BILL_EXPORT'', 1, 1, @saVal2 output
exec Polaris.SA_GetValueByOrg ''NSPARM_HOLD_EXPORT'', 1, 1, @saVal3 output
exec Polaris.SA_GetValueByOrg ''NSPARM_FINE_EXPORT'', 1, 1, @saVal4 output

if (@saVal1 = ''Yes'' or @saVal2 = ''Yes'' or @saVal3 = ''Yes'' or @saVal4 = ''Yes'')
begin
SETUSER
--
-- try to send out all types of email notices and a summary report
-- after email notices are sent successfully
--
select @szAppServer = AppServer FROM Polaris.PolarisDBVersion
exec Polaris.SA_GetPolarisPath NULL, @szPolarisPath OUT
select @szCmd = ''"'' + @szPolarisPath + ''bin\ExportNotices.exe" SERVERNAME='' + @szAppServer + '' PATH=['' + @szPolarisPath + '']''
exec @nRetVal = master..xp_cmdshell @szCmd, no_output
if @nRetVal <> 0
begin
select @szCmd = @szCmd + '' returned '' + cast(@nRetVal as varchar)
exec Polaris.Logit @szCmd
end
end',
@database_name=N'Polaris',
@output_file_name=N'C:\ProgramData\Polaris\4.0\Logs\SQLJobs\Notices_Processing.log',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Randy

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-03 : 18:09:46

What is the error you are getting?

RHarrison
Starting Member

4 Posts

Posted - 2011-06-13 : 12:28:42

The job runs whether the date exists in the 'ClosedDates' table or not using the query as you suggested so I'm not sure it is checking the table.

Randy

RHarrison
Starting Member

4 Posts

Posted - 2011-06-13 : 13:38:58

I made a minor change to the 1st step of the Job using what you provided to wrap around the first step and then changed the "On success action" to 'quit with success' and that appears to have done the trick.

Thank you so much for the information!

Randy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources