[SQL Server] Lancer dynamiquement un abonnement SSRS
-- =============================================
-- Author: Zouhaier KHARROUBI
-- Create date: 17/01/2018
-- Description: Lancer dynamiquement un abonnement SSRS
-- NB: Le présent process concerne un abonnement d'un rapport SSRS qui se génère automatiquement sur un partage réseau
-- =============================================
DECLARE @ExtensionSettings VARCHAR(MAX)
,@Parameters VARCHAR(MAX)
,@Parametre_1_Rpport INT
,@Dest_Mail VARCHAR(69)
,@job_name VARCHAR(150)
,@SubscriptionID VARCHAR(150)
,@Requete NVARCHAR(1500)
,@Chemin_Extract VARCHAR(250)='\\mektaba\MesRapports\Extract\'
,@Nom_Rapport VARCHAR(105)
,@TempsAttente VARCHAR(8)
,@Num_Temps INT
,@Requete NVARCHAR(1500)
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#ListeRapports]'))
BEGIN
DROP TABLE #ListeRapports
END
CREATE TABLE #ListeRapports
(
NomFichier VARCHAR(250)
)
USE ReportServer
SELECT @job_name = job_name , @SubscriptionID = SubscriptionID
FROM (
SELECT
T2.ScheduleID AS job_name
,T3.Description AS Desc_Abonnement
,T3.DeliveryExtension AS Type_Abonnement
,T3.[SubscriptionID]
,T3.ExtensionSettings
,t3.Parameters
,T4.Name AS Nom_Rapport
,T4.Path AS Chemin_Rapport
FROM ReportSchedule T1
INNER JOIN Schedule T2 ON T2.ScheduleID=T1.ScheduleID
INNER JOIN Subscriptions T3 ON T3.SubscriptionID=T1.SubscriptionID
INNER JOIN [Catalog] T4 ON T4.ItemID=T1.ReportID AND T4.ItemID=T3.Report_OID
WHERE T4.Name LIKE '%Mon Rapport%'
) T1
--Mettre à jour les paramètres de l'abonnement
SELECT @Dest_Mail = 'Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.'
,@Parametre_1_Rpport = 'ValeurPremierParametre'
SELECT @ExtensionSettings = '<ParameterValues><ParameterValue><Name>TO</Name><Value>' + CAST(@Dest_Mail AS VARCHAR(69)) + '</Value></ParameterValue><ParameterValue><Name>IncludeReport</Name><Value>True</Value>
</ParameterValue><ParameterValue><Name>RenderFormat</Name><Value>EXCEL</Value></ParameterValue>
<ParameterValue><Name>Subject</Name><Value>@ReportName</Value></ParameterValue>
<ParameterValue><Name>IncludeLink</Name><Value>False</Value></ParameterValue>
<ParameterValue><Name>Priority</Name><Value>NORMAL</Value></ParameterValue></ParameterValues>'
,@Parameters ='<ParameterValues><ParameterValue><Name>Parametre_1_Rpport</Name><Value>' + CAST(@Parametre_1_Rpport AS VARCHAR(24)) + '</Value></ParameterValue></ParameterValues>'
UPDATE [ReportServer].[dbo].[Subscriptions]
SET
[ExtensionSettings] = @ExtensionSettings
,[Parameters] = @Parameters
WHERE SubscriptionID = @SubscriptionID
--Supprimer le fichier existant
SELECT @Requete = 'DEL "' + @Nom_Rapport + '"'
EXEC xp_cmdshell@Requete
--Lancer l'abonnement du rapport SSRS
EXEC msdb.dbo.sp_start_job @job_name = @job_name
SELECT @Requete = N'DIR "' + @Nom_Rapport + '" /B'
INSERT INTO #ListeRapports
EXEC Master..xp_cmdshell @Requete
SELECT @Num_Temps = 1
WHILE EXISTS(SELECT JobID FROM [ReportServer].[dbo].[RunningJobs] WHERE RequestName LIKE '%Mon Rapport%')
AND NOT EXISTS(SELECT NomFichier FROM #ListeRapports WHERE NomFichier LIKE '%Mon Rapport%')
BEGIN
SELECT @TempsAttente = '00:' + REVERSE(SUBSTRING(REVERSE('0' + CAST(@Num_Temps AS VARCHAR(2))),1,2)) + ':00'
WAITFOR DELAY @TempsAttente
INSERT INTO #ListeRapports
EXEC Master..xp_cmdshell @Requete
SELECT @Num_Temps = @Num_Temps + 1
IF @Num_Temps=20
BEGIN
EXEC [msdb].[dbo].sp_stop_job @job_name = @job_name
BREAK
END
END
IF @Num_Temps=1
BEGIN
WAITFOR DELAY '00:00:25'
END