La nuova versione di SQL Server 2005 ha introdotto una nuova serie di "utilità della riga di comando". Tra queste spicca SQLCMD; essa viene usata per eseguire via "command prompt" query ad hoc o batch T-SQL su istanze SQL Server. Scopo di questo articolo è illustrare le funzionalità e le modalità d'impiego dell'utilità SQLCMD nel quotidiano lavoro di amministrazione di SQL Server 2005.
Cos'è SQLCMD?
L'utilità SQLCMD è scritta in .NET 2.0 e comunica con SQL Server via Client SQL Nativo oppure via ODBC. Fornisce un ambiente di scripting ricco di funzionalità (tra cui una primitiva gestione degli errori) in grado di mettere l'utente nelle condizioni di compiere sul server attività di elevata complessità.
È uno strumento poliedrico, perchè sia l'amministratore di sistema (per la creazione di script di manutenzione per database o tabelle) sia lo sviluppatore (per la creazione di sofisticati wizard di installazione dei database in grado modificare le proprie azioni in funzione delle variabili selezionate dall'utente) possono beneficiare del suo utilizzo.
Le precedenti versioni di SQL Server (6.5, 7.0, 2000) erano già in possesso di programmi di utilità simili quali osql e isql. In SQL Server 2005, osql è stato pensionato mentre isql eliminato (la compatibilità con gli script osql viene comunque mantenuta). SQLCMD può essere utilizzata in tre modi:
- Come utilità della riga di comando. Esegue gli script dalla finestra DOS impostando gli switches opportuni per la connessione, le variabili, ecc....
SQLCMD -S ./SQLEXPRESS -i "creadb.sql" -v dbname="NomeDB" - In modalità grafica da SQL Server Management Studio. All'interno del nuovo ambiente di gestione di SQL Server 2005 (il vecchio QA) c'è la possibilità di editare gli script SQLCMD (anche con la colorazione della sintassi).
- Interattivamente, per eseguire query SQL. Come il vecchio osql sostanzialmente.
Sintassi SQLCMD
L'utilità SQLCMD possiede gli "switches", cioè le opzioni di linea (indicate as esempio da -U -S), che come nel vecchio osql si limitano a stabilire la connessione al Server SQL. Nel listato 1 viene mostrata la sintassi completa dell'utilità SQLCMD.
Switches SQLCMD
SQLCMD [-U login id] [-P password] [-S server] [-H hostname] [-E trusted connection] [-d use database name] [-l login timeout] [-t query timeout] [-h headers] [-s colseparator] [-w screen width] [-a packetsize] [-e echo input] [-I Enable Quoted Identifiers] [-c cmdend] [-L[c] list servers[clean output]] [-q "cmdline query"] [-Q "cmdline query" and exit] [-m errorlevel] [-V severitylevel] [-W remove trailing spaces] [-u unicode output] [-r[0|1] msgs to stderr] [-i inputfile] [-o outputfile] [-f <codepage> | i:<codepage>[,o:<codepage>]] [-k[1|2] remove[replace] control characters] [-y variable length type display width] [-Y fixed length type display width] [-p[1] print statistics[colon format]] [-R use client regional setting] [-b On error batch abort] [-v var = "value"...] [-X[1] disable commands[and exit with warning]] [-? show syntax summary]
Avviare una sessione di connessione con SQLCMD verso un Server SQL è semplicissimo, basta aprire una finestra dos (Start > Esegui > cmd + invio) ed eseguire SQLCMD impostando le opzioni di connessione con le giuste credenziali.
È possibile connettersi a SQL Server sia in modalità Windows Authentication che SQL Server Authentication (cioè con username e password). Il listato 2 mostra alcuni esempi.
Esempio di connessione ad una istanza di SQL Server 2005 EXPRESS
Con Windows Authentication C:Documents and SettingsLuca Milan>SQLCMD -S .SQLEXPRESS Con user e password C:Documents and SettingsLuca Milan>SQLCMD -S SERVER01SQLEXPRESS -U sa -P secret!
È possibile connettersi a più istanze di Server SQL all'interno di un singolo script, ma solo una connessione può essere attiva per volta.
Una nuova funzionalità di SQLCMD, interessante per gli amministratori di sistema, è la possibilità di connettersi a SQL Server con una sessione ad alta priorità (Dedicated Administrator Connection) settando lo switches -A (SQLCMD -A). L'accesso in modalità DAC (consentito solo ai membri del ruolo sysadmin) consente di eseguire diagnosi su Servers SQL che non sono in grado di accettare connessioni standard a causa di malfunzionamenti o errori di varia natura (esempio problemi di locking). La connessione viene stabilita tramite il protocollo TCP/IP ed è possibile accedere a server locali o remoti. Maggiori dettagli su msdn.
I comandi e le variabili SQLCMD
Una volta che la sessione è stata avviata correttamente, si entra nell'ambiente di scripting di SQLCMD da dove possiamo eseguire sia query T-SQL che comandi specifici di SQLCMD. Tra questi ricordiamo GO, :RESET, :R (esegue script sql annidati nel batch principale) ed ED (apri un editor di testo da cui editare le istruzioni sql) ma altri nuovi comandi sono disponibili, per un elenco dettagliato potete consultare i BOL di SQL Server.
La novità assoluta di SQLCMD rimane però l'utilizzo di variabili definite dall'utente (scripting variables) all'interno dei batch SQLCMD. Questo permette di scrivere istruzioni T-SQL dinamiche che modificano il loro comportamento in base ai valori delle variabili. Vediamo un esempio:
Listato 1. Uso delle variabili via linea di comando SQLCMD
--Un semplice script che effettua il backup di un database, le variabili sono evidenziate dal simbolo $
BACKUP DATABASE $(nomeDB) TO DISK = "$(percorso)$(nomeDB).bak"
--Eseguo lo script e passo i valori alle variabili (nome db e percorso) tramite l'opzione -v
SQLCMD -ic:scriptsbackup.sql -vnomeDB="pubs" percorso="c:backups"
Esiste però un altro modo per dichiarare le variabili e passarne i valori agli scrips SQLCMD, vediamo come reinterpretando l'esempio precedente.
Listato 2. Uso delle variabili dall'interno di un batch SQLCMD
--Mi collego all'istanza 1 e faccio il backup del db pubs:
:connect ./istanza1
:setvar nomeDB pubs
:setvar percorso C:backups
:r "c:scriptsbackup.sql"
GO
--Mi collego all'istanza 2 e faccio il backup del db northwind:
:connect ./istanza2
:setvar nomeDB northwind
:setvar percorso C:backups
:r "c:scriptsbackup.sql"
GO
Il comando :r permette di eseguire lo script backup.sql dall'interno del bacth principale (isolando in questo modo la logica di backup) dal quale vengono anche impostati i corretti valori delle variabili in gioco. SQLCMD permette di scrivere script altamente flessibili che possono essere in seguito adattati ad ambienti differenti (ad esempio in test o in produzione). Vediamo un esempio:
Listato 3. Usare le variabili per eseguire batch su database differenti
SET nomeDB=AdventureWorks
sqlcmd -E -S .SQLEXPRESS -i createdb.sql
--Il corpo dello script contiene questo pseudo codice
USE $(nomeDB)Test
-- Serie di comandi da eseguire sul database di test
USE $(nomeDB)Prod
-- Serie di comandi da eseguire sul database in produzione
Impostare uno script di avvio SQLCMD
SQLCMD può essere inizializzato settando delle impostazioni personalizzate tramite uno script di avvio. Allo scopo esiste una variabile di ambiente SQLCMDINI che può essere settata con il percorso di uno script sql contente le impostazioni con cui aprire una sessione SQLCMD. Vediamo un esempio:
Creiamo lo script di inizializzazione chiamato "Init.sql" in c:scripts
Listato 4. Script di inizializzazione
set nocount on
go
print "Sei connesso a:" + rtrim(CONVERT(char(20), SERVERPROPERTY('servername')))
set nocount off
go
Digitiamo poi dal prompt DOS:
set sqlcmdini=c:scriptsInit.sql
Quindi avviando SQLCMD avremo un messaggio simile:
C:Documents and SettingsLuca Milan>SQLCMD Sei connesso a: PORTATILESQLSERVER 1> exit
Conclusione
L'utilità SQLCMD è uno strumento flessibile e potente che SQL Server mette a disposizione di amministratori e sviluppatori di database. SQLCMD può essere un'alternativa agli strumenti di gestione e produttività contenuti nella suite SQL Server Management Studio per connettersi e lavorare con SQL Server 2005.
