Error when running stored procedure for validation

[cecilia_petersson2]

Hi,

We’re on BPC10 MS, admin client v14, EPM client 18.1, SQL 2012.

We’ve set up a simple validation rule to test functionality that will cause a validation difference, see attached.

I also attach the properties of subtable dimension O_Cost_Center.

When running in the script logic debugger, we get this log:

==============[System
Exception Tracing]==============

[System
Name] : ScriptLogic

[Message
Type] : ErrorMessage

[Job
Name]     : Handler.ProcessSubLogic

[DateTime]   
: 09/16/2014 09:47:34

[UserId]     
: DELAVAL\peteceext

[Exception]

    DetailMsg
: {Error running stored procedure SPRUNVALID [Operational],
[ACTUAL,BUDGET,EST,SP], [LC], [SPSCOPE_2014916947726], [SPLOG_2014916947726]:
The definition for column 'NVARCHAR' must include a data type.

Incorrect
syntax near ','.

Incorrect
syntax near the keyword 'and'.

Incorrect
syntax near ','.

Incorrect
syntax near the keyword 'and'.

Incorrect
syntax near the keyword 'and'.

Incorrect
syntax near ','.

Incorrect
syntax near the keyword 'and'.

Incorrect
syntax near ','.

Invalid
column name 'sIGNEDDATA'.

Incorrect
syntax near ','.

Invalid
column name 'Signeddata'.

Invalid
column name 'Signeddata'.

Invalid
column name 'Signeddata'.

Invalid
column name 'Signeddata'.

Invalid
column name 'Signeddata'.

Invalid
column name 'Signeddata'.

Invalid
column name 'Signeddata'.

Invalid
column name 'Signeddata'.

Invalid
column name 'Signeddata'.

Invalid
column name 'Signeddata'.

Invalid
column name 'Signeddata'.

Invalid
column name 'Signeddata'.

Invalid
column name 'Signeddata'.

Invalid
column name 'Signeddata'.

DIMENSION   
: Category

DIMENSION   
: Currency

DIMENSION   
: O_Account

DIMENSION   
: O_AuditTrail

DIMENSION   
: O_Cost_Center

CREATE
TABLE [dbo].[CtblDimO_Cost_Center] (AGREGAT Nvarchar(20),[ID]
Nvarchar(20),Organization Nvarchar(3),Niv int )

INSERT INTO
#Prop SELECT b.name FROM dbo.SYSCOLUMNS b, dbo.sysobjects a WITH (NOLOCK)

                                              
where Left(b.name,7) IN ('PARENTH','DIMLIST') and a.id= b.id and
a.name=N'mbrO_Cost_Center'

INSERT INTO
#Prop SELECT b.name FROM dbo.SYSCOLUMNS b, dbo.sysobjects a WITH (NOLOCK)

                                                              
where Left(b.name,14) IN ('SUBTABLES_ORIG') and a.id= b.id and
a.name=N'mbrO_Cost_Center'

INSERT INTO
[DBO].[CTBLDIMO_Cost_Center] 

                                                                              
select DIMLIST,[id],'L1',0 from [dbo].[mbrO_Cost_Center] where [DIMLIST]
<> N''

INSERT INTO
#AGR select b.PARENTH1,a.[id],1 as niv 

                                                                                              
from #agr a, [dbo].[mbrO_Cost_Center] b 

                                                                                              
where a.AGREGAT = b.[id] and a.niv = 0 and b.[PARENTH1] <> ''

INSERT INTO
#AGR select b.PARENTH1,a.[id],2 as niv 

                                                                                              
from #agr a, [dbo].[mbrO_Cost_Center] b 

                                                                                              
where a.AGREGAT = b.[id] and a.niv = 1 and b.[PARENTH1] <> ''

INSERT INTO
#AGR select b.PARENTH1,a.[id],3 as niv 

                                                                                              
from #agr a, [dbo].[mbrO_Cost_Center] b 

                                                                                              
where a.AGREGAT = b.[id] and a.niv = 2 and b.[PARENTH1] <> ''

INSERT INTO
#AGR select b.PARENTH1,a.[id],4 as niv 

                                                                                              
from #agr a, [dbo].[mbrO_Cost_Center] b 

                                                                                              
where a.AGREGAT = b.[id] and a.niv = 3 and b.[PARENTH1] <> ''

delete from
#AGR from #AGR as a inner join ctbldimO_Cost_Center as b 

                                                                              
                                              
  on a.agregat = b.agregat and a.id = b.id

INSERT INTO
#AGR select b.PARENTH2,a.[id],1 as niv 

                                                                                              
from #agr a, [dbo].[mbrO_Cost_Center] b 

                                                                                              
where a.AGREGAT = b.[id] and a.niv = 0 and b.[PARENTH2] <> ''

INSERT INTO
#AGR select b.PARENTH2,a.[id],2 as niv 

                                                                                              
from #agr a, [dbo].[mbrO_Cost_Center] b 

                                                                                              
where a.AGREGAT = b.[id] and a.niv = 1 and b.[PARENTH2] <> ''

INSERT INTO
#AGR select b.PARENTH2,a.[id],3 as niv 

                                                                                              
from #agr a, [dbo].[mbrO_Cost_Center] b 

                                                                                              
where a.AGREGAT = b.[id] and a.niv = 2 and b.[PARENTH2] <> ''

INSERT INTO
#AGR select b.PARENTH2,a.[id],4 as niv 

                                                                                              
from #agr a, [dbo].[mbrO_Cost_Center] b 

                                                                                              
where a.AGREGAT = b.[id] and a.niv = 3 and b.[PARENTH2] <> ''

delete from
#AGR from #AGR as a inner join ctbldimO_Cost_Center as b 

                                                                                                                              
  on a.agregat = b.agregat and a.id = b.id

INSERT INTO
#AGR select b.PARENTH3,a.[id],1 as niv 

                                                                                              
from #agr a, [dbo].[mbrO_Cost_Center] b 

                                                                                              
where a.AGREGAT = b.[id] and a.niv = 0 and b.[PARENTH3] <> ''

INSERT INTO
#AGR select b.PARENTH3,a.[id],2 as niv 

                                                                                              
from #agr a, [dbo].[mbrO_Cost_Center] b 

                                                                                              
where a.AGREGAT = b.[id] and a.niv = 1 and b.[PARENTH3] <> ''

INSERT INTO
#AGR select b.PARENTH3,a.[id],3 as niv 

                                                                                              
from #agr a, [dbo].[mbrO_Cost_Center] b 

                                                                                              
where a.AGREGAT = b.[id] and a.niv = 2 and b.[PARENTH3] <> ''

delete from
#AGR from #AGR as a inner join ctbldimO_Cost_Center as b 

                                                                                                                              
  on a.agregat = b.agregat and a.id = b.id

INSERT INTO
#AGR select b.PARENTH4,a.[id],1 as niv 

                                                                                              
from #agr a, [dbo].[mbrO_Cost_Center] b 

                                                                                              
where a.AGREGAT = b.[id] and a.niv = 0 and b.[PARENTH4] <> ''

INSERT INTO
#AGR select b.PARENTH4,a.[id],2 as niv 

                                                                                              
from #agr a, [dbo].[mbrO_Cost_Center] b 

              
                                                                              
where a.AGREGAT = b.[id] and a.niv = 1 and b.[PARENTH4] <> ''

delete from
#AGR from #AGR as a inner join ctbldimO_Cost_Center as b 

                                                                                                                              
  on a.agregat = b.agregat and a.id = b.id

INSERT INTO
[DBO].[CTBLDIMO_Cost_Center] 

                                                                              
select SUBTABLES_ORIG,[id],'L1',0 from [dbo].[mbrO_Cost_Center] where
[SUBTABLES_ORIG] <> N''

INSERT INTO
[DBO].[CTBLDIMO_Cost_Center] 

                                              
select [id],[id],'H0',0 from [dbo].[mbrO_Cost_Center] where calc = 'N' 

DIMENSION   
: O_Counterpart

DIMENSION   
: O_Entity

DIMENSION   
: O_Product

DIMENSION   
: Time

DIMENSION   
: xTransactionCurrency

===============================  
0   =================================

=========================================}

==============[System
Exception Tracing  End ]==============

When leaving Flow blank on both sides in the validation rule, we get error message “*ERROR*
VALID-060 Nothing extracted from VALIDATION – VALIDATION_H Table” and this log:

Environment:DL

Model:Operational

Logic mode:1

Logic by:

Scope by:

Data File:

Debug
File:\\SETUSRV130\OSoft\WebFolders\DL\Operational\PrivatePublications\peteceext\TempFiles\DebugLogic.Log

Logic File:ZMF_TEMP.LGF

Selection:DIMENSION:Category

|BUDGET,EST,SP,ACTUAL

|DIMENSION:O_Entity

|CEPE_DUM_O_ENT

|DIMENSION:Time

|2013.MAR

|

Run mode:1

Query size:2

Delim:,

Query type:0

Simulation:0

Calc diff.:0

Formula script:

Max Members:

Test mode:0

Is Modelling:1

Work
status Check:1

Task name:ManualInput

Number of logic calls: 1

----------------------------------------------------------------------------------------------------

Call no. 1, logic:
\\SETUSRV130\OSoft\WebFolders\DL\AdminApp\Operational\ZMF_TEMP.LGF

----------------------------------------------------------------------------------------------------

signeddata is YTD

-------------------------

Building sub-query 1

-------------------------

Query Type: 0

Max members:

Executing stored procedure
SPRUNVALID [Operational], [ACTUAL,BUDGET,EST,SP], [LC], [SPSCOPE_2014916956895],
[SPLOG_2014916956895]

SPRunValid Version 10.0.14.0

*ERROR* VALID-060 Nothing
extracted from VALIDATION - VALIDATION_H Table

Time to run stored procedure:
0.36 sec.

call 1 completed and data
posted in 0.39 sec.

Run completed in 0.42 sec.

****************************************************************************************************

End time --->09:56:07  -
Date: 2014-09-16

****************************************************************************************************

SPRunValid
Version 10.0.14.0

*ERROR*
VALID-060 Nothing extracted from VALIDATION - VALIDATION_H Table

We can run the Currency Conversion stored procedure without errors,

Any ideas?

/Cecilia

[cecilia_petersson2 · Accepted solution]

Problem solved, we didn't have an audit type dimension.

/Cecilia

[Former Member]

Hi Cecilia,

what if you use another element from the subtable dimension? How is the NO.COST_CENTER defined?

BR,
Arnold

[cecilia_petersson2]

Hi Arnold,

Here are the populated properties. Also tried with top node DL01 but got same error.

/Cecilia

[Former Member]

Hi Cecilia,

are both sides of that validation the same? Could you try using account1 flow1 on one side and account2 flow1 on the other side?

BR,
Arnold

[cecilia_petersson2]

Hi again,

Yes they are, as we wanted to provoke a validation error. But even when I change the account I get the same error message.

/Cecilia

[Former Member]

Hi,

are you trying to run it for several categories at once? The log seems to suggest that. Could you try with just one category?

BR,
Arnold

[cecilia_petersson2]

Thanks for your persistance Arnold!

Just tried with one entity, one period and one category and same result.

/Cecilia

[cecilia_petersson2]

One more thought: We're working in a Financial type model, not a Consolidation one. Would that make a difference? In the Consolidation model, we can run the validation.

Our dimensions:

/Cecilia

[Former Member]

Hi,

if you can add the rule to a financial model then it should work. What is the script you use to run the validation?

BR,
Arnold

[cecilia_petersson2]

Hi,

*RUN_STORED_PROCEDURE=SPRUNVALID([%APP%], [%CATEGORY_SET%], [LC], [%SCOPETABLE%], [%LOGTABLE%])

*COMMIT

/Cecilia

[Former Member]

Hi,

could you try to replace %APP% with %MODEL%

BR,
Arnold

[former_member210696]

Just to confirm. Validation Rules work fine on both financial and consolidation type model. So, that should not be a problem.

There are two tables which are relevant for validation - clcvalidation and clcvalidationH. As it appears, system can't find any data in this table. I have seen such errors before while running currency conversion - Nothing extract from table where it meant that since there were no records to be converted to reporting currency, it failed. Trying to draw a similarity here, system is fetching data from clcvalidationH table but can't find anything there and thus failing.

Please check the contents in these 2 tables for your model and see if you have any data there.

Be sure about the validation that it is executing on dataset which has got some data in it.

Hope it helps.

Regards,

Ashish

[former_member186498]

Hi Cecilia,

have you verified that your validation ID is present in clcValidation_<Model> and clcValidation_h_<Model> ? Are the records ok?

Regards

     Roberto

[cecilia_petersson2]

Hi,

Yes, data is there: clcValidation_h_<Model>

clcValidation_<Model>

/Cecilia

[former_member186498]

Hi Cecilia,

in the log it seems you pass several categories to sprunvalid,

SPRUNVALID [Operational],[ACTUAL,BUDGET,EST,SP], [LC], [SPSCOPE_2014916947726], [SPLOG_2014916947726]:

try with just one (this parameter is 20 chars long so maybe it reach the maximum)

Regards

     Roberto