Error loading grid

Massimo Sommaruga

Hi,
I've tried to fill a grid in a new folder in the production order form;
everything works fine if the grid is based on a simple query, but when I run the following query:

SET NOCOUNT ON
DECLARE
@lvl int,
@line varchar(200),
@qt numeric(19,6),
@qtot numeric(19,6),
@sortcol nvarchar(100),
@current varchar(20)

SET @current='GM250484'
CREATE TABLE #stack (item varchar(200) COLLATE database_default, lvl int, quantity numeric(19,6), sortcol nvarchar(100))
CREATE TABLE #tree (item varchar(200) COLLATE database_default, lvl int, quantity numeric(19,6), sortcol nvarchar(100))
INSERT INTO #stack VALUES (@current, 1,1, Convert(nvarchar(100),'0'))

SET @lvl = 1
WHILE @lvl > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE lvl = @lvl)
BEGIN
SELECT @current = item,
@qt = quantity,
@sortcol= sortcol
FROM #stack
WHERE lvl = @lvl


SELECT @line = @current,
@qtot= @qt

INSERT INTO #Tree VALUES (@line, @lvl,@qtot,@sortcol)

DELETE FROM #stack
WHERE lvl = @lvl
AND item = @current
AND sortcol=@sortcol

INSERT #stack
SELECT
Db.code,
@lvl + 1,
Db.quantity,
Convert(nvarchar(100),@sortcol+cast(1000+ ChildNum as nvarchar(90)))
FROM ITT1 Db INNER JOIN
OITM P ON Db.Code=P.ItemCode
WHERE Db.father = @current and P.PrcrmntMtd='M' and IsNull(P.MinLevel,0)>0


IF @@ROWCOUNT > 0
SELECT @lvl = @lvl + 1
END
ELSE
SELECT @lvl = @lvl - 1


END

SELECT

T.item as ItemCode,
T.lvl-1 as Livello,

P.ItemName AS Descrizione,
P.InvntryUom as UM,
T.Quantity,

IsNull(P.MinLevel,0) as MinStock,
P.OnHand as Giac,
P.IsCommited as Impegnato,
P.OnOrder as Ordinato,
(P.OnHand - P.IsCommited + P.OnOrder) as Disponibile,
(case when T.quantity>(P.OnHand - P.IsCommited + P.OnOrder) then '!Attenzione!' else 'OK' end) as Warning,
Isnull(P.CardCode,'') as 'Fornitore',

(case when P.PrcrmntMtd='M' then 'Produrre' else 'Acquistare' end) as Tipo
FROM
#tree T INNER JOIN
OITM P ON T.item =P.ItemCode
ORDER BY T.sortcol


DROP TABLE #tree
DROP TABLE #stack


the grid isn't filled....

I'm working on a system with SBO2005A SP01 PL39 and MS SQL Server 2000, coresuite framework 2.50 and customize 1.53

Also in the Query Analyzer the query works fine (so it's not an SQL error).

In the coresuite debug log file I found exceptions like:

"customize C# rule 00000002 runtime error: The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT)) (Inner)-Trace: at SAPbouiCOM.DataTableClass.ExecuteQuery(String Query) at SwissAddonFramework.UI.Components.GridSAP.ExecuteQuery(String query) at SwissAddonFramework.UI.Components.Grid.ExecuteQuery(String query) at COR_Utility.Helper.Run_FO_COR_CUS_00000002(CustomizeEvent pVal)"

or

"Error occured in Item eventSystem.Runtime.InteropServices.COMException (0x80010105): The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT)) at SAPbouiCOM.DataTableClass.ExecuteQuery(String Query) at SwissAddonFramework.UI.Components.GridSAP.ExecuteQuery(String query) at SwissAddonFramework.UI.Components.Grid.ExecuteQuery(String query) at COR_Utility.Helper.<>c__DisplayClass1.b__0(Click eventVal) at SwissAddonFramework.UI.EventHandling.ItemEvents.ClickContainer.Execute(Event e) at SwissAddonFramework.UI.EventHandling.Global.Executer.Execute(Event e) at SwissAddonFramework.UI.EventHandling.ItemEvents.ItemEventBase.HandleEvent(String formUID, ItemEvent& pVal, Boolean& bubbleEvent) at SwissAddonFramework.Global.ItemEventHandler.Invoke(String formUID, ItemEvent& pVal, Boolean& bubbleEvent) at SwissAddonFramework.Global.CatchItemEvent(String formUID, ItemEvent& pVal, Boolean& bubbleEvent) on FormF_50"

What could be the cause?
SBO version? SQL Server version? COR customize?


Thanks in advance.

Regards.


PS: on a different system my rule works in the right way.

Manuel Marhold

Hi Massimo,

I think SBO doen't work with this query.
You can created a stored function on your sql-server with this query and run "SELECT * FROM dbo.storedFunctioName(parameter...)" to load the query and fill the grid.