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.
