SELECT z instrukcji systemowych

Automatyzacja wielu zadań systemowych wymaga użycia dynamicznych skryptów. Jest jednak pewien problem - część instrukcji nie daje się w łatwy sposób zawrzeć w instrukcjach SELECT, JOIN i innych podobnych konstrukcjach. Przyjrzyjmy się następującemu przypadkowi:

Istrukcja służy do podglądnięcia nagłówka kopii zapasowej bazy (ang. backup). Wynikiem powyższych instrukcji jest tabela podobna do zaprezentowanej poniżej (większość kolumn został pominięta):

I teraz najważniejsze - niby jest to tabela, ale nie da się tutaj w prosty sposób dołączyć innych danych, nie da się zawęzić wyników wybierając tylko kilka kolumn w instruckji SELECT. Mówiąc prościej: nie da się napisać między innymi takiej instrukcji:

Komunikat informuje nas, że coś złego jest ze składnią:

Nie pomagają nawiasy, przezywanie tabel. Analiza języka T-SQL również nie pozostawia złudzeń - tak się nie da. Da się nieco inaczej, ale o tym za chwilę.

Jak zapisać wyniki instrukcji systemowych w zmiennej

Podstawowa składnia SELECT nie daje się zaprząc do takich zadań. Aby trochę naprowadzić na rozwiązanie podpowiem, że biblioteki ADO radzą sobie z takimi instrukcjami bez żadnego problemu. Czy da się lokalnie zasymulować zdalne wywołanie instrukcji?

W takich przypadkach zasadne wydaje się użycie instrukcji OPENROWSET. Instrukcja stworzona jest właśnie do tego, aby umożliwić wykonanie zdalnego połączenia (w naszym przypadku zasymulować), a następnie pobrać potrzebne dane. Instrukcję OPENROWSET można wykorzystać na wiele sposobów. Nam potrzebna będzie wersja z następującymi parametrami: OPENROWSET(provider, connection_string, query). Provider to sterownik, który będzie wykorzystywany do wykonania połączenia. Podstawowy strownik OLE DB został nazwany SQLNCLI. Connection_string to łańcuch połączeniowy. W najprostszym przypadku podajemy adres serwera, oraz informacje potrzebne do logowania. Będzie to atrybut trusted_connection dla logowania Windows lub atrybuty UID i PWD dla nazwy użytkownika i hasła. Trzeci parametr, query, to nasza instrukcja.

OPENROWSET zachowuje się jak zwykła tabela. Można w takim zbiorze filtrować dane (WHERE), łączyć z innymi tabelami (JOIN), wybierać to co nam potrzebne (SELECT) i wykonywać wiele innych wymyślnych operacji. Przyjrzyjmy się zatem poniższej instrukcji:

Jeżeli instrukcja się wykona, powinniśmy ujrzeć następujący rezultat:

Zdarza się jednak, że zamiast wyniku pojawi się komunikat podobny do tego:

Oznacza on, że najprawdopodobniej mamy wyłączoną usługę zdalnych zapytań i musimy użyć sp_configure. Jeżeli wyświetli nam się taki błąd, należy wykonać instrukcje z listingu poniżej:

Aby system zareagował na zmianę opcji należy po każdej z nich uruchomić instrukcję RECONFIGURE. Po ustawieniu opcji można jeszcze raz wykonać instrukcję OPENROWSET. Tym razem powinno zadziałać.

Przyjrzyjmy się jeszcze kilku możliwościom połączenia wykorzystywanym w instrukcji OPENROWSET:

Wydaje się, że to już wszystko, ale dalej nie udaje się wykonać takiej oto instrukcji:

Tym razem komunikat zmienia się na nieco bardziej tajemniczy i według mnie trochę mylący:

Obiekt zwraca kolumny, a użytkownik posiada pełne prawa (jeżeli nie, to można spróbować z takim, który posiada), a mimo tego nie da się wykonać instrukcji. Co się stało, że nie działa?

Nieprawidłowe pobieranie metadanych

Tytuł nagłówka może się wydawać tajemniczy, ale zaraz wyjaśnię o co chodzi. Instrukcje do wywołań zdalnych, w tym OPENROWSET (to samo dotyczy instrukcji OPENQUERY), potrzebują na etapie kompilacji metadanych o kolumnach. Aby pobrać metadane OPENROWSET wykonuje zapytanie z włączoną opcją FMTONLY (SET FMTONLY ON). W wyniku takiej operacji sterownik otrzymuje komunikat o błędzie, bo nie udaje się znaleźć metadanych instrukcji systemowej RESTORE HEADERONLY. Problem dotyczy wszystkich instrukcji, które nie posiadają metadanych, między innymi procedur systemowych. Kolumn nie ma - stąd właśnie komunikat.

Rozwiązaniem problemu jest jawne wskazanie, że nie chcemy metadanych, czyli wyłączenie opcji FMTONLY.

Takie rozwiązanie jest nawet sugerowane w dokumentach Microsoftu, chociaż dokumentacja FMTONLY mówi jasno - nie używaj tej opcji. Trochę pokrętna ta dokumentacja, co w połączeniu z niedziałającą instrukcją zaprezentowaną poniżej może zasiać w głowie niepewność. Oto wspomniana instrukcja:

Komunikat jest ten sam, nie ma kolumn lub brak praw. Gdzie szukać kolejnych problemów?

Przygotowanie instrukcji czyli sp_prepare

Okazuje się, że to nie jest takie proste. Tradycyjnie przetwarzane instrukcje na takie polecenia zareagują. Przetwarzanie zdalne działa odrobinę inaczej. Pierwszy etap to przygotowanie instrukcji, a drugi to wykonanie jej. Cała koncepcja FMTONLY załamuje się z chwilą potraktowania SET FMTONLY OFF RESTORE ... jako jednej spójnej instrukcji. Do bazy danych przychodzi takie oto zadanie:

Jest to pierwsza faza, samo przygotowanie instrukcji. Domyślne ustawienie FMTONLY OLE DB nie zostaje w żaden sposób nadpisane, a kolumn z metadanymi jak nie było, tak nie ma.

Jawny kontekst OPENROWSET

SQL Server 2005 rozszerza instrukcję EXEC w taki sposób, aby mogła ona przesyłać instrukcje bezpośrednio (ang. pass-through) do zdalnego serwera bazy danych. Co więcej, tworzy dla takich instrukcji oddzielny kontekst. Bezpośrednie przesłanie sprawia, że nie ma dwóch faz: przygotowanie (sp_prepare) i wykonanie (sp_execute), lecz pełna jedna instrukcja EXEC (tak prawdę mówiąc faz jest więcej, bo trzeba jeszcze po sobie posprzątać (sp_unprepare), ale nie ma to wpływu na przedstawiany proces). Takie rozwiązanie prowadzi nas do ostatecznego rozwiązania:

Warto zwrócić uwagę na mnogość apostrofów. Jest to spowodowane koniecznością kodowania pojedynczego apostrofu w stałej tekstowej dwoma takimi samymi znakami. Kodowanie musimy wykonać dwukrotnie, więc z jednego początkowego apostrofu robią się cztery.

Podsumowanie

Tak oto po długiej drodzie doszliśmy do końcowego rozwiązania. Trzeba wiedzieć, że nie wszystkie zdalne instrukcje wymagają aż takich nakładów pracy. Rozpatrzony został chyba najtrudniejszy przypadek. Większość nie wymaga instrukcji EXEC, ogromna część nie wymaga nawet instrukcji SET FMTONLY OFF. I w końcu - nie wszystkie operacje wymagają użycia OPENROWSET - na szczęście!

Kategoria:SQL Server