Utiliser l’utilitaire sqlcmd

L’utilitaire sqlcmd est un utilitaire de ligne de commande pour l’exécution ad hoc et interactive d’instructions et de scripts Transact-SQL et pour automatiser les tâches de script Transact-SQL. Pour utiliser sqlcmd de manière interactive ou pour générer des fichiers de script à exécuter à l’aide sqlcmd, les utilisateurs doivent comprendre Transact-SQL. L’utilitaire sqlcmd est généralement utilisé de la manière suivante :

Les utilisateurs saisissent de façon interactive les instructions Transact-SQL, de manière similaire à travailler avec l'invite de commandes. Les résultats sont affichés à l’invite de commandes. Pour ouvrir une fenêtre d’invite de commandes, cliquez sur Démarrer, cliquez sur Tous les programmes, pointez sur Accessoires, puis sur Invite de commandes. À l'invite de commandes, tapez sqlcmd suivi d'une liste des options de votre choix. Pour obtenir la liste complète des options prises en charge par sqlcmd, consultez l’utilitaire sqlcmd.
Les utilisateurs envoient un sqlcmd travail en spécifiant une instruction Transact-SQL unique à exécuter, ou en pointant l’utilitaire vers un fichier texte qui contient des instructions Transact-SQL à exécuter. La sortie est généralement dirigée vers un fichier texte, mais elle peut également être affichée à l’invite de commandes.
Mode SQLCMD dans l’Éditeur de requête SQL Server Management Studio.
SMO (SQL Server Management Objects)
Travaux CmdExec de SQL Server Agent

Options sqlcmd généralement utilisées

Les options suivantes sont utilisées le plus fréquemment :

Option de serveur (-S) qui identifie l’instance de Microsoft SQL Server à laquelle sqlcmd se connecte.
Options d’authentification (-E, -U et -P) qui spécifient les informations d’identification qui sqlcmd permettent de se connecter à l’instance de SQL Server.

Remarque

L’option -E est la valeur par défaut et ne doit pas être spécifiée.
Options d’entrée (-Q, -q et -i) qui identifient l’emplacement de l’entrée à sqlcmd.
Option de sortie (-o) qui spécifie le fichier dans lequel sqlcmd placer sa sortie.

Connexion à l’utilitaire sqlcmd

Voici les utilisations courantes de l’utilitaire sqlcmd :

Connexion à une instance par défaut à l’aide de l’authentification Windows pour exécuter de manière interactive des instructions Transact-SQL :
```
sqlcmd -S <ComputerName>  
```
Remarque

Dans l’exemple précédent, -E n’est pas spécifié, car il s’agit de la valeur par défaut et sqlcmd se connecte à l’instance par défaut à l’aide de l’authentification Windows.
Connexion à une instance nommée à l’aide de l’authentification Windows pour exécuter de manière interactive des instructions Transact-SQL :
```
sqlcmd -S <ComputerName>\<InstanceName>  
```
ou
```
sqlcmd -S .\<InstanceName>  
```
Connexion à une instance nommée à l’aide de l’authentification Windows et spécification des fichiers d’entrée et de sortie :
```
sqlcmd -S <ComputerName>\<InstanceName> -i <MyScript.sql> -o <MyOutput.rpt>  
```
Connexion à l’instance par défaut sur l’ordinateur local à l’aide de l’authentification Windows, exécution d’une requête et rester actif après l’exécution de la requête : sqlcmd
```
sqlcmd -q "SELECT * FROM AdventureWorks2012.Person.Person"  
```
Connexion à l’instance par défaut sur l’ordinateur local à l’aide de l’authentification Windows, exécution d’une requête, transfert de la sortie vers un fichier et sortie après l’exécution sqlcmd de la requête :
```
sqlcmd -Q "SELECT * FROM AdventureWorks2012.Person.Person" -o MyOutput.txt  
```
Connexion à une instance nommée à l’aide de l’authentification SQL Server pour exécuter de manière interactive des instructions Transact-SQL, avec sqlcmd l’invite d’un mot de passe :
```
sqlcmd -U MyLogin -S <ComputerName>\<InstanceName>  
```
Remarque

Pour afficher la liste des options prises en charge par l’utilitaire sqlcmd , exécutez : sqlcmd -?.

Exécution interactive d’instructions Transact-SQL à l’aide de sqlcmd

Vous pouvez utiliser l’utilitaire sqlcmd de manière interactive pour exécuter des instructions Transact-SQL dans une fenêtre de commande. Pour exécuter interactivement les déclarations de Transact-SQL en utilisant sqlcmd, exécutez l'utilitaire sans utiliser les options -Q, -q, -Z ou -i pour spécifier des fichiers d'entrée ou des requêtes. Par exemple:

sqlcmd -S <ComputerName>\<InstanceName>

Lorsque la commande est exécutée sans fichiers ni requêtes d’entrée, sqlcmd se connecte à l’instance spécifiée de SQL Server, puis affiche une nouvelle ligne avec un 1> suivi d’un trait de soulignement clignotant nommé invite sqlcmd. Le 1 signifie qu'il s'agit de la première ligne d'une instruction Transact-SQL, et l'invite sqlcmd est le point où l'instruction Transact-SQL commencera lorsque vous la saisirez.

À l’invite sqlcmd, vous pouvez taper à la fois des instructions Transact-SQL et des commandes, telles que GO et EXIT. Chaque instruction Transact-SQL est placée dans une mémoire tampon appelée cache d’instructions. Ces instructions sont envoyées à SQL Server après avoir tapé la GO commande et appuyé sur Entrée. Pour quitter sqlcmd, tapez EXIT ou QUIT au début d’une nouvelle ligne.

Pour effacer le cache d'instruction, tapez :RESET. La saisie de ^C provoque la sortie de sqlcmd. ^C peut également être utilisé pour arrêter l’exécution du cache d’instructions après l’émission d’une GO commande.

Les instructions Transact-SQL qui sont entrées dans une session interactive peuvent être modifiées en utilisant la commande :ED et l’invite sqlcmd. L’éditeur s’ouvre et, après avoir modifié l’instruction Transact-SQL et fermé l’éditeur, l’instruction Transact-SQL révisée apparaît dans la fenêtre de commande. Entrez GO pour exécuter l’instruction révisée Transact-SQL.

Chaînes entre guillemets

Les caractères entourés par des guillemets sont utilisés sans autre prétraitement, sauf que des guillemets peuvent être insérés dans une chaîne en entrant deux guillemets consécutifs. SQL Server traite cette séquence de caractères comme un seul guillemet. (Toutefois, la traduction se produit dans le serveur.) Les variables de script ne seront pas développées lorsqu’elles apparaissent dans une chaîne.

Par exemple:

sqlcmd

PRINT "Length: 5"" 7'";

GO

Voici l'ensemble des résultats.

Length: 5" 7'

Chaînes qui s’étendent sur plusieurs lignes

sqlcmd prend en charge les scripts qui ont des chaînes qui s’étendent sur plusieurs lignes. Par exemple, l’instruction suivante SELECT s’étend sur plusieurs lignes, mais est une seule chaîne exécutée lorsque vous appuyez sur la touche Entrée après avoir tapé GO.

SELECT First line

FROM Second line

WHERE Third line;

GO

Exemple Sqlcmd Interactif

Il s’agit d’un exemple de ce que vous voyez lorsque vous exécutez sqlcmd de manière interactive.

Lorsque vous ouvrez une fenêtre d’invite de commandes, il existe une ligne similaire à :

C:\> _

Cela signifie que le dossier est le dossier C:\ actif et que, si vous spécifiez un nom de fichier, Windows recherche le fichier dans ce dossier.

Tapez sqlcmd pour vous connecter à l’instance par défaut de SQL Server sur l’ordinateur local, et le contenu de la fenêtre d’invite de commandes sera :

C:\>sqlcmd

1> _

Cela signifie que vous vous êtes connecté à l’instance de SQL Server et que sqlcmd est maintenant prêt à accepter des instructions Transact-SQL et des commandes sqlcmd. Le trait de soulignement clignotant après 1> est l’invite sqlcmd qui marque l’emplacement où les instructions et les commandes que vous tapez s’afficheront. À présent, tapez USE AdventureWorks2012 et appuyez sur Entrée, puis tapez GO et appuyez sur Entrée. Le contenu de la fenêtre d’invite de commandes sera :

sqlcmd

USE AdventureWorks2012;

GO

Voici l'ensemble des résultats.

Changed database context to 'AdventureWorks2012'.

1> _

Appuyer sur ENTRÉE après avoir entré USE AdventureWorks2012 a signalé à sqlcmd de commencer une nouvelle ligne. Appuyer sur ENTRÉE après avoir tapé GO, a signalé à sqlcmd d'envoyer l'instruction USE AdventureWorks2012 à l'instance de SQL Server. sqlcmd retourne ensuite un message pour indiquer que l’instruction USE s’est terminée correctement et qu’elle a affiché une nouvelle 1> invite en tant que signal pour entrer une nouvelle instruction ou une nouvelle commande.

L’exemple suivant montre ce que contient la fenêtre d’invite de commandes si vous tapez une SELECT instruction, une GO pour exécuter le SELECTet un EXIT pour quitter sqlcmd:

sqlcmd

USE AdventureWorks2012;

GO

SELECT TOP (3) BusinessEntityID, FirstName, LastName

FROM Person.Person;

GO

Voici l'ensemble des résultats.

BusinessEntityID FirstName LastName

----------- -------------------------------- -----------

1 Syed Abbas

2 Catherine Abel

3 Kim Abercrombie

(3 rows affected)

1> EXIT

C:\>

Les lignes après la ligne 3> GO sont la sortie d’une SELECT instruction. Après avoir généré la sortie, sqlcmd réinitialise l’invite sqlcmd et affiche 1>. Après avoir entré EXIT à la ligne 1>, la fenêtre de l'invite de commande affiche la même ligne que lorsque vous l’avez ouverte pour la première fois. Cela indique que la session de sqlcmd est terminée. Vous pouvez maintenant fermer la fenêtre de l'invite de commande en tapant une autre commande EXIT.

Exécution de fichiers de script Transact-SQL à l’aide de sqlcmd

Vous pouvez utiliser sqlcmd pour exécuter des fichiers de script de base de données. Les fichiers de script sont des fichiers texte qui contiennent un mélange d’instructions Transact-SQL, sqlcmd de commandes et de variables de script. Pour plus d’informations sur la façon de générer un script pour des variables, consultez Utiliser sqlcmd avec des variables de script. sqlcmd fonctionne avec les instructions, les commandes et les variables de script dans un fichier de script d’une manière similaire à la façon dont elles fonctionnent avec des instructions et des commandes entrées de manière interactive. La principale différence est que sqlcmd le fichier d’entrée est lu sans pause au lieu d’attendre qu’un utilisateur entre les instructions, les commandes et les variables de script.

Il existe plusieurs manières de créer des fichiers de script de base de données :

Vous pouvez générer et déboguer de manière interactive un ensemble d’instructions Transact-SQL dans SQL Server Management Studio, puis enregistrer le contenu de la fenêtre Requête en tant que fichier de script.
Vous pouvez créer un fichier texte qui contient des instructions Transact-SQL à l’aide d’un éditeur de texte, tel que le Bloc-notes.

Exemples

Un. Exécution d’un script à l’aide de sqlcmd

Démarrez le Bloc-notes et tapez les instructions Transact-SQL suivantes :

USE AdventureWorks2012;

GO

SELECT TOP (3) BusinessEntityID, FirstName, LastName

FROM Person.Person;

GO

Créez un dossier intitulé MyFolder puis enregistrez le script sous le fichier MyScript.sql dans le dossier C:\MyFolder. Entrez ce qui suit à l’invite de commandes pour exécuter le script et placer la sortie dans MyOutput.txt dans MyFolder:

sqlcmd -i C:\MyFolder\MyScript.sql -o C:\MyFolder\MyOutput.txt

Lorsque vous affichez le contenu du MyOutput.txt Bloc-notes, vous verrez les éléments suivants :

Changed database context to 'AdventureWorks2012'.

BusinessEntityID FirstName LastName

---------------- ----------- -----------

1 Syed Abbas

2 Catherine Abel

3 Kim Abercrombie

(3 rows affected)

B. Utilisation de sqlcmd avec une connexion administrative dédiée

Dans l’exemple suivant, sqlcmd est utilisé pour se connecter à un serveur qui rencontre un problème de blocage à l’aide de la connexion d’administrateur dédié (DAC).

C:\>sqlcmd -S ServerName -A

1> SELECT blocked FROM sys.dm_exec_requests WHERE blocked <> 0;

2> GO

Voici l'ensemble des résultats.

spid blocked

------ -------

62 64

(1 rows affected)

Permet sqlcmd de mettre fin au processus de blocage.

1> KILL 64;

2> GO

Chapitre C. Utilisation de sqlcmd pour exécuter une procédure stockée

L’exemple suivant montre comment exécuter une procédure stockée à l’aide sqlcmdde . Créez la procédure stockée suivante.

USE AdventureWorks2012;

IF OBJECT_ID ( ' dbo.ContactEmailAddress, 'P' ) IS NOT NULL

DROP PROCEDURE dbo.ContactEmailAddress;

GO

CREATE PROCEDURE dbo.ContactEmailAddress

(

@FirstName nvarchar(50)

,@LastName nvarchar(50)

)

AS

SET NOCOUNT ON

SELECT EmailAddress

FROM Person.Person

WHERE FirstName = @FirstName

AND LastName = @LastName;

SET NOCOUNT OFF

À l’invite sqlcmd, entrez les éléments suivants :

C:\sqlcmd

1> :Setvar FirstName Gustavo

1> :Setvar LastName Achong

1> EXEC dbo.ContactEmailAddress $(Gustavo),$(Achong)

2> GO

EmailAddress

-----------------------------

gustavo0@adventure-works.com

D. Utilisation de sqlcmd pour la maintenance de la base de données

L’exemple suivant montre comment utiliser sqlcmd une tâche de maintenance de base de données. Créez C:\BackupTemplate.sql à l'aide du code suivant.

USE master;

BACKUP DATABASE [$(db)] TO DISK='$(bakfile)';

À l’invite sqlcmd, entrez ce qui suit :

C:\ >sqlcmd

1> :connect <server>

Sqlcmd: Successfully connected to server <server>.

1> :setvar db msdb

1> :setvar bakfile c:\msdb.bak

1> :r c:\BackupTemplate.sql

2> GO

Changed database context to 'master'.

Processed 688 pages for database 'msdb', file 'MSDBData' on file 2.

Processed 5 pages for database 'msdb', file 'MSDBLog' on file 2.

BACKUP DATABASE successfully processed 693 pages in 0.725 seconds (7.830 MB/sec)

E. Utilisation de sqlcmd pour exécuter du code sur plusieurs instances

Le code suivant dans un fichier illustre la connexion d'un script à deux instances. Notez GO avant la connexion à la deuxième instance.

:CONNECT <server>\,<instance1>

EXEC dbo.SomeProcedure

GO

:CONNECT <server>\,<instance2>

EXEC dbo.SomeProcedure

GO

E. Retour d’une sortie XML

L'exemple suivant montre comment la sortie XML est retournée, sans mise en forme, dans un flux continu.

C:\>sqlcmd -d AdventureWorks2012

1> :XML ON

1> SELECT TOP 3 FirstName + ' ' + LastName + ', '

2> FROM Person.Person

3> GO

Syed Abbas, Catherine Abel, Kim Abercrombie,

F. Utilisation de sqlcmd dans un fichier de script Windows

Une sqlcmdcommande telle que sqlcmd -i C:\InputFile.txt -o C:\OutputFile.txt, peut être exécutée dans un fichier .bat avec VBScript. Dans ce cas, n’utilisez pas d’options interactives. sqlcmd doit être installé sur l’ordinateur qui exécute le fichier .bat.

Tout d’abord, créez les quatre fichiers suivants :

C :\badscript.sql

SELECT batch_1_this_is_an_error  
GO  
SELECT 'batch #2'  
GO

C :\goodscript.sql

SELECT 'batch #1'  
GO  
SELECT 'batch #2'  
GO

C :\returnvalue.sql

:exit(select 100)  
@echo off  
C:\windowsscript.bat  
@echo off  

echo Running badscript.sql  
sqlcmd -i badscript.sql -b -o out.log  
if not errorlevel 1 goto next1  
echo == An error occurred   

:next1  

echo Running goodscript.sql  
sqlcmd -i goodscript.sql -b -o out.log  
if not errorlevel 1 goto next2  
echo == An error occurred   

:next2  
echo Running returnvalue.sql  
sqlcmd -i returnvalue.sql -o out.log  
echo SQLCMD returned %errorlevel% to the command shell  

:exit

C:\windowsscript.bat

@echo off  

echo Running badscript.sql  
sqlcmd -i badscript.sql -b -o out.log  
if not errorlevel 1 goto next1  
echo == An error occurred   

:next1  

echo Running goodscript.sql  
sqlcmd -i goodscript.sql -b -o out.log  
if not errorlevel 1 goto next2  
echo == An error occurred   

:next2  
echo Running returnvalue.sql  
sqlcmd -i returnvalue.sql -o out.log  
echo SQLCMD returned %errorlevel% to the command shell  

:exit

Puis, à l'invite de commandes, exécutez C:\windowsscript.bat:

C:\>windowsscript.bat

Running badscript.sql

== An error occurred

Running goodscript.sql

Running returnvalue.sql

SQLCMD returned 100 to the command shell

G. Utilisation de sqlcmd pour définir le chiffrement sur Azure SQL Database

Vous pouvez exécuter une action sur une connexion à une base de données SQL pour spécifier le chiffrement et la confiance des certificats. Deux options « sqlcmd » sont disponibles :

Le commutateur -N est utilisé par le client pour demander une connexion chiffrée. Cette option est équivalente à l'option ADO.net ENCRYPT = true.
Le commutateur -C est utilisé par le client pour le configurer de manière à faire implicitement confiance au certificat du serveur, sans le valider. Cette option est équivalente à l'option ADO.net TRUSTSERVERCERTIFICATE = true.

Le service SQL Database ne prend pas en charge toutes les SET options disponibles sur une instance SQL Server. Les options suivantes provoquent une erreur lorsque l'option SET correspondante est définie sur ON ou OFF:

SET ANSI_DEFAULTS (paramètres par défaut ANSI)
SET ANSI_NULLS
SET REMOTE_PROC_TRANSACTIONS
SET ANSI_NULL_DEFAULT

Les options SET suivantes ne lèvent pas d’exceptions, mais ne peuvent pas être utilisées. Ils sont déconseillés :

SET CONCAT_NULL_YIELDS_NULL (définir que la concaténation des valeurs NULL donne NULL)
SET ANSI_PADDING
SET QUERY_GOVERNOR_COST_LIMIT

Syntaxe

Les exemples suivants font référence aux cas où les paramètres du fournisseur SQL Server Native Client incluent : ForceProtocolEncryption = False, Trust Server Certificate = No

Connexion à l'aide des informations d'identification Windows et d'une communication chiffrée :

SQLCMD -E -N

Connexion à l'aide des informations d'identification Windows et d'un certificat de serveur de confiance :

SQLCMD -E -C