problem in calling of stored procedure with variable parameters

[Former Member]

Hello!

When we try to call our stored procudure with variable parameter (year and month) we receive various errors.

Here's the dynamic package script code:

DEBUG(ON)

PROMPT(SELECTINPUT,,,"Please select time for restatement",%TIME_DIM%)

TASK(Execute formulas,USER,%USER%)

TASK(Execute formulas,APPSET,%APPSET%)

TASK(Execute formulas,APP,%APP%)

TASK(Execute formulas,SELECTION,%SELECTIONFILE%)

TASK(Execute formulas,LOGICFILE,%APPPATH%\..\AdminApp\%APP%\zbpc_to_fm.lgx)

TASK(Execute formulas,RUNMODE,1)

TASK(Execute formulas,LOGICMODE,1)

TASK(Execute formulas,SIMULATION,1)

TASK(Execute formulas,CHECKLCK,0)

Our logic script file (zbpc_to_fm.lgx) code is:

*RUN_STORED_PROCEDURE=ztest('%TIME_SET%')

*commit

The problem occures while transferring the variable parameter %TIME_SET% value.

How can we correctly transfer the selected value of TIME_SET into the procedure?

[Former Member]

Your syntax here looks correct; it matches exactly one that I have working.

The one exception is the line:

TASK(Execute formulas,SIMULATION,1)

I'm not sure exactly how the simulation mode works, but it may not be calling the stored proc at all. That's easy enough to test, by setting up a stored proc with no input variables (but which writes a record to some table) and seeing if it is executed when calling the LGX file via this package.

If that's not the root cause... What exactly do the various error messages say?

[Former Member]

Tim, thank you!

Now our problem is that the stored procedure gets this value of parameter %TIME_SET%: 2008.APR,2008.AUG,2008.DEC,2008.FEB,2008.JAN,2008.JUL,2008.JUN,2008.MAR,2008.MAY,2008.NOV,2008.OCT,2008.Q1,2008.Q2,2008.Q3,2008.Q4,2008.SEP,2008.TOTAL

but not the value we choosed in PROMPT(SELECTINPUT,,,"Please select time for restatement",%TIME_DIM%)

[Former Member]

I'm not sure about that one -- where does the logic get the notion of 2008? Is that from the user's current view in Excel, when you run the package, or somewhere else?

What does the full package debug log tell you?

What BPC version are you running?

[Former Member]

The logic gets the notion of 2008 from customized user authorities for application ZBUDGET. It doesn't depend on CV in Excel.

Our BPC version is 7.0 (for MS SQL)

Our log-file looks like this (see link): http://docs.google.com/Doc?docid=dcgpqfkg_11d2djd8hf&hl=en

Edited by: Mikhail Rusanov on May 20, 2009 1:00 PM

[Former Member]

Everything in the debug log looks correct -- the most important lines are the ones indicating the values passed from the user's response to the PROMPT(SELECTINPUT...)

INFO(%CATEGORY_SET%, BUDGET)
INFO(%ENTITY_SET%, 1733)
INFO(%FUND_SET%, )
INFO(%PROJECT_SET%, )
INFO(%TIME_SET%, 2008.NOV)

Are you certain that the stored proc is processing the input parameter for time correctly, when you run it directly in MS Mgmt Studio?

What error messages do you see running it from BPC?

By "customized user authorities" do you mean that the time dimension is secured, and this user has access only to 2008.total and its descendants? If that's the case, the user should be able to see only those members in the action pane / current view, but the SELECTINPUT prompt should only show the base members (and not the year total and quarters). I'm still confused as to why all 12 + 4 + 1 members are passed through to the stored proc.

[Former Member]

Hi

Our problem is mysteriously solved. The package now transfers the selected value of TIME. We are much confused of all this sitution...

But... now we have a new problem: the system doesn't save the changes in the package created by copying standard one from the examples (renamed and edited). We can edit the script and save it only in BI, but not in BPC... Why?