Technics:Abo-System: Difference between revisions

From CoPlanner 10
Jump to navigationJump to search
Newer edit →
VisualWikitext
Created page with '==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…'
 
Line 23: Line 23:
==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 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.


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 29: Line 29:
===dbo.ExecReportAbo===
===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:
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:




Line 45: Line 45:




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 abonnement’s id as parameter.


===dbo.ExecReportAboInternal===
===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.  
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===
===Logging===
Line 56: Line 56:
:*''dbo.tb_ExecReportAboLogMsg''
:*''dbo.tb_ExecReportAboLogMsg''


mit protokolliert. Die Tabellen enthalten für jedes Abonnement das Ergebnis der letzten Ausführung.
These tables contain the result of the latest execution for each abonnement.


{| style=cellspacing="1" cellpadding="5" border="1" width={{{breite|100%}}}
{| style=cellspacing="1" cellpadding="5" border="1" width={{{breite|100%}}}
|-
|-
|width="200pt" style=background-color:#f6f6f6 |'''''Spalte'''''   
|width="200pt" style=background-color:#f6f6f6 |'''''Column'''''   
|style=background-color:#f6f6f6 |'''''Beschreibung'''''
|style=background-color:#f6f6f6 |'''''Description'''''
|-
|-
|AboId
|AboId
|ID des Abonnements
|Abonnement ID
|-
|-
|AboName
|AboName
|Name des Abonnements
|Abonnement name
|-
|-
|ExecTime
|ExecTime
|Zeitpunkt der Ausführung (bzw. wann wurde der Log-Datensatz erstellt)
|Last abonnement execution
|}
|}


Line 76: Line 76:
{| style=cellspacing="1" cellpadding="5" border="1" width={{{breite|100%}}}
{| style=cellspacing="1" cellpadding="5" border="1" width={{{breite|100%}}}
|-
|-
|width="200pt" style=background-color:#f6f6f6 |'''''Spalte'''''   
|width="200pt" style=background-color:#f6f6f6 |'''''Column'''''   
|style=background-color:#f6f6f6 |'''''Beschreibung'''''
|style=background-color:#f6f6f6 |'''''Description'''''
|-
|-
|AboId
|AboId
|ID des Abonnements
|Abonnement ID
|-
|-
|LineNr
|LineNr
|Zeilennummer der Ausgabe
|Line number
|-
|-
|MsgTyp
|MsgTyp
|Typ der Zeile (siehe weiter unten)
|Line type (see below)
|-
|-
|MsgLine
|MsgLine
|Text der Zeile
|Line text
|}
|}


Mit dem Spalte ''MsgTyp'' werden die einzelnen Zeilen klassifiziert:
The ''MsgTyp'' column is used for categorizing the table’s records:


:*Kein Typ
:*Empty Type
Zeilen ohne Typ sind reine Informationszeilen. Auch Exceptions werden ohne Typ in die Log-Tabelle eingefügt, da sich diese Zeilen nicht leicht automatisch erkennen lassen.
 
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
:*WRN
Warnmeldungen, die vom Berichtsserver beim Rendern der Berichte ausgegeben werden.
Warning messages which are generated by the report server on rendering the reports.


:*ERR
:*ERR
Fehlermeldungen, die vom Berichtsserver beim Rendern der Berichte ausgegeben werden.
Error messages which are generated by the report server on rendering the reports.


:*VAR
:*VAR
Das sind spezielle, vom Abo-Executor erstellt Zeilen, über die in weiteren Abfragen oder in Berichten Informationen abgerufen werden können. Die Spalte MsgLine hat in diesen Zeilen immer den Aufbau
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'':=''Wert''


Derzeit ist nur die Variable AboName definiert, die den Namen des Abonnements liefert.
''variable:=value''


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




[[de:Technik:Abo-System]]
[[de:Technik:Abo-System]]

Revision as of 16:43, 2 June 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 abonnements 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  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.


Hinweis  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.

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.

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 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 abonnement’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 abonnement 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 abonnement.

Column Description
AboId Abonnement ID
AboName Abonnement name
ExecTime Last abonnement execution


Column Description
AboId Abonnement 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 abonnement’s name.

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