| Author |
Message
|
| GeneRK4 |
 Posted: Tue Sep 02, 2014 7:37 am Post subject: Transaction error while connecting to MSN SQL Server |
 |
|
Master
Joined: 08 Jul 2013
Posts: 220
|
Hi experts,
I sincerely need expert help on solving a critical issue..
From WMB we are using sqljdbc4.jar to connect to MSN SQL Server 2005 SP 2.
We are getting the below error while calling the Stored Procedure.
Sep 2 16:12:45 mbdhub01 WebSphere Broker v8004[18319]: (MBDHUB01.NSP_TEST)[10]BIP4361W: Java node warning: NSP04_SQLJDBC The server failed to resume the transaction. Desc:4900000003.. : MBDHUB01.527b3d80-4601-0000-0080-cd8302896d21: /build/slot1/S800_P/src/DataFlowEngine/NativeTrace/ImbNativeTrace.cpp: 739: ConnectionPoolWorker.checkForBrokenConnection: :
Sep 2 16:12:45 mbdhub01 WebSphere Broker v8004[18319]: (MBDHUB01.NSP_TEST)[10]BIP6265E: A problem was encountered when committing a transaction with the JDBC Datasource 'NSP04_SQLJDBC commit'. : MBDHUB01.527b3d80-4601-0000-0080-cd8302896d21: /build/slot1/S800_P/src/DataFlowEngine/NativeTrace/ImbNativeTrace.cpp: 739: JDBCType4SinglePhaseTrxnTable::commit: :
Sep 2 16:12:45 mbdhub01 WebSphere Broker v8004[18319]: (MBDHUB01.NSP_TEST)[10]BIP4362E: Java node error: Error committing Transaction for JDBC DataSourceName NSP04_SQLJDBC. : MBDHUB01.527b3d80-4601-0000-0080-cd8302896d21: /build/slot1/S800_P/src/DataFlowEngine/NativeTrace/ImbNativeTrace.cpp: 714:
============================
Snippet of our code on calling Database stored procedure,
stmt = conn.prepareCall("{call SPRequest1(?,?)}");
stmt.close();
We have Transaction set as "NO" in the Input node.
We are using Non-XA jdbc connection.
We are also closing the statement.
Still the transaction errors are there.DB team is saying their Stored Procedure code is completely fine.They are using Transactional statements like COMMIT at their end.
Could someone please help me on this? |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Tue Sep 02, 2014 4:07 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Doesn't the doctrine say the SP should not commit as the commit is issued by the flow? 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| GeneRK4 |
| Posted: Tue Sep 02, 2014 6:14 pm Post subject: |
|
|
Master
Joined: 08 Jul 2013
Posts: 220
|
Ok...You mean to say they have to switchoff the COMMIT at their end?
I have non-XA connection.Transaction=NO.
Could you let me know whether JDBC call still tries to do COMMIT while calling StoreProcedure? |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue Sep 02, 2014 7:58 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
XA gives you a multi-phase commit. Without XA you still have a single phase commit. I believe your flow is protesting that it can't commit the transaction...?
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| GeneRK4 |
| Posted: Tue Sep 02, 2014 10:15 pm Post subject: |
|
|
Master
Joined: 08 Jul 2013
Posts: 220
|
I believe doing commit and connection close can be handled by broker internally.
We are using connection establishment statement and statement close.
We could not think of any other parameters which can help in resolving this issue from WMB side.
Could you suggest please? |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Sep 03, 2014 1:29 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Have you tried removing the commit from the SP? What was the result?
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| GeneRK4 |
| Posted: Wed Sep 03, 2014 4:00 am Post subject: |
|
|
Master
Joined: 08 Jul 2013
Posts: 220
|
 DB team never accepted their fault.
Finally it started working after DB team moved the below two statements after BEGIN in StoredProcedure.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
Earlier it was before BEGIN statement.
I shared just for the benefit of others. |
|
| Back to top |
|
|
|
|
