You can do this by writing a script in adTempus that queries the database.
Here's one approach:
In your job, create a step that sends a notification message. You'll need to set the client up as a notification recipient in adTempus.
In the message body for the notification message, include the token "%TableRecordCount%" at the point where you want to have the record count. For example:
There are %TableRecordCount% records in table XYZ today
Then go to the Conditions tab for this step and add a condition that runs a script. Use VB.NET or C#. In your script you'll need to write the code to connect to the database and do the record count query.
If the table has records, you need to do two things:
- Define the "TableRecordCount" variable with the record count, so that it will be inserted in your message where you have the "%TableRecordCount%" token. Assuming you've got the record count in an integer variable named recordCount, the syntax for this from within the script would be:
adTempus.JobVariables.Add("TableRecordCount",recordCount.ToString(),false)
- The script function must return a true result, which tells adTempus to go ahead and do the notification.
If the table has no records, your script simply returns a false result. This tells adTempus not to do the notification.
Now schedule the job to run at whatever time is appropriate.
Here is an example of the condition script. Set the Language to VB.NET. You'll also need to add "System.Data" in the Referenced Assemblies section. This assumes your database is SQL Server.
'This script gets the record count from a table and sets the TableRecordCount variable based on the record count.
'The script is intended to be used as a condition script
'Modify the serverName, databaseName, and tableName settings below to meet your needs.
'The script connects to SQL Server using integrated Windows security, so the job must run under an
'account that has the necessary permissions on the database server.
Imports System
Imports System.Collections
Imports ArcanaDevelopment.adTempus.Server
Imports System.Data.SqlClient
Public Class UserScript
Inherits ArcanaDevelopment.adTempus.ScriptEngine.UserScriptBase
Public Overrides Function Run() As Object
Dim connection As SqlConnection
Dim command As SqlCommand
Dim recordCount As Integer
Dim serverName As String="servername"
Dim databaseName As String="databasename"
Dim tableName As String="tablename"
Dim connectionString As String
connectionString=String.Format("Data Source={0}; Initial Catalog={1}; Integrated Security=SSPI",serverName,databaseName)
connection=New SqlConnection(connectionString)
connection.Open()
command=New SqlCommand("select count(*) from " & tableName,connection)
recordCount=CInt(command.ExecuteScalar())
If recordCount=0 Then
'no records; tell adTempus not to run step
Return False
Else
'define the TableRecordCount variable to be used in the notification message
adTempus.JobVariables.Add("TableRecordCount",recordCount.ToString(),False)
'return True to tell adTempus to run the notification
Return True
End If
End Function
End Class
Staff
