Disable MSSQL transactions

One Star

Disable MSSQL transactions

Hi,
I am attempting to execute a stored procedure on my SQL Server database.
I get the following error:-
The requested operation could not be performed because OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" does not support the required transaction interface.
I understand why this is happening - Inside my stored procedure, I am using an OPENROWSET write operation using a OLEDB provider, which does not support transactions.
For this procedure I really don't care about the transactions. Is it possible to create a Talend MSSQL connection that does not wrap operations in transactions?
I introduced a tJava component, before my call and used the following code:-
java.sql.Connection conn = (java.sql.Connection)globalMap.get("conn_tMSSqlConnection_2");
conn.setTransactionIsolation(0);
This results in the following error:-
java.sql.SQLException: The TRANSACTION_NONE option is not currently supported by the setTransactionIsolation method.
Help?
One Star

Re: Disable MSSQL transactions

I gave up on this.
This scenario involved a datasource with an anonymous schema i.e. the input file can be different each time
I used SQL -> OLEDB to import the data. this works fine.
But, when I tried to write back to the source via Talend I got errors.
I ended up concatenating the table into one long line and passing back to Talend to write to file.
Seventeen Stars

Re: Disable MSSQL transactions

Did you have tested using a separate tMSSQLConnection component and configure it to auto commit.
Normally this causes no transaction will opened.
Or in your own Java code try this:
conn.setAutoCommit(true);
One Star

Re: Disable MSSQL transactions

I gave up on this.
This scenario involved a datasource with an anonymous schema i.e. the input file can be different each time
I used SQL -> OLEDB to import the data.  this works fine.
But, when I tried to write back to the source via Talend I got errors.
I ended up concatenating the table into one long line and passing back to Talend to write to file.

which component you used for oledb connection??
One Star

Re: Disable MSSQL transactions

I gave up on this.
This scenario involved a datasource with an anonymous schema i.e. the input file can be different each time
I used SQL -> OLEDB to import the data.  this works fine.
But, when I tried to write back to the source via Talend I got errors.
I ended up concatenating the table into one long line and passing back to Talend to write to file.

which oledb connection you used??

15TH OCTOBER, COUNTY HALL, LONDON

Join us at the Community Lounge.

Register Now

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch