Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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 (25749 B)
replied

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

/Cecilia

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question