Trying to figure out best way to trigger a task to be run based on when a record shows up in an audit table
We have a data warehouse that gets loaded every morning and when it completes it updates a record in an audit table. I am trying to figure out how to go about checking that value every ten minutes for an end date and then once I get that I know it is safe to run jobs against that server for running Crystal Reports into pdf files. Currently I have a daily, weekly, monthly, and a couple other jobs scheduled in task scheduler on a BusinessObjects server that are set to run at 4am with the assumption the nightly ETLs have run. But this gets broken if the jobs runs long or fail. So I want to check the audit log for a succesful completion and the name of the ETL so I can determine which task to fire off. I was trying to do it with Powershell but the task will not run. Belwo is my code for that process. So no I am looking for an alternative way to accomplish this. #The following command needs to run one time only on each machine this script will run in order to create events #new-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' #Log the start of this process write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'Script Started' -id 100 write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'Check for completed Executions' -id 201 $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server=EPIC;Database=CLARITY;Integrated Security=True" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = "usp_Get_CompletedBatches" $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) $SqlConnection.Close() $DataSet.Tables[0] write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'Execution check has completed' -id 202 #loop thru the rows returned and check to see which if any execution has completed #If a completed execution is not returned leave the task running, otherwise run the batch and reset the task start again the next day. foreach($Row in $DataSet.Tables[0].Rows) { SWITCH ($Row[1]) { "DAILY" { # Run the daily batch write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'Running Daily Batch' -id 311 & "D:\Epic\Analytics Tools\Epic Crystal\DAILY.bat" write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'Daily Batch Completed' -id 312 write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'Create Audit Record' -id 201 #Create a record in the [CHW].[CrystalBatchAudit] table. Pass in the execution name $Row[0] and the Batch name $Row[1], the date is set in the procedure to Getdate() $SqlCmd = New-Object System.Data.SqlClient.SqlCommand("CHW.usp_Insert_CrystalBatchAudit", $SqlConnection) $SqlConnection.Open() $SqlCmd.Commandtype = [System.Data.CommandType]'StoredProcedure' $SqlCmd.parameters.add("@Exec_Name", $Row[0]) $SqlCmd.parameters.add("@BatchName", $Row[1]) $rdr = $Sqlcmd.ExecuteReader() $SqlConnection.Close() write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'Audit Record Created' -id 202 #Need to set the task to not run again today } "WEEKLY" { # Run the Weekly batch write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'Running Weekly Batch' -id 321 & "D:\Epic\Analytics Tools\Epic Crystal\WEEKLY.bat" write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'Weekly Batch Completed' -id 322 write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'Create Audit Record' -id 201 #Create a record in the [CHW].[CrystalBatchAudit] table. Pass in the execution name $Row[0] and the Batch name $Row[1], the date is set in the procedure to Getdate() $SqlCmd = New-Object System.Data.SqlClient.SqlCommand("usp_Insert_CrystalBatchAudit", $SqlConnection) $SqlConnection.Open() $SqlCmd.Commandtype = [System.Data.CommandType]'StoredProcedure' $SqlCmd.parameters.add("@Exec_Name", $Row[0]) $SqlCmd.parameters.add("@BatchName", $Row[1]) $rdr = $Sqlcmd.ExecuteReader() $SqlConnection.Close() write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'Audit Record Created' -id 202 #Need to set the task to not run again today } "MONTHLY" { # Run the Monthly batch write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'Running Monthly Batch' -id 331 & "D:\Epic\Analytics Tools\Epic Crystal\Monthly.bat" write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'Monthly Batch Completed' -id 332 #Create a record in the [CHW].[CrystalBatchAudit] table. Pass in the execution name $Row[0] and the Batch name $Row[1], the date is set in the procedure to Getdate() write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'Create Audit Record' -id 201 $SqlCmd = New-Object System.Data.SqlClient.SqlCommand("usp_Insert_CrystalBatchAudit", $SqlConnection) $SqlConnection.Open() $SqlCmd.Commandtype = [System.Data.CommandType]'StoredProcedure' $SqlCmd.parameters.add("@Exec_Name", $Row[0]) $SqlCmd.parameters.add("@BatchName", $Row[1]) $rdr = $Sqlcmd.ExecuteReader() $SqlConnection.Close() write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'Audit Record Created' -id 202 #Need to set the task to not run again today } default { # No record found. Let the process Run again until it find one write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'No Record found. Run again in ten minutes' -id 110 } } }
May 14th, 2012 2:41pm

Couple of questions: 1. Can you add a column to the audit table so that you can determine whether to continue or stop processing because it was already processed? 2. Do you have the SQL Server powershell snap-in loaded in your Windows powershell environment? If so, you can use invoke-sqlcmd and dispense with the ADO.NET code. It will be easier to read, implement, maintain, and debug. To see if the SQL Server snap-in is loaded, run get-psdrive in a powershell command prompt. If you see the "SQLSERVER" drive then it is loaded. Farooq Mahmud | Support Escalation Engineer | Microsoft Health Solutions Group
Free Windows Admin Tool Kit Click here and download it now
May 14th, 2012 9:34pm

I am already inserting a record in an audit table. My problem right now is that the code on this line & "D:\Epic\Analytics Tools\Epic Crystal\Monthly.bat" is not firing. Currently these bat files run fine in the task scheduler and run at 3am without knowing if the warehouse has been refreshed. So far the environment is ready by 1am. The process I am trying to put in place would replace the scheduled task. my other option is to run the scheduled task and mark it as run but that code was failing also with a different error. I am going to try the route again so i can post the error here.
May 17th, 2012 2:19pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics