Blog

Quelques trucs à savoir sur Invoke-Sqlcmd

---

Très pratique pour l’industrialisation, Invoke-Sqlcmd est une applet de commande Powershell qui exécute des scripts T-SQL et XQuery, ainsi que les commandes prises en charge par l’utilitaire sqlcmd. Invoke-Sqlcmd est inclue depuis SQL Server 2008, autrement dit, il n’est pas utile d’ajouter un module dans Powershell pour pouvoir utiliser cette fonction. Il est important d’en connaître les principaux avantages et limitations, en sachant qu’elle peut avoir un comportement inattendu. Voici quelques éléments de réponse aux questions suivantes :

Pourquoi Invoke-Sqlcmd est-elle si lunatique ? Est-ce qu’il vaut mieux la lancer le matin, plutôt l’après-midi, les jours pairs et enfin doit-on tenir compte de la météo ?

Commande non reconnue

La première étape est de parvenir à lancer la commande Invoke-Sqlcmd, autrement dit qu’elle soit reconnue. Il se peut que vous soyez confronté à ce message d’erreur :

The term ‘invoke-sqlcmd’ is not recognized as the name of a cmdlet, function, script file, or operable program.

Pas de panique ! Déployez les outils ci-dessous dans cet ordre (suivant votre version, Microsoft SQL Server 2016 Feature Pack SP1 ou autre) et relancer Powershell :

– ENU\x64\SQLSysClrTypes.msi
– ENU\x64\SharedManagementObjects.msi
– ENU\x64\PowerShellTools.msi

Les packages sont téléchargeables ici : https://www.microsoft.com/en-US/download/details.aspx?id=54279

Dedicated Administrator Connection

Cette commande permet d’utiliser le mode DAC, soit le mode de connexion de secours SQL Server pour interroger une instance/base de données quand le mode d’accès classique n’est plus possible :

Passer des variables en entrée

Il est bien évidement possible de passer des variables en entrée d’une requête (-Query, -InputFile) comme filtre(s) ou encore pour modifier le contexte.

En pratique, ça n’est pas complètement fonctionnel et pour cause, il y a des caractères à échapper.On peut recevoir ce message d’erreur :

Error: The format used to define the new variable for Invoke-Sqlcmd cmdlet is invalid. Please use the ‘var=value’ format for defining a new variable.

Typiquement si votre variable contient des guillemets et que vous lui passer directement une valeur littérale, il y a deux manières d’échapper ce caractère :

Ou bien comme ceci :

Cependant, il y a un caractère qu’on ne pas peut échapper, c’est le =. Pourquoi me direz-vous ? Parce qu’il est déjà utilisé dans l’initialisation des variables. Invoke-Sqlcmd dépend de deux DLL que voici :

Microsoft.SqlServer.Management.PSProvider.dll
Microsoft.SqlServer.Management.PSSnapins.dll

C’est la seconde qui nous intéresse. Quand on lance à un Process Explorer, on observe qu’elles sont bien appelées par le processus powershell_ise.exe :

Si on ouvre la DLL Microsoft.SqlServer.Management.PSSnapins.dll avec le dé-compilateur dotPeek gratuit par ailleurs et qu’on s’attarde sur la section ExecutionProcessor en faisant une recherche sur la chaine “Variable”, voici ce qu’on peut y trouver :

En l’état, il n’y a rien qui nous permette d’échapper le caractère =. Néanmoins, ce problème peut être contourné de deux manières : soit en remplaçant le = une première fois par une chaine improbable dans votre variable et dans un second temps de procéder à son remplacement pour restituer la valeur d’origine au niveau de votre requête/script TSQL. Pas super propre…

Le seconde moyen de contournement est d’utiliser la fonction Invoke-Sqlcmd2 que nous évoquerons plus tard pour d’autres raisons.

A l’inverse, on peut au besoin désactiver l’alimentation de variables en entrée en ajoutant le paramètre  -DisableVariables.

Récupérer le résultat

Quand on y regarde de plus près, la fonction Invoke-Sqlcmd renvoie un objet en sortie. Dans le script ci-dessous, on récupère un DataTable unique :

Dans ce second exemple, on a la possibilité de récupérer plusieurs DataTables en sortie, en introduisant le paramètre -As DataTables :

Idem pour ce troisième exemple mais avec un cas d’usage concret, à savoir récupérer du T-SQL généré dynamiquement en vue de l’exécuter sur une autre instance/base de données :

Unicité des colonnes de sortie

L’unicité des colonnes en sortie doit être garantie, au risque de rencontrer l’erreur suivante :

Quand on observe la sortie de la requête EXEC sp_who2, on constate que la colonne SPID apparait à deux reprises :

QueryTimeout

Spécifiez le paramètre QueryTimeout (en secondes) pour les requêtes dont le temps d’exécution va au delà du timeout défini par défaut sur l’instance SQL Server.

Pour consulter le timeout définis sur une instance SQL Server, exécuter ce lot d’instructions :

Dans le résultat, relever la valeur (run_value) du paramètre remote query timeout (s) :

Pour un temps d’exécution illimité, définir le QueryTimeout à 0. Cela étant, sur les versions SQL Server 2008 et SQL Server 2008 R2, cette valeur n’est pas prise en compte. Pour contourner le problème, procéder comme suit :

Handling d’erreur non fonctionnel

Toutes les exceptions ne sont pas détectées, typiquement :

Aucune erreur n’est détectée alors qu’en temps normal, quand on exécute la requête depuis SQL Management Studio, on obtient l’exception suivante :

Chaîne tronquée

Soyez attentif sur les résultats ramenant du type VARCHAR(MAX), il se peut que les chaînes de caractères soient tronquées à l’arrivée. Dans le cas où vous avez généré du T-SQL dynamique, vous aurez l’occasion de vous en rendre compte lors de l’exécution, votre script générera une erreur de syntaxe en sortie. Pour remédier à cette limitation, utilisez l’option -MaxCharLength.

Invoke-Sqlcmd2

Une alternative possible est d’utiliser une variante de la fonction Invoke-Sqlcmd, Invoke-Sqlcmd2 qui n’est certes pas native mais beaucoup plus riche. On peut toutefois regretter l’absence du mode DAC et le fait que l’instruction GO ne soit pas supportée. Le détail de la commande est disponible sur Github :

https://github.com/RamblingCookieMonster/PowerShell/blob/master/Invoke-Sqlcmd2.ps1

Annexe

D’autres bugs ont été référencés sur Invoke-Sqlcmd : https://blogs.technet.microsoft.com/heyscriptingguy/2013/05/06/10-tips-for-the-sql-server-powershell-scripter/
L’aide est disponible en ligne : https://technet.microsoft.com/fr-fr/library/cc281720(v=sql.110).aspx