SQL Server 2005 w linii poleceń – SQLCMD, cz. I
Administracja serwerem baz danych Microsoft SQL Server 2005 nie ogranicza się do umiejętnego korzystania z narzędzi klienckich wyposażonych w interfejs graficzny, takich jak Management Studio. System SQL Server udostępnia także szereg narzędzi w linii poleceń, których możliwości są ogromne. W serii artykułów zatytułowanej „SQL Server 2005 w linii poleceń” autor przedstawi owe narzędzia, ich składnie i zastosowania.
Zawartość strony
 | Wstęp |
| Opcje narzędzia |
| Opcje logowania |
| Opcje wejścia/wyjścia |
| Opcje wykonywania zapytań |
| Opcje formatowania |
| Opcje raportowania błędów |
| Przeczytaj pozostałe części artykułu |
Wstęp
Narzędzie SQLCMD jest następcą używanego w SQL Server 2000 narzędzia OSQL. Podstawowe zastosowania SQLCMD to uruchamianie skryptów i praca interaktywna z instancjami systemu SQL Server. SQLCMD jest następcą narzędzi OSQL i ISQL.
SQLCMD do połączenia z instancją systemu SQL Server używa interfejsu OLE DB. Jest to interfejs szybszy, niż używany do tej pory przez narzędzie OSQL interfejs ODBC (starsze narzędzie - ISQL - używa interfejsu jeszcze wolniej działającego - DB Library), który dodatkowo posiada wiele ograniczeń (np. nie akceptuje w łańcuchach połączeń niektórych znaków, co ogranicza zasób możliwych do wykorzystania haseł dla loginów SQL Servera).
Nowe narzędzie posiada liczne nowe funkcjonalności i usprawnienia, które czynią je potężnym orężem w rękach administratora baz danych.
Opcje narzędzia
Oto pełna lista opcji narzędzia SQLCMD (według Books Online z 17 lipca 2006):
sqlcmd
[{ { -U login_id [ -P password ] } | –E trusted connection }]
[ -z new password ] [ -Z new password and exit]
[ -S server_name [ \ instance_name ] ] [ -H wksta_name ] [ -d db_name ]
[ -l login time_out ] [ -A dedicated admin connection ]
[ -i input_file ] [ -o output_file ]
[ -f < codepage > | i: < codepage > [ < , o: < codepage > ] ]
[ -u unicode output ] [ -r [ 0 | 1 ] msgs to stderr ]
[ -R use client regional settings ]
[ -q "cmdline query" ] [ -Q "cmdline query" and exit ]
[ -e echo input ] [ -t query time_out ]
[ -I enable Quoted Identifiers ]
[ -v var = "value"...] [ -x disable variable substitution ]
[ -h headers ][ -s col_separator ] [ -w column_width ]
[ -W remove trailing spaces ]
[ -k [ 1 | 2 ] remove[replace] control characters ]
[ -y display_width ] [-Y display_width ]
[ -b on error batch abort ] [ -V severitylevel ] [ -m error_level ]
[ -a packet_size ][ -c cmd_end ]
[ -L [ c ] list servers[clean output] ]
[ -p [ 1 ] print statistics[colon format]]
[ -X [ 1 ] ] disable commands, startup script, enviroment variables [and exit]
[ -? show syntax summary ]
Kolejność umieszczania wymienionych opcji w wywołaniach narzędzia SQLCMD nie ma znaczenia. A więc po kolei, według zastosowań...
Opcje logowania
-U login_id
Nazwa logina SQL Server. Rozróżnianie wielkości liter w nazwach loginów zależy od ustawienia collation bazy master.
Jeśli nie zostanie podana opcja -U ani opcja –P, narzędzie próbuje łączyć się z serwerem przy użyciu uwierzytelnienia Windows (patrz opcja –E) czyli używając konta Windows użytkownika, który uruchamia narzędzie SQLCMD.
Próba jednoczesnego użycia opcji –U i –E kończy się zwróceniem komunikatu błędu. Podobnie kończy się próba podania więcej niż jednego argumentu po przełączniku –U.
W przypadku, gdy nie zostanie podany żaden argument po przełączniku –U, narzędzie używa jako argumentu aktualnej wartości zmiennej skryptowej SQLCMDUSER.
-P password
Hasło dla logina SQL Server. Wielkość liter w haśle ma znaczenie. Uwaga! Wpisane po opcji –P hasło będzie widoczne na ekranie monitora!
Jeśli opcja –P zostanie użyta razem z opcją –E, zostanie wygenerowany komunikat błędu. Podobnie kończy się próba podania więcej niż jednego argumentu po przełączniku –P.
W przypadku, gdy nie zostanie podany żaden argument po przełączniku –P, narzędzie używa jako argumentu aktualnej wartości zmiennej skryptowej SQLCMDPASSWORD.
Jeżeli użyta zostanie opcja –U, a nie zostanie użyta opcja –P i nie jest ustawiona zmienna skryptowa SQLCMDPASSWORD, to narzędzie SQLCMD zapyta użytkownika o hasło (wpisywane przez użytkownika hasło jest niewidoczne na ekranie monitora). Po podaniu nieprawidłowego hasła, narzędzie SQLCMD nie ponawia pytania i kończy pracę.
-E trusted connection
Użycie logina Windows użytkownika, który próbuje uruchomić narzędzie SQLCMD. SQLCMD domyślnie używa uwierzytelnienia Windows.
Jeśli opcja –E zostanie użyta z opcją –U lub z opcją –P, zostanie wygenerowany komunikat błędu.
-z new password
Zmiana hasła dla logina SQL Server i pozostawienie aktywnego połączenia z serwerem. Niezbędne jest podanie obowiązującego hasła.
Rys. 1. Zmiana hasła dla logina SQL za pomocą opcji -z narzędzia SQLCMD.
-Z new password and exit
Zmiana hasła dla logina SQL Server i zamknięcie połączenia z serwerem. Niezbędne jest podanie obowiązującego hasła.
-S server name [\instance name]
Instancja SQL Server, z którą narzędzie SQLCMD będzie próbowało nawiązać połączenie. Opcja ta ustawia zmienną skryptową SQLCMDSERVER.
Jeżeli opcja –S nie zostanie użyta, narzędzie jako nazwy instancji używa wartości zmiennej skryptowej (środowiskowej) SQLCMDSERVER. Jeśli zmienna ta nie jest ustawiona, narzędzie używa nazwy maszyny jako nazwy instancji, czyli próbuje połączyć się z domyślną instancją na lokalnej maszynie.
| Informacja |
Jeżeli chcesz sprecyzować port, na którym nasłuchuje instancja, port należy podać wymieniając go po nazwie serwera i przecinku. Na przykład polecenie: SQLCMD –E –S WARSAW,1435 podejmie próbę połączenia z instancją (domyślną lub nazwaną) nasłuchującą na porcie 1435 na maszynie o nazwie WARSAW. |
Rys. 2. Nawiązanie połączenia z instancją nazwaną SQL Servera przy pomocy narzędzia SQLCMD.
-H wksta_name
Nazwa stacji roboczej. Opcja ta ustawia zmienną skryptową SQLCMDWORKSTATION.
Nazwa stacji roboczej może zostać zwrócona przy użyciu: funkcji systemowej HOST_NAME, systemowej procedury składowanej sp_who oraz widoku dynamicznego sys.dm_exec_sessions (kolumna hostname).
Opcja ta może być użyta do rozróżniania różnych sesji SQLCMD tego samego użytkownika.
| Informacja |
Opcja –H narzędzia SQLCMD umożliwia podanie nazwy dowolnej (nawet nieistniejącej) maszyny. Aby zobaczyć, z jakich maszyn rzeczywiście następują połączenia do serwera, można wykorzystać widok dynamiczny sys.dm_exec_connections (przy połączeniach z użyciem protokołu TCP/IP kolumna client_net_address to adres IP) lub widok systemowy sys.sysprocesses (kolumna net_address to adres MAC). Uwaga! We wczesnych wersjach SQL Server 2005 można spotkać widok katalogowy sys.processes. Widok ten został prawdopodobnie zastąpiony przez wspomniany widok dynamiczny sys.dm_exec_sessions. |
-d db_name
Nazwa bazy danych, do której zostanie podjęta próba wejścia po podłączeniu do serwera. W razie nieudanej próby (nieistniejąca baza lub brak uprawnień) generowany jest komunikat błędu i narzędzie SQLCMD jest zamykane. Opcja ta ustawia zmienną skryptową SQLCMDDBNAME.
Jeśli opcja –d nie zostanie użyta, narzędzie jako nazwy bazy danych używa wartości zmiennej skryptowej (środowiskowej) SQLCMDDBNAME. Jeśli zmienna ta nie jest ustawiona, narzędzie używa nazwy domyślnej bazy dla logina używanego do nawiązania połączenia z serwerem.
| Informacja |
Podanie kilka razy opcji –d powoduje, że po nawiązaniu połączenia z serwerem narzędzie SQLCMD wykona próbę wejścia do bazy, która została wymieniona w ostatnim wystąpieniu opcji –d. |
-l login_timeout
Ilość sekund, jaką narzędzie SQLCMD oczekuje na połączenie z serwerem. Opcja ta ustawia zmienną skryptową SQLCMDLOGINTIMEOUT. Domyślny czas oczekiwania to 8 sekund. Liczba podana jako argument opcji -l musi zawierać się w przedziale od 0 do 65534 i musi być liczbą całkowitą. W przeciwnym razie zostanie wygenerowany komunikat błędu. Wartość 0 oznacza nieograniczony czas oczekiwania.
-A dedicated admin connection
Logowanie z wykorzystaniem dedykowanego połączenia administratora (ang. Dedicated Administrator Connection – DAC). Jest to połączenie, które można wykorzystać do rozwiązywania problemów z serwerem. Więcej informacji na temat DAC znajdziesz pod hasłem „Using a Dedicated Administrator Connection” w Books Online. Jeżeli DAC jest niedostępne, generowany jest komunikat błędu.
Rys. 3. Połączenie DAC z poziomu narzędzia SQLCMD z weryfikacją połączenia.
Opcje wejścia/wyjścia
-i input_file [, input_file2]
Wskazuje na plik wejściowy zawierający polecenia SQL lub procedury składowane. Plików wejściowych może być wiele. Są wówczas przetwarzane w kolejności podania. Między nazwami plików nie umieszczaj spacji. Nazwy plików, które zawierają spacje muszą być umieszczone w cudzysłowach. Jeżeli co najmniej jeden z wymienionych plików nie istnieje, zostanie wygenerowany komunikat błędu i nie zostanie przetworzony żaden z plików.
Rys. 4. Wykonanie skryptu przy pomocy narzędzia SQLCMD.
-o output_file
Wskazuje na plik docelowy, do którego zostanie zapisany wynik działań w narzędziu SQLCMD. SQLCMD nie umożliwia jednoczesnego zapisu z wielu uruchomionych instancji tego narzędzia do jednego pliku. Jeżeli plik docelowy nie istnieje, zostanie stworzony. Jeśli plik docelowy istnieje, jego zawartość jest nadpisywana. Nazwa pliku zawierająca spacje musi być umieszczona w cudzysłowach.
-f <codepage> | i: <codepage> [<, o:<codepage>]
Strony kodowe wejścia i wyjścia podawane jako wartości numeryczne reprezentujące zainstalowane strony kodowe Windows (np. 1252).
Jeżeli nie zostanie określona strona kodowa, to dla plików wejściowych i wyjściowego używana jest aktualnie ustawiona w systemie strona kodowa. Wyjątkiem jest sytuacja, gdy plik wejściowy jest plikiem z kodowaniem Unicode. Wówczas konwersja nie jest potrzebna.
Jeżeli nie zostanie określony plik wyjściowy, jako strona kodowa wyjścia zostanie użyta aktualnie ustawiona strona kodowa konsoli (dzięki temu dane mogą być poprawnie wyświetlone w konsoli).
| Informacja |
Aby sprawdzić stronę kodową używaną przez cmd.exe użyj polecenia chcp. |
-u unicode output
Wyjście będzie formatowane jako Unicode niezależnie od strony kodowej pliku wejściowego.
-r [ 0 | 1 ] msgs to stderr
Przekierowuje komunikaty błędów na ekran (stderr).
Jeżeli nie zostanie podany parametr lub podana zostanie wartość 0, tylko komunikaty błędów na poziomie co najmniej 11 zostaną przekierowane na ekran. Jeżeli zostanie podana wartość 1, wszystkie komunikaty błędów włącznie z komunikatami produkowanymi jako wynik wykonania polecenia PRINT, są przekierowane na ekran.
Opcja –r nie jest uwzględniana, gdy zostanie określona opcja –o.
Domyślnie komunikaty błędów są wysyłane do stdout.
-R use client regional settings
Powoduje użycie przez providera SQL Server OLE DB ustawień regionalnych klienta podczas konwersji danych walutowych oraz daty/czasu do danych tekstowych. Domyślnie używane są opcje serwera.
Opcje wykonywania zapytań
-q “cmdline query”
Powoduje wykonanie podanego zapytania po uruchomieniu narzędzia SQLCMD, ale pozostawia narzędzie SQLCMD działające po zakończeniu wykonywania zapytania.
Można podać wiele zapytań oddzielonych średnikami. Zapytanie (-a) powinno być otoczone cudzysłowami.
W zapytaniach nie należy używać słowa GO (zapytanie powinno być napisane w języku Transact-SQL, zaś słowo GO nie jest słowem kluczowym tego języka).
Jeżeli opcja –q zostanie użyta razem z opcją –b, zostanie wygenerowany komunikat błędu i narzędzie SQLCMD zakończy pracę.
-Q “cmdline query” and exit
Powoduje wykonanie podanego zapytania po uruchomieniu narzędzia SQLCMD, a następnie zamknięcie narzędzia SQLCMD
Można podać wiele zapytań oddzielonych średnikami. Zapytanie (-a) powinno być otoczone cudzysłowami.
W zapytaniach nie należy używać słowa GO (zapytanie powinno być napisane w języku Transact-SQL, zaś słowo GO nie jest słowem kluczowym tego języka).
Jeżeli opcja –q zostanie użyta razem z opcją –b, zostanie wygenerowany komunikat błędu i narzędzie SQLCMD zakończy pracę.
-e echo input
Przesyła polecenia zapisane w skrypcie wejściowym do standardowego wyjścia (stdout).
-I enable Quoted Identifiers
Powoduje ustawienie wartości opcji SET QUOTED_IDENTIFIER na ON. Domyślna wartość tej opcji to OFF. Więcej informacji na temat tej opcji znajdziesz w Books Online pod hasłem „SET QUOTED_IDENTIFIER (Transact-SQL)”.
-t query time_out
Określa liczbę sekund, która będzie maksymalnym dopuszczalnym czasem wykonania polecenia (składni Transact-SQL). Opcja ta ustawia zmienną skryptową SQLCMDSTATTIMEOUT. Liczba podana jako argument opcji -t musi zawierać się w przedziale od 0 do 65535 i musi być liczbą całkowitą. W przeciwnym razie zostanie wygenerowany komunikat błędu.
-v var=value[ var=value…]
Tworzy zmienne skryptowe (i przypisuje im wartości) dla narzędzia SQLCMD, które następnie mogą zostać użyte w skryptach. Występujące po opcji –v pary zmienna=”wartość” muszą być rozdzielone spacją. Wartości zawierające spacje muszą być otoczone cudzysłowami.
-x diasable variable substitution
Powoduje, że narzędzie SQLCMD ignoruje zmienne skryptowe – traktuje ich wystąpienie w skrypcie jako fragment składni Transact-SQL i nie zamienia ich na wartości.
Opcje formatowania
-h headers
Określa, po ilu wierszach powtórzyć nagłówek w wyniku zapytania. Domyślnie nagłówek jest drukowany tylko raz dla każdego wyniku zapytania. Opcja ta ustawia zmienną skryptową SQLCMDHEADERS. Liczba podana jako argument opcji -h musi być liczbą całkowitą i musi być równa -1 lub zawierać się w przedziale od 1 do 2147483647. W przeciwnym razie zostanie wygenerowany komunikat błędu.
Jeżeli jako argument opcji –h zostanie podana wartość-1, nagłówki nie będą wyświetlane w wynikach zapytań.
| Informacja |
Używając liczby -1 jako wartości argumentu opcji –h możesz użyć zarówno notacji ze spacją -h -1, jak i bez spacji –h-1. |
Rys. 5. Formatowanie wyników w narzędziu SQLCMD - nie wyświetlanie nagłówków.
-s col_separator
Określa znak, który będzie używany jako separator kolumn w wynikach zapytań. Opcja ta ustawia zmienną skryptową SQLCMDCOLSEP. Domyślnym separatorem kolumn jest spacja. Separatorem może być dowolny 8-bitowy znak.
Aby użyć jako separatora znaku specjalnego (np. & lub ;), należy znak ten otoczyć cudzysłowami.
| Informacja |
Jeżeli jako separator zostanie podany ciąg znaków, narzędzie SQLCMD jako separatora użyje pierwszego z podanych znaków. |
-w column_width
Określa (w znakach) szerokość ekranu dla rezultatów zapytań (po osiągnięciu tej szerokości następuje złamanie wiersza). Opcja ta ustawia zmienną skryptową SQLCMDCOLWIDTH. Domyślna szerokość ekranu dla rezultatów zapytań to 80 znaków. Liczba podana jako argument opcji -w musi być liczbą całkowitą i musi zawierać się w przedziale od 9 do 65535. W przeciwnym razie zostanie wygenerowany komunikat błędu.
-W remove trainling spaces
Powoduje usunięcie spacji dopełniających z kolumn.
Opcja –W nie może być użyta z opcjami –y i –Y.
| Informacja |
Opcje –s oraz –W doskonale nadają się do przygotowywania danych do eksportu danych. Jednak należy pamiętać o tym, że opcja –W powoduje usunięcie także spacji dodanych przez opcję –s. |
-k [ 1 | 2 ] remove [replace] control characters
Usuwa znaki kontrolujące wyświetlanie, takie jak tabulatory czy przejścia do nowego wiersza z wyników zapytania.
Jeżeli jako argument opcji –k zostanie podana wartość 1, każdy znak kontrolujący wyświetlanie zostanie zastąpiony spacją.
Jeżeli jako argument opcji –k zostanie podana wartość 2, występujące po sobie znaki kontrolujące wyświetlanie zostaną zastąpione przez jedną spację.
-y display_width
Określa maksymalną liczbę znaków zwracanych dla zmiennych lub kolumn następujących typów danych: varchar(max), nvarchar(max), varbinary(max), xml, UDT(user-defined types), text, ntext, image. Opcja ta ustawia zmienną skryptową SQLCMDMAXVARTYPEWIDTH. Domyślnie opcja jest ustawiona na 0.
Jeżeli jako argument opcji –y zostanie podana wartość 0, z dużych zmiennych tekstowych zostanie zwrócony 1MB danych.
Aby zapobiec „obcinaniu” danych, można użyć opcji :XML ON.
-Y display_width
Określa maksymalną liczbę znaków zwracanych dla zmiennych lub kolumn następujących typów danych: char(n), nchar(m), varbinary(n), varchar(n), nvarchar(m), sql_variant, gdzie: 1<=n<=8000 oraz 1<=m<=4000. Opcja ta ustawia zmienną skryptową SQLCMDMAXFIXEDTYPEWIDTH. Domyślnie opcja jest ustawiona na 256. Liczba podana jako argument opcji -Y musi być liczbą całkowitą i musi zawierać się w przedziale od 0 do 8000. W przeciwnym razie zostanie wygenerowany komunikat błędu
Jeżeli jako argument opcji –Y zostanie podana wartość 0, wartości zmiennych wymienionych powyżej typów będą wyświetlane w całości.
Opcje raportowania błędów
-b on error batch abort
Powoduje, że w momencie wystąpienia błędu narzędzie SQLCMD zakończy pracę i zwróci odpowiednią wartość do zmiennej DOS ERRORLEVEL. Jeżeli wystąpi błąd SQL Servera na poziomie wyższym niż 10, zmienna DOS ERRORLEVEL będzie miała wartość 1. W przeciwnym razie zmienna DOS ERRORLEVEL będzie miała wartość 0.
Jeżeli oprócz opcji –b zostanie użyta opcja –V, narzędzie SQLCMD nie będzie kończyło pracy i zwracało wartości 1 dla zmiennej DOS ERRORLEVEL dla błędów SQL Servera na poziomie niższym niż poziom określony przez opcję –V. Ponadto w takim przypadku zmienna DOS ERRORLEVEL będzie miała wartość poziomu napotkanego błędu SQL Servera, który spowodował zakończenie pracy narzędzia SQLCMD.
Narzędzie SQLCMD nigdy nie zakończy pracy w wyniku wystąpienia błędów na poziomie 10 (Information). Takie błędy nie są również raportowane przez zmienną ERRORLEVEL.
Rys. 6. Zwracanie statusu błędu do systemu operacyjnego przez narzędzie SQLCMD.
-V severitylevel
Określa najniższy poziom błędu SQL Servera, raportowany przez narzędzie SQLCMD. Jeżeli poziom błędu jest niższy niż wartość podana w opcji –V, raportowany jest poziom 0 (który jest także poziomem domyślnym). Poziom błędu raportowany przez narzędzie SQLCMD można sprawdzić używając zmiennej DOS ERRORLEVEL.
-m error_level
Pozwala skonfigurować wyświetlanie komunikatów błędów. Numer, stan i poziom błędu będzie wyświetlany tylko dla błędów o poziomach wyższych niż poziom podany jako argument opcji -m. Dla błędów o niższych poziomach nie wyświetlane są żadne informacje. Opcja ta ustawia zmienną skryptową SQLCMDERRORLEVEL. Domyślna wartość tej zmiennej to 0.
Jeżeli jako argument opcji –m zostanie podana wartość -1, nagłówek wskazujący na numer, poziom i stan błędu będzie wyświetlany dla wszystkich błędów (także dla błędów na poziomie 10).
| Informacja |
Używając liczby -1 jako wartości argumentu opcji –m możesz użyć zarówno notacji ze spacją -m -1, jak i bez spacji –m-1. |
Przeczytaj pozostałe części artykułu
| • |
 | Paweł Potasiński (Microsoft Certified Trainer, Asseco Business Solutions S.A.) |