SQL Server 2000 : osql et la gestion des codes retour


1- 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.

2- 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.

2-1- Syntaxe d'utilisation du binaire osql

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

C:\ 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), 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>

2-2- 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"

L'option -q lance également une commande SQL mais avec cette option, à l'issue de la commande SQL, l'utilisateur ne sort pas du programme osql.

3- La commande T-SQL RAISERROR

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

3-1- 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)
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.

3-1-1- 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.

Niveau de sévérité Description
10 Message d'information, le problème est causé par une donné entrée par l'utilisateur.
11-16 Erreurs qui peuvent être corrigés par l'utilisateur (problème de conversion etc…)
17 Ressources insuffisantes (verrous, espace disque etc…)
18 Erreur interne non fatale (problème dans l'optimisation d'une requête)
19 Erreur ressource SQL Server qui ne peut être que corrigé par un administrateur système

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.

Niveau de sévérité Description
20 Erreur fatale : process infecté sans supposer que cela soit dommageable à la base de données
21 Erreur fatale : problème sur tous les process dans la base de données sans supposer que cela soit dommageable à la base de données
22 Erreur fatale : intégrité de la table suspecte
23 Erreur fatale : intégrité de la base de données suspecte
24 Erreur fatale des supports disque

3-1-2- Options WITH LOG | NOWAIT | SETERROR

Option Description
LOG Ecrit l'erreur dans le fichier de log du serveur avec une limite à 440 bytes. L'option WITH LOG est obligatoire pour toutes les sévérités comprises entre 19 et 25.
NOWAIT Envoi immédiat du message au client
SETERROR Applique la valeur 50000 ou msg_id à la variable global T-SQL @@ERROR selon le niveau de sévérité (@@ERROR vaut 0 par défaut pour toutes les sévérités comprises entre 1 et 10)

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.

4- Gestion des codes retour du binaire osql avec la commande RAISERROR

4-1- 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%

4-2- 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).

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

5-1- 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.

Fichier 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

5-2- 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.

Commande RAISERROR %ERRORLEVEL% Exit du binaire osql
raiserror('Raiserror declenche',10,1) 0 Non
raiserror('Raiserror declenche',10,127) 0 Oui
raiserror('Raiserror declenche',11-24,1) [WITH LOG] 1 Oui
raiserror('Raiserror declenche',11-24,127) [WITH LOG] 50000 Oui

Annexe

Historique

Version Date Commentaires
1.0 09/2005 Version initiale

Liens

MSDN Books Online, SQL Server 2000, Using the SQL Server Tools, Command Prompt Utilities - osql utility
MSDN Books Online, SQL Server 2000, Transact-SQL Reference, RAISERROR