Scheduling Full optimize in BPC 4.1

[Former Member]

Hi,

My question is related to Everest 4.1 (Hope there is still some knowledge about it).

I want to schedule a full optimize each night. I did it through the admin interface which allow user to schedule the task.

It created a job on SQL server (2000) named <<APPSET>>.

This job run the following command :

DTSRun /s <<ApplicationServer>> /u sa /e /n <<DTSPackage>>

So I opened the package in SQL Manager : it's empty beside the default properties of the packages.

Under Global Variables, there is 3 important variables :

• FullProcess_option (integer)

• Compress (boolean)

• URL (string)

The "URL" variable is set to "HTTP://SJLBIFI2/oSoft/K2AdminSchedule.asp". So i presume that in this version of "BPC", it don't really use a DTS task like the newest version, but call an ASP page in order to run the Optimize.

Btw, if we run manually the optimize, it don't attack the same URL (request is sent to /OSOFT/K2AdminOptimize.asp).

So :

1. Do you know a workaround to schedule Full Optimize in Everest 4.1 ?

2. If not, was it referenced as a bug somewhere and is it corrected in a Service Pack for this version ?

Sincerely,

[sorin_radulescu · Accepted solution]

DTSRUN can be run only into SQL server. It can not be run into application server.

Into 4.1 you can schedule the full optimize using the DM packages which can be found into

Apshell\Finance\Data Manager\...\Example or it can be schedule standard from admin module.

If you schedule from admin module the package then it will create into SQL server a SQL DTS which is scheduled.

This DTS if you saved like VB script you will be able to see the entire code to run the full optimize.

Kind Regards

Sorin Radulescu

[Former Member · Accepted solution]

Hi Sorin,

I complety agree with you, it's what i was expecting to see on 4.1.

But, I think it's not working like that for CPM 4.1. It seems that the Optimize DTS task is something which has been implemented with version 4.2 (there is no DM package in Apshell\Finance\Data Manager\...\Example about Full optimize).

For 4.1, it seems that it call an ASP page (with parameters) in order to start the task.

The created job on SQL Server is quite empty. There is only Global variables in the packages.

And futhermore, i was not clear on the initial message :

• When we run package manually from the application, it's ok.

• When we schedule it through CPM Admin, job is created and executed at the scheduled time. But it don't run an optimize (in fact it do nothing, just run during 1sec and exit with successfull message).

I got same behaviour if i run the DTSrun command in a cmd shell and if i manually run the package from SQL Server. It just do nothing

Edited by: Sebastien Michel on Jan 28, 2009 12:28 PM

[sorin_radulescu]

I don't think 4.1 is supported anymore. 4.2 is the version still supported until 2010.

Regards

Sorin

[sorin_radulescu]

Sebastien DTS is fine. 401 error is providing the information that DTS is started with an user with no access to that appset.

You have to open an internet explorer into Database server (SQL server) and to input the URL

HTTP://SJLBIFI2/oSoft/K2AdminSchedule.asp

Are you receiving any error? I expect you are receiving 401 because probably because you have into internet explorer a proxy.

So you have to login into database server with installation user of CPM and you have to uncheck any proxy or any Internet connection.

Try again to use

HTTP://SJLBIFI2/oSoft/K2AdminSchedule.asp

Until you will not make this page to work without error (it will be an empty page) optimize schedule will not work.

Once which you make this working then schedule of optimize wil start.

Regards

Sorin Radulescu

[Former Member]

Hi Sorin,

SQL server and Web server are same (single server configuration).

So When i call HTTP://SJLBIFI2/oSoft/K2AdminSchedule.asp while logged on the server, i get the XML message i gave you in my last post.

I already modified Internet Settings (no proxy / no internet connexion wizard / start page point to about:blank).

I keep you updated

On a side note : Do you know the sunset of CPM 4.1 Support ?

[sorin_radulescu]

ok, I got it.

You have to do two things:

1. After you scheduled from admin module you can see it was generate a SQL DTS.

If you double click into that DTS it will be empty but you can save as and you have an option VB Script.

This will create a file with extension I think .vbs

Open that file with notepad and you will see the code how it is expected to run Optimize.

Usually it is one asp page which can not be reach from db server and you have to correct that.

You have to open into internet explorer from db server that URL from VB code and to see what is the problem. If that is fine then you have to go to next step:

You have to check what is the user running SQL Server agent because this probably doesn't have any rights regarding CPM 4.1 I suggest to put the installation user of CPM like user which is running the SQL server agent.

I'm sure it is one of these two problems.

[Former Member]

Hi Sorin,

I worked on the topic.

So when i extract the DTS in a vbs file, the only reference to the URL is in these lines :

	Set oGlobal = goPackage.GlobalVariables.New("URL")
	oGlobal = "HTTP://SJLBIFI2/oSoft/K2AdminSchedule.asp"
	goPackage.GlobalVariables.Add oGlobal
	set oGlobal = Nothing

Which is mainly the definition of the Global Variables of the package.

I presume that the DTS, when executed, may make a SOAP request to the ASP page (i mean, parameters are passed in a format).

I changed the SQL agent account in order to use the install account with no effect.

I went in IIS log file. It displays the 2 followings lines :

2009-01-28 17:01:24 98.0.5.104 - 98.0.5.104 80 POST /oSoft/K2AdminSchedule.asp - 401 Mozilla/4.0+(compatible;+MSIE+5.01;+Windows+NT+5.0)
2009-01-28 17:01:24 98.0.5.104 SJLBIFI2administrator 98.0.5.104 80 POST /oSoft/K2AdminSchedule.asp - 200 Mozilla/4.0+(compatible;+MSIE+5.01;+Windows+NT+5.0)

I presume that the first one (with HTTP 401 code) is due to the fact that the server ask for authentification. The second line show that the ASP page is called under the administrator account (install account) with a HTTP code 200 (so i think it's ok).

But so far, no optimize are started.

I also tested the following (with no success) :

• Change IIS security on file K2AdminSchedule.asp and authorized "Anonymous" logon = not working (but i don't have the HTTP 401 error code, just the 200 one).

• Tried to send request to K2AdminOptimize.asp, which is the one used when you start a full optimize manually = not working also.

I really think that the DTS just send an empty SOAP message to the web server.

By the way, if i open the URL HTTP://SJLBIFI2/oSoft/K2AdminSchedule.asp with Administrator account, i get the following response from web server :

<?xml version="1.0" encoding="ISO-8859-1" ?> 
  <ENDWORK></ENDWORK>

It's a very treacky stuff we are discussing here

Hope you get new hint on this topic.

Sincerely,

Sébastien