Transaction that was started in mars batch is still active

[jak]

Hello!

i have added a trigger to DB_Main table for credit limit, trigger works perfectly and update values as defined inside trigger, but when we click on save button it shows this message.
when we apply this trigger on test table it works perfectly without given this error, is there any way we can stop this error in jiwa.

i researched about it and on websites it says we need to add this "MultipleActiveResultSets=False"
but i don't know where to add it in jiwa, or it might not be required.

can you please let me know the solution for this error.

Thank you in advance

[Mike.Sheen]

MultipleActiveResultSets=False is referring to the connection string we use to connect to the database.

I'm pretty sure Jiwa the application requires MultipleActiveResultSets=True, so we can't just change that.

Give me a sample trigger and steps to reproduce your error and I'll have a change then of working out what is going on and what we can do to work around it or address it.

Mike

[jak]

I'm using linked server in ms sql to connect jiwa database to mysql database,
connection is successfull and now we need that whenever a user apply credit limit in jiwa it will be reflect on magento tables as well,

this is a testing trigger, after some research i found that we need to add "begin tran" before end of the trigger to avoid error like "Batch is aborted",
but now it will start giving this error in jiwa "Transaction that was started is MARS Batch is still active"

Code: Select all

CREATE TRIGGER Creditlimit on DB_Main
after update
as
declare @jid nvarchar(50);
declare @mid nvarchar(50);
declare @jmcredit nvarchar(50);
declare @aid nvarchar(10);
select @jid= inserted.DebtorID from inserted
print 'jiwa id'+@jid;
set @aid = 167;
select @jmcredit = inserted.CreditLimit from inserted
print 'jiwa credit'+@jmcredit;

if update(CreditLimit)
begin

set @mid = (select Contents from DB_CustomSettingValues where DebtorID = @jid)
print ' magento id: '+@mid;
set XACT_ABORT ON
commit
UPDATE OPENQUERY (MYSQL, 'SELECT * FROM mysqldatabase.customer_entity_decimal')   
SET value = ''+@jmcredit+'' where entity_id=''+@mid+'' and attribute_id=''+@aid+'';
begin tran
end
go

can you please let me know any fix for this error, is there any other way for work around it.

Thank you in advance

[Mike.Sheen]

Hi Jak,

I've setup a test environment and reproduced the issue with your trigger.

The message "A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back" - means exactly that - you have an uncommitted update on the linked server because you do not issue a commit as the last statement of your trigger.

Move your commit to the end of your trigger. I used the following in my test:

First I created a table in my linked server - in a database I created called "TestDatabase":

Code: Select all

-- In linked server database TestDatabase
CREATE TABLE TestTable
(
   DebtorID CHAR(20),
   AccountNo VARCHAR(50),
   CreditLimit DECIMAL(19,6)
)
GO
-- Populate with some values
INSERT INTO TestTable
SELECT '0000000061000000001V', '1001', 500.00


Then in the Jiwa database I created the trigger:

Code: Select all

-- In Jiwa database
CREATE TRIGGER Creditlimit on DB_Main
AFTER UPDATE
AS
DECLARE @jid CHAR(20)
DECLARE @mid nvarchar(50)
DECLARE @jmcredit DECIMAL(19,6)
DECLARE @aid nvarchar(10)

SELECT  @jid= inserted.DebtorID from inserted

set @aid = 167;
select @jmcredit = inserted.CreditLimit from inserted

if update(CreditLimit)
begin

   set XACT_ABORT ON
   
   UPDATE OPENQUERY (JIWASUPPORTSQL3, 'SELECT * FROM TestDatabase.dbo.TestTable')
   SET CreditLimit = @jmcredit WHERE DebtorID = @jid
   commit
end
go


Then when I run Jiwa and edit the debtor record with account no 1001, I no longer get the error.

[jak]

thanks for your reply,

i did the exactly same that you have done, but still no success,
and also after "commit" we need to add begin tran, otherwise it will start execution, i don't know why this is required, but when i add begin tran it update data into magento database but still same error.

[Mike.Sheen]

Hi Jak,

In my tests the linked server was a MSSQL server - so given your results I'm thinking this is related specifically to a linked MySQL server.

It's beyond our scope of support to include scenarios where you are using triggers accessing linked servers, let alone MySQL servers.

Unfortunately that means I can't be of any further assistance to you on this.

Mike