Sqlcmd gebruiken met scriptvariabelen

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL-database in Microsoft Fabric

Met het hulpprogramma sqlcmd kunt u Transact-SQL instructies, systeemprocedures en scriptbestanden invoeren.

Scriptvariabelen in sqlcmd

Variabelen die in scripts worden gebruikt, worden scriptvariabelen genoemd. Met scriptvariabelen kan één script in meerdere scenario's worden gebruikt. Als u bijvoorbeeld één script wilt uitvoeren op meerdere servers in plaats van het script voor elke server te wijzigen, kunt u een scriptvariabele gebruiken voor de servernaam. Door de servernaam te wijzigen die is opgegeven in de scriptvariabele, kan hetzelfde script op verschillende servers worden uitgevoerd.

Scriptvariabelen kunnen expliciet worden gedefinieerd met behulp van de setvar opdracht of impliciet met behulp van de sqlcmd -v optie.

Dit artikel bevat ook voorbeelden van het definiëren van omgevingsvariabelen op de opdrachtregel met behulp van SET.

Scriptvariabelen instellen met de opdracht setvar

Met de setvar opdracht worden scriptvariabelen gedefinieerd. Variabelen die zijn gedefinieerd met behulp van de setvar opdracht, worden intern opgeslagen. Scriptvariabelen mogen niet worden verward met omgevingsvariabelen die zijn gedefinieerd bij de opdrachtprompt met behulp van SET. Als een script verwijst naar een variabele die geen omgevingsvariabele is of niet is gedefinieerd met behulp setvarvan, wordt er een foutbericht geretourneerd en wordt de uitvoering van het script gestopt. Zie de optie -b in sqlcmdvoor meer informatie.

Variabele prioriteit (laag tot hoog)

Als meer dan één type variabele dezelfde naam heeft, wordt de variabele met de hoogste prioriteit gebruikt.

1. Omgevingsvariabelen op systeemniveau
2. Omgevingsvariabelen op gebruikersniveau
3. Commandoshell (SET X=Y) ingesteld bij de opdrachtprompt voordat u sqlcmd start
4. sqlcmd -v X=Y
5. :Setvar X Y

Instellen van scriptvariabelen via impliciete methode

Wanneer u sqlcmd- start met een optie met een gerelateerde sqlcmd variabele, wordt de variabele sqlcmd impliciet ingesteld op de waarde die is opgegeven met behulp van de optie. In het volgende voorbeeld wordt sqlcmd gestart met de optie -l. Hiermee stelt u impliciet de SQLLOGINTIMEOUT variabele in.

sqlcmd -l 60

U kunt ook de optie -v gebruiken om een scriptvariabele in te stellen die in een script bestaat. In het volgende script (de bestandsnaam is testscript.sql), is ColumnName een scriptvariabele.

USE AdventureWorks2022;

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

U kunt vervolgens de naam opgeven van de kolom die u wilt retourneren met behulp van de optie -v:

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

Als u een andere kolom wilt retourneren met hetzelfde script, wijzigt u de waarde van de ColumnName scriptvariabele.

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

Richtlijnen voor het benoemen en toewijzen van scriptvariabelen en waarden

Houd rekening met de volgende richtlijnen bij het benoemen van scriptvariabelen:

• Namen van variabelen mogen geen spatietekens of aanhalingstekens bevatten.

• Namen van variabelen mogen niet hetzelfde formulier hebben als een variabeleexpressie, zoals $(var).

• Scriptvariabelen zijn niet hoofdlettergevoelig.

  Notitie

  Als er geen waarde is toegewezen aan een sqlcmd omgevingsvariabele, wordt de variabele verwijderd. Als u :setvar VarName zonder een waarde gebruikt, wordt de variabele gewist.

Houd rekening met de volgende richtlijnen wanneer u waarden opgeeft voor scriptvariabelen:

• Variabelewaarden die zijn gedefinieerd met behulp van setvar of de -v optie moeten tussen aanhalingstekens worden geplaatst als de tekenreekswaarde spaties bevat.
• Als aanhalingstekens deel uitmaken van de variabele waarde, moeten ze te worden geescaped. Bijvoorbeeld: :setvar MyVar "spac""e".

Richtlijnen voor cmd.exe SET-variabelewaarden en -namen

Variabelen die zijn gedefinieerd met behulp van SET maken deel uit van de cmd.exe-omgeving en kunnen worden verwezen door sqlcmd-. Houd rekening met de volgende richtlijnen:

• Namen van variabelen mogen geen spatietekens of aanhalingstekens bevatten.
• Variabele waarden kunnen spaties of aanhalingstekens bevatten.

sqlcmd-scriptvariabelen

¹ SQLCMDUSER, SQLCMDPASSWORD en SQLCMDSERVER worden ingesteld wanneer :Connect wordt gebruikt.

² R geeft aan dat de waarde slechts één keer kan worden ingesteld tijdens de initialisatie van het programma.

³ R/W geeft aan dat de waarde opnieuw kan worden ingesteld met behulp van de setvar opdracht en de volgende opdrachten de nieuwe waarde gebruiken.

Voorbeelden

De codevoorbeelden in dit artikel gebruiken de AdventureWorks2022 of AdventureWorksDW2022 voorbeelddatabase die u kunt downloaden van de startpagina van Microsoft SQL Server Samples en Community Projects .

Een. De opdracht setvar gebruiken in een script

Veel sqlcmd opties kunnen worden beheerd in een script met behulp van de setvar opdracht. In het volgende voorbeeld wordt het script test.sql gemaakt waarin de SQLCMDLOGINTIMEOUT variabele is ingesteld op 60 seconden en een andere scriptvariabele, server, is ingesteld op testserver. De volgende code bevindt zich in test.sql.

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

USE AdventureWorks2022;

SELECT FirstName, LastName
FROM Person.Person;

Het script wordt vervolgens aangeroepen met behulp van sqlcmd:

sqlcmd -i c:\test.sql

B. De opdracht setvar interactief gebruiken

In het volgende voorbeeld ziet u hoe u een scriptvariabele interactief instelt met behulp van de opdracht setvar.

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

Dit is de resultaatset.

Changed database context to 'AdventureWorks2022'
1>

C. Omgevingsvariabelen van de opdrachtprompt gebruiken in sqlcmd

In het volgende voorbeeld worden vier omgevingsvariabelen are ingesteld en vervolgens aangeroepen vanuit 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. Omgevingsvariabelen op gebruikersniveau gebruiken in sqlcmd

In het volgende voorbeeld wordt de omgevingsvariabele op gebruikersniveau %Temp% ingesteld bij de opdrachtprompt en doorgegeven aan het sqlcmd invoerbestand. Als u de omgevingsvariabele op gebruikersniveau wilt verkrijgen, dubbelklikt u in Configuratieschermop System. Selecteer het tabblad Geavanceerde en selecteer vervolgens Omgevingsvariabelen.

De volgende code bevindt zich in het invoerbestand C:\testscript.txt:

:OUT $(MyTempDirectory)
USE AdventureWorks2022;

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

Deze volgende code wordt ingevoerd bij de opdrachtprompt:

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

Het volgende resultaat wordt verzonden naar het uitvoerbestand C:\Documents and Settings\<user>\Local Settings\Temp\output.txt.

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

(4 rows affected)

E. Een opstartscript gebruiken

Er wordt een sqlcmd- opstartscript uitgevoerd wanneer sqlcmd- wordt gestart. In het volgende voorbeeld wordt de omgevingsvariabele SQLCMDINIingesteld. Dit is de inhoud van 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

Hiermee wordt het init.sql-bestand aanroepen wanneer sqlcmd wordt gestart.

SET sqlcmdini=c:\init.sql
sqlcmd

Dit is de uitvoer.

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

F. Variabele uitbreiding

In het volgende voorbeeld ziet u hoe u met gegevens werkt in de vorm van een sqlcmd variabele.

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

Voeg één rij in Col1 van dbo.VariableTest die de waarde $(tablename)bevat.

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

Wanneer bij de sqlcmd prompt geen variabele is ingesteld die gelijk is aan $(tablename), retourneren de volgende instructies de rij en ook het bericht 'tabelnaam' scriptvariabele niet gedefinieerd. Standaard is de vlag sqlcmd-b niet ingesteld. Als -b wordt ingesteld, wordt sqlcmd beëindigd na de fout "variabele niet gedefinieerd".

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

Dit is de resultaatset.

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

De variabele MyVar is ingesteld op $(tablename).

6> :setvar MyVar $(tablename)

Deze instructies geven de rij terug en geven ook het bericht 'de scriptvariabele voor de tabelnaam is niet gedefinieerd' terug.

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

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

Deze uitspraken geven de rij terug.

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

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

Verwante inhoud

• Meer informatie over het hulpprogramma sqlcmd (Go) op GitHub
• Quickstart: Sql Server Linux-containerinstallatiekopieën uitvoeren met Docker
• het hulpprogramma sqlcmd starten
• T-SQL uitvoeren vanuit een scriptbestand met sqlcmd
• het hulpprogramma gebruiken
• Sqlcmd gebruiken met scriptvariabelen
• Verbinding maken met SQL Server met sqlcmd
• SQLCMD-scripts bewerken met queryeditor
• taakstappen beheren
• een cmdExec-taakstap maken