Registrieren Sie sich jetzt!
drop view [dbo].[EMAX0_ENERGY_v] go CREATE VIEW [dbo].[EMAX0_ENERGY_v] WITH SCHEMABINDING AS SELECT [Datum] ,[Haus] ,[Netz+] ,[Netz-] ,[Pv] ,[Batt+] ,[Batt-] , NULL as ProgH , NULL as ProgHIcon , NULL as ProgM , NULL as ProgMIcon FROM [dbo].[2015 EMAX0_ENERGY] union SELECT [Datum] ,[Haus] ,[Netz+] ,[Netz-] ,[Pv] ,[Batt+] ,[Batt-] , NULL as ProgH , NULL as ProgHIcon , NULL as ProgM , NULL as ProgMIcon FROM [dbo].[2016 EMAX0_ENERGY] union SELECT [Datum] ,[Haus] ,[Netz+] ,[Netz-] ,[Pv] ,[Batt+] ,[Batt-] , NULL as ProgH , NULL as ProgHIcon , NULL as ProgM , NULL as ProgMIcon FROM [dbo].[2017 EMAX0_ENERGY] union SELECT [Datum] ,[Haus] ,[Netz+] ,[Netz-] ,[Pv] ,[Batt+] ,[Batt-] ,[ProgH] ,[ProgHIcon] ,[ProgM] ,[ProgMIcon] FROM [dbo].[2018 EMAX0_ENERGY] union SELECT [Datum] ,[Haus] ,[Netz+] ,[Netz-] ,[Pv] ,[Batt+] ,[Batt-] ,[ProgH] ,[ProgHIcon] ,[ProgM] ,[ProgMIcon] FROM [dbo].[2019 EMAX0_ENERGY] -- select * from [dbo].[EMAX0_ENERGY_v]
USE [msdb] GO /****** Object: Job [jobControl] Script Date: 03.06.2020 12:09:11 ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 03.06.2020 12:09:11 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'jobControl', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'No description available.', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'WIN-JPAMM3FCFEK\Administrator', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [Job Check/Cancel] Script Date: 03.06.2020 12:09:11 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Job Check/Cancel', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'declare @start_execution_date datetime declare @DauerMax int = 15 -- 15 min declare @JobName nvarchar(100) = ''myGekko'' declare @uni uniqueidentifier SELECT top 1 @start_execution_date = sja.start_execution_date FROM msdb.dbo.sysjobactivity AS sja INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id WHERE sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL and sj.name = @JobName and sja.start_execution_date > ''20200522 00:00:00'' order by sja.start_execution_date desc if (datediff(minute, @start_execution_date, getdate()) > @DauerMax) begin print ''Dauer überschritten, Job Stop'' exec msdb..sp_stop_job @JobName end ', @database_name=N'master', @flags=16 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'daily', @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=4, @freq_subday_interval=15, @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20200522, @active_end_date=99991231, @active_start_time=0, @active_end_time=235959, @schedule_uid=N'7cf0d044-1855-45f2-8530-6a7b3c2ec94a' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO