Problems with SQL Server 2000 connection
-
Dear all, I have some problems with SQL Server 2000 that I want to ask. I am using the database with OLEDB connection that is called from VB.NET. The application that I make is an ERP software. 1. Sometimes, and it is often happened in the application, is an error saying "Connection is busy". 2. Then sometimes the application also shows a dialog box with a connection error saying "Cannot create a new transaction becase capacity was exceeded". I don't know how to solve them, I've tried to look for the answers from Google, but the only answer that I found is that I should update the service pack into the latest version. But I've checked my database, and its version is SP4. From the application itself, some people said that maybe I forgot to close the connection, but I've made only one function to fill a datatable, and I've put the Connection.Close() in the end after doing every transaction. Are there any better answer for these?
- No Signature Available -
-
Dear all, I have some problems with SQL Server 2000 that I want to ask. I am using the database with OLEDB connection that is called from VB.NET. The application that I make is an ERP software. 1. Sometimes, and it is often happened in the application, is an error saying "Connection is busy". 2. Then sometimes the application also shows a dialog box with a connection error saying "Cannot create a new transaction becase capacity was exceeded". I don't know how to solve them, I've tried to look for the answers from Google, but the only answer that I found is that I should update the service pack into the latest version. But I've checked my database, and its version is SP4. From the application itself, some people said that maybe I forgot to close the connection, but I've made only one function to fill a datatable, and I've put the Connection.Close() in the end after doing every transaction. Are there any better answer for these?
- No Signature Available -
Check your connection pooling settings. You may be hitting the limit. Also check that you close the connections properly using Close method after you have used them and that you end the transactions gracefully.
The need to optimize rises from a bad design.My articles[^]
-
Check your connection pooling settings. You may be hitting the limit. Also check that you close the connections properly using Close method after you have used them and that you end the transactions gracefully.
The need to optimize rises from a bad design.My articles[^]
I still don't know about the connection pooling Mika. Is it possible that the problem was caused by the fact that a lot of users of the application were accessing the database at the same time? Can you give me a little description about that, and what should I do to check it, or maybe a link of reference. Thanks before.
- No Signature Available -
-
Dear all, I have some problems with SQL Server 2000 that I want to ask. I am using the database with OLEDB connection that is called from VB.NET. The application that I make is an ERP software. 1. Sometimes, and it is often happened in the application, is an error saying "Connection is busy". 2. Then sometimes the application also shows a dialog box with a connection error saying "Cannot create a new transaction becase capacity was exceeded". I don't know how to solve them, I've tried to look for the answers from Google, but the only answer that I found is that I should update the service pack into the latest version. But I've checked my database, and its version is SP4. From the application itself, some people said that maybe I forgot to close the connection, but I've made only one function to fill a datatable, and I've put the Connection.Close() in the end after doing every transaction. Are there any better answer for these?
- No Signature Available -
Indra PR wrote:
I've put the Connection.Close()
although, you have put close connection request after every command. Does your program make sure that connection.close() is executed every time. I mean to say, If there is a error during the transaction, is close() method called. try using code as
try
{
Connection.Open();
//Your Block
}
catch(Exception EX)
{
//Exception handling code
}
finally
{
Connection.Close();
}One thing you should try checking is 1. When Your application starts giving the error reported, at that instance of time how many users are connected to your server and on the particular DB. 2. How many requests from the application are running in pool of DB
-
I still don't know about the connection pooling Mika. Is it possible that the problem was caused by the fact that a lot of users of the application were accessing the database at the same time? Can you give me a little description about that, and what should I do to check it, or maybe a link of reference. Thanks before.
- No Signature Available -
Indra PR wrote:
I still don't know about the connection pooling Mika
Connection pooling is quite well decsribed here: OLE DB, ODBC, and Oracle Connection Pooling [^].
Indra PR wrote:
Is it possible that the problem was caused by the fact that a lot of users of the application were accessing the database at the same time
If you have a lots of users simultaneously (thousands of them) or if you have limited maximum amount of connections, that's possible. You can check the max connection count using:
SELECT @@MAX_CONNECTIONS
You can find the maximum capacity specifications here: http://msdn.microsoft.com/en-us/library/aa933149.aspx[^]
The need to optimize rises from a bad design.My articles[^]
-
Indra PR wrote:
I've put the Connection.Close()
although, you have put close connection request after every command. Does your program make sure that connection.close() is executed every time. I mean to say, If there is a error during the transaction, is close() method called. try using code as
try
{
Connection.Open();
//Your Block
}
catch(Exception EX)
{
//Exception handling code
}
finally
{
Connection.Close();
}One thing you should try checking is 1. When Your application starts giving the error reported, at that instance of time how many users are connected to your server and on the particular DB. 2. How many requests from the application are running in pool of DB
Yep, my code is exactly as the same as yours. I still don't know about pool? Before, Mika also said something about DB pooling, or pooling connection. What is that? I've read the reference but still don't understand :laugh: I only know that DB pooling is a feature in ADODB to minimize cost of opening connection, we have to set it first or what? :confused:
- No Signature Available -
-
Yep, my code is exactly as the same as yours. I still don't know about pool? Before, Mika also said something about DB pooling, or pooling connection. What is that? I've read the reference but still don't understand :laugh: I only know that DB pooling is a feature in ADODB to minimize cost of opening connection, we have to set it first or what? :confused:
- No Signature Available -