Microsoft SQL Server/Importer et exporter

Un livre de Wikilivres.
Sauter à la navigation Sauter à la recherche


Interfaces graphiques[modifier | modifier le wikicode]

Importer[modifier | modifier le wikicode]

Dans SSMS, l'option d'importation de fichier Excel ou Access dans MSSQL est disponible par un clic droit sur la base de destination, Tâches..., Importer des données...[1].

Exporter[modifier | modifier le wikicode]

De même, il existe Tâches..., Exporter des données... ou Copier la base de données.

Il est par ailleurs possible d'exporter le résultat d'une requête. En effet, par défaut dans SSMS, le bouton Résultats dans des grilles est enfoncé. En cliquant sur Résultats dans un fichier, il devient possible d'exporter le contenu de tables dans un fichier .rpt avec une requête SQL.

Pour transformer une base MS-SQL en MySQL il existe MySQL Workbench[2]. Attention car la syntaxe des procédures stockées est différente[3].

xp_cmdShell[modifier | modifier le wikicode]

xp_cmdShell permet de d’interagir avec le système de fichier, en manipulant le shell du système d'exploitation : il comprend donc les commandes DOS.

Créer un fichier texte[modifier | modifier le wikicode]

EXECUTE master.dbo.xp_cmdShell 'echo Hello World! > C:\Test.txt'
-- ou en abrégeant :
xp_cmdShell 'echo Hello World! > C:\Test.txt'

Pour le lire, il suffit de le charger dans une table avec BULK INSERT.

Copier un fichier[modifier | modifier le wikicode]

xp_cmdshell 'copy C:\Test.txt C:\Test2.txt'

Cacher un fichier[modifier | modifier le wikicode]

xp_cmdshell 'attrib +h C:\Test.txt'

Supprimer un fichier[modifier | modifier le wikicode]

xp_cmdshell 'del C:\Test2.txt'

Créer un dossier[modifier | modifier le wikicode]

L'antislash de fin est facultatif :

xp_cmdShell 'mkdir C:\Test\'

Lister le contenu d'un dossier[modifier | modifier le wikicode]

xp_cmdShell 'dir C:\Test\'

Supprimer un dossier[modifier | modifier le wikicode]

xp_cmdShell 'rmdir C:\Test\'

OPENROWSET[modifier | modifier le wikicode]

Pour archiver vers d'autres formats que ceux de la base de données, il existe la fonction OPENROWSET[4]. Elle permet d'importer ou d'exporter des données aux formats MS-Access ou MS-Excel[5].

Si ce pilote XLS demande une activation[6], il faut configurer :

sp_configure 'Show Advanced Options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries'; -- affiche l'état avant configuration
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries'; -- affiche l'état après configuration
GO

Soient les fichiers C:\Test_OPENROWSET.csv, C:\Test_OPENROWSET.xls et C:\Test_OPENROWSET.xlsx existants, avec une feuille nommée "Feuil1", dont les en-têtes de colonnes sont "Nom" et "Prénom".

Insertions dans un tableur[modifier | modifier le wikicode]

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Test_OPENROWSET.xls;', 'SELECT * FROM [Feuil1$]')
SELECT Nom, Prénom
FROM table1
 si le fichier XLS(X) existe, il sera rempli à la suite avec le format de sa dernière ligne. Pour forcer les cellules en format texte (pour éviter les arrondis), il faut donc faire précéder leurs valeurs d'un apostrophe.
Attention !

Logo Si le fichier XLS(X) a des en-têtes de colonnes, il faut les nommer au lieu d'employer *.


Sélections dans un tableur[modifier | modifier le wikicode]

CSV[modifier | modifier le wikicode]

On définit le dossier puis le fichier. Il y a deux pilotes au choix :

SELECT * FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=C:\;','select * from Test_OPENROWSET.csv');

ou

SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Text;Database=C:\;','SELECT * FROM [Test_OPENROWSET.csv]')

XLS[modifier | modifier le wikicode]

Pour lire un XLS ou ou l'importer dans une table, on définit le fichier puis la feuille :

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Test_OPENROWSET.xls;', 'SELECT * FROM [Feuil1$]')

Plusieurs options peuvent aussi être placées après le nom du fichier. Elles n'impactent pas le mode écriture, seulement le mode lecture.

  • "HDR" (comme header) : les en-têtes de colonnes sont sur la première ligne (comportement par défaut) "HDR=YES". Si "HDR=NO", les colonnes sélectionnées s'appellent alors "F1", "F2", "F3"...
  • "IMEX" (pour ImportMixedTypes)[7] :
    • "=0" : Export mode, Excel devine les types des champs.
    • "=1" : Import mode, les champs sont tous convertis en texte.
    • "=2" : Linked mode.
  • Types des données[8] :
    1. "DT_BOOL" : booléen.
    2. "DT_CY" : devise.
    3. "DT_DATE" : date et heure.
    4. "DT_NTEXT" : texte.
    5. "DT_R8" : numérique.
    6. "DT_WSTR" : chaîne de caractères.

On peut aussi créer un serveur lié pour l'occasion[9] :

EXEC sp_addlinkedserver
    @server = 'ExcelServer1',
    @srvproduct = 'Excel',
    @provider = 'Microsoft.Jet.OLEDB.4.0',
    @datasrc = 'C:\Test_OPENROWSET.xls',
    @provstr = 'Excel 8.0;IMEX=1;HDR=YES;'
GO
SELECT * FROM ExcelServer1...[Sheet1$]
GO
SELECT * FROM OPENROWSET(ExcelServer1, 'SELECT * FROM [Sheet1$]')

XLSX[modifier | modifier le wikicode]

Pour un XLSX c'est un autre pilote :

select * from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;Database=C:\Test_OPENROWSET.xlsx;', 'SELECT * FROM [Feuil1$]')

S'il faut enregistrer le pilote, l'installer depuis : https://www.microsoft.com/fr-FR/download/details.aspx?id=23734.

Modification d'un tableur[modifier | modifier le wikicode]

La fonction OPENROWSET de SQL Server 2008 ne permet pas de modifier les propriétés des cellules d'un tableur, pour se faire se reporter au paragraphe sur sp_OACreate. Dans tous les cas, il peut tout à fait mettre à jour ses valeurs comme si elles étaient dans des tables :

UPDATE OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Test_OPENROWSET.xls;HDR=yes','SELECT * FROM [Feuil1$]') 
SET F1 = '2'
WHERE F1 = '1'
 la commande DELETE ne fonctionne pas sur des lignes Excel.

BCP[modifier | modifier le wikicode]

bcp (pour Bulk Copy) est un utilitaire d'importation et exportation de données avec des fichiers plats uniquement (XML ou CSV[10]).

DECLARE @cmd VARCHAR(255)
SET @cmd = 'bcp "select ''Hello'', ''World''" queryout "C:\Test_bcp.csv" -U MonCompte -P MonMotDePasse -c'
Exec xp_cmdshell @cmd

sp_OACreate[modifier | modifier le wikicode]

master.dbo.sp_OAMethod est une procédure stockée étendue permettant de manipuler des fichiers et des dossiers[11]. Elle n'est pas activée par défaut sous SQL Server 2008, il faut donc le faire ainsi :

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures'; -- affiche l'état avant configuration
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures'; -- affiche l'état après configuration

Voici un exemple de création puis de modification de fichier XLS (les numéros des couleurs sont les mêmes qu'en VBA[12]) :

DECLARE @r int,	-- résultats des commandes
	@FileName varchar(512),
	@Excel int,
	@WorkBooks int,
	@WorkBook int,
	@WorkSheet int,
	@Cells int

-- 1) Création
SET @FileName = 'C:\Test_sp_OACreate.xls'
EXEC @r = sp_OACreate 'Excel.Application', @Excel output
IF @r=0 EXEC @r = sp_OAMethod @Excel, 'Workbooks', @WorkBooks OUTPUT
IF @r=0 EXEC @r = sp_OAMethod @WorkBooks, 'Add', @WorkBook OUTPUT, -4167
IF @r=0 EXEC @r = sp_OAMethod @WorkBook, 'Worksheets(1)', @WorkSheet output, 2
IF @r=0 EXEC @r = sp_OAMethod @WorkSheet, 'Activate'
IF @r=0 EXEC @r = sp_OASetProperty @WorkSheet, 'Name', 'Reporting'
IF @r=0 EXEC @r = sp_OASetProperty @WorkSheet, 'Cells(1,3).Value', 'Hello World!'
IF @r=0 EXEC @r = sp_OAMethod @WorkBook, 'SaveAs', NULL, @FileName
IF @r=0 EXEC @r = sp_OAMethod @WorkBook, 'Close'

-- 2) Une fois le fichier fermé on peut le remplir ici avec OPENROWSET

-- 3) Retouches
IF @r=0 EXEC @r = sp_OAMethod @Excel, 'WorkBooks.Open', @WorkBook output, @FileName
IF @r=0 EXEC @r = sp_OAMethod @WorkBook, 'Worksheets(1)', @WorkSheet output, 2
IF @r=0 EXEC @r = sp_OAMethod @WorkSheet, 'Activate'

EXEC @r = sp_OASetProperty @WorkSheet, 'Range("A1").Value', 'Hello World1!'

EXEC @r = sp_OAGetProperty @WorkSheet, 'Cells', @Cells OUTPUT, 2, 2 -- Position de la cellule B2
EXEC @r = sp_OASetProperty @Cells, 'Value', 'Hello World2!'
EXEC @r = sp_OASetProperty @Cells, 'Font.Bold', 1					-- gras
EXEC @r = sp_OASetProperty @Cells, 'Font.Colorindex', 3				-- rouge pour la police
EXEC @r = sp_OASetProperty @Cells, 'Interior.ColorIndex', 4			-- vert pour le fond
EXEC @r = sp_OASetProperty @Cells, 'Borders.ColorIndex', 5			-- bleu pour les bordures

EXEC @r = sp_OASetProperty @Excel, 'ActiveWorkbook.Worksheets(1).Cells(3,3).Value', 'Hello World3!'
EXEC @r = sp_OASetProperty @Excel, 'ActiveWorkbook.Worksheets(1).Cells(3,3).Font.Colorindex',  9

EXEC sp_OAMethod @Excel, 'ActiveWorkbook.Save'
EXEC sp_OAMethod @Excel, 'Workbooks.Close'
EXEC sp_OAMethod @Excel, 'Close'

EXEC sp_OADestroy @Cells
EXEC sp_OADestroy @WorkSheet
EXEC sp_OADestroy @WorkBook
EXEC sp_OADestroy @WorkBooks
EXEC sp_OADestroy @Excel

Sauvegardes .bak et .trn[modifier | modifier le wikicode]

Tout d'abord, il faut distinguer l'archivage des bases (copie d'un .mdf en .bak) de celui des logs (.ldf en .trn) appelés aussi journaux de transaction.

Backup des bases[modifier | modifier le wikicode]

Il est recommandé d'effectuer automatiquement celui un backup des bases toutes les nuits, à l'aide d'un job (dans SSMS, en bas de l'arborescence, menu Travaux). Un deuxième pourra s'occuper de supprimer les .bak après une certaine durée de rétention qui peut dépendre de l'espace disponible sur le serveur.

Exemple de sauvegarde sur un disque dur du serveur[13] (et non pas un du client où SSMS est lancé) :

declare @chemin as varchar(255) = 'C:\' + CONVERT(char(10), GetDate(),126) + '-sugarcrm.bak'
BACKUP DATABASE sugarcrm
TO DISK = @chemin
   WITH FORMAT,
   MEDIANAME = '',
   NAME = 'Full Backup';
GO

Journaux[modifier | modifier le wikicode]

Requêtes[modifier | modifier le wikicode]

Par défaut, SSMS ne permet pas de consulter les requêtes SQL envoyées au serveur.

Toutefois, les dernières requêtes sont stockées dans un fichier des traces .trc. Pour connaitre son nom :

SELECT * FROM ::fn_trace_getinfo(default)

Ensuite on peut en extraire les 10 dernières requêtes SQL envoyées au serveur :

SELECT top 10 StartTime, TextData
FROM fn_trace_gettable('D:\MSSQL10.MSSQLSERVER\MSSQL\Log\log_424.trc', default)
where ISNULL(convert(varchar,TextData,112), '')<>''
order by StartTime desc

Soit en une seule requête :

declare @logs varchar(255) = (SELECT top 1 convert(varchar(255),value,112) FROM ::fn_trace_getinfo(default) where property = 2)
SELECT top 10 StartTime, TextData
FROM fn_trace_gettable(@logs, default)
where ISNULL(convert(varchar,TextData,112), '')<>''
order by StartTime desc

Autre solution, passer par les statistiques de performance :

SELECT top 10 d.last_execution_time, e.text
FROM    sys.dm_exec_query_stats d
        CROSS APPLY sys.dm_exec_sql_text(d.plan_handle) AS e
order by d.last_execution_time desc
Attention !

Logo La plupart des requêtes des traces sont différentes de celles des statistiques, plus exhaustive mais sans les backups.


Transactions[modifier | modifier le wikicode]

Il existe une fonction pour lire les journaux des transactions non archivées :

SELECT * FROM fn_dblog(NULL, NULL)

Afin de gagner de la place, ces logs doivent par contre être supprimés régulièrement (c'est sans incidence sur l'utilisation du système). Il existe trois façons de les tronquer :

  1. DBCC SHRINKFILE (N'MaBase_log' , 0, TRUNCATEONLY). DBCC est le sigle de DataBase Console Commands (commandes en console de base de données), c'est un ensemble de commandes sur les bases[14].
  2. Clic droit sur la base, tâches, réduire... Fichiers, Type de fichier : Journal.
  3. Clic droit sur la base, tâches, détacher... (la base disparait ensuite de la liste), déplacer le .ldf, puis clic droit sur le serveur, joindre... En sélectionnant le .mdf, un nouveau .ldf vierge sera automatiquement créé avec.

Pour le définir automatiquement, il faut créer un job : Agent SQL server, Travaux, Clic droit : Nouveau travail[15].

Restaurations[modifier | modifier le wikicode]

Dans SSMS, pour restaurer une base de données, il faut faire un clic droit sur Bases de données, puis :

  1. Restaurer les fichiers : si le backup ne doit pas écraser la base originale.
  2. Restaurer la base de données... : pour remplacer une base (préalablement détachée) par sa sauvegarde. On doit ensuite choisir une base de données existante ou un fichier de backup (.bak) avec l'option "Périphérique".

Sinon en SQL ça donne[16] :

RESTORE DATABASE MaBase
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Backup\2016-02-16-MaBase.bak'
WITH REPLACE

Références[modifier | modifier le wikicode]

  1. http://blog.sqlauthority.com/2015/06/24/sql-server-fix-export-error-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine/
  2. http://www.mysql.fr/products/workbench/
  3. http://www.thegeekstuff.com/2014/03/mssql-to-mysql-stored-procedure/
  4. https://msdn.microsoft.com/fr-fr/library/ms190312.aspx
  5. http://stackoverflow.com/questions/87735/how-do-you-transfer-or-export-sql-server-2005-data-to-excel
  6. http://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.htm
  7. https://msdn.microsoft.com/fr-fr/library/ms141683.aspx
  8. https://msdn.microsoft.com/fr-fr/library/ms141036.aspx
  9. http://www.excel-sql-server.com/excel-import-to-sql-server-using-linked-servers.htm
  10. https://msdn.microsoft.com/fr-fr/library/ms162802(v=sql.100).aspx
  11. http://sqlindia.com/copy-move-files-folders-using-ole-automation-sql-server/
  12. https://msdn.microsoft.com/fr-fr/library/office/ff840443.aspx
  13. https://msdn.microsoft.com/fr-fr/library/ms186865%28v=sql.120%29.aspx?f=255&MSPPError=-2147217396
  14. https://msdn.microsoft.com/fr-fr/library/ms188796(v=sql.120).aspx
  15. http://communitybi.blogspot.fr/2011/12/comment-creer-un-job-dans-sql-server.html
  16. https://msdn.microsoft.com/fr-fr/library/ms186858%28v=sql.100%29.aspx