Trigger na serwerze 1 - uruchamia job na 2 serwerze

0

Witam,

walczę z tematem i coś nie mogę dojść do brzegu. Generalnie geneza "problemu" jest taka, że chcemy aby na 1 serwerze (SQL Server 2014) gdy zostaną wprowadzone nowe wartości do tabeli - został uruchomiony job na drugim serwerze (także SQL Server 2014). Serwery podłączone ze sobą przez "Linked Servers".

Na serwerze 1 stworzyłem Trigger:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

 CREATE TRIGGER [dbo].[tr_Test] ON [dbo].[Xrate]
    AFTER INSERT
    AS
    BEGIN
	EXEC [CDWSVR03].msdb..sp_start_job
        @job_name = 'TriggerTest2';

    END

GO

Na serwerze 2 stworzyłem job "TriggerTest2" - ot prosty przykład który wprowadza jakieś dane do tabeli.
I teraz tak serwery się "widzą" i mogą się odpytywać ... po małych problemach i googlowaniu mogę także bezpośrednio z serwera 1 odpalić job na serwerze 2 (ustawienie RPC Out = True w właściwościach "Linked ServerS"). Niestety mogę to zrobić tylko bezpośrednio z okna " new query". Natomiast już żeby job się odpalił po przez Trigger dostaję błąd:

OLE DB provider "SQLNCLI11" for linked server "CDWSVR03" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Procedure tr_Test, Line 5
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "CDWSVR03" was unable to begin a distributed transaction.

Dodam, że na początku miałem podobny błąd tylko wtedy w 1 linii miałem:

OLE DB provider "SQLNCLI11" for linked server "CDWSVR03" returned message "CDWSVR03" was unable to begin a distributed transaction."

Ale szukając rozwiązania natrafiłem na artykuł: https://www.virtualizationhowto.com/2015/04/ole-db-provider-sqlncli11-linked-server-unable-distributed-transaction/

Wydaje mi się, że zastosowałem się do wytycznych i ostatecznie błąd zmienił na ten zacytowany jako pierwszy: "..returned message "No transaction is active."."
Nie wiem jak z tym ruszyć dalej :| Podpowiedzcie proszę co tu robić dalej :)

Z góry dzięki,
BB

0

Załóżmy, że zrobiłeś to rozwiązanie.

  1. Wstawiasz wiersz, odpala się JOB
  2. Robisz rollback transakcji (albo serwer robi jak poleci jakiś błąd).

Jak zrobisz rollback JOBa na zdalnym serwerze? :-)

0

Nie zakładam takiej sytuacji ;) ... Generalnie plan jest taki, że system ERP ładuje sobie plik z danymi wbudowanymi skryptami i na koniec operacji wstawia np date do tabeli LOG (robione przez kogoś innego). Ja odpowiadam za to by wykonać operacje jak "odświeżenie" tabeli i przeliczenie wyników po tym gdy pojawi się nowy wpis w tabeli LOG na serwerze 1.
Zakładam, że jeśli coś pójdzie nie tak to nie zostanie wprowadzony nowy wpis do tabeli LOG w ogóle ...

1

Spróbuj zatem to wywołanie joba opakować w transakcję atutonomiczną.

Widzę jeszcze jedną rzecz do przemyślenia. Odpalając joba w triggerze przyblokujesz sesję, która wstawia dane do LOGA. Może to ma dla Ciebie znaczenie, może nie.
Nie wiem czy np. drugie wstawienia do LOGa zanim Twój Job zakończy pracę nie będzie problematyczn (będziesz miał 2 joby równolegle).

Rozważałeś coś innego? Np. Trigger wstaia wiadomość do kolejki, a kolejka ma skojarzoną procedurę (np. wywołanie tego joba) + max. ilość wątków?
Jeśli się nie mylę, kolejka powinna być obsługiwana asynchronicznie i sesja wstawiająca do loga nie będzie blokowana.

0

Ok .. doczytuje/dokształcam się teraz na temat "transakcji autonomicznej" i będę próbował. (ewentualnie jakieś przykłady / rozwiązania do mojej konkretnej sytuacji mile widziane )

Co do reszty: raczej nie powinno mieć znaczenia - rekord do LOGA będzie wstawiany raz dziennie po zakończeniu wcześniejszych operacji. Job zrobi swoje w ciągu kilku/kilkunastu minut.
Nie rozważałem kolejki, nie mam praktyki z tym - natomiast spróbowałem w Triggerze na serwerze 1 wywołać procedure (zamiast joba) na serwerze2 ale zwraca ten sam błąd.

Edit: Ok dzięki Tobie i podpowiedziom o "trnskacji autonomicznej" - znalazłem rozwiązanie aby w ustawieniach Linked Server zmienić "remote proc transaction promotion" na False.
Po zmianie działa jak zakładałem. Oczywiście wezmę pod uwagę także inne Twoje punkty gdy zaczniemy testy. Dzięki wielkie za pomoc!

0

Nigdy nie robiłem tego w ten sposób, czemu to jest akurat job?

Generalnie raz miałem sytuacje, żeby dodanie czegoś w jednej tabeli generowało wykonanie akcji na drugim serwerze, ale wtedy użyłem service brokera do "gadania" między serwerami.
Jako ze było to jakiś czas temu to szczegółów implementacyjnych nie pamiętam (dostępu do baz już też nie mam) ale działało to sprawnie.

Do poczytania
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-service-broker
https://technet.microsoft.com/en-us/library/bb839483(v=sql.105).aspx

1 użytkowników online, w tym zalogowanych: 0, gości: 1