🚀 go-pugleaf

I found the reason and a solution, but no explanation so far.

The error comes after an attempt to start transaction after a set of SQL
operations (Select, Insert, Update) on the DB without explicit transactions.
I've workarounded it with the method ClearPool after Closing DB:#

DB.Open
<Do some SQL>
DB.Close
DB.ClearPool

DB.Open
DB.StartTransaction
<Do some SQL>
DB.CommitTransaction
DB.Close
DB.ClearPool

I would appreciate if somebody can explain me the background (no infos in
online Help to ClearPool(s)), thanks in advance.
/Efim

"Efim Mett" <Efim.Mett@t-online.de> schrieb im Newsbeitrag
news:44dce115@newsgroups.borland.com...
> Hi all,
>
> I use BDP with MSSQL 2005 within a migration program. For each table to
> migrate I start a transaction and on the end of table or after a definite
> number of records if it too long I commit it and start a new one.
>
> After about 15 commited transactions I get always an error message
> (translated from german): "Begin of transaction failed. A new transaction
> can't be created because the capacity is exceeded".
>
> The same run with DBExpress does run without problems. How can I control
> this with BDP?
>
> /Efim
>
>

Efim Mett wrote:
> I found the reason and a solution, but no explanation so far.
> 
> The error comes after an attempt to start transaction after a set of SQL
> operations (Select, Insert, Update) on the DB without explicit transactions.
> I've workarounded it with the method ClearPool after Closing DB:#

Have you made sure you close all command objects, data readers etc when 
you are finished using them? It is not enough to close the connection 
object. Otherwise connections to the SQL server might be left open. You 
can check this using SQL Server Management Studio (Management - Activity 
Monitor). If there are many connections from your application, there 
might be some object you forgot to close.

Regards,
Robert

Hello Robert,

yes, as Bob and Mark pointed out, I did it. Before that I had problems with
memory consumption (s. the previous thread in this NG).
This new effect takes place definitely in the scenario I described.

Thank you anyway
/Efim

"Robert �gren" <robert.ogren.re-mo-ve-me@prodacapo.remove.com.remove>
schrieb im Newsbeitrag news:44e30086@newsgroups.borland.com...
> Efim Mett wrote:
> > I found the reason and a solution, but no explanation so far.
> >
> > The error comes after an attempt to start transaction after a set of SQL
> > operations (Select, Insert, Update) on the DB without explicit
transactions.
> > I've workarounded it with the method ClearPool after Closing DB:#
>
> Have you made sure you close all command objects, data readers etc when
> you are finished using them? It is not enough to close the connection
> object. Otherwise connections to the SQL server might be left open. You
> can check this using SQL Server Management Studio (Management - Activity
> Monitor). If there are many connections from your application, there
> might be some object you forgot to close.
>
> Regards,
> Robert

Hi Efim,

> The error comes after an attempt to start transaction after a set of SQL
> operations (Select, Insert, Update) on the DB without explicit transactions.
> I've workarounded it with the method ClearPool after Closing DB:#

Which database do you use? Do you have a small code snippet (or small 
project) to reproduce this? Feel free to e-mail it to me and I'll check 
it out tomorrow. No promises, but I'll see what I can do to get to the 
bottom of this...

> I would appreciate if somebody can explain me the background (no infos in
> online Help to ClearPool(s)), 

ClearPool removes all connections from the pool. This will (most likely) 
also explicitly close the connection, and commit the transaction if it 
wasn't commited already?

> /Efim

Groetjes,
           Bob Swart

-- 
Bob Swart Training & Consultancy (eBob42.com)  Forever Loyal to Delphi
Blog: http://www.drbob42.com/blog - RSS: http://drbob42.com/weblog.xml
New Delphi 2006 Courseware e-books at http://www.eBob42.com/courseware

Hi Bob,

I use MS SQL 2005. I've created a small project as you adviced with
WinForms, but haven't got any error.
Could it be that BDP has another behaviour under VCL.Net? Not very probably
I think.

As I have soon my holidays I have no time now for firther invesigation of
the problem, I hope after that I'll have it.
Thanks for your help

/Efim

"Bob Swart" <b.swart@chello.nl> schrieb im Newsbeitrag
news:44E38181.5070509@chello.nl...
> Hi Efim,
>
> > The error comes after an attempt to start transaction after a set of SQL
> > operations (Select, Insert, Update) on the DB without explicit
transactions.
> > I've workarounded it with the method ClearPool after Closing DB:#
>
> Which database do you use? Do you have a small code snippet (or small
> project) to reproduce this? Feel free to e-mail it to me and I'll check
> it out tomorrow. No promises, but I'll see what I can do to get to the
> bottom of this...
>
> > I would appreciate if somebody can explain me the background (no infos
in
> > online Help to ClearPool(s)),
>
> ClearPool removes all connections from the pool. This will (most likely)
> also explicitly close the connection, and commit the transaction if it
> wasn't commited already?
>
> > /Efim
>
> Groetjes,
>            Bob Swart
>
> -- 
> Bob Swart Training & Consultancy (eBob42.com)  Forever Loyal to Delphi
> Blog: http://www.drbob42.com/blog - RSS: http://drbob42.com/weblog.xml
> New Delphi 2006 Courseware e-books at http://www.eBob42.com/courseware

Hi Efim,

> I use MS SQL 2005. I've created a small project as you adviced with
> WinForms, but haven't got any error.
> Could it be that BDP has another behaviour under VCL.Net? Not very probably
> I think.

If you want to use BDP with VCL for .NET, then you are probably creating 
everything at run-time instead of at design-time (like with WinForms or 
ASP.NET), so that may give a different result indeed...

> /Efim

Groetjes,
           Bob Swart

-- 
Bob Swart Training & Consultancy (eBob42.com)  Forever Loyal to Delphi
Blog: http://www.drbob42.com/blog - RSS: http://drbob42.com/weblog.xml
New Delphi 2006 Courseware e-books at http://www.eBob42.com/courseware

"Bob Swart" <b.swart@chello.nl> schrieb im Newsbeitrag
news:44E46268.7020003@chello.nl...
> Hi Efim,
>
> > I use MS SQL 2005. I've created a small project as you adviced with
> > WinForms, but haven't got any error.
> > Could it be that BDP has another behaviour under VCL.Net? Not very
probably
> > I think.
>
> If you want to use BDP with VCL for .NET, then you are probably creating
> everything at run-time instead of at design-time (like with WinForms or
> ASP.NET), so that may give a different result indeed...

Yes, at run-time. I suppose because of that I have also some connection
problems on out-of-office servers (s. my another thread from 11.08) because
I'm not sure the connection string built at my development environment
should look like the same in some other evironment (apart from host name, db
name and user/password of course)

/Efim

Hi Efim,

> Yes, at run-time. I suppose because of that I have also some connection
> problems on out-of-office servers (s. my another thread from 11.08) because
> I'm not sure the connection string built at my development environment
> should look like the same in some other evironment (apart from host name, db
> name and user/password of course)

Note that apart from ConnectionString, the BdpConnection component also 
uses the ConnectionOptions parameter.

In fact, now that I check the generated source code in an ASP.NET 
project, I see the following ConnectionOptions:

   Self.BdpConnection1.ConnectionOptions :=
     'transaction isolation=ReadCommitted;blobsize=1024';

Since you may not have that line in your VCL.NET project, and since you 
mentioned transactions problems... I wonder... ;-)

> /Efim

Groetjes,
           Bob Swart

-- 
Bob Swart Training & Consultancy (eBob42.com)  Forever Loyal to Delphi
Blog: http://www.drbob42.com/blog - RSS: http://drbob42.com/weblog.xml
New Delphi 2006 Courseware e-books at http://www.eBob42.com/courseware

> In fact, now that I check the generated source code in an ASP.NET
> project, I see the following ConnectionOptions:
>
>    Self.BdpConnection1.ConnectionOptions :=
>      'transaction isolation=ReadCommitted;blobsize=1024';
>
> Since you may not have that line in your VCL.NET project, and since you
> mentioned transactions problems... I wonder... ;-)

I've put this line fore some day and commented it again ;-)
I feel we need some insider informazions

/Efim