Hi ,
I’m trying to Insert in SQL server using insert operation in many tables in SQL. it is inserting in first table(first step of Ing Component)then giving following Error:
“The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION”
any suggestion would be highly appreciated.
Ashutosh, if you are using the pub.db:execSQL built-in service (which I assume that you are), you do not need to explicitly call pub.db:commit.
The execSQL service will not only commit your changes to the database, it will also open the connection to the database (or grab a thread from the database thread pool), begin the transaction, execute one SQL statement, commit the transaction, and close the database connection.
Ashutosh, here is an example of insert transactions to multiple tables. The transaction is committed on success of all the insert transactions else is rolledback using startTransaction - Commit/ Rollback steps…
1 INVOKE debugLog (if required…)
2 SEQUENCE (exit on SUCCESS)
2.1 SEQUENCE (exit on FAILURE)
2.11 INVOKE clearTransaction (Just to make sure there is no open transaction)
2.12 INVOKE startTransaction
2.13 INVOKE insertEmp (execSQL DBservice- insert into EMP table)
2.14 LOOP OVER ‘/deptL’
2.141 MAP
2.142 INVOKE insertDept (execSQL DBservice - insert into DEPT table)
2.15 INVOKE commit
2.16 INVOKE clearTransaction
2.3 SEQUENCE (exit on DONE)
2.31 INVOKE getLastError
2.32 INVOKE logExceptionDetails (custom service to log exception details)
2.33 INVOKE rollback
2.34 INVOKE sendEmail (custom service to send email)
Hope this helps!
Ashutosh, the above example is from Integration Server 4.6. Please ignore this if it not helpful.
Hi,
My problem of Insert is related to Enterprise Server not B2B. any clues for SQL server Insert.
Hi,
Can you please tell me what version of ES, adapter and SQL Server are you using.
Sharath
Hi Sharath
It is strange when i am inserting through Document Tracker it is working fine but when trying to insert by complete flow ,it is showing error. i’m using ES 5.0,SQL server 7.0,Adapter4.1.
is there any file need to include or required some driver.
Ashutosh
Have you tried using the ODBC Adapter instead of the SQL Server Intelligent Adapter. The SQL Server adapter has had issues and we here also use the ODBC Adapter instead. Another alternative is to use the JDBC Adapter.
Have you tried recreating the IC and the Configured Operation again. Enterprise Integrator 4.5.2 and up does lot of caching which causes unnecessary problems.
These two options should help resolve your problem
Hi Sharath,
Thankx for ur suggestion. don’t bother now i have resolved the issue. by the way u r from which company??
Regards,
Ashu
Appreciate if you can tell how you solved the problem. Its could be a learning.
Thanks,
Sharath