Sql Server 2005: Logger les messages et résultats de script sql dans un fichier

Introduction
Nous avons de longs scripts de migrations et ces derniers produisent de nombreux messages (jusqu'a 15 millions de lignes).
Pour des raisons de validation, cette information doit être tenue en rétention pendant une période plus ou moins longue.
Dans cette quantité incroyable d'information (dont certains "x rows affected" ne peuvent être évités), il convient également de pouvoir faire des recherches sur des mots clés (comme par exemple "error").

C'est que la manipulation de logs/résultats aussi longs n'est pas franchement dans les cordes du Management Studio de Sql Server.
Certains logiciels en ligne de commande s'en sortent vraiment mieux.

Le besoin
Il faut donc avoir la possibilité d'exécuter des scripts sql et récupérer les messages (et si possible les résultats) dans un fichier texte.
Ce fichier pourra par la suite être utilisé pour effectuer les vérifications nécessaires.

La solution: SqlCmd
SqlCmd est un utilitaire Sql Serveur en ligne de commande.
Apparu avec Sql Server 2005, ce dernier permet d'exécuter des scripts (en intéractif ou non) depuis une console DOS. Très pratique pour automatiser certaines tâche (ou accéder à la connexion administrative), SqlCmd permet également d'exécuter des fichier Sql et récupérer les résultat et ce y compris les messages.

Voici, en guise d'exemple, le fichier batch "Exec.bat".
Il exécutera les scripts sql contenu dans le fichier "MessageToFile.sql" (voir plus bas) à l'aide de SqlCmd.
Les résultats d'exécution sont eux stockés dans le fichier d'output "result.txt":
Cet exemple démontre:

1. Que les datasets SQL sont écrits dans le fichier output.
2. Que les messages sont écrits dans le fichier output.
3. Que les erreurs sont également écrites dans le fichier output :-)

Exec.bat

sqlcmd -U SqlUser -P SqlPassword -S SqlServer -d myDatabase -i MessageToFile.Sql -o result.txt 

Voici une description sommaire des paramètres (attention: il sont case-sensitive).

• S: Le serveur Sql à contacter. 
• U: Le login Sql Server utilisé par SqlCmd. Notez qu'il est possible d'utiliser une "trusted connection" à la place. 
• P: Le mot de passe correspondant au login sql. 
• d: La base données sur laquelle le script Sql sera exécuté. 
• i: Input file, le fichier sql à exécuté. Dans le cas ci-present, ke fichier MessageToFile.Sql se trouve dans le même répertoire que Exec.bat. 
• o: Le fichier d'output dans lequel les messages et résultats seront écrits (dans ce cas, rien n'est affiché à l'écran).

MessageToFile.sql

Ce fichier contient les commandes exécutés par Sql Cmd (via le batch Exec.bat)

-- Output a message
print "Today We are "+Cast( GetDate() as Varchar(30) )
go
-- Output a result
select top 10 name from sysobjects where name like "DF%A%"
go
-- Output having a convertion error
--    Prooving that error are also written to output file
select top 10 Cast( Name as Int ) from sysobjects where name like "DF%A%"
go
-- Output the final message 
--    Proving that even with the covertion error, this messahe
--    is still logged to the file.
print "--- END OF TEST ----"

Result.txt

Le contenu de "result.txt" demontre bien l'écriture (log) des messages, datasets et erreurs sql.

Today We are Jan 21 2010 11:42AM
name                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------
DF__tblTMProj__Chdat__005FFE8A                                                                                                  
DF__tblLSSign__Chdat__027D5126                                                                                                  
DF__tblDMNumI__ChUse__03A67F89                                                                                                  
DF__tblDMNumI__Chdat__049AA3C2                                                                                                  
DF__tblDSDocu__ChUse__0524B3A7                                                                                                  
DF__tblDSDocu__Chdat__0618D7E0                                                                                                  
DF__dtpropert__versi__0697FACD                                                                                                  
DF__tblRDArm__ChUser__07420643                                                                                                  
DF__tblRDArm__Chdate__08362A7C                                                                                                  
DF__tblDMIFCA__ChUse__095F58DF                                                                                                  

(10 rows affected)
Msg 245, Level 16, State 1, Server myDatabase, Line 3
Conversion failed when converting the nvarchar value 'DF__tblTMProj__Chdat__005FFE8A' to data type int.
--- END OF TEST ----