Technics:Abo-System

Prerequisites

The following components from the Feature Pack for SQL Server 2005 (even if a new version of SQL Server is used) must be installed on the CoPlanner Server using the proper architecture (x86 resp. x86):

• Microsoft SQL Server Native Client
• Microsoft SQL Server 2005 Management Objects Collection (XMO)

As far as possible the component language should meet the SQL Server language.

The xp_cmdshell-feature must be enabled for executing subscriptions

The account of the process spawned by xp_cmdshell must be in the Report-Builder (or higher) role on the report server.

The "SQL Server-Agent (MSSQLSERVER) must be started.

You have to provide a ClientConfig.xml-configuration file for the Abo-Executor.

In the default configuration at least the PowerUser's password must be set.

Hint  The abo-system is suited only for reports, which access CoPlanner data with CoPlanner Reporting Services Integration. The access to OLAP-cubes is not supported via Matrixbuilder, because the abo-system runs with a service account usually and the OLAP access is only available with SSPI.

Hint  For using the abo-system you have to pay attention to the login information of the data source. The full functionality is available only, if the mode request login information is selected. In case of windows authentication the subscriptions run in the context of xp_cmdshell.

AboExecutor.exe.config

In the file "AboExecutor.exe.config" in the folder "\server\AboExecutor" you configure the e-mail. The following points have to be adopted:

• maxAttachmentSizeInKB: The maximum attachment size in kB. If 0 or not available, then unlimited.
• mailSettings: e-mail settings

AboExecutor.exe.config

In the file "AboExecutor.exe.config" in the directory "\server\AboExecutor" you find the configuration for the Email dispatch.

<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<configuration>
   <appSettings>
   <add key="abo" value="633964077278817699"/>
   <add key="user" value="PowerUser"/> 
   <add key="pass" value="UserPasswort"/> 
   <add key="maxAttachmentSizeInKB"value="1000"/>
   </appSettings>

   <system.net>
   <mailSettings>
      <smtp deliveryMethod="Network" from="aboexecutor@coplanner.com">
         <network
            host="psad001.graz.coplanner.com"
            port="25"
            defaultCredentials="true"
            />
      </smtp>
   </mailSettings>
 </system.net>

</configuration>

The abo-attribute can be ignored, because it will be set by the job for the subscription.

Valid entries for the user-attribute are PowerUser, Partner or Hersteller.

At the pass-attribute you have to enter the users password.

MaxAttachmentSize IKB: The maximum attachment size for e-mail delivery in kB when "0" or not existing, then unlimited.

The Delivery Method-attribute specifies the delivery method for emails. Valid values are "network", "pickupDirectoryFromIis" and "specifiedPickupDirectory".

The from-attribute specifies the sender address for emails.

The specifiedPickupDirectory-attribute specifies the directory in which the applications e-mail messages will be stored for later processing with the SMTP server. In the example, "c:\temp\pickup" as the e-mail pickup directory.

The network-attribute configures the network options for an external SMTP server.

Architecture (Customizing)

For each subscription defined in the CoPlanner client the CoPlanner Server will create a SQL Server Agent job. Each job consists of a single T-SQL step which is executing the stored procedure dbo.ExecReportAbo passing the subscription’s COPSYS_ID as parameter.

The job will be executed according to the schedule, but can be also started manually in the CoPlanner client

dbo.ExecReportAbo

This stored procedure will be called by the SQL Server Agent job. This – and only this – procedure may be ajdusted as needed. dbo.ExecReportAbo’s tasks are:

• Determine the Abo-Executor’s path

In the default configuration the Abo-Excutor AboExecutor.exe is located in the AboExecutor directory in the CoPlanner Server directory.

• Determine the path to the configuration file ClientConfig.xml

In the default configuration the configuration file is located in the Abo-Executor-directory.

• Set the username and password the Abo-Excutor will use for connecting tot he CoPlanenr server

In the default configuration the PowerUser with an empty password will be used. The Abo-Executor is working with the accounts PowerUser, partner and manufacturer only.

• Set additional Abo-Executor parameters

In the default configuration no additional parameters are used.

The stored procedure dbo.ExecReportAboInternal will be called using these values and the subscription’s id as parameter.

dbo.ExecReportAboInternal

This stored procedure must not be customized. It will be overwritten by future CopVersion scripts. This stored procedure builds the Abo-Executor’s command line, executes it using xp_cmdshell and writes to the log tables.

Logging

Each subscription execution (started automatically or manually) will be logged in the following tables:

• dbo.tb_ExecReportAboLog
• dbo.tb_ExecReportAboLogMsg

These tables contain the result of the latest execution for each subscription.

Column	Description
AboId	Subscription ID
AboName	Subscription name
ExecTime	Last subscription execution

Column	Description
AboId	Subscription ID
LineNr	Line number
MsgTyp	Line type (see below)
MsgLine	Line text

The MsgTyp column is used for categorizing the table’s records:

• Empty Type

Lines without type are for information only. At present also exceptions are inserted without type into the log table because it is difficult to detect these lines automatically.

• WRN

Warning messages which are generated by the report server on rendering the reports.

• ERR

Error messages which are generated by the report server on rendering the reports.

• VAR

These are special lines which can be used for retrieving information in queries or reports. For this type of lines the MsgLine-column always looks like

variable:=value

At present the only defined variable is AboName which returns the subscription’s name.