Mit DeltaMaster Publisher steht ein großartiges Werkzeug zur Verfügung, um Berichte aktuell und zielgruppenorientiert zu erzeugen und bereitzustellen. Standardberichte werden in der Regel zu definierten Zeitpunkten erstellt und verschickt. Trotzdem gibt es immer wieder die Anforderung, ad hoc einen Bericht oder eine Berichtsgruppe zu versenden. Jedoch ist der Publisher nicht immer bei den Anwendern installiert oder die IT kann nicht zeitnah unterstützen. Für diesen Fall wird in diesem Blogbeitrag erläutert, wie einzelne Jobs dynamisch aus DeltaMaster heraus über den SQL Server-Agent angestoßen werden können.

Das Grundgerüst

Zunächst werden die für diese Aktion grundsätzlichen, notwendigen Berechtigungen und Bestand-teile veranschaulicht.

Custom App Berechtigungen

Da der Aufruf aus DeltaMaster über die CustomApp heraus erfolgt, muss die Nutzung der CustomApp generell aktiviert sein. Über die Tabelle „T_SYS_CustomApp_Rights“ kann die Ausfüh-rung der einzelnen Menüeinträge, die im CustomApp Menü erscheinen, für einzelne Benutzer oder Benutzerrollen erteilt oder verweigert werden. Weiterhin müssen die Benutzer für P_APP Prozeduren berechtigt werden, also die im Menüpunkt verwendeten P_APP_Select_ und die aufgerufenen P_APP Prozeduren.

Datenbank Berechtigungen

In diesem Schritt werden die notwendigen User und Gruppen angelegt und in den entsprechenden Bereichen berechtigt.

Windows Berechtigungen

Zunächst wird empfohlen, eine Windows Benutzergruppe einzurichten, damit später ggf. weitere User einfach hinzugefügt werden können.

Abbildung 1: Lokale Gruppe anlegen

Danach werden die entsprechenden AD-User dieser Gruppe hinzugefügt.

SQL Server Berechtigungen

Im nächsten Schritt wird diese Gruppe dem SQL Server als Login bekannt gemacht und in der msdb Datenbank in die Rolle „SQLAgentOperatorRole“ aufgenommen. Da hier dynamisch Jobs erstellt und gelöscht werden sollen, reichen die Rollen „SQLAgentReaderRole“ oder „SQLAgentUserRole“ nicht aus. Eine exaktere Differenzierung lässt Microsoft hier leider nicht zu.

Abbildung 2: SQL Server Login Informationen

Microsoft empfiehlt für den SQL Server-Agent die Verwendung eines Proxys. Hier wird zur Vereinfachung DeltaMaster Service User (bc-hsg-lap\dmrs) verwendet. Zunächst muss dieser User jedoch in einer neuen Anmeldeinformation hinterlegt werden.

Abbildung 3: Neue Anmeldeinformation für Proxy

Unter dem Menüpunkt SQL Server-Agent/Proxy/Betriebssystem (CmdExec) wird schließlich ein neues Proxy Konto eingerichtet und die obige Anmeldeinformation diesem Konto zugeordnet.

Abbildung 4: Zuordnung Anmeldeinformation zu Proxykonto

Repository Berechtigungen

Nun muss die Gruppe noch im Repository hinzugefügt und den Rollen „Berichtsverteilung ausführen“ und „Berichtsverteilung definieren“ zugeordnet werden.

Abbildung 5: Berechtigungen in der Repository Verwaltung

Der verwendete DeltaMaster Service User muss natürlich noch in den betroffenen Datenbanken berechtigt werden. Bei der hier verwendeten Chair und in der Publisher Datenbank reichen Leserechte aus. In der Repository Datenbank sind „db_owner“ Rechte notwendig.

Prozeduren und Logik

Zunächst wird eine P_APP_Select_Job Prozedur erstellt, die die vorhandenen Jobs aus dem Publisher ausliest und in DeltaMaster zur Auswahl anbietet. Alternativ könnten hier auch Jobgruppen abgefragt werden.

CREATE PROC [dbo].[P_APP_SELECT_Job] 
AS 

BEGIN 

SELECT 
[JobID]	AS PublisherJobID, 
[Description]	AS JobName 
FROM 
DeltaMasterPublisher.dbo.Job 
ORDER BY 1 
END  

Danach wird im SQL Server-Agent mit dem Aufruf des DeltaMaster Publishers via „Ausführen als” unter dem angelegten Proxy ein generischer Job erstellt und der Code dazu wird als Skript im SQL Server Management Studio ausgegeben.

Abbildung 6: Neuer Job im SQL Server-Agent

Dieses Grundgerüst wird nun etwas „getunt“, damit generische Jobs mit Übergabe der Publisher JobID und eindeutigen Namen erzeugt werden können.

Daher werden einige Variablen benötigt, die den Zeitstempel und den User angeben, um daraus den eindeutigen Job Namen zu erstellen.

DECLARE @Zeitstempel SMALLDATETIME = GETDATE(); 
DECLARE @JobStarter	 NVARCHAR(MAX) = SUSER_NAME(); 
DECLARE @JobName	 NVARCHAR(MAX) = CONCAT(N'BerichtsversandManuell', '_', @JobStarter, '_', @Zeitstempel);  

Danach wird der starre Aufruf des Publishers mit einer fixen Jobnummer auf einen dynamischen Aufruf mit Übergabe der JobID aus der CustomApp geändert.

DECLARE @Command	 NVARCHAR(MAX) = N'"C:\Program Files (x86)\DeltaMaster
6\DeltaMaster.Publisher.exe" ' + @PublisherJobID; 

Als nächstes wird noch ein Aufruf zum Starten des Jobs benötigt und über eine While Schleife mit Abfrage in der msdb wird überprüft, ob der Job beendet ist.

EXEC msdb.dbo.sp_start_job @JobName; 

DECLARE @Job_UID UNIQUEIDENTIFIER; 
SELECT @Job_UID = job_id 
FROM msdb.dbo.sysjobs 
WHERE name = @JobName; 

WHILE ( 
      ( 
          SELECT COUNT(*) 
          FROM msdb.dbo.sysjobactivity 
          WHERE job_id = @Job_UID 
                AND stop_execution_date IS NOT NULL 
      ) < 1 
      ) 
BEGIN 
    WAITFOR DELAY '00:00:01'; 
END; 

Abschließend muss der Job dann noch nach Beendigung gelöscht werden.

EXEC msdb.dbo.sp_delete_job @job_name = @JobName;

Dieser ganze Code kurz zusammengefasst ergibt folgendes Skript:

CREATE PROC [dbo].[P_APP_StartPublisherJob_SQLAgent] 
(@PublisherJobID VARCHAR(MAX)) 
AS 
DECLARE @Zeitstempel SMALLDATETIME = GETDATE(); 
DECLARE @JobStarter	 NVARCHAR(MAX) = SUSER_NAME(); 
DECLARE @JobName	 NVARCHAR(MAX) = CONCAT(N'BerichtsversandManuell', '_', @JobStarter, '_', @Zeitstempel); 
DECLARE @Command	 NVARCHAR(MAX) = N'"C:\Program Files (x86)\DeltaMaster
6\DeltaMaster.Publisher.exe" ' + @PublisherJobID; 

BEGIN TRANSACTION; 
DECLARE @ReturnCode INT; 
SELECT @ReturnCode = 0; 

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 = @JobName, 
                                       @enabled = 1, 
                                       @notify_level_eventlog = 0, 
                                       @notify_level_email = 0, 
                                       @notify_level_netsend = 0, 
                                       @notify_level_page = 0, 
                                       @delete_level = 0, 
                                       @description = N'Keine Beschreibung verfügbar.', 
                                       @category_name = N'[Uncategorized (Local)]', 
                                       @owner_login_name = N'BC\huesgen', 
                                       @job_id = @jobId OUTPUT; 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) 
    GOTO QuitWithRollback; 

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId, 
                                           @step_name = N'Berichtsversand', 
                                           @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'CmdExec', 
                                           @command = @Command, 
                                           @flags = 0, 
                                           @proxy_name = N'DMSQLAgentProxyKonto'; 
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_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: 

EXEC msdb.dbo.sp_start_job @JobName; 

DECLARE @Job_UID UNIQUEIDENTIFIER; 
SELECT @Job_UID = job_id 
FROM msdb.dbo.sysjobs 
WHERE name = @JobName; 

WHILE ( 
      ( 
          SELECT COUNT(*) 
          FROM msdb.dbo.sysjobactivity 
          WHERE job_id = @Job_UID 
                AND stop_execution_date IS NOT NULL 
      ) < 1 
      ) 
BEGIN 
    WAITFOR DELAY '00:00:01'; 
END; 

EXEC msdb.dbo.sp_delete_job @job_name = @JobName; 

Konfiguration CustomApp

Zur generellen Konfiguration und den vielfältigen Optionen der CustomApp sei hiermit auf den hervorragenden Blogbeitrag „CustomApp Teil1 - Anwendung im Client“ verwiesen.

Der Menüeintrag wird erzeugt, indem die gewünschten Texte und Parameter in die Tabelle „T_SYS_CustomAppMenue“ eingetragen werden. Der Subtype = 2 (Synchroner Aufruf einer Prozedur mit Parametern ohne Transaktion) wird ausgewählt und in der Spalte Criteria1 steht der Name der Prozedur, die den Job erzeugt.

Abbildung 7: Einträge „T_SYS_CustomAppMenue“

In die dazugehörige Parameter Tabelle wird eingetragen, welche Prozedur die Combobox mit Inhalten füllt (Spalte SQLCommand) und welcher Parameter an die Prozedur übergeben werden soll (ParameterID).

Weiterhin ist die Spalte InputType = 2 wichtig, da dies eine Combobox ohne zusätzliche freie Eingabemöglichkeit erzeugt.

Abbildung 8: Einträge „ T_SYS_CustomAppMenue_ProcParameters“

Über die Tabelle „T_SYS_CustomApp_Rights“ wird festgelegt, welcher User/Rolle welchen Menüeintrag nutzen darf.

Abbildung 9: Einträge „ T_SYS_CustomApp_Rights“

Aufruf in DeltaMaster

Nun wird betrachtet, wie der Aufruf aus DeltaMaster aussieht.

Abbildung 10: DeltaMaster CustomApp Menü

Abbildung 11: DeltaMaster Auswahlliste

Abbildung 12: DeltaMaster Ergebnis

Damit steht dem Anwender nun eine komfortable und dynamische Möglichkeit zur Verfügung, um Publisher Jobs zu starten. Das Prinzip lässt sich beliebig erweitern, um beispielsweise Datenladeprozesse oder Aufbereitungsprozesse anzustoßen, außerhalb der regulären fest definierten Nachtprozesse.