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
OLEDB Error 16389 - Cannot create new connection because in manual or distributed transaction mode
OLEDB Error 16389 - Cannot create new connection because in manual or distributed transaction mode
Last edited by RainerE on 15 Dec 2022, 12:00, edited 1 time in total.
Re: OLEDB Error 16389 - Cannot create new connection because in manual or distributed transaction mode
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
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
Re: OLEDB Error 16389 - Cannot create new connection because in manual or distributed transaction mode
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
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
Re: OLEDB Error 16389 - Cannot create new connection because in manual or distributed transaction mode
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
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
Who is online
Users browsing this forum: No registered users and 3 guests