{"type":"doc","content":[{"type":"paragraph","content":[{"text":"We improved the Expense Transfer Process. The delete would cause incredible lag so we orphan those deleted records and created a scheduled job to delete those records. This will need to be set up to run nightly.","type":"text"}]},{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"paragraph","content":[{"text":"USE [msdb]","type":"text"},{"type":"hardBreak"},{"text":"GO","type":"text"}]},{"type":"paragraph","content":[{"text":"/****** Object: Job [AIM-3598 Delete Orphaned Expense Recores] Script Date: 2/20/2020 10:48:17 AM ******/","type":"text"},{"type":"hardBreak"},{"text":"BEGIN TRANSACTION","type":"text"},{"type":"hardBreak"},{"text":"DECLARE @ReturnCode INT","type":"text"},{"type":"hardBreak"},{"text":"SELECT @ReturnCode = 0","type":"text"},{"type":"hardBreak"},{"text":"/****** Object: JobCategory [Database Maintenance] Script Date: 2/20/2020 10:48:17 AM ******/","type":"text"},{"type":"hardBreak"},{"text":"IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)","type":"text"},{"type":"hardBreak"},{"text":"BEGIN","type":"text"},{"type":"hardBreak"},{"text":"EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'","type":"text"},{"type":"hardBreak"},{"text":"IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback","type":"text"}]},{"type":"paragraph","content":[{"text":"END","type":"text"}]},{"type":"paragraph","content":[{"text":"DECLARE @jobId BINARY(16)","type":"text"},{"type":"hardBreak"},{"text":"EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'AIM-3598 Delete Orphaned Expense Recores', ","type":"text"},{"type":"hardBreak"},{"text":" @enabled=1, ","type":"text"},{"type":"hardBreak"},{"text":" @notify_level_eventlog=0, ","type":"text"},{"type":"hardBreak"},{"text":" @notify_level_email=0, ","type":"text"},{"type":"hardBreak"},{"text":" @notify_level_netsend=0, ","type":"text"},{"type":"hardBreak"},{"text":" @notify_level_page=0, ","type":"text"},{"type":"hardBreak"},{"text":" @delete_level=0, ","type":"text"},{"type":"hardBreak"},{"text":" @description=N'During business hours, Transfer expenses are recalcualted, and old versions are not deleted. This job will clean up these records off hours.', ","type":"text"},{"type":"hardBreak"},{"text":" @category_name=N'Database Maintenance', ","type":"text"},{"type":"hardBreak"},{"text":" @owner_login_name=N'VGNET\\tony.ornelas', @job_id = @jobId OUTPUT","type":"text"},{"type":"hardBreak"},{"text":"IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback","type":"text"},{"type":"hardBreak"},{"text":"/****** Object: Step [Delete Orphan Expenses For All LIVE Databases] Script Date: 2/20/2020 10:48:18 AM ******/","type":"text"},{"type":"hardBreak"},{"text":"EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Delete Orphan Expenses For All LIVE Databases', ","type":"text"},{"type":"hardBreak"},{"text":" @step_id=1, ","type":"text"},{"type":"hardBreak"},{"text":" @cmdexec_success_code=0, ","type":"text"},{"type":"hardBreak"},{"text":" @on_success_action=1, ","type":"text"},{"type":"hardBreak"},{"text":" @on_success_step_id=0, ","type":"text"},{"type":"hardBreak"},{"text":" @on_fail_action=2, ","type":"text"},{"type":"hardBreak"},{"text":" @on_fail_step_id=0, ","type":"text"},{"type":"hardBreak"},{"text":" @retry_attempts=0, ","type":"text"},{"type":"hardBreak"},{"text":" @retry_interval=0, ","type":"text"},{"type":"hardBreak"},{"text":" @os_run_priority=0, @subsystem=N'TSQL', ","type":"text"},{"type":"hardBreak"},{"text":" @command=N'","type":"text"},{"type":"hardBreak"},{"text":"declare @databases table (TableName varchar(100));","type":"text"},{"type":"hardBreak"},{"text":"insert into @databases","type":"text"},{"type":"hardBreak"},{"text":"select name from sys.databases","type":"text"},{"type":"hardBreak"},{"text":"where state = 0","type":"text"},{"type":"hardBreak"},{"text":"and name like '","type":"text"},{"text":"'Live_%_User''","type":"text","marks":[{"type":"textColor","attrs":{"color":"#ff0000"}}]},{"type":"hardBreak"},{"text":";","type":"text"}]},{"type":"paragraph","content":[{"text":"while exists (select 1 from @databases)","type":"text"},{"type":"hardBreak"},{"text":"begin","type":"text"},{"type":"hardBreak"},{"text":" declare @db varchar(100);","type":"text"},{"type":"hardBreak"},{"text":" select top 1 @db = TableName from @databases;","type":"text"}]},{"type":"paragraph","content":[{"text":"declare @version varchar(100);","type":"text"},{"type":"hardBreak"},{"text":" declare @sql nvarchar(200);","type":"text"},{"type":"hardBreak"},{"text":" declare @params nvarchar(200);","type":"text"},{"type":"hardBreak"},{"text":" set @sql = N''select @versionOut=cast(value as varchar(100)) from ''+@db+''.sys.extended_properties where name = ''''aim_version'''''';","type":"text"},{"type":"hardBreak"},{"text":" set @params = N''@versionOut varchar(100) output''","type":"text"},{"type":"hardBreak"},{"text":" exec sp_executesql @sql,@params,@versionOut = @version output;","type":"text"},{"type":"hardBreak"},{"text":" ","type":"text"},{"type":"hardBreak"},{"text":" print @db","type":"text"},{"type":"hardBreak"},{"text":" print @version","type":"text"},{"type":"hardBreak"},{"text":" ","type":"text"},{"type":"hardBreak"},{"text":" if (@version != '''')","type":"text"},{"type":"hardBreak"},{"text":" begin","type":"text"},{"type":"hardBreak"},{"text":" print ''Disabling Expense Trigger''","type":"text"},{"type":"hardBreak"},{"text":" set @sql = N''exec ''+@db+''.dbo.spAutoAuditDrop ''''Expense''''''; ","type":"text"},{"type":"hardBreak"},{"text":" exec sp_executesql @stmt=@sql;","type":"text"},{"type":"hardBreak"},{"text":" print ''Mass-Delete Orphan Expenses''","type":"text"},{"type":"hardBreak"},{"text":" set @sql = N''delete from ''+@db+''.dbo.Expense where GroupID = 0'' ","type":"text"},{"type":"hardBreak"},{"text":" exec sp_executesql @stmt=@sql; ","type":"text"},{"type":"hardBreak"},{"text":" print ''Enabling Expense Trigger''","type":"text"},{"type":"hardBreak"},{"text":" set @sql = N''exec ''+@db+''.dbo.spAutoAudit ''''Expense'''''';","type":"text"},{"type":"hardBreak"},{"text":" exec sp_executesql @stmt=@sql;","type":"text"},{"type":"hardBreak"},{"text":" end","type":"text"}]},{"type":"paragraph","content":[{"text":"delete from @databases where TableName = @db;","type":"text"},{"type":"hardBreak"},{"text":"end","type":"text"},{"type":"hardBreak"},{"text":";","type":"text"},{"type":"hardBreak"},{"text":"', ","type":"text"},{"type":"hardBreak"},{"text":" @database_name=N'master', ","type":"text"},{"type":"hardBreak"},{"text":" @flags=0","type":"text"},{"type":"hardBreak"},{"text":"IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback","type":"text"},{"type":"hardBreak"},{"text":"EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1","type":"text"},{"type":"hardBreak"},{"text":"IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback","type":"text"},{"type":"hardBreak"},{"text":"EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Run Nightly', ","type":"text"},{"type":"hardBreak"},{"text":" @enabled=1, ","type":"text"},{"type":"hardBreak"},{"text":" @freq_type=4, ","type":"text"},{"type":"hardBreak"},{"text":" @freq_interval=1, ","type":"text"},{"type":"hardBreak"},{"text":" @freq_subday_type=1, ","type":"text"},{"type":"hardBreak"},{"text":" @freq_subday_interval=0, ","type":"text"},{"type":"hardBreak"},{"text":" @freq_relative_interval=0, ","type":"text"},{"type":"hardBreak"},{"text":" @freq_recurrence_factor=0, ","type":"text"},{"type":"hardBreak"},{"text":" @active_start_date=20200219, ","type":"text"},{"type":"hardBreak"},{"text":" @active_end_date=99991231, ","type":"text"},{"type":"hardBreak"},{"text":" @active_start_time=220000, ","type":"text"},{"type":"hardBreak"},{"text":" @active_end_time=235959, ","type":"text"},{"type":"hardBreak"},{"text":" @schedule_uid=N'2a6186ab-043e-447e-88da-4cea7912cf53'","type":"text"},{"type":"hardBreak"},{"text":"IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback","type":"text"},{"type":"hardBreak"},{"text":"EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'","type":"text"},{"type":"hardBreak"},{"text":"IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback","type":"text"},{"type":"hardBreak"},{"text":"COMMIT TRANSACTION","type":"text"},{"type":"hardBreak"},{"text":"GOTO EndSave","type":"text"},{"type":"hardBreak"},{"text":"QuitWithRollback:","type":"text"},{"type":"hardBreak"},{"text":" IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION","type":"text"},{"type":"hardBreak"},{"text":"EndSave:","type":"text"},{"type":"hardBreak"},{"text":"GO","type":"text"}]},{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"paragraph","content":[{"type":"mediaInline","attrs":{"id":"78f62f09-5a71-4890-b6a3-382726cbf761","collection":"contentId-34713333"}}]},{"type":"paragraph","content":[{"type":"hardBreak"}]}],"version":1}

Browser not supported