Technics:Abo-System: Difference between revisions

From CoPlanner 10
Jump to navigationJump to search
← Older editNewer edit →
VisualWikitext
No edit summary
No edit summary
Line 5: Line 5:
:*Microsoft SQL Server 2005 Management Objects Collection  (XMO)
:*Microsoft SQL Server 2005 Management Objects Collection  (XMO)


As far as possible the component language should meet the SQL Server language. For executing abonnements the '''xp_cmdshell'''-feature must be enabled.
As far as possible the component language should meet the SQL Server language. For executing subscriptions the '''xp_cmdshell'''-feature must be enabled.
The account of the process spawned by '''xp_cmdshell''' must be in the Report-Builder (or higher) role on the report server.
The account of the process spawned by '''xp_cmdshell''' must be in the Report-Builder (or higher) role on the report server.
The stored procedure '''dbo.ExecReportAbo''' must be customized. In the default configuration at least the PowerUser‘s password must be set.
The stored procedure '''dbo.ExecReportAbo''' must be customized. In the default configuration at least the PowerUser‘s password must be set.
Line 12: Line 12:
{| {{Bausteindesign1}}
{| {{Bausteindesign1}}
|-
|-
| <span style="color: darkblue;">'''Hint'''</span> &nbsp;Das Abo-System ist nur für Berichte geeignet, die mit der CoPlanner Reporting Services Integration auf reine CoPlanner-Daten zugreifen. Der Zugriff über den Matrixbuilder auf Olap-Würfel wird aus technischen Gründen nicht unterstützt, da das Abo-System üblicherweise mit einem Serviceaccount ausgeführt wird und der Olap-Zugriff nur mit SSPI möglich ist.
| <span style="color: darkblue;">'''Hint'''</span> &nbsp;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.
|}
|}


Line 18: Line 18:
{| {{Bausteindesign1}}
{| {{Bausteindesign1}}
|-
|-
| <span style="color: darkblue;">'''Hinweis'''</span> &nbsp;Bei der Verwendung des Abo-Systems ist auf die Anmeldeinformationen für die Datenquelle zu achten. Die volle Funktionalität (Berechtigungen für die Benutzer) ist nur dann gegeben, wenn für die Anmeldeinformationen der Modus Zur Eingabe der ''Anmeldeinformationen auffordern'' eingestellt ist. Bei ''Windows Authentifizierung'' wird das Abonnement immer im Kontext von ''xp_cmdshell'' ausgeführt.
| <span style="color: darkblue;">'''Hint'''</span> &nbsp;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''.
|}
|}


Line 30: Line 30:
==Architecture (Customizing)==
==Architecture (Customizing)==


For each abonnement 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 abonnement’s ''COPSYS_ID'' as parameter.
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
The job will be executed according to the schedule, but can be also started manually in the CoPlanner client
Line 52: Line 52:




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


===dbo.ExecReportAboInternal===
===dbo.ExecReportAboInternal===
Line 58: Line 58:


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


:*''dbo.tb_ExecReportAboLog''
:*''dbo.tb_ExecReportAboLog''
:*''dbo.tb_ExecReportAboLogMsg''
:*''dbo.tb_ExecReportAboLogMsg''


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


{| style=cellspacing="1" cellpadding="5" border="1" width={{{breite|100%}}}
{| style=cellspacing="1" cellpadding="5" border="1" width={{{breite|100%}}}
Line 71: Line 71:
|-
|-
|AboId
|AboId
|Abonnement ID
|Subscription ID
|-
|-
|AboName
|AboName
|Abonnement name
|Subscription name
|-
|-
|ExecTime
|ExecTime
|Last abonnement execution
|Last subscription execution
|}
|}


Line 87: Line 87:
|-
|-
|AboId
|AboId
|Abonnement ID
|SubscriptionID
|-
|-
|LineNr
|LineNr
Line 116: Line 116:
''variable:=value''
''variable:=value''


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




Revision as of 10:25, 15 July 2010

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. For executing subscriptions the xp_cmdshell-feature must be enabled. The account of the process spawned by xp_cmdshell must be in the Report-Builder (or higher) role on the report server. The stored procedure dbo.ExecReportAbo must be customized. In the default configuration at least the PowerUser‘s password must be set. You have to provide a ClientConfig.xml-configuration file for the Abo-Executor.

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

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.

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 Hersteller 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 SubscriptionID
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.

Retrieved from "https://copsupport.coplanner.com/help10_en/index.php?title=Technics:Abo-System&oldid=10637"