Skip to Content

Error when running stored procedure for validation

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

FlowDim.PNG (25.7 kB)
Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    Sep 24, 2014 at 08:23 AM

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

    /Cecilia

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 16, 2014 at 11:05 AM

    Hi Cecilia,

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

    BR,
    Arnold

    Add comment
    10|10000 characters needed characters exceeded

    • 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