Microsoft SQL Server/Importer et exporter

Performances

SugarCRM

Microsoft SQL Server

Annexes

Version imprimable

[ Modifier le sommaire ]

Performances

SugarCRM

Interfaces graphiques

Importer

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

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

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

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

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

Cacher un fichier

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

Supprimer un fichier

xp_cmdshell 'del C:\Test2.txt'

Créer un dossier

L'antislash de fin est facultatif :

xp_cmdShell 'mkdir C:\Test\'

Lister le contenu d'un dossier

xp_cmdShell 'dir C:\Test\'

Supprimer un dossier

xp_cmdShell 'rmdir C:\Test\'

OPENROWSET

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

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.

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

CSV

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

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

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

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

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

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

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

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

Requêtes

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

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

Transactions

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 :

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].
Clic droit sur la base, tâches, réduire... Fichiers, Type de fichier : Journal.
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

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

Restaurer les fichiers : si le backup ne doit pas écraser la base originale.
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

[1] ttp://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] ttp://www.mysql.fr/products/workbench/

[3] ttp://www.thegeekstuff.com/2014/03/mssql-to-mysql-stored-procedure/

[4] ttps://msdn.microsoft.com/fr-fr/library/ms190312.aspx

[5] ttp://stackoverflow.com/questions/87735/how-do-you-transfer-or-export-sql-server-2005-data-to-excel

[6] ttp://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.htm

[7] ttps://msdn.microsoft.com/fr-fr/library/ms141683.aspx

[8] ttps://msdn.microsoft.com/fr-fr/library/ms141036.aspx

[9] ttp://www.excel-sql-server.com/excel-import-to-sql-server-using-linked-servers.htm

[10] ttps://msdn.microsoft.com/fr-fr/library/ms162802(v=sql.100).aspx

[11] ttp://sqlindia.com/copy-move-files-folders-using-ole-automation-sql-server/

[12] ttps://msdn.microsoft.com/fr-fr/library/office/ff840443.aspx

[13] ttps://msdn.microsoft.com/fr-fr/library/ms186865%28v=sql.120%29.aspx?f=255&MSPPError=-2147217396

[14] ttps://msdn.microsoft.com/fr-fr/library/ms188796(v=sql.120).aspx

[15] ttp://communitybi.blogspot.fr/2011/12/comment-creer-un-job-dans-sql-server.html

[16] ttps://msdn.microsoft.com/fr-fr/library/ms186858%28v=sql.100%29.aspx

[1]

[2]

[3]

[4]

[5]

[6]

[7]

[8]

[9]

[10]

[11]

[12]

[13]

[14]

[15]

[16]