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??