SQL Server 2000 : osql et la gestion des codes retour

Introduction

La problématique est simple : il est très souvent nécessaire de tester le code retour d’un batch sql lancé avec l’utilitaire osql de Microsoft SQL Server 2000. Ceci est particulièrement vrai pour le lancement de osql par un ordonnanceur comme ControlM ou dans un fichier de commande DOS.

Cet article se concentre sur l’utilisation de la commande Transact-SQL RAISERROR en combinaison avec l’utilitaire osql pour s’assurer de la gestion du code retour.

L’utilitaire osql

Le binaire osql est localisé classiquement dans le répertoire C:\Program Files\Microsoft SQL Server\80\Tools\Binn pour SQL Server 2000 et permet de lancer des fichiers ou des commandes batchs SQL sur un serveur MS SQL Server.

Le répertoire C:\Program Files\Microsoft SQL Server\80\Tools\Bin est généralement dans la variable d’environnement %PATH% et il n’est pas nécessaire de spécifier le chemin complet pour solliciter le binaire osql.

osql utilise la couche ODBC pour se connecter à un serveur MS SQL Server.

Syntaxe d’utilisation du binaire osql

Pour obtenir la syntaxe d’utilisation avec le binaire osql :

osql - ?
usage: osql              [-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 columnwidth]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-L list servers]      [-c cmdend]            [-D ODBC DSN name]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-n remove numbering]  [-m errorlevel]
  [-r msgs to stderr]    [-V severitylevel]
  [-i inputfile]         [-o outputfile]
  [-p print statistics]  [-b On error batch abort]
  [-O use Old ISQL behavior disables the following]
      <EOF> batch processing
      Auto console width scaling
      Wide messages
      default errorlevel is -1 vs 1
  [-? show syntax summary]

Pour se connecter à un serveur MS SQL Server avec osql :

Avec un login SQL Server spécifique :

C:\> osql -U<login> -P<login_password> -S<ServerName>

Exemple :

C:\> osql -Usa -Psa_pwd -SCGC\DBA_T1_MSQ

Avec une authentification NT (ou mode trusted connection), dans ce cas là aucun mot de passe n’est exigé :

C:\> osql -E -S<ServerName>

Exemple :

C:\> osql -E -SCGC\DBA_T1_MSQ

Dans les deux cas de figure, la connexion s’effectue alors dans la base de données par défaut du login, pour rentrer dans une autre base de données à la connexion, utiliser l’option -d <db_name>

Lancement en mode batch de fichiers ou de commandes SQL avec le binaire osql

Pour lancer en mode batch un fichier de commandes SQL avec osql, l’option -i <input_file> est utilisée :

C:\> osql -E -S<ServerName> -i <path_to_sqlfile>

Exemple :

C:\> osql -E -SCGC\DBA_T1_MSQ -i C:\what.sql

Pour lancer en mode batch des commandes SQL avec osql, l’option -Q est utilisée avec la commande SQL entre double quotes (obligatoire) :

C:\> osql -E -S<ServerName> -Q "SQL Command"

Exemple :

C:\> osql -E -SCGC\DBA_T1_MSQ -Q "select name from sysdatabases"

La commande T-SQL RAISERROR

La gestion des erreurs en T-SQL peut être effectuée avec la commande T-SQL RAISERROR.

Syntaxe de la commande RAISERROR

RAISERROR ( { msg_id | msg_str } { , severity , state } [ , argument [ ,...n ] ] )
             [ WITH LOG | NOWAIT | SETERROR ]

Exemple :

RAISERROR ('The database name is invalid',11,1)
go

Server: Msg 50000, Level 11, State 1, Line 1
The database name is invalid

Lors de l’utilisation de la commande RAISERROR avec un message utilisateur numérique (msg_id), il faut utiliser un numéro de message supérieur à 50 000 et une sévérité comprise entre 0 et 18. Seuls les administrateurs systèmes peuvent utiliser des sévérités entre 19 et 25.

Le numéro de message 50 000 est réservé à l’utilisation d’un message texte avec la commande RAISERROR.

Option severity

Les niveaux de sévérité sont compris entre 0 et 25.

Pour les niveaux de sévérité compris entre 0 et 19, la session de l’utilisateur n’est pas interrompue.

Pour les niveaux de sévérité compris entre 20 et 25, des erreurs systèmes se sont produites, ce sont des erreurs fatales et la connexion est interrompue.

Options WITH LOG | NOWAIT | SETERROR

L’option WITH LOG est obligatoire pour toutes les sévérités comprises entre 19 et 25 :

RAISERROR ('The database name is invalid',19,1)

Server: Msg 2754, Level 16, State 1, Line 1
Error severity levels greater than 18 can only be specified by members
of the sysadmin role, using the WITH LOG option.

Gestion des codes retour du binaire osql avec la commande RAISERROR

L’option -b avec osql et la variable DOS %ERRORLEVEL%

L’option -b du binaire osql force le binaire osql à se terminer et à retourner une valeur DOS lorsqu’une erreur se produit, valeur DOS qui peut être récupérée avec la variable système DOS %ERRORLEVEL%.

%ERRORLEVEL% vaut 1 lorsque SQL Server retourne une erreur avec une sévérité supérieure à 10. Dans le cas contraire %ERRORLEVEL% vaut 0. Ainsi les batchs ou les ordonnanceurs de type ControlM peuvent tester ce code sortie :

osql -E -SCGC\DBA_T1_MSQ -i C:\what.sql

SET ERROR=%ERRORLEVEL%
if %ERROR% NEQ 0 (echo = Erreur retournee par osql)
if %ERROR% EQU 0 (echo = Aucune erreur retournee par osql OK)

exit %ERROR%

State 127 dans la commande RAISERROR et le binaire osql

Lorsqu’un script lancé avec le binaire osql rencontre la commande RAISERROR avec l’option state égale à 127, osql se termine immédiatement quelque soit la sévérité et retourne le message au client.

RAISERROR ('The database name is invalid',10,127)

Dans l’exemple ci-dessus, osql se termine même si la sévérité est à 10 (message d’information).

Cas pratique de test des codes retour osql avec la commande RAISERROR

Contexte du cas pratique

Dans le cas pratique, le fichier what.sql contient deux commandes T-SQL : une commande RAISERROR suivie d’une commande select getdate(). Ce dernier fichier T-SQL est lancé avec le binaire osql en combinaison avec l’option -b.

what.sql

set nocount on
go
raiserror('Raiserror declenche',<serverité>,<state>)
go
select getdate()
go

Lancement du fichier what.sql avec osql :

@echo off

osql -E -SCGC\DBA_T1_MSQ -n -b -i what.sql

set ERROR=%ERRORLEVEL%
echo %ERROR%

pause

Comportement et code retour du binaire osql

Le tableau qui suit retrace le comportement et le code retour osql avec les différents arguments <sévérité> et <state> spécifiés dans la commande T-SQL RAISERROR.