Won't Fix OLEDB Error 16389 - Cannot create new connection because in manual or distributed transaction mode

General discussion forum about all databases other than SqlBase.
RainerE
Germany
Posts: 2278
Joined: 27 Apr 2021, 12:40
Location: Germany

OLEDB Error 16389 - Cannot create new connection because in manual or distributed transaction mode

Post by RainerE » 18 Aug 2022, 12:15

We have a large TD 7.4.3 application which connects via OLEDB to MS Sql Server.
In the application if you open an MDI child table and execute a query, which takes e.g. 2,5 minutes to prepare and execute, we get the SQL Error "16389 - Cannot create new connection because in manual or distributed transaction mode." from SQL Server.
We already use front end result sets, which is told to do if this error occures.
I cannot send a reproducible case because it does not happen with a small test database and I'm not allowed to send the database of our customer.
The problem occurs in the While SqlFetchNext() loop, where a second handle is used to fetch additional data with SqlPrepareAndExecute() and SqlFetchNext().

I have already investigated 2 mandays, but I cannot norrow down the problem until now.
I will go on trying to narrow down the problem...

Has anyone had this problem, too?
Any ideas?

Kind regards,
Rainer
Last edited by RainerE on 15 Dec 2022, 12:00, edited 1 time in total.

thomas.uttendorfer
Site Admin
Site Admin
Germany
Posts: 316
Joined: 05 Mar 2017, 17:19
Location: Munich Germany

Re: OLEDB Error 16389 - Cannot create new connection because in manual or distributed transaction mode

Post by thomas.uttendorfer » 18 Aug 2022, 12:59

Hi Rainer,

I had a similar case with Sql Server and two cursors where one fetches and the other fetches the details.
But I got another errormessage (Connection is busy with results for another hstmt).

One solution was to put the two cursors in different sessions.
You can do that with using different users when connecting the cursors (easiest way for testing)
or you might try the SqlSession-commands from Gupta.

Maybe that helps.

Regards Thomas
Thomas Uttendorfer
[ frevel & fey ] Software-System GmbH
https://thomasuttendorfer.wordpress.com/

RainerE
Germany
Posts: 2278
Joined: 27 Apr 2021, 12:40
Location: Germany

Re: OLEDB Error 16389 - Cannot create new connection because in manual or distributed transaction mode

Post by RainerE » 18 Aug 2022, 16:40

Hi Thomas,

thx for this hint.

I've found the reason, why this happens now but not in the last years:
Due to a hint of someone here in the forum, I changed my connection string as follows:
MSOLEDBSQL;Data Source=TSTSRVSQL;Initial Catalog=Test;User ID=Test;Password=Test;Persist Security Info=False;Extended Properties="CLIENTCURSOR=TRUE;";Encrypt=Yes;'

But if the result contains to much records (700.000 records with 150 columns each), using Extended Properties="CLIENTCURSOR=TRUE;"; leads to the problem described.
I still can run through the recordset, but I cannot execute any other statement on a separate handle during this time (except I would connect all those other handles in another session - or better, connect the main select in a separate session).
We have decided to remove the client result set and live with some % loss of performance.

Note that simply setting SqlUDL to a different (database, user, ...) and call SqlConnect() will not work for us. We had tried this in another scenario, but the application was running less stable after this.
We now use SqlUDL and SqlConnect() for the main database/user and SqlCreateSession()/SqlCreateStatement() for each additional dabase/user. This works stable.

Kind regards,
Rainer

RainerE
Germany
Posts: 2278
Joined: 27 Apr 2021, 12:40
Location: Germany

Re: OLEDB Error 16389 - Cannot create new connection because in manual or distributed transaction mode

Post by RainerE » 15 Dec 2022, 12:04

Large result sets are limited by the memory of the client and/or the memory of 32bit applications and/or the internal memory usable by TD.
So very large results sets will not work with TD even if enough memory is available.
You can change this either to a main query with client result set off or to a main query with only the PK column(s). In the loop you can use additional handles with client result set on to query additional data.

Regards,
Rainer

Return to “General Discussion”

Who is online

Users browsing this forum: No registered users and 3 guests