crystal reports unreleased connections

[Peter Proost]

Hi Group,

I've got an interesting problem, I don't know if this is the right group but
I think so because everything I've read about it so far says it's a .net
problem. Here's the problem, we're using crystal reports 9 and vb.net and
we're using the crystalrepotViewer to show our reports. But every time we
open a report the connection to or sql server remains, so if I open 5 forms
with the report viewer I've got 5 sleeping connections in my sql server. If
I close all 5 forms I still got 5 open connections, and they will not go
away until I close the entire application. The problem with this is that
some user only use the app to print reports so after about an hour or so
they've got 80 sleeping connections. I've tried putting
crystalreportviewer1.dispose in the form closing event but this has no
effect. The crviewer also hasn't got a connection.close method or something
like that
Any help would be very much appreciated

Grtz Peter

 

[Bernie Yaeger]

Hi Peter,

This is interesting - how do you know you have these sleeping connections?

Bernie Yaeger

 

[Peter Proost]

Hi Bernie,

sorry I haven't replied earlier to your post but it didn't show up in my
news reader and it still doesn't but I saw it on google. I know I've got the
sleeping connections because they show up in the sql server enterprise
manager

greetz Peter

 

[Bernie Yaeger]

Hi Peter,

Your ISP is blocking you from perceived spam; happens to me all the time.
Just check back here periodically.

You're correct; I am working on the problem now to see if I can find a fix;
I will get back to you asap.

Bernie

 

[Bernie Yaeger]

Hi Peter,

It looks like there is a good reason for what you've encountered, and I was
aware of it but didn't really observe its behavior before.

The issue is 'connection pooling'. In .net, open connections are pooled and
reused, up to a maximum (I believe 100). They really don't hurt anything
and provide for speedy reuse of a connection. If you want to shut it down,
you can set pooling = false, but I'm told this is not a good idea. Here's a
link that you may find helpful:

http://msdn.microsoft.com/library/d...nectionPoolingForSQLServerNETDataProvider.asp

HTH,

Bernie Yaeger

 

[Peter Proost]

Hi Bernie,

First of all, thnx for your time&help. I know about connection pooling, but
the problem is that some of our users open and close reports all the time
and then when they're up to 20-30 sleeping connections in sql server they
start getting connection timeouts, and they have to reboot the program. But
I was wondering if there isn't a possibility to let crystal reports only use
for example maximum 5 connections or something, because I don't realy want
to shut down connection pooling, because of it's advantages.
thnx again

Peter Proost

 

[Peter Proost]

Hi Bernie,

I wanted to test to set the pooling to false for the reports but I think I
can't set this property for the crystalreportsviewer connection, because it
seems to me that it manages it's own connections, I only have to pass the
servername, databasename, userid and password to the
CrystalDecisions.Shared.Connectioninfo
There is CrystalDecisions.Shared.DbConnectionAttributes, maybe I can set it
here but I'm not sure how to use it.

Greetz

Peter

 

[Bernie Yaeger]

Hi Peter,

Below is a section on connection pooling that might be of value to you. It
indicates ways in which you can customize connection pooling using the
connection object. Try to set some of the parameters and let me know what
happens.

I'm a little concerned, though, about what you said - I'm wondering why your
users are encountering timeout problems and if this is really the result of
sleeping connections - let me know what you discover in this regard.

Bernie
--------------------------
Controlling Connection Pooling with Connection String Keywords
The ConnectionString property of the SqlConnection object supports
connection string key/value pairs that can be used to adjust the behavior of
the connection pooling logic.

The following table describes the ConnectionString values you can use to
adjust connection pooling behavior.

Name Default Description
Connection Lifetime 0 When a connection is returned to the pool, its
creation time is compared with the current time, and the connection is
destroyed if that time span (in seconds) exceeds the value specified by
Connection Lifetime. This is useful in clustered configurations to force
load balancing between a running server and a server just brought online.
A value of zero (0) will cause pooled connections to have the maximum
time-out.

Connection Reset 'true' Determines whether the database connection is
reset when being removed from the pool. For Microsoft SQL Server version
7.0, setting to false avoids making an additional server round trip when
obtaining a connection, but you must be aware that the connection state,
such as database context, is not being reset.
Enlist 'true' When true, the pooler automatically enlists the
connection in the current transaction context of the creation thread if a
transaction context exists.
Max Pool Size 100 The maximum number of connections allowed in the
pool.
Min Pool Size 0 The minimum number of connections maintained in the
pool.
Pooling 'true' When true, the connection is drawn from the appropriate
pool, or if necessary, created and added to the appropriate pool.

 

[Bernie Yaeger]

Hi Peter,

I now see that getting to the viewer's connection object might not be
possible, so my advice doesn't serve any purpose; I will continue to look
into this to see if it's possible.

Bernie

 

[Bernie Yaeger]

Hi Peter,

OK; I'm at my wits end. There seems no way of getting at the connection
string of the crystal viewer, even of the crystal reportdocument. I'd
recommend you post to microsoft.public.dotnet.framework.adonet. There are
very strong people there who might be able to help us. If you come up with
anything, please let me know, as I am concerned about this issue for the
same reasons you are.

Bernie

 

[Bernie Yaeger]

Hi Peter,

Making a little - very little - progress.

1. I have a message into Crystal re the problem;
2. I notice that you can change the timeout property in the connection
itself inside crystal reports. While I see no way there to edit the
connection string itself re pooling, this may help. For example, it
defaults to 15; I was able to change it to 1250 (presumably seconds) which
may resolve the timeout issues you've encountered.

Yes, it's a pain to have to change all of the reports' connection
properties, but if that's what we down to, at least it may be a solution.
Let me know what you think.

Bernie

 

[Peter Proost]

Hi Bernie,

thnx for your suggestions and help, I also had found the connect timeout
property in the reports but the problem is that at a certain time the user
has so many sleeping connections that he can't open a report (= an extra
connection) and I think that the connect timeout property is the time the
report trys to connect to the server, but I'm not sure. But if it is what
you say it is, it will only cause the report to stay connected for a longer
time, and that's the problem the reports staying connected ;-)

If you've got any more hints,tips or thoughts they're welcome, and I'll
follow your advice and post the question in the
microsoft.public.dotnet.framework.adonet group and see what that gives.

thnx again for thinking with me and if you have got an other idea I'm very
interested in hearing it.

Greetz Peter

 

[Bernie Yaeger]

Hi Peter,

I have the answer!!!

Place this in the closing event of the form that closes when the viewer
closes:
crreportdocument.Dispose()

where crreportdocument is the reportdocument object that is the source for
the report - eg,

CrystalReportViewer1.ReportSource = crreportdocument

This worked perfectly for me - I had 16 connections; when the report viewer
displays the report I had 17 connections; when I closed the viewer, I had 16
again. You were disposing the viewer, but it is the reportdocument object
that had to be disposed.

I got this, if you can believe it, from crystal (business objects), after
berating them for 2 days.

Let me know if this solves your problem.

Regards,

Bernie

 

[Peter Proost]

Hi Bernie,

you got it!! Thnx a million times, There still is a little problem that's if
I call for example 5 reportForms like this

dim objReport as new frmReport
objReport.show

I've ofcourse got 5 connections, but when I close 4 of the report forms I
still got 5 connections but when I close the last form and leave my app
running I got 0 connections and that's good enough for me

Thnx again I hope I can help you out some time

Greetz Peter and again thnx a million times

 

[Bernie Yaeger]

Hi Peter,

Glad to help; it ended up helping me as much as it helped you!

Regards,

Bernie