Kilka słów o SQLCMD Mode w Visual Studio i Management Studio

SQLCMD jest narzędziem linii komend pozwalającym na połączenie do instancji SQL Server, wykonywanie na niej szeregu poleceń wzbogaconych o funkcjonalności wbudowane w to narzędzie. Zarówno w Management Studio jak i Visual Studio mamy możliwość wykonywania standardowych komend języka TSQL, które mogą być wzbogacone o komendy SQLCMD w ramach tego samego okna zapytań. Jest to bardzo użyteczna technika wykorzystywana do wielu celów m.in.  do obsługi omawianych wcześniej Pre i Post Deployment skryptów w projekcie bazodanowym Visual Studio. W ramach niniejszego artykułu opowiemy sobie jakie komendy mamy do dyspozycji w ramach SQLCMD Mode i w jaki sposób mogą nam one pomóc w codziennej pracy – zapraszam do lektury.

Na samym wstępie pokażmy sobie gdzie można włączyć SQLCMD Mode. W przypadku Management Studio wybieramy z menu Query -> SQLCMD Mode:

W przypadku zapytań pisanych z poziomu Visual Studio wybieramy SQL -> Execution Settings -> SQLCMD Mode:

Jeśli chcemy możemy również włączyć SQLCMD jako domyślny tryb działania naszych okien zapytań w Visual Studio jest to dostępne w opcjach w SQL Server Tools-> Transact-SQL Editor -> Query Execution -> General (w SSMS będzie to Query Execution -> SQL Server):

Nasze przykłady będziemy pokazywać na podstawie Visual Studio jednakże niemal identycznie wygląda to w SSMS.  Zanim zaczniemy testować SQLCMD Mode warto wspomnieć, że nie wszystkie dostępne komendy SQLCMD działają w omawianym przez nas trybie. Z dokumentacji możemy wyciągnąć informacje, że następujące struktury są wspierane:

• [!!:]GO[count]
• !! <command>
• :exit(statement)
• :Quit
• :r <filename>
• :setvar <var> <value>
• :connect server[\instance] [-l login_timeout] [-U user [-P password]]
• :on error [ignore|exit]
• :error <filename>|stderr|stdout
• :out <filename>|stderr|stdout

Dziś omówimy sobie kilka najważniejszych. Zacznijmy od komendy :out – w celach testowych wykonałem następujące polecenia z okna zapytań z włączonym SQLCMD Mode:

:out C:\temp\Person.txt  

SELECT TOP 100 * FROM Person.Person

Zwróćcie uwagę na kolorowanie składni gdzie komendy SQLCMD są zaznaczone szarym kolorem tła, a SQL ma standardowe kolorowanie składni dzięki czemu w łatwy sposób możemy je od siebie odróżnić:

W odróżnieniu od języka SQL nie możemy umieszczać kilku komend SQLCMD w jednej linii gdyż otrzymamy błąd. Wracając do tematu – co robi zatem użyta komenda :out? Nic innego jak to, że zapisuje rezultat zapytania do wskazanego pliku. Format w jakim zapisane będą dane jest możliwy do ustawienia, wystarczy w Visual Studio wejść w opcje zapytań SQL -> Execution Settings -> Query Options:

Właśnie w tym oknie w sekcji Results -> Text możemy wybrać czy rezultat ma być formatowany jako np. Column aligned, Comma Delimited czy ma zawierać zapytanie które ten rezultat wygenerowało itp:

Jest to dosyć ciekawa funkcjonalność o której istnieniu nie każdy wie, a jest szybkim sposobem na eksport rezultatu zapytania bez konieczności odpalania dodatkowych narzędzi jak Import Export Wizard. Do pliku możemy wyeksportować nie tylko rezultaty wykonania zapytań ale również informacje o potencjalnych błędach. Do tego typu działań służy komenda :error – pokażmy to sobie na konkretnym przykładzie. Napisałem następujące zapytanie do tabeli Person.Person gdzie wybrać rekord o identyfikatorze ID=1:

SELECT * FROM Person.Person
WHERE ID=1

Niestety tabela nie posiada kolumny o nazwie ID i w standardowym oknie zapytań zwrócony zostanie błąd:

Msg 207, Level 16, State 1, Line 5
Invalid column name 'ID'.

Ten właśnie tekst możemy przekierować do pliku używając następującej składni:

:error C:\Temp\Errors.txt

SELECT * FROM Person.Person
WHERE ID=1

Ważne jest to aby pamiętać, że przekierowanie błędu powoduje, że na zakładce Messages nie dostaniemy już szczegółowej informacji na temat błędu, ale samo narzędzie zasygnalizuje nam jedynie, że taki błąd wystąpił:

Plik Errors.txt jest za każdym razem nadpisywany jednakże zawiera on błędy z danego wykonania więc w momencie gdy mamy więcej niż jedno zapytanie:

:error C:\Temp\Errors.txt

SELECT * FROM Person.Person
WHERE ID=1
GO

SELECT * FROM Person.Person
WHERE ID=2
GO

to w pliku znajdą się błędy pochodzące z obu komend:

Msg 207, Level 16, State 1, Line 4
Invalid column name 'ID'.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'ID'.

Oczywiście mamy pewną dowolność i poszczególne komendy mogą trafiać do osobnych plików:

:error C:\Temp\Errors.txt 
SELECT * FROM Person.Person 
WHERE ID=1 
GO 

:error C:\Temp\Errors2.txt 

SELECT * FROM Person.Person 
WHERE ID=2 GO

Z błędami wiąże się jeszcze jedna ważna komenda, a mianowicie :on error. Przyjmuje ona dwie wartości, a mianowicie exit oraz ignore i jak sama nazwa wskazuje determinuje co ma się stać w przypadku wystąpienia błędu. Jeśli ustawimy exit to skrypt się zatrzyma na pierwszym napotkanym błędzie, w przypadku ignore wykonanie będzie kontynuowane.

Spróbujmy zatem wykonać następujące zapytania:

:on error exit

SELECT * FROM Person.Person
WHERE ID=1
GO

SELECT TOP 10 * FROM Person.Person
GO

W rezultacie otrzymaliśmy błąd ale kolejne zapytanie z SELECT TOP 10 nie zostało wykonane o czym świadczy puste okno Result:

W przypadku gdy zignorujemy błędy to pierwsza wykona się z błędem, a kolejna wykona się poprawnie:

:on error ignore

SELECT * FROM Person.Person
WHERE ID=1
GO

SELECT TOP 10 * FROM Person.Person
GO

Po co nam taki mechanizm? Daje nam to mnóstwo możliwości kontroli nad tym co i jak ma się dziać w konkretnych okolicznościach co jest bardzo ważne np. w przypadku tworzenia skryptów deploymentowych itp.

Przechodząc dalej mamy kolejną funkcjonalność, która pozwala nam wykonywać standardowe komendy znanej z Windows linii komend. Aby tego dokonać wystarczy wpisać dwa wykrzykniki, a następnie interesującą nas komendę – jej rezultat powinniśmy zobaczyć w zakładce Messages okna zapytań:

!!dir C:\Temp

Powyższa komenda zwróciła zawartość folderu Temp:

 Volume in drive C is OS
 Volume Serial Number is 2410-C674

 Directory of C:\Temp

04/05/2020  11:04 AM    <DIR>          .
04/05/2020  11:04 AM    <DIR>          ..
04/05/2020  11:04 AM         1,576,349 Person.txt
               1 File(s)      1,576,349 bytes
               2 Dir(s)  178,528,067,584 bytes free

Po xp_cmdshell jest to kolejna możliwość wywołania konkretnych komend z poziomu SQL co z całą pewnością może znaleźć ciekawe zastosowania.

Przejdźmy dalej do kolejnej komendy, którą po prostu trzeba znać czyli :r. Umożliwia ona wskazanie konkretnego pliku z komendą SQL, która zostanie wykonana. W moim testowym folderze C:\Temp zapisałem plik Query.sql w którym wpisałem następującą komendę:

USE AdventureWorks2017
GO

SELECT * FROM Person.Person
GO

Z poziomu okna zapytań możemy odnieść się do tego pliku w następujący sposób:

:r C:\Temp\Query.sql

Dzięki temu zapytanie z pliku zostało wywołane i wykonane w poprawny sposób:

Komenda ta jest niezwykle istotna i jest szeroko stosowana w projektach bazodanowych do odwoływania się do konkretnych komend w plikach (wspomniałem o tym w artykule o Pre i PostDeployment skryptach gdzie pokazałem jedno z zastosowań – artykuł znajduje się tutaj).

Kolejną omawianą komendą trybu SQLCMD jest :setvar czyli możliwość definiowania zmiennych w skrypcie, które następnie możemy użyć w standardowych poleceniach języka TSQL lub w innych komendach SQLCMD.  W przypadku tej konkretnej jej użycie jest bardzo proste i sprowadza się do podania nazwy zmiennej oraz wartości. Nieco wyżej zdefiniowaliśmy plik błędu do którego trafiały wszelkie informacje o niepowodzeniu konkretnych akcji. Powiedzmy sobie, że chcemy mieć możliwość zmiany ścieżki i nazwy pliku do którego będą trafiać błędy. Z łatwością uzyskamy pożądany efekt wykorzystując zmienne:

:setvar ErrorFilePath "C:\Temp\"
:setvar ErrorFileName "Errors.txt"
:error $(ErrorFilePath)$(ErrorFileName)


SELECT * FROM Person.Person WHERE ID=1
GO

Jak wspomniałem definiowanie zmiennej sprowadza się do zapisu :setvar nazwa wartość, a do konkretnych zmiennych odwołujemy się wpisując $(nazwa). W powyższym przykładzie użyliśmy zmiennej w komendzie SQLCMD, ale równie dobrze możemy to zrobić w komendach TSQL:

:setvar TableName "Production.Product"

SELECT TOP 10 * FROM $(TableName)

Rezultat został zwrócony w prawidłowy sposób bez żadnych komplikacji. Możemy również odwoływać się np. do określonej nazwy bazy danych w której znajduje się obiekt, który chcemy odpytać czy też podać listę interesujących nas kolumn:

:setvar ColumnsName "Name,ProductNumber,MakeFlag"
:setvar DatabaseName "AdventureWorks2017"

SELECT TOP 10 $(ColumnsName) FROM $(DatabaseName).Production.Product

W ramach ciekawostki można powiedzieć, że ze względu na fakt, iż działamy w SQLCMD mode to możemy parametryzować niemal całe zapytanie:

:setvar NumberOfRows "10"
:setvar ColumnsName "Name,ProductNumber,MakeFlag"
:setvar DatabaseName "AdventureWorks2017"
:setvar SchemaName "Production"
:setvar TableName "Product"

SELECT TOP $(NumberOfRows) $(ColumnsName) FROM $(DatabaseName).$(SchemaName).$(TableName)

Coś takiego możemy osiągnąć jako alternatywę do dynamicznego SQL jednakże pamiętajmy, że we wszystkim liczby się zdrowy rozsądek (ale warto pamiętać, że technicznie jak najbardziej jesteśmy to w stanie zrobić). Zmienne oraz parametryzacja skryptów jest bardzo istotnym czynnikiem pracy z projektem bazodanowym i z całą pewnością powrócę do niego w najbliższym czasie w ramach osobnego artykułu.

Zbliżamy się do końca, a tutaj komenda :connect umożliwiająca podłączenie się do konkretnej instancji SQL. Dzięki niej mamy kolejny sposób na wykonanie np. powtarzających się zadań administracyjnych na wielu serwerach:

:setvar Query "SELECT @@ServerName AS ServerName"

:connect localhost

$(Query)
GO

:connect (localdb)\MSSQLLocalDB

$(Query)
GO

W powyższym skrypcie stworzyłem zmienną z zapytaniem sprawdzającym zmienną systemową @@Servername, następnie nawiązałem połączenie do dwóch instancji SQL Server zainstalowanych na mojej maszynie. W rezultacie oba zapytania zostały wykonane bez większych przeszkód:

Jest to bardzo przydatna funkcjonalność gdzie aby osiągnąć podobne efekty musimy sięgnąć po zewnętrzne języki jak np. Powershell, stworzyć połączenie między serwerami w postaci np. Linked Server czy uruchomić zewnętrzne oprogramowanie. Oczywiście połączenie w powyższym przykładzie zostało nawiązane przy pomocy uwierzytelnienia Windows, jednak możemy również podać login oraz hasło jednakże powinniśmy tego po prostu unikać.

Na sam koniec dwie komendy wyjściowe czyli :exit oraz :quit. :Exit kończy skrypt i dodatkowo może przyjąć jako parametr zapytanie, które będzie zwrócone jako ostatnie:

SELECT TOP 10 * FROM Production.Product

:exit(SELECT @@VERSION AS SQLVersion)

SELECT TOP 1 * FROM Production.Product

Na powyższym zrzucie ekranowym możecie zauważyć, że wykonana została:

• komenda SELECT TOP 10
• komenda podana jako parametr :exit

Komenda SELECT TOP 1 została zignorowana ponieważ została wywołana po komendzie :ext.

W przypadku :quit nie potrzebujemy już przykładu ponieważ działa dokładnie tak jak exit z tym, że nie przyjmuje żadnego parametru.

To by było na tyle jeśli chodzi o SQLCMD Mode w ramach tego artykułu. Z całą pewnością nie wyczerpaliśmy tematu możliwych zastosowań itd. ale mam nadzieję, że był dobry wstęp do tego narzędzia. Pozdrawiam