[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