sqlcmd - Usare con variabili di scripting

  • Articolo

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics Piattaforma di strumenti analitici (PDW)

Per variabile di scripting si intende una variabile utilizzata negli script. Le variabili di scripting consentono di utilizzare uno script in più scenari. Se, ad esempio, si desidera eseguire uno script su più server, è possibile utilizzare una variabile di scripting per il nome del server anziché modificare lo script per ogni server. È infatti sufficiente modificare il nome del server fornito alla variabile di scripting per eseguire lo stesso script su server diversi.

Le variabili di scripting possono essere definite in modo esplicito con il comando setvar oppure in modo implicito con l'opzione sqlcmd -v.

Questo articolo riporta anche alcuni esempi in cui vengono definite variabili di ambiente al prompt dei comandi Cmd.exe usando SET.

Impostare le variabili di scripting tramite il comando setvar

Il comando setvar consente di definire variabili di scripting. Le variabili definite con il comando setvar vengono archiviate internamente. Le variabili di scripting non devono essere confuse con le variabili di ambiente che vengono definite al prompt dei comandi usando SET. Se uno script fa riferimento a una variabile che non corrisponde a una variabile di ambiente oppure che non è stata definita con il comando setvar, viene restituito un messaggio di errore e l'esecuzione dello script viene arrestata. Per ulteriori informazioni, vedere l'opzione -b in sqlcmd.

Precedenza delle variabili (in ordine crescente)

Se è stato assegnato lo stesso nome a più tipi di variabile, verrà utilizzata la variabile con la precedenza più alta.

  1. Variabili di ambiente a livello di sistema
  2. Variabili di ambiente a livello di utente
  3. Shell dei comandi (SET X=Y) impostata al prompt dei comandi prima dell'avvio di sqlcmd
  4. sqlcmd -v X=Y
  5. :Setvar X Y

Nota

Per visualizzare le variabili di ambiente, nel Pannello di controllo aprire Sistema quindi fare clic sulla scheda Avanzate.

Impostazione implicita delle variabili di scripting

Quando si avvia sqlcmd con un'opzione cui è correlata una variabile sqlcmd , la variabile sqlcmd viene impostata in modo implicito sul valore specificato con l'opzione. Nell'esempio seguente sqlcmd viene avviato con l'opzione -l . La variabile SQLLOGINTIMEOUT viene quindi impostata in modo implicito.

sqlcmd -l 60

È anche possibile usare l'opzione -v per impostare una variabile di scripting presente in uno script. Nello script seguente (il nome di file è testscript.sql) ColumnName è una variabile di scripting.

USE AdventureWorks2022;

SELECT x.$(ColumnName)
FROM Person.Person x
WHERE x.BusinessEntityID < 5;

È quindi possibile specificare il nome della colonna che si desidera restituire utilizzando l'opzione -v :

sqlcmd -v ColumnName ="FirstName" -i c:\testscript.sql

Per restituire una colonna diversa utilizzando lo stesso script, modificare il valore della variabile di scripting ColumnName .

sqlcmd -v ColumnName ="LastName" -i c:\testscript.sql

Linee guida relative ai nomi e ai valori delle variabili di scripting

Quando si assegna un nome alle variabili di scripting, è necessario tenere presenti le linee guida seguenti:

  • I nomi delle variabili non devono contenere spazi vuoti o virgolette.

  • Il formato dei nomi delle variabili non deve essere identico a quello di un'espressione con variabili, ad esempio $(var).

  • Per le variabili di scripting non viene fatta distinzione tra maiuscole e minuscole.

    Nota

    Se non viene assegnato alcun valore a una variabile di ambiente sqlcmd , la variabile viene rimossa. Se si usa :setvar VarName senza un valore, la variabile viene cancellata.

Quando si specifica un valore per le variabili di scripting, è necessario tenere presenti le linee guida seguenti:

  • I valori delle variabili definiti con setvar o l'opzione -v devono essere racchiusi tra virgolette se il valore stringa contiene spazi.
  • Se le virgolette fanno parte del valore della variabile, è necessario utilizzare i caratteri di escape. Ad esempio, :setvar MyVar "spac""e".

Linee guida relative ai valori e ai nomi delle variabili definite tramite SET in cmd.exe

Le variabili definite usando fanno parte dell'ambiente cmd.exe e SET sqlcmd può farvi riferimento. Considerare le linee guida seguenti:

  • I nomi delle variabili non devono contenere spazi vuoti o virgolette.
  • I valori delle variabili possono contenere spazi o virgolette.

Variabili di scripting di sqlcmd

Le variabili definite con sqlcmd sono note come variabili di scripting. Nella tabella seguente sono elencate le variabili di scripting di sqlcmd .

Variabile Opzione correlata L/S Predefiniti
SQLCMDUSER 1 -U R 2 ""
SQLCMDPASSWORD 1 -P -- ""
SQLCMDSERVER 1 S- R 2 "DefaultLocalInstance"
SQLCMDWORKSTATION -H R 2 "ComputerName"
SQLCMDDBNAME -d R 2 ""
SQLCMDLOGINTIMEOUT -l R/W 3 "8" (secondi)
SQLCMDSTATTIMEOUT -t R/W 3 "0" = attesa illimitata
SQLCMDHEADERS -h R/W 3 "0"
SQLCMDCOLSEP -s R/W 3 " "
SQLCMDCOLWIDTH -w R/W 3 "0"
SQLCMDPACKETSIZE -a R 2 "4096"
SQLCMDERRORLEVEL -m R/W 3 "0"
SQLCMDMAXVARTYPEWIDTH -y R/W 3 "256"
SQLCMDMAXFIXEDTYPEWIDTH -y R/W 3 "0" = numero illimitato
SQLCMDEDITOR R/W 3 "edit.com"
SQLCMDINI R 2 ""

1 Le variabili SQLCMDUSER, SQLCMDPASSWORD e SQLCMDSERVER vengono impostate quando viene usato :Connect.

2 La lettera R indica che il valore può essere impostato una sola volta durante l'inizializzazione del programma.

3 R/W indica che il valore può essere reimpostato con il comando setvar e i comandi successivi useranno il nuovo valore.

Esempi

R. Usare il comando setvar in uno script

Molte opzioni di sqlcmd possono essere controllate in uno script usando il comando setvar . Nell'esempio seguente viene creato lo script test.sql , in cui la variabile SQLCMDLOGINTIMEOUT viene impostata su 60 secondi e un'altra variabile di scripting, server, viene impostata su testserver. Il codice seguente è incluso in test.sql.

:setvar SQLCMDLOGINTIMEOUT 60
:setvar server "testserver"
:connect $(server) -l $(SQLCMDLOGINTIMEOUT)

USE AdventureWorks2022;

SELECT FirstName, LastName
FROM Person.Person;

Lo script viene chiamato usando sqlcmd:

sqlcmd -i c:\test.sql

B. Usare il comando setvar in modalità interattiva

Nell'esempio seguente viene illustrato come impostare una variabile di scripting in modo interattivo tramite il comando setvar .

sqlcmd
:setvar MYDATABASE AdventureWorks2022
USE $(MYDATABASE);
GO

Il set di risultati è il seguente.

Changed database context to 'AdventureWorks2022'
1>

C. Usare le variabili di ambiente dal prompt del comandi all'interno di sqlcmd

Nell'esempio seguente vengono impostate quattro variabili di ambiente are, che vengono quindi chiamate da sqlcmd.

SET tablename=Person.Person
SET col1=FirstName
SET col2=LastName
SET title=Ms.
sqlcmd -d AdventureWorks2022
1> SELECT TOP 5 $(col1) + ' ' + $(col2) AS Name
2> FROM $(tablename)
3> WHERE Title ='$(title)'
4> GO

D. Usare le variabili di ambiente a livello di utente all'interno di sqlcmd

Nell'esempio seguente viene impostata al prompt dei comandi la variabile di ambiente a livello di utente %Temp%, che viene quindi passata al file di input sqlcmd. Per ottenere la variabile di ambiente a livello di utente, nel Pannello di controllofare doppio clic su Sistema. Fare clic sulla scheda Avanzate e quindi fare clic sul pulsante Variabili d'ambiente.

Il codice seguente è presente nel file di input C:\testscript.txt:

:OUT $(MyTempDirectory)
USE AdventureWorks2022;

SELECT FirstName
FROM AdventureWorks2022.Person.Person
WHERE BusinessEntityID < 5;

Il codice seguente viene immesso al prompt dei comandi:

SET MyTempDirectory=%Temp%\output.txt
sqlcmd -i C:\testscript.txt

Il risultato è simile al file di output C:\Documents and Settings\<user>\Local Settings\Temp\output.txt.

Changed database context to 'AdventureWorks2022'.
FirstName
--------------------------------------------------
Gustavo
Catherine
Kim
Humberto

(4 rows affected)

E. Usare uno script di avvio

Uno script di avvio di sqlcmd viene eseguito all'avvio di sqlcmd . Nell'esempio seguente viene impostata la variabile di ambiente SQLCMDINI. Di seguito è riportato il contenuto di init.sql.

SET NOCOUNT ON
GO

DECLARE @nt_username nvarchar(128)
SET @nt_username = (SELECT rtrim(convert(nvarchar(128), nt_username))
FROM sys.dm_exec_sessions WHERE spid = @@SPID)
SELECT  @nt_username + ' is connected to ' +
rtrim(CONVERT(nvarchar(20), SERVERPROPERTY('servername'))) +
' (' +`
rtrim(CONVERT(nvarchar(20), SERVERPROPERTY('productversion'))) +
')'
:setvar SQLCMDMAXFIXEDTYPEWIDTH 100
SET NOCOUNT OFF
GO

:setvar SQLCMDMAXFIXEDTYPEWIDTH

Di conseguenza, viene chiamato il file init.sql all'avvio di sqlcmd .

SET sqlcmdini=c:\init.sql
sqlcmd

Di seguito è riportato l'output.

1> <user> is connected to <server> (9.00.2047.00)

Nota

L'opzione -X disabilita la funzionalità degli script di avvio.

F. Espansione delle variabili

L'esempio seguente illustra l'uso dei dati sotto forma di variabile di sqlcmd .

USE AdventureWorks2022;
GO
CREATE TABLE AdventureWorks2022.dbo.VariableTest (Col1 NVARCHAR(50));
GO

Inserire una riga nella colonna Col1 di dbo.VariableTest contenente il valore $(tablename).

INSERT INTO AdventureWorks2022.dbo.VariableTest (Col1)
VALUES ('$(tablename)');
GO

Al prompt di sqlcmd, quando non viene impostata alcuna variabile uguale a $(tablename), le istruzioni seguenti restituiscono la riga e restituiscono anche il messaggio "Variabile di scripting 'tablename' non definita". Per impostazione predefinita, il flag -b di sqlcmd non è impostato. Se il flag -b è impostato, sqlcmd verrà terminato dopo l'errore "variabile non definita".

sqlcmd
1> SELECT Col1 FROM dbo.VariableTest WHERE Col1 = '$(tablename)';
2> GO
3> SELECT Col1 FROM dbo.VariableTest WHERE Col1 = N'$(tablename)';
4> GO

Il set di risultati è il seguente.

1> Col1
2> ------------------
3> $(tablename)
4>
5> (1 rows affected)

A condizione che la variabile MyVar sia impostata su $(tablename).

6> :setvar MyVar $(tablename)

Queste istruzioni restituiscono la riga e il messaggio "Variabile di scripting 'tablename' non definita".

6> SELECT Col1 FROM dbo.VariableTest WHERE Col1 = '$(tablename)';
7> GO

1> SELECT Col1 FROM dbo.VariableTest WHERE Col1 = N'$(tablename)';
2> GO

La riga viene restituita dalle istruzioni seguenti.

1> SELECT Col1 FROM dbo.VariableTest WHERE Col1 = '$(MyVar)';
2> GO

1> SELECT Col1 FROM dbo.VariableTest WHERE Col1 = N'$(MyVar)';
2> GO

Passaggi successivi