Transaction that was started in mars batch is still active  Topic is solved

Support for Microsoft SQL Server in the context of Jiwa installations.

Transaction that was started in mars batch is still active

Postby jak » Wed Feb 22, 2017 4:22 pm

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
Attachments
jiwa rollback error.png
jiwa rollback error.png (7.77 KiB) Viewed 22512 times
jak
Occasional Contributor
Occasional Contributor
 
Posts: 22
Joined: Mon Oct 10, 2016 10:00 pm
Topics Solved: 0

Re: Transaction that was started in mars batch is still acti

Postby Mike.Sheen » Wed Feb 22, 2017 4:58 pm

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
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: Transaction that was started in mars batch is still acti

Postby jak » Wed Feb 22, 2017 5:32 pm

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
jak
Occasional Contributor
Occasional Contributor
 
Posts: 22
Joined: Mon Oct 10, 2016 10:00 pm
Topics Solved: 0

Re: Transaction that was started in mars batch is still acti

Postby Mike.Sheen » Fri Feb 24, 2017 10:41 am

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.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: Transaction that was started in mars batch is still acti

Postby jak » Sat Feb 25, 2017 9:07 pm

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.
jak
Occasional Contributor
Occasional Contributor
 
Posts: 22
Joined: Mon Oct 10, 2016 10:00 pm
Topics Solved: 0

Re: Transaction that was started in mars batch is still acti  Topic is solved

Postby Mike.Sheen » Sun Feb 26, 2017 3:38 pm

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
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755


Return to Microsoft SQL Server

Who is online

Users browsing this forum: No registered users and 1 guest

cron