topic Re: Disable MSSQL transactions in Talend Studio

Disable MSSQL transactions

Anonymous — Sat, 16 Nov 2024 11:46:15 GMT

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?

Re: Disable MSSQL transactions

Anonymous — Wed, 29 Jan 2014 11:23:44 GMT

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.

Re: Disable MSSQL transactions

Anonymous — Wed, 29 Jan 2014 22:37:38 GMT

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);

Re: Disable MSSQL transactions

Anonymous — Wed, 29 Jul 2015 08:42:49 GMT

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

Re: Disable MSSQL transactions

Anonymous — Wed, 29 Jul 2015 08:44:02 GMT

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