connections not closed by app

we inherited this java web app that uses a jdbc-odbc bridge for connecting
to 2 sql server 2000 instances. there are probably hundreds of places in
the application where connections are not being closed (resultsets and
statements not being closed as well).
my question:
1) i assume that as DBAs you would all frown on this? do you have
suggestions about doing something on the db side to address this?
2) what i though was something like: does sql server automatically (or can
i make it) close connections after a peroid of time? would that work?Dont know that I would actually do this, but if all else fails:
1; sp_who2 into a temp table
2; have a cursor loop through the LastBatch column, using dynamic sql, and
KILL everything > 5 minutes. (Or whatever time you decide on.)
I would really try to exhaust all other resources before using this method,
just an idea to keep in mind in case you get this deperate.
--
TIA,
ChrisR
"usenetjb" wrote:
> we inherited this java web app that uses a jdbc-odbc bridge for connecting
> to 2 sql server 2000 instances. there are probably hundreds of places in
> the application where connections are not being closed (resultsets and
> statements not being closed as well).
> my question:
> 1) i assume that as DBAs you would all frown on this? do you have
> suggestions about doing something on the db side to address this?
> 2) what i though was something like: does sql server automatically (or can
> i make it) close connections after a peroid of time? would that work?
>|||Wow. The program must be fixed but for now, I would consider setting up a
schedule to reboot the server at night.
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns97335385B8126usenetjb@.207.115.17.102...
> we inherited this java web app that uses a jdbc-odbc bridge for connecting
> to 2 sql server 2000 instances. there are probably hundreds of places in
> the application where connections are not being closed (resultsets and
> statements not being closed as well).
> my question:
> 1) i assume that as DBAs you would all frown on this? do you have
> suggestions about doing something on the db side to address this?
> 2) what i though was something like: does sql server automatically (or can
> i make it) close connections after a peroid of time? would that work?|||"Grant" <email@.nowhere.com> wrote in
news:uUcMfmzBGHA.3572@.TK2MSFTNGP14.phx.gbl:
> Wow. The program must be fixed but for now, I would consider setting
> up a schedule to reboot the server at night.
>
>
wow is not what i said, but then again ... believe it or not the app has
to be up 24/7. so we generally reboot the box after a week to make the app
speed up, sigh. it is leaking resources all over the place on the java app
side.
anyway, can sql server reboot itself? do you have any suggestions in terms
of tools to automate this sql server reboot?
thx

connections not closed by app

we inherited this Java web app that uses a jdbc-odbc bridge for connecting
to 2 sql server 2000 instances. there are probably hundreds of places in
the application where connections are not being closed (resultsets and
statements not being closed as well).
my question:
1) i assume that as DBAs you would all frown on this? do you have
suggestions about doing something on the db side to address this?
2) what i though was something like: does sql server automatically (or can
i make it) close connections after a peroid of time? would that work?Dont know that I would actually do this, but if all else fails:
1; sp_who2 into a temp table
2; have a cursor loop through the LastBatch column, using dynamic sql, and
KILL everything > 5 minutes. (Or whatever time you decide on.)
I would really try to exhaust all other resources before using this method,
just an idea to keep in mind in case you get this deperate.
TIA,
ChrisR
"usenetjb" wrote:

> we inherited this Java web app that uses a jdbc-odbc bridge for connecting
> to 2 sql server 2000 instances. there are probably hundreds of places in
> the application where connections are not being closed (resultsets and
> statements not being closed as well).
> my question:
> 1) i assume that as DBAs you would all frown on this? do you have
> suggestions about doing something on the db side to address this?
> 2) what i though was something like: does sql server automatically (or can
> i make it) close connections after a peroid of time? would that work?
>|||Wow. The program must be fixed but for now, I would consider setting up a
schedule to reboot the server at night.
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns97335385B8126usenetjb@.207.115.17.102...
> we inherited this Java web app that uses a jdbc-odbc bridge for connecting
> to 2 sql server 2000 instances. there are probably hundreds of places in
> the application where connections are not being closed (resultsets and
> statements not being closed as well).
> my question:
> 1) i assume that as DBAs you would all frown on this? do you have
> suggestions about doing something on the db side to address this?
> 2) what i though was something like: does sql server automatically (or can
> i make it) close connections after a peroid of time? would that work?|||"Grant" <email@.nowhere.com> wrote in
news:uUcMfmzBGHA.3572@.TK2MSFTNGP14.phx.gbl:

> Wow. The program must be fixed but for now, I would consider setting
> up a schedule to reboot the server at night.
>
>
wow is not what i said, but then again ... believe it or not the app has
to be up 24/7. so we generally reboot the box after a week to make the app
speed up, sigh. it is leaking resources all over the place on the Java app
side.
anyway, can sql server reboot itself? do you have any suggestions in terms
of tools to automate this sql server reboot?
thx

connections not closed by app

we inherited this java web app that uses a jdbc-odbc bridge for connecting
to 2 sql server 2000 instances. there are probably hundreds of places in
the application where connections are not being closed (resultsets and
statements not being closed as well).
my question:
1) i assume that as DBAs you would all frown on this? do you have
suggestions about doing something on the db side to address this?
2) what i though was something like: does sql server automatically (or can
i make it) close connections after a peroid of time? would that work?
Dont know that I would actually do this, but if all else fails:
1; sp_who2 into a temp table
2; have a cursor loop through the LastBatch column, using dynamic sql, and
KILL everything > 5 minutes. (Or whatever time you decide on.)
I would really try to exhaust all other resources before using this method,
just an idea to keep in mind in case you get this deperate.
TIA,
ChrisR
"usenetjb" wrote:

> we inherited this java web app that uses a jdbc-odbc bridge for connecting
> to 2 sql server 2000 instances. there are probably hundreds of places in
> the application where connections are not being closed (resultsets and
> statements not being closed as well).
> my question:
> 1) i assume that as DBAs you would all frown on this? do you have
> suggestions about doing something on the db side to address this?
> 2) what i though was something like: does sql server automatically (or can
> i make it) close connections after a peroid of time? would that work?
>
|||Wow. The program must be fixed but for now, I would consider setting up a
schedule to reboot the server at night.
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns97335385B8126usenetjb@.207.115.17.102...
> we inherited this java web app that uses a jdbc-odbc bridge for connecting
> to 2 sql server 2000 instances. there are probably hundreds of places in
> the application where connections are not being closed (resultsets and
> statements not being closed as well).
> my question:
> 1) i assume that as DBAs you would all frown on this? do you have
> suggestions about doing something on the db side to address this?
> 2) what i though was something like: does sql server automatically (or can
> i make it) close connections after a peroid of time? would that work?
|||"Grant" <email@.nowhere.com> wrote in
news:uUcMfmzBGHA.3572@.TK2MSFTNGP14.phx.gbl:

> Wow. The program must be fixed but for now, I would consider setting
> up a schedule to reboot the server at night.
>
>
wow is not what i said, but then again ... believe it or not the app has
to be up 24/7. so we generally reboot the box after a week to make the app
speed up, sigh. it is leaking resources all over the place on the java app
side.
anyway, can sql server reboot itself? do you have any suggestions in terms
of tools to automate this sql server reboot?
thx

Connections Closed but still getting errors on page

I have a page that I have 3 connections. I've made sure that each of these are closed when they are not being used and opened just right before being used. I keep getting the error "There is already an open DataReader associated with this Command which must be closed first." This error might show up as being produced by a dataadapter or sqldatareader...I have many. I've even tried to make separate connections as some have mentioned for each...leaving me with 15+ connections. I have added "MultipleActiveResultSets=True" to the connection strings as some have mentioned. I just don't know where to go from here...

Is it possible that the problem lies in multiple instances of this page being opened? Also, the data refreshes every 15 seconds. I really need this to work, but I have no clue on how to fix this problem. The error is easy to reproduce by opening up multiple instances, but some of the times is doesn't give an error at all?!

could you provide some example code how you execute your process when your get error?

|||

This section has showed up as producing an error...

 SqlCommand SqlCommand_ACK =new SqlCommand("SQL_STATEMENT", Connection_dis01_8);if (Connection_dis01_8.State == ConnectionState.Closed) Connection_dis01_8.Open(); SqlCommand_ACK.ExecuteNonQuery(); //<<<<<<<<ERROR HERE<<<<<<<<if (Connection_dis01_8.State == ConnectionState.Open) Connection_dis01_8.Close();

This section has also showed up...

 SqlDataReader SqlReader_OOME; SqlCommand SqlCommand_OOME =new SqlCommand("SQL_STATEMENT", Connection_dis01_6);if (Connection_dis01_6.State == ConnectionState.Closed) Connection_dis01_6.Open(); SqlReader_OOME = SqlCommand_OOME.ExecuteReader(); //<<<<<<<<ERROR HERE<<<<<<<<while (SqlReader_OOME.Read()) {//Manipulate Data... } SqlReader_OOME.Close();if (Connection_dis01_6.State == ConnectionState.Open) Connection_dis01_6.Close();

I have the message show up in this section...

if (Connection_dis01_5.State == ConnectionState.Closed) Connection_dis01_5.Open(); dis01DataAdapter.Fill(TempSet2); //<<<<<<<<ERROR HERE<<<<<<<<if (Connection_dis01_5.State == ConnectionState.Open) Connection_dis01_5.Close();

As you can see, I have multiple connecions and one connection is not used twice. I can easily recreate the message by just opening up multiple instances of this page. Thanks! I hope this code helps...the sections are really random and could show up anywhere a connection is used. Could it be a SQL Server setting I need to change?|||Here are my connections...

//Setting up Connections to dis01public static SqlConnection Connection_dis01_1 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_dis01_2 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_dis01_3 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_dis01_4 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_dis01_5 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_dis01_6 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_dis01_7 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_dis01_8 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_dis01_9 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_dis01_10 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_dis01_11 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_dis01_12 =new SqlConnection("CONNECTION_STRING_HERE");//Setting up Connections to ICCPpublic static SqlConnection Connection_ICCP_1 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_ICCP_2 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_ICCP_3 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_ICCP_4 =new SqlConnection("CONNECTION_STRING_HERE");//Setting up Connections to SCADApublic static SqlConnection Connection_SCADA_1 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_SCADA_2 =new SqlConnection("CONNECTION_STRING_HERE");public static SqlConnection Connection_SCADA_3 =new SqlConnection("CONNECTION_STRING_HERE");

|||

it is not good idea to create connections and keeping them all the time, modify your code to create connectiuon every time you would like to use it. .Net will take care about pooling so you connections if connection string is the same will be available very fast.

SqlConnection Connection_dis01_1 =new SqlConnection("CONNECTION_STRING_HERE");SqlCommand SqlCommand_ACK =new SqlCommand("SQL_STATEMENT", Connection_dis01_1);

//     try

      Connection_dis01_8.Open();

       SqlCommand_ACK.ExecuteNonQuery(); //<<<<<<<<ERROR HERE<<<<<<<<

// finally

          Connection_dis01_1.Close();

//end try

put your code in try catch finally and close connecion in finally part (I use VB so you have to writy your trty catsh yourself)

THis way your connection will be always closed and you save some server memory and time also.

Connection sleeping

If an MS SQL 2005 Connection is sleeping in the activity monitor, does it mean it's not closed? I'm pretty sure it's closed, just want to confirm.

Lately, our DB server got hammered with requests and it reached max-pool size (we implemented caching after that).

On MS SQL 2005 if you close the connection, you'll see it as sleeping waiting for you to open a new connection, when you do that MS SQL 2005 will give you one of those sleeping connections. Make sure all connections are close on your end.|||

I have not found this to be true. I have a method which opens a connection, runs a SQL statement, then closes the connection. If I cal this method quickly, hundreds of times (such as in a loop), then I spawn hundreds of connections (which all appear to be sleeping) and the pool size is exceeded and everything locks up. There do not appear to be any connection leaks (the .Close() method is always called).

The very interesting thing is that this only happens in IIS -- it does not happen in Casini (the "built-in" ASP.NET server within Visual Studio). In Casini the connections seem to be handled much better -- this tells me it is not a SQL Server issue but may be an IIS connection pool handling issue.

Does anyone have any thoughts?

Thanks everyone,

Matt

connection problem...

I have installed SQLEVAL and the service pach 3 on
Windows XP Home edition... now i can't use telnet/ftp
connection because my port23 is closed and i can't
uninstall SQL because there isn't an uninstallation file.
I don't know what i have to do.
thanks to much.
lmcHi
I have never used the EVAL edition, but...
I am not sure why you think that SQLServer will be using port 23?
You may want to check out the server network utitily in the SQL Server
program group.
If there is no add/remove program in control panel, have you check the
installation log to see that it worked? You may find a remove option in the
original setup program?
John
"lmc" <lmc76@.libero.it> wrote in message
news:006e01c3cc97$b1630350$a101280a@.phx.gbl...

quote:

---

> I have installed SQLEVAL and the service pach 3 on
> Windows XP Home edition... now i can't use telnet/ftp
> connection because my port23 is closed and i can't
> uninstall SQL because there isn't an uninstallation file.
> I don't know what i have to do.
> thanks to much.
> lmc

---