Xcelsius error 2032 when using stored procedure as a datasource

[AlexGrebennikov]

Hello!

We have a problem with customized dashboard and SAP support suggested to post the request here, hopefully someone has an idea:

Scenario:

Design the dashboard as per the guide from Yatsea Li "Creating New Dashboard Packages for SAP

Business One 8.8", as a datasource use the stored procedure call with %-parameters in the query manager.

Actual result:

In the testing mode Xcelsius returns the error message:

An error has occurred.

For more information, contact the file creator or your system administrator.

Error: Error #2032

Connection Type: XML Data

In the same time B1if logs shows the following exceptions:

IPO Step / Time Stamp / Error Description

=

/vP.001sap0004.in_HCSX/com..../vP.001sap0004.in_HCSX/com.sap.b1i.vplatform.runtime/INB_HT_CALL_SYNC_XPT/INB_HT_CALL_SYNC_XPT.ipo/ErrorHandler

= 20110519002115

= com.sap.b1i.xcellerator.Xce...com.sap.b1i.xcellerator.XcelleratorException: XCE001 Nested

exception: com.sap.b1i.bizprocessor.BizProcException: BPE001 Nested exception:

com.sap.b1i.utilities.UtilException: UTE001 Nested exception:

com.sap.engine.lib.xml.parser.ParserException: = expected in attlist(:main:, row:28, col:1929)

=

/vP.001sap0004.in_HCSX/com..../vP.001sap0004.in_HCSX/com.sap.b1i.vplatform.runtime/INB_HT_CALL_SYNC_XPT/INB_HT_CALL_SYNC_XPT.ipo/proc

= 20110519002115

= com.sap.b1i.xcellerator.Xce...com.sap.b1i.xcellerator.XcelleratorException: XCE001 Nested

exception: com.sap.b1i.bizprocessor.BizProcException: BPE001 Nested exception:

com.sap.b1i.utilities.UtilException: UTE001 Nested exception:

com.sap.engine.lib.xml.parser.ParserException: = expected in attlist(:main:, row:1, col:1950)

Dashboard is setup properly, verified and tested and there is no any issues with this, as simply

changing the query from the EXEC to dummy SELECT statement eliminates the issue. Replacing the EXEC

with SELECT is not an option as we are talking about the complicated SQL logic for the financial module, thus the stored procedure call is the only option here.

In the MSSQL profiler we see the SP call with the right parameter values, again it means the

dashboard setup is correct.

'Declare' statement is not used in the query as suggested by the SAP Note 1536078. The query is

EXEC [dbo].[MySP] '[%0]', '[%1]'

Expected result:

Dashboard should work the same way regardless this is simple SELECT statementor SP call.

B1 - 8.81 PL05

B1if - latest

Xcelsius - 5.3.5.0

Thanks in advance!

[AlexGrebennikov]

Just FYI:

i redesigned the query of stored proc and am able to run it directly from the query manager now. dashboard in preview mode does not show error message, but does not show the data as well.

now, 2 interesting points:

1. while previewing the dashboard from the Xcelsius we see in the MSSQL profiler our query with the right parameters values. Running this query returns us the expected data set.

2. checking B1if messages in Message Log shows us in the "Success" section the entries related to our dashboard tests with our dashboard like

Payload id="atom3" Role="C" disable-output-escaping="false" sql="...." method="Query Statement(Query Statement)" mode="single" system="0010000100"
..SqlExceptions xmlns="urn:com.sap.b1i.adapter:jdbcadapter"
....SqlException
......ErrorCode 0 /ErrorCode
......Message *The statement did not return a result set*. /Message

As you may guess, running manually the query copied from the B1if message returns the data as well.

So, as a conclusion, now we have 2 issues

1. SP does not work as a dashboard datasource

2. Complex query does not work as a dashboard datasource as well

Thanx in advance for any ideas!

[YatseaLi]

Hi Alexey,

The message inidicates that no result return from SQL.

Please check the following:

1.Be sure the query can be executed successfully in B1 Query Manager

2.Be sure single quote used in for varchar field. such as cardtype = '[0%]' .

Or you may share your sql statement for review. Thanks.

Regards, yatsea

[AlexGrebennikov]

Dear Yatsea,

i have checked your points from the very beginning and indeed it worked perfect either from B1 or from Management studio. I wouldn't post the question here or at SAP Support not verifying everything i could imagine as a reason.

The good point is that finally i have resolved the issue and below is the explanation of the reason, hopefully this will prevent the community spending so much time on the diagnosis. Well, the reason was the usage in the table valuables column names with spaces, once i got rid of the spaces in the column names, it worked like a charm.

The steps to reproduce the issue:

1. create the SP as a datasource for the dashboard

2. use the following query of the SP (sample):

	DECLARE @Periods table
		(
			[Period Code] int,
			[Period Name] nvarchar(20)
		)

	INSERT INTO @Periods
		SELECT '1', 'Quarter 1' UNION
		SELECT '2', 'Quarter 2'

	SELECT
		T0.*, 100
	FROM
		@Periods T0

Now, running the dashboard, you'll receive the error while this SP works perfect from B1/MSSQL. I know this is not the best practice to use the spaces in the column names, but the error messages in Xcelsius/B1if should be much more descriptive than it is right now.

Hopefully this could be taken as a resolution to be included into the next B1if PLs.

Thanks.

[AlexGrebennikov]

Dear Yatsea,

please see if you can advice anything on the issue described in my message @ .

Thanks.