CREATE  Procedure dbo.sp_CRR_Daily_Control_Summary_Total_Update(@pvar_Run_Date varchar (11) = null)
As

*****************************************************************************************/
 

    -- Declare variables    
		Declare @var_Server_Name       		Varchar(100)
   	Declare @var_Database_Name       	Varchar(100)
    Declare @var_Procedure_Name     	Varchar(100)
    Declare @var_Procedure_Name_Call 	Varchar(100)
		Declare @var_Environment	    		Varchar(100)
    Declare @var_Document_Type    		Varchar(30)
    Declare @var_batch_Name						Varchar(30)
    Declare @var_import_ID						Varchar(3)
    Declare @var_Job_Process_Name			Varchar(3)
    Declare @var_Job_Process_Status		Varchar(20)
    Declare @var_Job_Control_Status		Varchar(20) 
    Declare @var_Run_Date         		Varchar(11)
    Declare @var_Job_Message					Varchar(200)
    Declare @var_Job_Process_runDate	Varchar(11)
    Declare @var_ErrorMessage  				Varchar(4000)
	

    Declare @int_Status_Code					Integer
    Declare @int_CRR_ErrorCode				integer
    Declare @int_Document_Count				Integer
    Declare @int_Job_Control_ID 			Integer 
    Declare @int_Job_ID         			Integer 
    Declare @int_Job_Step_ID					Integer
    Declare @int_Return_Code					Integer
    Declare @int_Logic_Block					Integer
    Declare @int_Job_Process_ID				Integer
    Declare @int_Last_Step_Completed	Integer
    Declare @int_RowCount							Integer
		Declare @int_ERROR_ROLLBACK				Integer
		Declare @int_ERROR_NO_ROLLBACK		Integer

    Declare @dtm_Curr_Date	    			Datetime
    Declare @dtm_Run_Date	    				Datetime
    Declare @dtm_scandate							Datetime
    Declare @dtm_commitdate						Datetime
    Declare @dtm_Job_Message_Date			Datetime

    Declare @num_IDM_Total	    			Numeric(10)
    Declare @num_IDM_Total_Repl	    	Numeric(10)
		Declare	@num_IDM_Total_Count			Numeric(10)

    Set @var_Server_Name 						= @@ServerName
    Set @var_Database_Name      		= DB_NAME()	
    Set @var_Procedure_Name 				= 'CRR_Daily_Control_Summary_Total_Update'
    Set @var_Procedure_Name_Call		= 'none'
    Set @var_Job_Process_Name				= 'CRR'
		
		Set @int_Job_ID 								= 3		--this is the 3rd job (Database Update) in the process
    Set @int_Job_Step_ID  					= 1		--this is the 1st procedure in the second job.
    Set @int_RowCount  							= 0	
    Set @int_RowCount  							= 0	
    Set @int_Status_Code						= 0
    Set @int_Return_Code						= 0
    Set @int_CRR_ErrorCode 					= 0 
    Set @int_Logic_Block 						= 1
		Set @int_ERROR_ROLLBACK					= 2
		Set @int_ERROR_NO_ROLLBACK			= 1		

		Set @num_IDM_Total   						= 0


--  In all CRR Stored Procedures an @int_Logic_Block of 1 always indicates either the Job Status
--	Check or initialization process	 
    Set @int_Logic_Block 					= 1
		Set @var_Procedure_Name_Call 	= 'sp_CRR_Job_Step_Status_Check'

-- 	This procedure checks to ensure that this is the correct procedure to run
    EXEC @int_Return_Code =  dbo.sp_CRR_Job_Step_Status_Check
				 									   @pvar_Run_Date
		 		 									 	,@int_Job_Step_ID
				 										,@int_Job_ID
				 										,@pint_job_Process_ID = @int_job_Process_ID output
				 										,@pint_Job_Control_ID = @int_Job_Control_ID output
				 										,@pdtm_Run_Date = @dtm_Run_Date output
				 										,@pvar_Job_Process_runDate = @var_Job_Process_runDate output
				
		if @int_Return_Code <> 0  
				BEGIN
	  			--@int_CRR_ErrorCode = 1 indicates an error has occured but no rollback is required.
					  Set @int_Status_Code 	=	@int_Return_Code
	    			Set @int_CRR_ErrorCode = @int_ERROR_NO_ROLLBACK
	    			Set @var_Job_Message = 'ERROR: Bad return code from sp_CRR_Job_Step_Status_Check' 

						GOTO ERROR_ROUTINE
        END	
				
--	this message is just to confirm that this procedure is ready to run	
	  Set @int_CRR_ErrorCode = 0
		Set @dtm_Job_Message_Date = GetDate()
		Set @var_Job_Message = 'Job Control Initializtion Successful' 
	                        
		EXEC @int_Return_Code =  dbo.sp_CRR_Job_Message_Update 
									 					 @int_Job_Process_ID
														,@int_Job_Control_ID
														,@int_Job_Step_ID
														,@int_Status_Code 	
														,@dtm_Job_Message_Date
														,@var_Procedure_Name
														,@int_Logic_Block
														,@var_Procedure_Name_Call
														,@var_Job_Message
		
--	if a bad return code was received from the Message Update then end the process
		IF @int_Return_Code <> 0 
				BEGIN
						SET @int_CRR_ErrorCode = @int_ERROR_ROLLBACK
				GOTO EXCEPTION_ROUTINE		
		END
	    
    BEGIN TRAN

								
-- 			**********************************************************************************
--			Get the total System Count of IFNID's from local IDM
-- 			**********************************************************************************
       	Set @int_Logic_Block = 2
				Set @var_Procedure_Name_Call 	= 'Get local IDM totals from view'
				
				set @num_IDM_Total = (select daily_total from dbo.v_CRR_Daily_Totals_IDM)

      	Set @int_Status_Code = @@ERROR	

				If @int_Status_Code <> 0
          	Begin
	      				Set @int_CRR_ErrorCode = @int_ERROR_ROLLBACK	
	      				set @var_Job_Message = 'ERROR: Select from local IDM view failed'
														
	      				GOTO ERROR_ROUTINE
						END	
		
-- 			**********************************************************************************
--			Get the total System Count of IFNID's from Replication IDM
-- 			**********************************************************************************
       	Set @int_Logic_Block = 3
				Set @var_Procedure_Name_Call 	= 'Get local IDM Replication totals from view'
			  set @num_IDM_Total_Repl = (select daily_total from dbo.v_CRR_Daily_Totals_IDM_Repl)

      	Set @int_Status_Code = @@ERROR	

				If @int_Status_Code <> 0
          	Begin
	      				Set @int_CRR_ErrorCode = @int_ERROR_ROLLBACK	
	      				set @var_Job_Message = 'ERROR: Select from IDM Replication view failed'
														
	      				GOTO ERROR_ROUTINE
						END	
--	--------------------------------------------------------------------------------------
--				Replace the views above with the stored procedure calls below
--	--------------------------------------------------------------------------------------
--				EXEC @int_Job_Step_rtncode = sp_CRR_IDM_DAILY_TOTALS 
--																		@dtm_Run_Date
--																		@pnum_IDM_Total_Count = @num_IDM_Total_Count output

--				EXEC @int_Job_Step_rtncode = sp_CRR_IDM_DAILY_TOTALS_REPL 
--																		@dtm_Run_Date
--																		@pnum_IDM_Total_Count_REPL = @num_IDM_Total_Count_REPL output
																		
--			*******************************************************************************
--			Update with the system and replication total.
--			*******************************************************************************
       	Set @int_Logic_Block = 4
				Set @var_Procedure_Name_Call 	= 'Update CRR_Daily_Control_Summary'

				
				Update dbo.CRR_Daily_Control_Summary 
           set IDM_System_Total = IsNull(@num_IDM_Total, 0) 
	      			,IDM_Replication_Total= IsNull(@num_IDM_Total_Repl, 0) 
         Where Convert(nvarchar(10), baseline_date, 101) = Convert(nvarchar(10), @dtm_Run_Date, 101)

       	Set @int_Status_Code = @@ERROR	
    
       	If @int_Status_Code <> 0
          	Begin
	      				Set @int_CRR_ErrorCode = @int_ERROR_ROLLBACK	
	      				set @var_Job_Message = 'ERROR: Update of IDM and IDM Replication Totals failed'
														
	      				GOTO ERROR_ROUTINE
						END	
							
-------------------------------------------------------------------------- 	
-------------------------------------------------------------------------- 
-- UPDATE THE JOB CONTROL CARD with a status of "COMPLETE"
-------------------------------------------------------------------------- 
-------------------------------------------------------------------------- 
	Set @int_Logic_Block  =  20	
	Set @var_Procedure_Name_Call 	= 'Update Job Control Card'

	Update dbo.CRR_Job_Control
		set  Last_step_Completed = @int_Job_Step_ID,
		     enddate = GetDate (),
		     job_status = 'COMPLETE'	
             Where job_Control_ID = @int_Job_Control_ID 
	
 	Set @int_Status_Code = @@ERROR        

	If @int_Status_Code <> 0
	    Begin
	       	Set @int_CRR_ErrorCode = @int_ERROR_ROLLBACK	
					Set @var_Job_Message = 'ERROR: Update of CRR_JOB_CONTROL failed'  

	      	GOTO ERROR_ROUTINE
            
      End 


		
	COMMIT TRAN


END_PROCEDURE:

    If @int_CRR_ErrorCode = 0 and @int_Status_Code = 0	
				BEGIN
				
        		Set @var_Job_Message = 'Procedure has Completed Successfully ' 
						Set @dtm_Job_Message_Date = getDate()
						
						EXEC @int_Return_Code =  dbo.sp_CRR_Job_Message_Update 
					 								 					 @int_Job_Process_ID
																		,@int_Job_Control_ID
																		,@int_Job_Step_ID
																		,@int_Status_Code 	
																		,@dtm_Job_Message_Date
																		,@var_Procedure_Name
																		,@int_Logic_Block
																		,@var_Procedure_Name_Call
				  													,@var_Job_Message
    		END
				
    RETURN


ERROR_ROUTINE:

    set  @dtm_Job_Message_Date = getDate()
	
    If @int_CRR_ErrorCode <> 0
        Begin 
			
						EXEC @int_Return_Code =  dbo.sp_CRR_Job_Message_Update 
					 								 					 @int_Job_Process_ID
																		,@int_Job_Control_ID
																		,@int_Job_Step_ID
																		,@int_Status_Code 	
																		,@dtm_Job_Message_Date
																		,@var_Procedure_Name
																		,@int_Logic_Block
																		,@var_Procedure_Name_Call
				  													,@var_Job_Message

--	    		this internal error codes indicates whether a rollback is required
            IF @int_CRR_ErrorCode = @int_ERROR_ROLLBACK     
	        			BEGIN 
	           				GOTO EXCEPTION_ROUTINE
                END
	    			ELSE
								BEGIN
		   							GOTO END_PROCEDURE
	       		 		END 
						END	

EXCEPTION_ROUTINE:
    ROLLBACK TRAN
    GOTO End_Procedure


GO

Error Source Microsoft OLE DB Provider for SQL Server
The Operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a Distributed
Transaction. OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransaction Join::...]
MSDTC on server KOCTOWERDEV01\TOWErtEST is unavailable