Bien qu’il ne soit pas conseillé de le faire, il est possible de manipuler les fichiers de Windows sous MS SQL Server. voici une liste des procédures et moyens d’y parvenir.
ATTENTION : s’agissant pour la plupart de commandes non documentées, toutes ces commandes peuvent être modifiées sans préavis en fonctions des différentes version de SQL Server et leur exécution peut différer en fonction des OS (éditions et version) ainsi que de certains paramètres comme ceux de la langue d’installation de l’OS et de SQL Server.
1 – PROCÉDURES ÉTENDUES DE MANIPULATION DES FICHIERS SOUS WINDOWS AVEC MS SQL SERVER
1.1 – xp_fixeddrives
Obtention des lecteurs disque fixe et du nombre de Mo disponibles.
Exemple :
EXEC master.sys.xp_fixeddrives
drive Mo disponibles
----- --------------
C 138475
D 215215
S 931390
Z 4026
1.2 – xp_availablemedia :
Obtention des média de stockage.
Exemple :
EXEC master.sys.xp_availablemedia
name low free high free media type
--------- ----------- ----------- ----------
C:\ -827088896 33 2
D:\ -1963040768 52 2
S:\ 1675632640 227 2
Z:\ -72544256 0 2
Je ne sais pas très bien à quoi correspondent les colonnes low free et high free, mais je soupçonne une erreur de calcul 32/64 bits sur les capacités de stockages d’unité. En revanche en ce qui concerne le type de média, la liste est la suivante :
1 Floppy
2 Hard Disk
8 CD-ROM
4 Tape
16 Shared Disk
1.3 – xp_subdirs
Liste des sous répertoire d’un répertoire en cours.
Exemple :
EXEC master.sys.xp_subdirs 'C:\'
subdirectory
-----------------------------
AUDIT_SQL
data
DATABASES
DATALOG
DataStream
Documents and Settings
hp
i386
Inetpub
LOGDB
MSOCache
MS_Office_2007
oraclexe
PerfLogs
Perl
pgRails
PostGreSQL
POWERAMC
Program Files
RECYCLER
SQL
SQLMAINTENANCE
SQL_databases
System Recovery
System Volume Information
SYSTEM.SAV
TEMP
WINDOWS
XEClient
1.4 – xp_dirtree
Liste des sous répertoires, tous niveaux confondus
EXEC master.sys.xp_dirtree 'C:\SQL'
subdirectory depth
--------------------------------- -----------
Data 1
logs 1
Serveurs 1
Microsoft 2
SQLServers 3
V2000 4
MSSQL$SQL2000 5
Binn 6
dll 7
exe 7
Resources 7
1033 8
1036 8
Install 6
SQL Server 2008 1
Bases exemple 2
1.5 – xp_create_subdir
Création d’un sous répertoire.
Exemple :
EXEC master.sys.xp_create_subdir 'C:\nimportequoi'
1.6 – xp_fileexist
Test d’existence d’un objet dans un répertoire.
Exemple :
EXEC master.sys.xp_fileexist 'C:\boot.ini'
Ce fichier existe déjà Ce fichier est un répertoire Le répertoire parent existe
---------------------- ---------------------------- ---------------------------
1 0 1
1.7 – xp_delete_file
Suppression des fichiers de sauvegarde de SQL Server (SQL Server vérifie que ce sont bien des sauvegardes SQL Server ou des fichiers de rapport de sauvegarde d’extension .TXT).
Exemple :
Paramètres :
- 1 : FileTypeSelected (0 = sauvegardes, 1 = rapport de sauvegarde)
- 2 : chemin du répertoire contenant les fichiers de sauvegardes (doit se terminer par un « backslash »
- 3 : suffixe des fichiers à supprimer (BAK, TRN, TXT)
- 4 : date avant laquelle les fichiers sont effectivement supprimés
- 5 : suppression dans les sous répertoires (0 => le répertoire en cours, 1 => le répertoire en cours + les sous répertoires de niveau 1)
Ce dernier paramètre n’étant valable qu’à partir de la version 2008.
SQL Server vérifie qu’il s’agit bien de fichier spécifiquement dédiés à la sauvegarde en commençant par en lire certaines informations.
2 – EXÉCUTION D’UNE COMMANDE GÉNÉRIQUE DE l’OS WINDOWS SOUS SQL SERVER
La procédure xp_cmdshell permet de lancer une commande de l’OS dans SQL Server. ATTENTION : le lancement de certains commande peut conduire à un état instable, c’est pourquoi elle est désactivée par défaut.
Exemple :
EXEC master.sys.xp_cmdshell 'DIR "c:\*.*"'
Liste des éléments d’un répertoire
2e exemple :
EXEC master.sys.xp_cmdshell 'DEL "C:\unfichiertest.txt"'
Suppression d’un fichier
Pour débloquer l’exécution de cette procédure, il faut reconfigurer le serveur comme suit :
RECONFIGURE;
Si cela ne suffit pas il faut préalablement débloquer l’accès aux options avancées de la reconfiguration du serveur :
RECONFIGURE;
Autres commandes OS utilisables :
- DIR : liste les objets du répertoire en cours (/A pour les attributs, /T pour la dateheure affichée)
- CD ou CHDIR : (change directory) changement de repertoire courant
- MD ou MKDIR : (make directory) création d’un répertoire dans le répertoire courant
- COPY : copie des fichiers
- XCOPY : copie des fichiers et des répertoires
- MOVE : déplace des fichiers et des répertoires
Avec pour la commande DIR :
/A
- D : répertoire
- R : fichiers en lecture seule (READ ONLY)
- H : fichiers cachés (HIDDEN)
- A : fichiers archives (ARCHIVE)
- S : fichiers systèmes (SYSTEM)
- - préfixe de négation.
/T
- C : Creation (CREATE)
- A : dernier accès (ACCESS)
- W : dernières écriture (WRITE)
3e exemple :
EXEC master.sys.xp_cmdshell 'DIR "C:\Windows\*.*" /A-D /TW '
output
-----------------------------------------------------------
Le volume dans le lecteur C s'appelle root
Le numéro de série du volume est 0BC0-B433
NULL
Répertoire de C:\Windows
NULL
15/05/2011 09:41 0 0.log
28/12/2006 12:01 19Â 569 003018_.tmp
03/05/2005 19:43 69Â 632 Alcmtr.exe
04/05/2006 17:26 2Â 808Â 832 alcwzrd.exe
23/10/2007 18:46 262Â 144 ATKKBService.exe
15/05/2011 09:39 2Â 048 bootstat.dat
02/03/2006 03:00 65Â 978 Bulles de savon.bmp
02/03/2006 03:00 82Â 944 clock.avi
13/04/2011 11:24 200 cmsetacl.log
17/04/2011 02:21 104Â 060 comsetup.log
08/05/2006 10:22 0 control.ini
...
Il existe d’autres commandes OS mais elle présentent peu d’intérêt pour MS SQL Server.
3 – UTILISATION DE OLE POUR L’ACTIVATION DE COMMANDES FICHIER SOUS SQL SERVER
Une autre voie pour manipuler des fichiers sous MS SQL Server est d’utiliser un accès OLE pour effectuer certaines manipulations.
Cela nécessite au préalable de débloquer l’utilisation de OLE via les procédures conçues à cet effet et pour ce faire il faut lancer la commande de reconfiguration du serveur, suivante :
RECONFIGURE;
Si cela ne suffit pas il faut préalablement débloquer l'accès aux options avancées de la reconfiguration du serveur :
<code>EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
Les procédures de manipulation OLE sont les suivantes :
sys.sp_OACreate : Crée une instance d'un objet OLE et retourne un token d'accès à l'objet
sys.sp_OAGetProperty : Retourne la valeur d'uee propriété de l'objet OLE manipulé.
sys.sp_OASetProperty : Affecte une nouvelle valeur à une propriété de l'objet OLE manipulé.
sys.sp_OAMethod : Invoque une méthode de l'objet OLE manipulé.
sys.sp_OAStop : Arrête l'environnement d'exécution de la procédure stockée OLE Automation au niveau du serveur.
sys.sp_OADestroy : Détruit l'objet OLE précédemment créé.
sys.sp_OAGetErrorInfo : Obtient des informations d'erreur OLE Automation.
Voici par exemple une procédure stockée retournant les disques fixes et leur taux d’occupation. Elle utilise OLE et quelques procédures vues ci dessus.
USE master;
GO
-- création d'un schéma SQL de nom SQO (SQL OS)
CREATE SCHEMA SOS;
GO
-- création de la procédure de récupération des espaces dans les disques fixes
CREATE PROCEDURE SOS.P_DISK_SPACE
AS
/******************************************************************************
* Frédéric Brouard - alias SQLpro - http://sqlpro.developpez.com - 2011-05-15 *
*******************************************************************************
* retourne une table contenant des informations d’occupation des disques *
* du serveur *
*******************************************************************************
* copyright : Frédéric Brouard / SQLpro / SQLspot - http://www.sqlspot.com *
* Expertise, audit, tuning, optimisation, conseil, formation... MS SQL Server *
******************************************************************************/
SET NOCOUNT ON;
-- variables locales
DECLARE @HDL INT, @FSO_TOKEN INT, @LCT_LECTEUR CHAR(1), @NUM_LECTEUR INT, @TAILLE_TOTALE VARCHAR(32), @MB BIGINT;
DECLARE @T_LECTEUR_LCT TABLE
(LCT_NOM_SERVEUR VARCHAR(15),
LCT_LECTEUR CHAR(1) PRIMARY KEY,
LCT_NUM INT,
LCT_ESPACE_LIBRE FLOAT NULL,
LCT_TAILLE_TOTALE FLOAT NULL,
LCT_DATEHEURE DATETIME NULL)
-- assignation préalable :
SET @MB = 1048576;
INSERT @T_LECTEUR_LCT(LCT_LECTEUR, LCT_ESPACE_LIBRE)
EXEC master.dbo.xp_fixeddrives;
-- appel OLE Ã fso (File System Object) pour manipuler les fichiers
-- création d'un jeton de communication avec le FSO
EXEC @HDL = sp_OACreate 'Scripting.FileSystemObject', @FSO_TOKEN OUT
IF @HDL 0
EXEC sp_OAGetErrorInfo @FSO_TOKEN;
-- tant que la table n'est pas complétement renseignée :
WHILE EXISTS(SELECT *
FROM @T_LECTEUR_LCT
WHERE LCT_TAILLE_TOTALE IS NULL)
BEGIN
-- selection du premier lecteur
SELECT TOP 1 @LCT_LECTEUR = LCT_LECTEUR
FROM @T_LECTEUR_LCT
WHERE LCT_TAILLE_TOTALE IS NULL;
-- obtention du n° du lecteur
EXEC @HDL = sp_OAMethod @FSO_TOKEN, 'GetDrive', @NUM_LECTEUR OUT, @LCT_LECTEUR
--EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @HDL 0
EXEC sp_OAGetErrorInfo @FSO_TOKEN;
-- obtention du volume du lecteur
EXEC @HDL = sp_OAGetProperty @NUM_LECTEUR, 'TotalSize', @TAILLE_TOTALE OUT
IF @HDL 0
EXEC sp_OAGetErrorInfo @NUM_LECTEUR;
-- mise à jour de la table
UPDATE @T_LECTEUR_LCT
SET LCT_TAILLE_TOTALE = @TAILLE_TOTALE/@MB,
LCT_NUM = @NUM_LECTEUR,
LCT_NOM_SERVEUR = host_name(),
LCT_DATEHEURE = GETDATE()
WHERE LCT_LECTEUR = @LCT_LECTEUR;
END
-- suppression du jeton FSO
EXEC @HDL = sp_OADestroy @FSO_TOKEN;
IF @HDL 0
EXEC sp_OAGetErrorInfo @FSO_TOKEN;
-- extraction des résultats
SELECT LCT_NOM_SERVEUR, LCT_LECTEUR, LCT_NUM,
LCT_TAILLE_TOTALE AS TOTAL_MO, LCT_ESPACE_LIBRE AS ESPACE_LIBRE_MO,
CAST((LCT_ESPACE_LIBRE/(LCT_TAILLE_TOTALE*1.0))*100.0 AS DECIMAL(16,2)) AS POURCENT_LIBRE,
LCT_DATEHEURE
FROM @T_LECTEUR_LCT
ORDER BY LCT_LECTEUR;
GO
Exemple d’utilisation :
EXEC SOS.P_DISK_SPACE
LCT_NOM_SERVEUR LCT_LECTEUR LCT_NUM TOTAL_MO ESPACE_LIBRE_MO POURCENT_LIBRE LCT_DATEHEURE
--------------- ----------- ----------- ------------ ------------------ ----------------- -----------------------
HPWFRED C 33488638 464629 138347 29.78 2011-05-15 15:22:36.773
HPWFRED D 50265854 476937 215215 45.12 2011-05-15 15:22:36.777
HPWFRED S 67043070 1907757 931390 48.82 2011-05-15 15:22:36.780
HPWFRED Z 83820286 12291 4026 32.76 2011-05-15 15:22:36.783
4 – UTILISATION DE POWERSHELL POUR MANIPULER DES FICHIER SOUS SQL SERVER
PowerShell est un outil en ligne de commande pour la manipulation des objets système. C’est le nom à la fois de l’outil et du langage. Il est inclus en standard à partir de la version 7 et 2008 de Windows 7, mais on peut télécharger l’outil pour les autres versions de Windows (XP, Vista…) à condition qu’ils supportent le framework .net version 2.0
Pour entrer dans une fenêtre de commande PowerShell, il suffit de taper dans une fenêtre de commande OS (CMD.exe) la commande PowerShell. Dès lors on peut s’exercer à lancer des commandes PowerShell.
Exemple :
gci c:\*.* | Sort-Object length
Liste les fichiers du répertoire C:\ dans l’ordre de volumétrie.
Pour encapsuler une commande PowerShell au sein d’une commande système, il est possible d’utiliser xp_cmdshell :
EXEC xp_cmdshell 'POWERSHELL.exe "gci c:\*.* | Sort-Object length"'
output
--------------------------------------------------------------------------
NULL
NULL
Répertoire : C:\
NULL
NULL
Mode LastWriteTime Length Name
---- ------------- ------ ----
-a--- 05/08/2008 12:44 51 RmDev.log
-a--- 05/09/2009 23:41 838 updatedatfix.log
-a--- 25/02/2009 22:40 111191 gzip.txt
-a--- 27/04/2010 15:08 142667 comparatif-sgbd.odt
-a--- 12/01/2011 14:26 1048576 DBTESTE3_Data2.ndf
-a--- 27/04/2011 14:33 16128000 testcap.avi
-a--- 18/01/2011 10:10 43224576 ALL server.bak
NULL
NULL
NULL
On peut bien entendu supprimer des fichiers à l’aide de PowerShell. Voici un exemple de suppression de certains fichiers via une commande PowerShell :
Get-Childitem $DataFolder -Path C:\Test\ -include *.bak -recurse | ? {!$_.PSIsContainer -and ($_.CreationTime -lt (get-Date).adddays(-60))} | remove-item
Cette commande supprime de manière récursive (donc dans tous les sous répertoires) tous les fichiers dont l’extension est .bak, situées en racine du répertoire C:\Test\ pour des fichiers datant de plus de 60 jours.
NOTA : les codes figurant dans cet article ont été testés sous Microsoft SQL Server 2008 R2 (10.50.1600.1 / Intel X86) Developer Edition sous Windows XP (Build 2600: Service Pack 3)
Références WEB :
Procédures de manipulation des fichiers sous MS SQL Server (et autres procédures non documentées)
http://www.mssqlcity.com/Articles/Undoc/UndocExtSP.htm
http://deepakrangarajan.blogspot.com/2008/02/some-useful.html
http://stopwords.ru/LiB0093.shtml
http://www.windowsitpro.com/Files/3539/Table_01.html
http://www.scribd.com/doc/13844517/MSSQL-Extended-Stored-Procedures
PowerShell :
Initiation : http://syskb.com/powershell-pour-les-nuls/
Cours : http://windows.developpez.com/cours/?page=powershell#powershell-debutants
Expert S.G.B.D relationnelles et langage S.Q.L
Moste Valuable Professionnal Microsoft SQL Server
Société SQLspot : modélisation, conseil, formation,
optimisation, audit, tuning, administration SGBDR
Enseignant: CNAM PACA, ISEN Toulon, CESI Aix en Prov.
L’entreprise SQL Spot
Le site web sur le SQL et les SGBDR
Bonjour,
Bonne information, c’est bien ce que je veux faire, mais ….
Nous avons 1 serveur SQL (sans accès utilisateurs au niveau fichiers), et un serveur de fichiers (partages, disques mappés, ..).
Un batch tourne, lance des exportation IS pour générer des fichiers XLS en local. Il reste à copier ce fichier dans le répertoire ad hoc du serveur de fichier, disponible aux utilisateurs.
Pour générer le fichier XLS en local, c’est OK.
Quand je copie ce fichier vers des disques réseau, je reçois « Permission denied » (Accès refusé). Et ce malgré les droits en écriture mis sur les partages les fichiers les répertoires.
Renseignements pris, même si la procédure stockée est lancé par moi (avec mon compte), et même si elle est lancée par un compte de service SqlServer semble être exécutée ni par moi ni par le compte de service, mais par dbo, qui est un compte local. Impossible de données les droits à ce compte sur un autre serveur….
Aurais-je loupé une étape ?