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 a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

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

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

    /Cecilia

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on 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 a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.