1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
|
--> Creér la procédure d'export des données de la table vers le fichier Excel
CREATE PROCEDURE EXPORT_Table_To_Excel
@RepDestination NVARCHAR(255)-- repertoire de destination des fichiers de sorties
,@NomFichierExport NVARCHAR(255) -- Nom du fichier Excel exporté
,@NomInstanceSQLServer NVARCHAR(255) -- Nom de mon instance SQL SERVER,
,@Login NVARCHAR(255) -- login de l'instance
,@Pwd NVARCHAR(255) -- mot de passe
,@NomTableSource SYSNAME -- Nom de la table à exporter.
/* Exemple d'utilisation : EXPORT_Table_To_Excel N'C:\',N'FichierExport.xls',N'MonInstanceSQL',N'monLogin', N'monMotDePasse',N'MaBase.dbo.TMP2' */
AS
BEGIN
SET NOCOUNT ON
--> Tester l'existence du repertoire de destination
DECLARE @t_RepDestination TABLE
(
file_exists bit
, file_is_a_directory bit
, parent_directory_exists bit
)
INSERT @t_RepDestination
EXEC xp_fileexist @RepDestination
IF EXISTS
(
SELECT *
FROM @t_RepDestination
WHERE file_is_a_directory = 0
)
BEGIN
RAISERROR('Le repertoire "%s" n''existe pas .', 16, 1, @RepDestination)
RETURN
END
--> Traitement pour l'export
DECLARE @FullNomFichier NVARCHAR(255) -- Nom absolu du fichier de sorti (@RepDestination + @NomFichierExport)
DECLARE @bcp_cmd VARCHAR(1024)
DECLARE @_export_fichier_temp_entete VARCHAR(1024) -- pour pré-formatter l'entete du fichier export
DECLARE @_export_fichier_temp_data VARCHAR(1024) -- préparer lexport des données
DECLARE @append VARCHAR(1024) -- pour ajouter les données dans le fichier de sorti
DECLARE @del VARCHAR(1024) -- pour supprimer les fichiers temporaires
SET @FullNomFichier = @RepDestination + @NomFichierExport
SET @_export_fichier_temp_entete = 'c:\temp\entete'+ REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(23), GETDATE(), 121),'.','_'),':','_'),'-','_'),' ','_') +'.xls'
SET @_export_fichier_temp_data = 'c:\temp\data'+ REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(23), GETDATE(), 121),'.','_'),':','_'),'-','_'),' ','_') +'.xls'
BEGIN TRY
-- Copie de l'entete du fichier dans le fichier de sortie
SET @bcp_cmd = 'bcp "SELECT ''[Text]'',''[last_execution_time]'',''[temps_en_seconde]'',''[nombre_d_execution]'' " QUERYOUT "' + @_export_fichier_temp_entete + '" -c -S "' + '" -U "'+@Login + '" -P "'+ @Pwd
--PRINT @bcp_cmd
EXEC xp_cmdshell @bcp_cmd
SET @append = 'type ' + @_export_fichier_temp_entete + ' >> ' + @FullNomFichier
--PRINT @append
EXEC master..xp_cmdshell @append
-- Copie des données dans le fichier de sortie
SET @bcp_cmd = 'bcp " select replace(replace(replace(Text,CHAR(13),'' ''),CHAR(10),'' ''),CHAR(9),'' ''),last_execution_time,temps_en_seconde,nombre_d_execution from ' + @NomTableSource + ' " QUERYOUT "' + @_export_fichier_temp_data + '" -c -S "' + '" -U "'+@Login + '" -P "'+ @Pwd
--PRINT @bcp_cmd
EXEC xp_cmdshell @bcp_cmd
SET @append = 'type ' + @_export_fichier_temp_data + ' >> ' + @FullNomFichier
--PRINT @append
EXEC master..xp_cmdshell @append
--Suppression des fichiers temporaires
SET @del = 'del "' + @_export_fichier_temp_data + '"'
EXEC master..xp_cmdshell @del
SET @del = 'del "' + @_export_fichier_temp_entete + '"'
EXEC master..xp_cmdshell @del
END TRY
-- Gestion des erreurs
BEGIN CATCH
SELECT 'Echec de l''export '
END CATCH;
END
--> Pour tester la procédure
--> Créer la table temporaire TMP2
IF OBJECT_ID('TMP2') IS NOT NULL DROP TABLE TMP2
CREATE TABLE dbo.TMP2(
[text] nvarchar(max) NULL,
last_execution_time datetime NOT NULL,
temps_en_seconde bigint NULL,
nombre_d_execution bigint NOT NULL
)
--> Chargement des données dans la table temporaire TMP2
insert into TMP2 (text,last_execution_time,temps_en_seconde,nombre_d_execution)
SELECT text,last_execution_time, max_worker_time/1000000, execution_count FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st WHERE max_worker_time > 1000000 ORDER BY last_execution_time DESC
--> Utilisation de la procédure stockée pour faire l'export dans un fichier Excel
EXPORT_Table_To_Excel N'C:\',N'FichierExport.xls',N'MonInstanceSQL',N'monLogin', N'monMotDePasse',N'MaBase.dbo.TMP2' |
