Dobry sposób wywoływania wielu zadań agenta programu SQL Server kolejno z jednego głównego zadania?

Mam kilka zadań agenta SQL Server, które powinny działać sekwencyjnie. Aby zachować ładny przegląd zadań, które powinny zostać wykonane, utworzyłem główne zadanie, które wywołuje inne zadania z wywołaniem EXEC msdb.dbo.sp_start_job N'TEST1'. Do sp_start_jobwykończenia błyskawicznie Job (etap 1), ale potem chcę moje główne zadanie czekać aż zadanie TEST1zostało zakończone przed wywołaniem następnej pracy.

Napisałem więc ten mały skrypt, który zaczyna się wykonywać zaraz po wywołaniu zadania (Etap 2) i zmusza główne zadanie do czekania, aż zadanie podrzędne się zakończy:

WHILE 1 = 1
  BEGIN
    WAITFOR DELAY '00:05:00.000';

    SELECT *
    INTO   #jobs
    FROM   OPENROWSET('SQLNCLI', 'Server=TESTSERVER;Trusted_Connection=yes;',
           'EXEC msdb.dbo.sp_help_job @job_name = N''TEST1'',
           @execution_status = 0, @job_aspect = N''JOB''');

    IF NOT (EXISTS (SELECT top 1 * FROM #jobs))
      BEGIN
        BREAK
      END;

    DROP TABLE #jobs;

  END;

To działa wystarczająco dobrze. Ale mam wrażenie, że mądrzejsze i / lub bezpieczniejsze ( WHILE 1 = 1?) Rozwiązania powinny być możliwe.

Jestem ciekawy następujących rzeczy, mam nadzieję, że możesz podać mi pewne informacje:

• Jakie są problemy z tym podejściem?
• Czy możesz zasugerować lepszy sposób na zrobienie tego?

( Najpierw opublikowałem to pytanie na StackOverflow , ponieważ skupiałem się na ulepszeniu kodu. Nadal jest poprawny. Ale zgaduję, że ludzie tutaj będą ogólnie mieli mądrzejsze rzeczy do powiedzenia na temat tego, dlaczego nie powinienem robić tego tak, jak ja ”. robię to teraz lub zapewniam dobre alternatywy).

EDYCJA (25 lipca)
Najwyraźniej nie ma zbyt wiele błędów w moim skrypcie, zgodnie z małą liczbą odpowiedzi wskazujących na problemy z nim :-) Alternatywą dla tego rodzaju skryptów wydaje się być użycie narzędzia zaprojektowanego dla tych skryptów zadania (takie jak SQL Sentry Event Manager lub ...) - lub samodzielne napisanie takiego narzędzia. Nie będziemy kupować takiego narzędzia w mojej obecnej firmie, więc na razie pozostanę przy skrypcie.

Uwaga: Pracuję dla SQL Sentry.

Nasz produkt SQL Sentry Event Manager ma właśnie taką funkcję: do łączenia zadań i ustawiania ich w różne zamówienia przepływu pracy.

Zacząłem używać SQL Sentry wiele lat temu, zanim dołączyłem do firmy, aby to zrobić. To, czego chciałem, to sposób rozpoczęcia zadania przywracania na naszym serwerze testowym natychmiast po zakończeniu tworzenia kopii zapasowej na produkcji.

Pierwotnie zaimplementowałem tylko znaczny bufor między czasem rozpoczęcia zadania tworzenia kopii zapasowej a czasem rozpoczęcia przywracania. To nie było do końca niezawodne; ponieważ czasy tworzenia kopii zapasowych były różne, bufor często pozostawiał nam zmarnowany czas, w którym przywracanie nie rozpoczęło się, nawet gdyby mogło. Czasami bufor nie wystarczał.

To, co następnie wdrożyłem, było podobne do tego, co masz - napisałem zadanie na serwerze testowym, który rozpoczął się wkrótce po zaplanowanym utworzeniu kopii zapasowej, i nadal odpytywałem, aby zobaczyć, kiedy zadanie zostało zakończone. Zostało to później zmienione, aby mieć tylko drugi krok w kopii zapasowej, która zaktualizowała tabelę na serwerze testowym. Nie bardzo różni się, z tym wyjątkiem, że zadanie przywracania musiało tylko oglądać lokalnie tabelę zamiast zdalnego monitorowania historii zadań. Przypomnienie tego mogło być wyzwalaczem dla tej tabeli, która wywołała, sp_start_jobwięc zadanie nie musiało być uruchamiane co n minut (lub w ogóle być planowane).

Ostatnim rozwiązaniem było połączenie zadań razem ... po zakończeniu tworzenia kopii zapasowej na serwerze A Menedżer zdarzeń uruchamia zadanie przywracania na serwerze B. A jeśli było trzecie zadanie i czwarte zadanie lub logika warunkowa na podstawie tego, co należy zrobić kiedy zadanie kończy się niepowodzeniem, a kończy się sukcesem itp., można to wszystko uwzględnić. Projektant przepływu pracy przypomni ci trochę o SSIS:

Oczywiście mechaniką tego, co opisuję, nie jest operacja rakietowa. Możesz sam napisać ten rodzaj owijarki, jeśli usiądziesz i to zrobisz. Po prostu daje Ci jedną alternatywę, gdzie nie musisz.

Główny problem z twoim podejściem polega na tym, że musisz ciągle zapętlać, aż coś się wydarzy (co może być okropnie długo lub nawet nigdy) i to nie wydaje się właściwe. Dlatego myślę, że zadajesz pytanie.

A co powiesz na podejście oparte na danych do Twojego problemu? Na przykład utwórz tabelę „audytu”, do której każde zadanie zapisze po uruchomieniu i zakończeniu:

Nazwa pracy | Czas rozpoczęcia | Koniec czasu
--------- + ------------------- + ------------------
Test1 2012-07-26 07:30 2012-07-26 07:35

Utwórz tabelę „przetwarzania”, która zawiera listę wszystkich zadań i kolejność, w jakiej muszą zostać wykonane:

Nazwa pracy | Uruchom zamówienie
--------- + ---------
Test1 | 1
Test2 | 2)
Test3 | 3)

Utwórz wyzwalacz wstawiania w tabeli kontroli, aby po zakończeniu zadania i wstawieniu rekordu kontroli wyzwalacz zapytał tabeli przetwarzania o następne zadanie (według polecenia uruchomienia), a następnie uruchomił go.

Korzyści z tego podejścia to:

1. Jest dość prosty w rozwoju i utrzymaniu.
2. Daje to możliwość dodawania nowych zadań lub zmiany kolejności istniejących zadań za pomocą tabeli przetwarzania bez konieczności zmiany wiersza kodu.
3. Tabela kontroli pokazuje, kiedy coś się wydarzyło.
4. Nie marnuje cykli procesora. Spust zadziała tylko, gdy coś się wydarzy.
5. To czuje się w prawo 😉

HTH