Reporting Services, schedulazioni multiple anche se non sono consentite
Ebbene sì, in Azienda da me vive e prospera una vecchia installazione di SQL Server utilizzata solo per i reporting services.
Si tratta di una versione molto datata, uscita dal supporto, la versione 2005.
Il motore di report non ha buchi e i report RDL sono più che dignitosi; gli oggetti messi a disposizione dall’ambiente, anche se datati, si comportano ancora meglio degli stessi delle versioni correnti (esclusa la 2016 che non è ancora uscita…).
La pecca è… CHE NON HA SCHEDULAZIONI così dette “data driven”.
E cosa sono?
Immaginate un report da distribuire a una serie di persone, ognuna delle quali deve vedere solo una “fetta” di dati (non so… per esempio un gruppo di agenti che devono vedere solo i loro ordini o i loro clienti), ma non volete annoiarvi con la creazione “manuale” di una pubblicazione per ogni persona. In questo contesto farebbe comodo avere una lista di destinatari, uno o più parametri da usare per i report, e un report da inviare.
Io ho risolto così:
stante che quando voi sottoscrivete un report la schedulazione passa sempre dall’SQL Agent e che i dati della schedulazione (destinatari, formati, oggetto della mail, parametri, ecc.) sono indicati in un record del database, basta un pizzico di fantasia e…
Iniziamo dal report: deve avere un parametro che identifichi la sezione dei dati, per esempio PERSONA; diciamo che ora avete il report parametrico.
Di questo fate una schedulazione sul vostro utente: prevedete per esempio un invio via mail in formato EXCEL a un indirizzo “PIPPO”, mettete un titolo, ecc., poi schedulatelo per un solo invio nel passato, così il report server non lo spedirà mai.
A questo punto, salvata la schedulazione, vediamo come capire dov’è.
Vi serve questa istruzione SQL:
FROM Subscriptions INNER JOIN ReportSchedule
ON Subscriptions.SubscriptionID = ReportSchedule.SubscriptionID
ORDER BY Subscriptions.ModifiedDate DESC
Questi sono i due record riferiti alle vostre schedulazioni; c’è tutto ciò che serve per il nostro scopo!
Innanzi tutto ReportSchedule.ScheduleID è il nome del job di SQL Server agent che invia il report, mentre Subscriptions.SubscriptionID è il GUID del record che contiene le informazioni che userà il report server per gestire la schedulazione.
In particolare una select come questa, con in GUID giusto:
FROM Subscriptions
WHERE SubscriptionID = ’04D22E98-5037-415B-9736-FED5F96D73C2′
Vi esporrà i campi che modificheremo in fase di esecuzione che sono ExtensionSettings e Parameters.
Ora ci facciamo una bella stored procedure con l’idea di: attingere a una tabella di parametri e indirizzi, modificare il record della schedulazione quindi far eseguire all’agent il job della schedulazione.
Io ho fatto più o meno così:
AS BEGIN
DECLARE AM CURSOR FOR
SELECT <parametro1> ParamVal, <indirizzoemail1> Email UNION
SELECT <parametro2> ParamVal, <indirizzoemail2> Email;
DECLARE @ParamVal NVARCHAR(20), @Email NVARCHAR(100);
OPEN AM
FETCH NEXT FROM AM INTO @ParamVal, @Email;
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE S SET
ExtensionSettings ='<ParameterValues><ParameterValue><Name>TO</Name><Value>’
+ @Email +'</Value></ParameterValue><ParameterValue><Name>IncludeReport</Name><Value>True</Value></ParameterValue><ParameterValue><Name>RenderFormat</Name><Value>EXCEL</Value></ParameterValue><ParameterValue><Name>Subject</Name><Value>Conte pezzi di area (‘+ @ParamVal +’)</Value></ParameterValue><ParameterValue><Name>IncludeLink</Name><Value>False</Value></ParameterValue><ParameterValue><Name>Priority</Name><Value>NORMAL</Value></ParameterValue></ParameterValues>’,
[Parameters] ='<ParameterValues><ParameterValue><Name>AREA</Name><Value>’+ @ParamVal +'</Value></ParameterValue></ParameterValues>’
FROM Subscriptions S
WHERE SubscriptionID =<GUIDDellaSchedulazione>;
exec msdb..sp_start_job@job_name =<IdDelJob>;
WAITFORDELAY’00:00:15′
FETCH NEXT FROM AM INTO @ParamVal, @Email;
END;
CLOSE AM;
DEALLOCATE AM;
END;
Il cursore che viene ciccato può essere ottenuto in ogni modo via SQL: tabella temporanea, variabile tabella, creato al volo come ho fatto io, openquery… chi più ne ha ne metta.
I campi ExtensionSettings e Parameters sono copiati dat record e incollati nel codice, quindi modificati nelle parti di interesse (Valore del parametro e indirizzo mail di destinazione).
Viene poi eseguito manualmente il job della schedulazione e si attende un po’ prima di eseguirlo una seconda volta (l’agent di sql server non esegue il job se il precedente è già in esecuzione, la cosa spiacevole è che lo skippa senza dare errori).
Volendo essere perfezionisti, si può identificare il job e riprendere il codice che lui stesso esegue (che è una una cosa tipo: exec ReportServer.dbo.AddEvent @EventType=’TimedSubscription’, @EventData=’04d22e98-5037-415b-9736-fed5f96d73c2′ dove il GUID è quello del record Subscriptions, campo SubscriptionID…).