Transcend Batch problems alert

We had an issue as well....We have a CC Reconcile Alert job that runs nightly....This is the Batch Error Alert portion of it.  It isn't anything fancy but prompts us to check if an error is raised. 

Hope this helps.

 /**********************************
BATCH ALERT
***********************************/

Insert into LTW_BATCH_SETTLEMENT
select batchID, BatchMessage
from TPAU.dbo.T_Batch_summary_records
where batchRunTime between @start_dt and @end_dt
  and BatchState <>10
order by batchruntime desc

if exists (select 1 from LTW_BATCH_SETTLEMENT)
begin
 --declare @sendto varchar(25)  select @sendto = <email>

 declare @commentB varchar(150)
 select  @commentB = 'Batch/s had a Settlement Error on ' +Convert(varchar,@start_dt,107) +  '.'

 declare @subjectB varchar(100)
 select @subjectB ='Batch Settlement Alert'

 DECLARE @myQueryB VARCHAR(4000)
 select @myQueryB = 'select BatchID, BatchMessage from LTW_BATCH_SETTLEMENT'
 exec msdb.dbo.sp_send_dbmail    
  @profile_name = <Profile Name>
  @recipients = @sendto,
  @body = @commentB,  
  @query = @myQueryB,
  @subject = 'Batch Settlement Alert',
  @query_result_header =1,  
  @execute_query_database = 'impresario'

    
end

TRUNCATE TABLE LTW_BATCH_SETTLEMENT

We use something similar but it runs on the TPAU "Intrix/Transcend" database.

USE [TPAU]

GO

/****** Object:  StoredProcedure [dbo].[LP_Goodman_Batch_Date_Check]    Script Date: 07/15/2011 06:28:20 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Chris Hipschen

-- Create date: 9/4/2008

-- Description: SP to check for batches WITH today's date, if they do not exist then an email notice is sent (MS SQL SMS Job Notification on Failure)

-- =============================================

ALTER PROCEDURE [dbo].[LP_Goodman_Batch_Date_Check]

(@Batch_None_This_Date INT = NULL)

AS

BEGIN

SET NOCOUNT ON;

SELECT @Batch_None_This_Date = 1

WHERE NOT EXISTS (SELECT * FROM [TPAU].[dbo].[T_BATCH_SUMMARY_RECORDS] 

where CONVERT(datetime,CONVERT(varchar,[T_BATCH_SUMMARY_RECORDS].[BatchRunTime],103),103)

=

CONVERT(datetime,CONVERT(varchar,getdate(),103),103))

IF(@Batch_None_This_Date = 1)

       BEGIN

RAISERROR ('A non-complete.settled batch exists', 16, 1)

RETURN

       END

END

We use something similar but it runs on the TPAU "Intrix/Transcend" database.

USE [TPAU]

GO

/****** Object:  StoredProcedure [dbo].[LP_Goodman_Batch_Date_Check]    Script Date: 07/15/2011 06:28:20 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Chris Hipschen

-- Create date: 9/4/2008

-- Description: SP to check for batches WITH today's date, if they do not exist then an email notice is sent (MS SQL SMS Job Notification on Failure)

-- =============================================

ALTER PROCEDURE [dbo].[LP_Goodman_Batch_Date_Check]

(@Batch_None_This_Date INT = NULL)

AS

BEGIN

SET NOCOUNT ON;

SELECT @Batch_None_This_Date = 1

WHERE NOT EXISTS (SELECT * FROM [TPAU].[dbo].[T_BATCH_SUMMARY_RECORDS] 

where CONVERT(datetime,CONVERT(varchar,[T_BATCH_SUMMARY_RECORDS].[BatchRunTime],103),103)

=

CONVERT(datetime,CONVERT(varchar,getdate(),103),103))

IF(@Batch_None_This_Date = 1)

       BEGIN

RAISERROR ('A non-complete.settled batch exists', 16, 1)

RETURN

       END

END

My above script will verify that a settlement batch was created.  To verify that Transcend and Tessitura are in balance we also run a custom credit card report.  I believe there is another posted in Shared Reports. I'm willing to share our custom report as well.  I'd post a screenshot of it but I don't see how to add my own media here.  In essence, it shows a total of all Tessitura credit card payment methods, a total for each Transend merchant id and the difference between Tessitura and Transcend.  If there is a difference a sub-report will display details of the transaction and whether it resides in Transcend or Tessitura.