SBO_SP_TransactionNotification problem

[Former Member]

Hi!

I use SBO 2004C. My SBO_TN executes another stored procedure (for example, 'STORED_PROC'). STORED_PROC inserts data into UDT ([@table3]) and use TRANSACTION ROLLBACK. STORED_PROC returns an error code when transaction rollback. Error code sets for @error variable which SBO_TN returns. Here is a part of SBO_TN code (simplified):

-

---

/...code.../

--SALES ORDER

IF @object_type = 17 and @transaction_type = 'A'

BEGIN

/...code.../

insert into [@table1]

select * from ORDR

/STORED_PROC uses data of UDT [@table1] in CURSOR operation/

exec STORED_PROC @var1,@var2,@error output

IF (select @error)<>0

set @error_message = N'Error occures'

/...code.../

END

/...code.../

-- Select the return values

select @error, @error_message

end

-

---

If I execute SBO_TN using Query Analyzer I`ve got no problem: @error and @error_message are returns according to my algorithm. If I try to add 'bad' Sales Order from B1 client it doesn`t notify me with error message. Sales Order seems to be added but exactly it`s not. And some DB objects are being blocked untill I close B1 client.

Please help me to understand what is the problem.

Thanx.

Dmitry.

[Former Member]

hi Dmitry

I use this code for execute a stored procedure in SQLServer:

...

Dim sboRecordset As SAPbobsCOM.Recordset

...

...

If Not sboRecordset Is Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(sboRecordset)

sboRecordset = SBOCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)

sboRecordset.DoQuery("EXEC XX_ActualizaPendenteEnc " & intOrder & "," & intOrderLine & "," & dblQTItem)

...

Sérgio Sousa

[Gianluigi]

Pls note that stored procedures in B1 DB are not allowed

by the B1 solution certification guidelines.

[Former Member]

Hi,

Try using quotes in the object type.

IF @object_type = '17' ...

Hope helps,

Ibai

[Former Member]

Hello, Ibai!

Thank you for advice, but unfortunaly it`s not work.

Maybe you got some kind of rules or restrictions of using that SP?

I`ve read messages of forum about SP but couldn`t find any solution. Some links to SP descriptions are dead.

Thanx for cooperations, Dmitry.

[Former Member]

Hi,

This scenario use to happen when there is somekind of error in the SP SQL. SBO says everything went ok, but no document is added.

Can´t help you more.

Sorry,

Ibai

PD: You are asigning the @error variable, aren´t you?

[AlexGrebennikov]

It's easy to verify if it works fine by the following steps:

1. comment your code in SBO_SP_TN

2. add the order using SBO Application

3. uncomment your code

4. find out the DocEntry of your order

5. execute SBO_SP_TN with the corresponding parameters using QA

hope, you'll get the error.

it is the reason of such behaviour

HTH

[Former Member]

Hi,

Yes, I used such method to verify an error. IF I execute SP from QA it`s work well and returns right results.

So I see that the problem not in SP.

In short: SP work good in QA but it doesn`t work in B1.

Thank you.

Dmitry Lebedev

[AlexGrebennikov]

Dmitry, did you exactly follow the steps or did you realize the approximate shema?

At the head of such trouble is the lack (bug) of (within) exception handling while SAP Business One App / DI API performs transaction commiting.