« SSIS für nicht sysadmins
Reporting mit Oracle-Daten »

Datengesteuerte Abonnements mit der Standard-Edition

Leider wird dieses Feature vollständig nur in der Enterprise-Edition unterstützt, aber es gibt auch Wege, dies selbstständig zu realisieren.

Inspiriert durch den Artikel Data Driven Subscriptions for Reporting Services fasse ich im folgenden die notwendigen Punkte zusammen, die zur Einrichtung eines "datengesteuerten Abonnements" notwendig sind. Eine schöne Ergänzung zu dem ersten Artikel mit einem Ausflug in die Welt des XML findet sich in diesem Blog.

Acknowledgement/Danksagung

 Many thanks to Jason Selburg for giving the permission to use his code in this translation.

Was nicht beachtet wurde

In der hier verwendeten Version der Prozedur, basierend auf dem ersten Artikel wurde auf die Übergabe mehrerer Parameter verzichtet. Wer dies benötigt, sollte sich den zweiten Artikel anschauen.

Vorrausetzungen

Die Vorraussetzungen zum Verständnis des weiteren Vorgehens sind simpel:

  • Man kann ein Standard-Abonnements einrichten
  • Man beherrscht die Verwendung von Stored Procedures

Anleitung

Standard-Abonnement einrichten

Zuerst definiert man ein Abonnement mit einer eindeutigen Betreff-Zeile. Dieser Betreff wird später benötigt, um das Abonnement wiederzufinden. Der zweite wichtige Schritt ist, dass man einen Zeitplan anlegt, der aber der Einfachheit halber bereits in der Vergangenheit endet. Damit kommen sich der später noch einzurichtende Auftrag für das datengesteuerte Abonnement und der für das Abo eingerichtete Auftrag nicht in die Quere.
Jedes Abonnement findet sich als Auftrag im SQLServer-Agent wieder, auch wenn die Namen nicht unbedingt auf den auszuführenden Report schliessen lassen. Wer sich einmal den Zusammenhang von Aufträgen und Abonnements anschauen will, kann dies z. B. so tun: 

Use ReportServer
go
select z.ScheduleID as Auftrag, u.UserName as CreatedBy,
coalesce(s1.EventType, s2.EventType, 'AusführungsSnapshot') EventType, coalesce(c1.Path, c2.Path) Path,
coalesce(c1.Description, c2.Description) Description,
coalesce(S1.Description, S2.Description) Aktion, s1.laststatus
from dbo.Schedule Z
left join dbo.Subscriptions s1  on s1.SubscriptionID = z.EventData
left join dbo.Catalog c1        on c1.ItemID         = s1.Report_OID
left join dbo.Subscriptions s2  on s2.Report_OID     = z.EventData         -- Snapshot
left join dbo.Catalog c2        on c2.ItemID         = z.EventData         -- Snapshot
left join dbo.users u           on u.UserId          = z.CreatedByID
order by path, aktion

Tabelle und Prozedur anlegen

Man legt als erstes die Hilftstabelle und die Prozedur an. Der Prozedur wird später ein Parameter übergeben, der zum Auffinden eines Standard-Abos dient. Dieser Parameter ist die Betreff-Zeile des oben definierten Standard-Abonnements.

Die Eingabeparameter für die zweite Prozedur werden im folgenden beschrieben. Hierbei müssen die drei möglichen EMail-Adressaten nicht unbedingt angegeben werden. Gleichwohl ist wenigstens die Angabe einer drei Parameter notwendig.

  • @emailTO = Der Adressat der EMail
  • @emailCC = Der Kopie-Empfänger der EMail
  • @emailBCC = Der Empfänger der Blindkopie
  • @emailReplyTO = Die Absenderadresse
  • @emailBODY = Der Text in der EMail
  • @parameterName = Ein Parameter-Name. Dieser muss dem Namen eines Parameters im Report entsprechen.
  • @parameterValue = Der Wert für den Parameter
  • @sub = Die Betreff-Zeile der EMail
  • @renderFormat = Das rendering Format des Reports.
    Gültige Werte: Diese können von der Installation und Konfiguration des Servers abhängen, sind aber in der "reportServer.config" Datei aufgeführt.
    Diese Datei befindet sich in einem Ordner, ähnlich wie
    "C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\"
    - XML
    - IMAGE
    - PDF
    - EXCEL
    - CSV

Wie kommt man jetzt an den genauen Namen des Parameters, wenn man den Report nicht selber geschrieben hat? Das Web-Front End zeigt einem leider nur den Text an, der für den Parameter hinterlegt wurde. Man kann aber mit TSQL auf die Definition des Abos zugreifen und sich die notwendigen Werte extrahieren. In der Spalte Param1 findet sich der Wert, der für den ersten Parameter angegeben wurde und in Param_xml findet sich die vollständige Darstellung der Parameter-Definition. 

with s_cte as
(SELECT cast(extensionsettings as xml) ext_xml, cast(Parameters as xml) as Param_xml
from reportserver..subscriptions
)
select ext_xml.value('(/ParameterValues[1]/ParameterValue[Name="Subject"][1]/Value[1]/text()[1])', 'varchar(200)' ) Subject,
ext_xml.value('(/ParameterValues[1]/ParameterValue[Name="TO"][1]/Value[1]/text()[1])', 'varchar(200)' ) [TO],
ext_xml.value('(/ParameterValues[1]/ParameterValue[Name="CC"][1]/Value[1]/text()[1])', 'varchar(200)' ) CC,
ext_xml.value('(/ParameterValues[1]/ParameterValue[Name="BCC"][1]/Value[1]/text()[1])', 'varchar(200)' ) BCC,
Param_xml.value('(/ParameterValues[1]/ParameterValue[1]/Value[1]/text()[1])', 'varchar(200)' ) Param1,
Param_xml
from s_cte
where ext_xml.value('(/ParameterValues[1]/ParameterValue[Name="Subject"][1]/Value[1]/text()[1])', 'varchar(200)' ) is not null

Falls man Parameter mit einem Default-Wert versehen hat und diesen beibehält, so finden sich diese auch nicht in der Auflistung der Parameter für das Abonnement. Wenn also die anderen Parameter des Abos z. B. das Tagesdatum als Default verwenden, können auch solche Reports mit dieser Methode publiziert werden. Demjenigen, der Reports mit mehreren Parametern publizieren will, sei nochmals der zweite Artikel empfohlen.

Die weiteren Schritte

Wir haben jetzt also ein

  • Standard-Abonnement mit einem eindeutigen Betreff
  • Eine Hilfstabelle und eine Prozedur

Nun können wir also über die Prozedur ein Abonnement einmalig mit diversen Übergabeparametern aufrufen. In der ebenfalls angelegten Tabelle kann man sich den Verlauf dieses Aufrufs ansehen. Damit das ganze aber automatisiert über viele EMail-Adressen und Abonnements läuft, sind noch eine weitere Tabelle und eine verarbeitende Prozedur notwendig.

Tabelle der Abonnenten

CREATE TABLE dbo.Subscribers(
                 SubscriptionID int IDENTITY(1,1) NOT NULL,
                 ScheduleName varchar(260) NOT NULL,
                 EMail varchar(600) NOT NULL,
                 EMail_CC varchar(600) NULL,
                 parameterName nvarchar(4000) NULL,
                 parameterValue nvarchar (256) NULL,
                 renderFormat nvarchar(50) NOT NULL,
                    CONSTRAINT XPKSubscriber PRIMARY KEY CLUSTERED
                          (SubscriptionID
                          )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
                ) ON [PRIMARY]
go

Hier werden später die Angaben zu allen datengesteuerten Abonnements und deren Abonnenten abgelegt. Auch hier ist wieder der ScheduleName der Wert, der im Abo als Betreff definiert wurde.

Bearbeitung aller Abonnenten

Die nun folgende Prozedur verarbeitet alle Einträge in der eben angelegten Tabelle zu einem bestimmten Betreff (ScheduleName) und ruft hierfür die Routine auf, welche das eigentliche Abonnement anstösst. Die Antwortadresse, der Email-Text und die neue Betreffzeile habe ich bewusst aus der oben aufgeführten Tabelle ausgeklammert, damit sichergestellt ist, dass alle Adressaten bei einem Aufruf auch wirklich die gleichen Angaben erhalten. Andernfalls müsste man diese Daten redundant pflegen und über irgendeinen Mechanismus konsistent halten. 

create procedure usp_Subscription_Call
(
@MyScheduleName varchar(260) ,
@MyEmailReplyTO nvarchar (2000) = NULL ,
@MyEmailBODY nvarchar (4000) ,
@MySub nvarchar (1000)
)
as
/* ***************************************************************** */
/* *                                            Autor:    Muthmann * */
/* * Beschreibung:                                                 * */
/* * Verarbeitung aller Abonnements zu einem Report wobei die      * */
/* * Parameter aus der Datenbank kommen                            * */
/* * Der Report wird an dem Text erkannt, der bei der Erstellung   * */
/* * des initialen Reports als Betreff angegeben wurde.            * */
/* * Der initiale Report sollte einen abgelaufenen Zeitplan haben  * */
/* * um sicherzustellen, dass er nicht selbstständig läuft.        * */
/* *                                                               * */
/* * Änderungen                                           am       * */
/* * ---------------------------------------------------- -------  * */
/* * Erstellung                                           15.11.07 * */
/* *                                                               * */
/* ***************************************************************** */
Declare         @Anzahl                int,
                @Fehler                int,
                @Fehler_Gesamt         int,
                @Fehlertext            varchar(1000),
                @myExitCode            int ,
                @myExitMessage         nvarchar(255),
                @EMail                 varchar(600) ,
                @EMail_CC              varchar(600) ,
                @ExecuteStatus         nvarchar(260),
                @parameterName         nvarchar(4000) ,
                @parameterValue        nvarchar (256) ,
                @renderFormat          nvarchar(50),
                @Subject               varchar(255)

SET NOCOUNT ON
/* Vorbelegen der variablen zur Fehlerverarbeitung und Rückgabe */
Set @Fehlertext        = 'Die Reports wurden fehlerfrei aufgerufen!'
Set @Fehler            = 0
Set @Fehler_Gesamt     = 0
Set @Anzahl            = 0


/* Schleife über die Abonnements zu dem Report */
Declare Subscriber_Cursor CURSOR for
Select  EMail, EMail_CC, parameterName, parameterValue, renderFormat
from    dbo.Subscribers
where   ScheduleName = @MyScheduleName
order by SubscriptionID


OPEN Subscriber_Cursor
Set @Fehler = @@error
If @Fehler <> 0
   begin
        set @Fehlertext = 'Fehler bei Open Cursor!'
        set @Fehler_Gesamt = 1
   end
Else
   begin
        FETCH NEXT FROM Subscriber_Cursor INTO @EMail, @EMail_CC, @parameterName, @parameterValue, @renderFormat
        Set @Fehler = @@error
        If @Fehler <> 0
           begin
                set @Fehlertext = 'Fehler bei Fetch Cursor!'
                set @Fehler_Gesamt = 1
           end
   end

WHILE (@@fetch_status <> -1) and @Fehler_Gesamt = 0
   BEGIN
        IF (@@fetch_status <> -2)
           BEGIN
                Set @myExitCode = 0
                Set @myExitMessage = 'Ok'

                Set @Subject = @MySub

                exec @Fehler = [ReportServer].[dbo].[usp_data_driven_subscription]
                         @scheduleName = @MyScheduleName ,
                         @emailTO = @EMail,
                         @emailCC = @EMail_CC,
                         @emailBCC = NULL,
                         @emailReplyTO = @MyEmailReplyTO,
                         @emailBODY = @MyEmailBODY,
                         @parameterName = @parameterName,
                         @parameterValue = @parameterValue,
                         @sub = @Subject ,
                         @renderFormat = @renderFormat,
                         @exitCode = @myExitCode output,
                         @exitMessage  = @myExitMessage  output

                 If @Fehler <> 0
                   begin
                        set @Fehlertext = 'Fehler bei Aufruf! Code' + cast(@MyExitCode as varchar(2)) + ' Message: ' + @MyExitMessage
                        set @Fehler_Gesamt = 1
                   end

                 If @MyExitCode <> 1
                   begin
                        print 'Fehler bei Aufruf! Code' + cast(@MyExitCode as varchar(2)) + ' Message: ' + @MyExitMessage
                   end



           END -- (@@fetch_status <> -2)
        FETCH NEXT FROM Subscriber_Cursor INTO @EMail, @EMail_CC, @parameterName, @parameterValue, @renderFormat
        Set @Fehler = @@error
        If @Fehler <> 0
           begin
                set @Fehlertext = 'Fehler bei Fetch Cursor!'
                set @Fehler_Gesamt = 1
           end
   END -- While


DEALLOCATE Subscriber_Cursor
Set @Fehler = @@error
If @Fehler <> 0
   begin
        set @Fehlertext = 'Fehler bei Deallocate Cursor!'
        set @Fehler_Gesamt = 1
   end


set @Fehlertext = 'usp_Subscription_Call: ' + @Fehlertext

/* Abschlußroutine */
If @Fehler_Gesamt = 0
   begin
        Print @Fehlertext
        Return(0)
   end
Else
   begin
        RaisError(@Fehlertext, 14, 1)
        Return(1)
   end
GO

Das Kernstück

Das eigentliche Kernstück der Verarbeitung ist natürlich die Prozedur von Jason Selburg. Ich habe aber festgestellt, dass es bei Reports, die länger als 2 Sekunden zur Erstellung brauchen Probleme bei der Verarbeitung gibt. Deshalb habe ich diese Prozedur an dieser Stelle leicht modifiziert.

/* Hier ist die Erklärung, warum die ursprüngliche Lösung mit starttime nicht funktioniert, wenn die Reports länger als 2 Sekunden laufen
run  lastruntime   starttime   getdate   duration   lastruntime_afterwards   comment                                   action
1    10:00:00      11:59:58    12:00:00   30 sec    12:00:30                 wait while starttime > lastruntime        mail sent
2    12:00:30      12:00:29    12:00:31    1 sec    12:00:30                 starttime < lastruntime, no wait occurs   mail sent
3    12:00:30      12:00:30    12:00:32    1 sec    12:00:30                 job was already running                   no mail sent
*/
Use ReportServer
go
/*
 DATE CREATED:   12/21/2006
 AUTHOR:         Jason L. Selburg, modified by Christoph Muthmann

 PURPOSE:
    This procedure extends the functionality of the subscription feature in
  Microsoft SQL Reporting Services 2005, allowing the subscriptions to be triggered
  via code.
    The code supplied will function with reports that have one parameter. Reports
  that have multiple parameters must be addressed individually or with another method.
  There are many possible ways to handle multi-parameter reports, which is why it is not addressed here.
  However, one suggestion:
    - Create a subscription table that will hold subscription names and IDs.
    - Create a table to hold subscription IDs mapped to the previous table and hold the parameter names and
      values.
    - These tables would be queried and looped through to populate the parameter XML string below.

 NOTES:
   This procedure does not address "File Server Share" subscriptions.

 PARAMETERS:
   @scheduleName   = This is the NAME that is put into the subject line of the subscription when created.
                     It is STRONGLY suggested that you use a naming convention that will prevent
                     duplicate names.
   @emailTO        = The TO of the email  (not required.)  \
   @emailCC        = The CC of the email  (not required.)  |---One of these are REQUIRED!
   @emailBCC       = The BCC of the email (not required.)  /
   @emailReplyTO   = The reply to address that will appear in the email.
   @emailBODY      = The text in the body of the email.
   @parameterName  = The paramerter name. This MUST match the parameter name in the report definition.
   @parameterValue = The parameter value.
   @sub            = The subject line of the email.

   @renderFormat   = The rendering format of the report.
      VALID VALUES : May be different depending on the installation and configuration
                     of your server,  but these are listed in the "reportServer.config" file.
                     This file is located in a folder similar to
                     "C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\"
                     XML
                     IMAGE
                     PDF
                     EXCEL
                     CSV

   @exitCode       = The returned integer value of the procedure's execution result.
                   -1  'A recipient is required.'
                   -2  'The subscription does not exist.'
                   -3  'No delivery settings were supplied.'
                   -4  'A data base error occurred inserting the subscription history record.'
                   -5  'A data base error occurred clearing the previous subscription settings.'
                   -6  'A data base error occurred retrieving the TEXT Pointer of the Delivery Values.'
                   -7  'A data base error occurred updating the Delivery settings.'
                   -8  'A data base error occurred retrieving the TEXT Pointer of the Parameter Values.'
                   -9  'A data base error occurred updating the Parameter settings.'
                   -10 'A data base error occurred updating the subscription history record.'
                   -11 'A data base error occurred resetting the previous subscription settings.'

  @exitMessage    = The text description of the failure or success of the procedure.

 PRECONDITIONS:
    The subscription being called must exist and the SUBJECT line of the subscription MUST contain
  the exact name that is passed into this procedure.
    If any of the recipients email address are outside of the report server's domain, then you may
  need to contact your Network Administrator to allow email forwarding from your email server.

 POST CONDITIONS:
    The report is delivered or an error code and message is returned.

 SECURITY REQUIREMENTS:
    The user which calls this stored procedure must have execute permissions.

 DEPENDANCES:
   Tables:
       ReportSchedule       = Installed with SQL RS 2005
       Subscription_History = Must be created using the following script.
                            ---------------------------------------------------------------------
                            CREATE TABLE [dbo].[Subscription_History](
                            [nDex] [int] IDENTITY(1,1) NOT NULL,
                            [SubscriptionID] [uniqueidentifier] NULL,
                            [ScheduleName] [nvarchar](260) COLLATE Latin1_General_CI_AS_KS_WS NULL,
                            [parameterSettings] [varchar](8000) COLLATE Latin1_General_CI_AS_KS_WS NULL,
                            [deliverySettings] [varchar](8000) COLLATE Latin1_General_CI_AS_KS_WS NULL,
                            [dateExecuted] [datetime] NULL,
                            [executeStatus] [nvarchar] (260) NULL,
                            [dateCompleted] [datetime] NULL,
                            [executionTime] AS (datediff(second,[datecompleted],[dateexecuted])),
                            CONSTRAINT [PK_Subscription_History] PRIMARY KEY CLUSTERED
                              (
                                   [nDex] ASC
                              )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
                             ) ON [PRIMARY]
                            ---------------------------------------------------------------------

       Subscriptions           = Installed with SQL RS 2005
       Schedule                = Installed with SQL RS 2005

*/
ALTER     procedure [dbo].[usp_data_driven_subscription]
        ( @scheduleName nvarchar(255),
        @emailTO nvarchar (2000) = NULL,
        @emailCC nvarchar (2000) = NULL,
        @emailBCC nvarchar (2000) = NULL,
        @emailReplyTO nvarchar (2000) = NULL,
        @emailBODY nvarchar (4000) = NULL,
        @parameterName nvarchar(4000) = NULL,
        @parameterValue nvarchar (256) = NULL,
        @sub nvarchar(1000) = NULL,
        @renderFormat nvarchar(50) = 'PDF',
        @exitCode int output,
        @exitMessage nvarchar(255) output
        )
AS
Set Nocount On
DECLARE
    @ptrval binary(16),
    @PARAMptrval binary(16),
    @subscriptionID uniqueidentifier,
    @scheduleID uniqueidentifier,
    @starttime datetime,
    @lastruntime datetime,
    @execTime datetime,
    @dVALUES  nvarchar (4000),
    @pVALUES  nvarchar (4000),
    @previousDVALUES  nvarchar (4000),
    @previousPVALUES  nvarchar (4000),
    @lerror int,
    @insertID int,
    @lretval int,
    @rowcount int

-- SET @starttime = DATEADD(second, -2, getdate())
-- Stattdessen wird jetzt die letzte RunTime gemerkt (s.u.)
SET @emailTO = rtrim(IsNull(@emailTO, ''))
SET @emailCC = rtrim(IsNull(@emailCC, ''))
SET @emailBCC = rtrim(IsNull(@emailBCC, ''))
SET @emailReplyTO = rtrim(IsNull(@emailReplyTO, ''))
SET @emailBODY = rtrim(IsNull(@emailBODY, ''))
SET @parameterValue = rtrim(IsNull(@parameterValue, ''))
SET @lerror = 0
SET @rowcount = 0
SET @exitCode = 0

IF @emailTO = '' AND @emailCC = ''
   AND @emailBCC = ''
 BEGIN
   SET @exitCode = -1
   SET @exitMessage = 'A recipient is required.'
   RETURN 0
 END


-- get the subscription ID
SELECT
  @subscriptionID = rs.subscriptionID,
  @scheduleID = rs.ScheduleID
 FROM
  ReportSchedule rs
 INNER JOIN subscriptions s
  ON rs.subscriptionID = s.subscriptionID
 WHERE
  extensionSettings like '%' + @scheduleName + '%'

IF @subscriptionID Is Null
 BEGIN
   SET @exitCode = -2
   SET @exitMessage = 'The subscription does not exist.'
   RETURN 0
 END

-- Bestimme den Zeitpunkt des letzten Laufs des Reports
-- Der aktuelle Report ist erst fertig, wenn die Zeit geändert wurde
SELECT @starttime = coalesce(LastRunTime, getdate()) FROM ReportServer..Schedule WHERE ScheduleID = @scheduleID


/* just to be safe */
SET @dVALUES  = ''
SET @pVALUES  = ''
SET @previousDVALUES  = ''
SET @previousPVALUES  = ''

/* apply the settings that are defined */
IF IsNull(@emailTO, '') <> ''
  SET @dVALUES  = @dVALUES  + '<parametervalue><name>TO</name><value>'
                  + @emailTO + '</value></parametervalue>'

IF IsNull(@emailCC, '') <> ''
  SET @dVALUES  = @dVALUES  + '<parametervalue><name>CC</name><value>'
                  + @emailCC + '</value></parametervalue>'

IF IsNull(@emailBCC, '') <> ''
  SET @dVALUES  = @dVALUES  + '<parametervalue><name>BCC</name><value>'
                  + @emailBCC + '</value></parametervalue>'

IF IsNull(@emailReplyTO, '') <> ''
  SET @dVALUES  = @dVALUES  + '<parametervalue><name>ReplyTo</name><value>'
                  + @emailReplyTO + '</value></parametervalue>'

IF IsNull(@emailBODY, '') <> ''
  SET @dVALUES  = @dVALUES  + '<parametervalue><name>Comment</name><value>'
                  + @emailBODY + '</value></parametervalue>'

IF IsNull(@sub, '') <> ''
  SET @dVALUES  = @dVALUES  + '<parametervalue><name>Subject</name><value>'
                  + @sub + '</value></parametervalue>'

IF IsNull(@dVALUES , '') <> ''
  SET @dVALUES  = '<parametervalues>' + @dVALUES
                  + '<parametervalue><name>IncludeReport</name><value>True</value></parametervalue>'

IF IsNull(@dVALUES , '') <> ''
  SET @dVALUES  = @dVALUES  +        '<parametervalue><name>RenderFormat</name><value>' +
      @renderFormat + '</value></parametervalue>' +
          '<parametervalue><name>IncludeLink</name><value>False</value></parametervalue></parametervalues>'

IF IsNull(@parameterName, '') <> '' and IsNull(@parameterValue, '') <> ''
  SET @pVALUES  = '<parametervalues><parametervalue><name>' +
      @parameterName +
      '</name><value>' +
      @parameterValue +
          '</value></parametervalue></parametervalues>'

/* verify that some delivery settings where passed in */
-- @pVALUES are not checked as they may all be defaults
IF IsNull(@dVALUES , '') = ''
 BEGIN
   SET @exitCode = -3
   SET @exitMessage = 'No delivery settings were supplied.'
   RETURN 0
 END

/* get the current parameter values and delivery settings */
SELECT @previousDVALUES  = extensionSettings
  FROM Subscriptions
  WHERE SubscriptionID = @SubscriptionID
SELECT @previousPVALUES  = parameters
  FROM Subscriptions
  WHERE SubscriptionID = @SubscriptionID

UPDATE Subscriptions
  SET extensionSettings = '', parameters = ''
  WHERE SubscriptionID = @SubscriptionID

SELECT @lerror=@@error, @rowCount=@@rowCount

IF @lerror <> 0 OR IsNull(@rowCount, 0) = 0
 BEGIN
   SET @exitcode = -5
   SET @exitMessage = 'A data base error occurred clearing the previous subscription settings.'
   RETURN IsNull(@lerror, 0)
 END

-- set the text point for this record
SELECT @ptrval = TEXTPTR(ExtensionSettings)
  FROM Subscriptions
  WHERE SubscriptionID = @SubscriptionID

SELECT @lerror=@@error

IF @lerror <> 0 OR @ptrval Is NULL
 BEGIN
   SET @exitcode = -6
   SET @exitMessage = 'A data base error occurred retrieving the TEXT Pointer of the Delivery Values.'
   RETURN IsNull(@lerror, 0)
 END

UPDATETEXT Subscriptions.ExtensionSettings
        @ptrval
        null
        null
        @dVALUES

SELECT @lerror=@@error

IF @lerror <> 0
 BEGIN
   SET @exitcode = -7
   SET @exitMessage = 'A data base error occurred updating the Delivery settings.'
   RETURN IsNull(@lerror, 0)
 END

-- set the text point for this record
SELECT @PARAMptrval = TEXTPTR(Parameters)
  FROM Subscriptions
  WHERE SubscriptionID = @SubscriptionID

SELECT @lerror=@@error

IF @lerror <> 0 OR @ptrval Is NULL
 BEGIN
   SET @exitcode = -8
   SET @exitMessage = 'A data base error occurred retrieving the TEXT Pointer of the Parameter Values.'
   RETURN IsNull(@lerror, 0)
 END

UPDATETEXT Subscriptions.Parameters
   @PARAMptrval
   null
   null
   @pVALUES

SELECT @lerror=@@error

IF @lerror <> 0
 BEGIN
   SET @exitcode = -9
   SET @exitMessage = 'A data base error occurred updating the Parameter settings.'
   RETURN IsNull(@lerror, 0)
 END

/* insert a record into the history table */
SET @execTime = getdate()
INSERT Subscription_History
   (subscriptionID, scheduleName, ParameterSettings, DeliverySettings,  dateExecuted, executeStatus)
 VALUES
   (@subscriptionID, @scheduleName, @parameterValue, @dVALUES , @execTime, 'incomplete' )

SELECT @lerror=@@error, @insertID=@@identity

IF @lerror <> 0 OR IsNull(@insertID, 0) = 0
 BEGIN
   SET @exitcode = -4
   SET @exitMessage = 'A data base error occurred inserting the subscription history record.'
   RETURN IsNull(@lerror, 0)
 END

-- run the job
EXEC msdb..sp_start_job @job_name = @scheduleID

-- this gives the report server time to execute the job
-- Wait while the LastRunTime is not refreshed
SELECT @lastruntime = coalesce(LastRunTime, @starttime) FROM ReportServer..Schedule WHERE ScheduleID = @scheduleID
WHILE (@starttime = @lastruntime)
 BEGIN
   WAITFOR DELAY '00:00:01'
   SELECT @lastruntime = coalesce(LastRunTime, @starttime) FROM ReportServer..Schedule WHERE ScheduleID = @scheduleID
 END

/* update the history table with the completion time */
UPDATE Subscription_History
 SET dateCompleted = getdate(), executeStatus = 'complete'
 WHERE subscriptionID = @subscriptionID
 and scheduleName = @scheduleName
 and ParameterSettings = @parameterValue
 and dateExecuted = @execTime

SELECT @lerror=@@error, @rowCount=@@rowCount

IF @lerror <> 0 OR IsNull(@rowCount, 0) = 0
 BEGIN
   SET @exitcode = -10
   SET @exitMessage = 'A data base error occurred updating the subscription history record.'
   RETURN IsNull(@lerror, 0)
 END

/* reset the previous delivery and parameter values  */
UPDATE Subscriptions
  SET extensionSettings = @previousDVALUES
    , parameters = @previousPVALUES
  WHERE SubscriptionID = @SubscriptionID

SELECT @lerror=@@error, @rowCount=@@rowCount

IF @lerror <> 0 OR IsNull(@rowCount, 0) = 0
 BEGIN
   SET @exitcode = -11
   SET @exitMessage = 'A data base error occurred resetting the previous subscription settings.'
   RETURN IsNull(@lerror, 0)
 END

/* return the result of the subscription */
SELECT @exitMessage = LastStatus
  FROM subscriptions
  WHERE subscriptionID = @subscriptionID

If @exitCode = 0
   begin
        SET @exitCode = 1
        RETURN(0)
   end
Else
        RETURN(1)
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

Auswertung

Eine abschliessende Auswertung der History-Tabelle (vielleicht als Report?) erfolgt am besten per SQL mit XML: 

with s_cte as
(SELECT ScheduleName, ParameterSettings, cast(deliverysettings as xml) del_xml, dateExecuted, executeStatus,dateCompleted, -executionTime execTime
from Reportserver..Subscription_History
)
select ScheduleName, ParameterSettings, dateExecuted, executeStatus, dateCompleted, execTime,
del_xml.value('(/ParameterValues[1]/ParameterValue[Name="TO"][1]/Value[1]/text()[1])', 'varchar(200)' ) [TO],
del_xml.value('(/ParameterValues[1]/ParameterValue[Name="ReplyTo"][1]/Value[1]/text()[1])', 'varchar(200)' ) [ReplyTo],
del_xml.value('(/ParameterValues[1]/ParameterValue[Name="Comment"][1]/Value[1]/text()[1])', 'varchar(200)' ) Comment,
del_xml.value('(/ParameterValues[1]/ParameterValue[Name="Subject"][1]/Value[1]/text()[1])', 'varchar(200)' ) [Subject],
del_xml.value('(/ParameterValues[1]/ParameterValue[Name="IncludeReport"][1]/Value[1]/text()[1])', 'varchar(200)' ) [IncludeReport],
del_xml.value('(/ParameterValues[1]/ParameterValue[Name="IncludeLink"][1]/Value[1]/text()[1])', 'varchar(200)' ) [IncludeLink],
del_xml.value('(/ParameterValues[1]/ParameterValue[Name="RenderFormat"][1]/Value[1]/text()[1])', 'varchar(200)' ) [RenderFormat]
from s_cte
order by ScheduleName, dateExecuted

  SSRS_DataDrivenSubscription SQL
  SSRS_DataDrivenSubscription_Kern SQL

enterprise feature, reporting, sql server 2005
SQL Server
 Print article This entry was posted by cmu on 14.02.08 at 07:45:00 . Follow any responses to this post through RSS 2.0.