Spis treści:

Kategoria:SQL Server


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:

RESTORE HEADERONLY FROM DISK'D:\Template.bak'

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):

BackupNameBackupDescriptionBackupType...DatabaseNameDatabaseVersion...
Template-Full Database BackupNULL1...Template655...

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:

SELECT DatabaseName FROM
RESTORE HEADERONLY FROM DISK'D:\Template.bak'

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

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'RESTORE'.

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:

SELECTFROM OPENROWSET('SQLNCLI',
  'server=localhost\SQLEXPRESS;trusted_connection=yes',
  'SELECT 101 [Zdalna kolumna 1]')

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

Zdalna kolumna 1
101

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

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

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:

--Ad Hoc Distributed Queries to opcja zaawansowana
--więc na wszelki wypadek włączmy możliwość
--modyfiakcji takich opcji
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
--Włączmy możliwość zdalnych połączeń
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

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:

--Zdalne wywołanie instrukcji z logowaniem Windows
SELECTFROM OPENROWSET('SQLNCLI',
  'server=localhost;trusted_connection=yes',
  'SELECT 101 [Zdalna kolumna 1]')
--Jeżeli mamy nazwaną instancję, podajemy ją w adresie serwera
SELECTFROM OPENROWSET('SQLNCLI',
  'server=localhost\SQLEXPRESS;trusted_connection=yes',
  'SELECT 102 [Zdalna kolumna 2]')
--Jeżeli logujemy się za pomocą loginu i hasła,
--korzystamy z atrybutów UID i PWD
SELECTFROM OPENROWSET('SQLNCLI',
  'server=localhost\SQLEXPRESS;UID=login;PWD=haslo',
  'SELECT 103 [Zdalna kolumna 3]')

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

SELECTFROM OPENROWSET('SQLNCLI''server=localhost\SQLEXPRESS;trusted_connection=yes',
  'RESTORE HEADERONLY FROM DISK = N''D:\Template.bak''')

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

Msg 7357, Level 16, State 2, Line 1
Cannot process the object "RESTORE HEADERONLY FROM DISK = N'D:\Template.bak'". The OLE DB provider "SQLNCLI10" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

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:

SELECTFROM OPENROWSET('SQLNCLI''server=localhost\SQLEXPRESS;trusted_connection=yes',
  'SET FMTONLY OFF RESTORE HEADERONLY FROM DISK = N''D:\Template.bak''')

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:

exec sp_prepare @p1 output,NULL,N'SET FMTONLY OFF RESTORE HEADERONLY FROM DISK = N''D:\Template.bak''',1

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:

SELECTFROM OPENROWSET('SQLNCLI''server=localhost\SQLEXPRESS;trusted_connection=yes',
     'EXEC(''SET FMTONLY OFF RESTORE HEADERONLY FROM DISK = N''''D:\Template.bak'''''')')

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

, 2013-12-20

Brak komentarzy - bądź pierwszy

Dodaj komentarz
Wyślij
Ostatnie komentarze
Dzieki za te informacje. były kluczowe
Dobrze wyjaśnione, dzięki !
a z innej strony - co gdybym ciąg znaków chciał mieć rozbity nie na wiersze a na kolumny? Czyli ciąg ABCD: 1. kolumna: A, 2. kolumna: B, 3. kolumna: C, 4 kolumna: D?
Ciekawy artykuł.
Czy można za pomocą EF wysłać swoje zapytanie?
Czy lepiej do tego użyć ADO.net i DataTable?