|
I have run a few tests and am now convinced that there must be a problem with connection pooling, or connections not being freed up correctly by .NET
My test is as follows. I have an XML Web Service running on an IIS site running on .NET 1.0 with all service packs applied with the following site configuration:
Anonymous Access: Yes
User name: domain\xxxxx <-- this username has access to the SQL Server
Allow IIS to control password: Yes
Basic Authentication: No
Integrated Windows authentication: Yes
The test method I am calling is written in VB.NET (code follows), when I pass in '50', the method creates 50 connections, pauses for 20 seconds to hold them open, and then they are closed and disposed - this works fine. The problem happens when I pass in '150'. The following exception is thrown;
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. at System.Data.SqlClient.SqlConnection.Open() at MyService.DataService.AuthenticateUser(String DomainUsername) in \\servername\CREQSERVICE\DataService.asmx.vb:line 172 --- End of inner exception stack trace ---
But even worse, the connections never seem to be released and the server becomes unstable; further connections to IIS time out. If I stop/start the web service it doesn't clear the connection pool. I have tested this on three different test servers, and get the same problem.
Any help with this would be appreciated, I'm trying to get some code into production, and this problem has delayed me for a week already. Does anyone know if there is a known bug with connection pools?
Code used for testing:
<WebMethod()> Public Function CreateTestConnections(ByVal NumberOfConnections As Integer) As String
'This is for testing only!, remove from production code
Dim conSQL As Array = Array.CreateInstance(GetType(SqlConnection), NumberOfConnections)
Dim t As Long
For t = 0 To NumberOfConnections - 1
Try
conSQL(t) = New SqlConnection(ConfigurationSettings.AppSettings("connectionstring"))
conSQL(t).Open()
Catch e As Exception
End Try
Next t
'pause for 20 seconds
Dim Start, Finish, TotalTime As Double
Start = Timer
Finish = Start + 20.0
Do While Timer < Finish
' Do other processing while waiting for 20 seconds to elapse.
Loop
TotalTime = Timer - Start ' Calculate total time.
For t = 0 To NumberOfConnections - 1
conSQL(t).Close()
conSQL(t).Dispose()
Next t
Return "Finished"
End Function
www.silveronion.com[^]
|
|
|
|
|
I am doing a project in SQLJ and would like to know if the connection to the Database(Oracle) exists until,I explicitly
call the Oracle.close() function or will it be closed automatically after fetching the resultset?
Preferably I want to keep the connection alive as long as I want to and close it only when I want to.(i,e when I close the window of a GUI, the connection to the Oracle database should close)
|
|
|
|
|
Usually a connection stays open until you close it yourself. There are various arguments for and against leaving a connection open for longer than you should. For example, leaving a connection open saves you the time it takes to reopen the connection should you need it again. However, if your database server is limited to a certain number of connections, you don't want to leave a connection open when it's idle.
Having said that, I lean more towards opening the connection, making the request and then closing the connection. If you want to leave your connection open, it's still advised that you don't assume the connection is still open when you try to use it. (A DBA might close your connection for a number of reasons). So make a habit of typing something like (pseudocode):
if (connection.state <> open) connection.open();
|
|
|
|
|
The connection stays open until either you close it yourself, or, in the case of objects (COM,.NET,etc), when the object terminates. It is always best to close a connection as soon as you're don using it to free up connections.
"Do unto others as you would have them do unto you." - Jesus
"An eye for an eye only makes the whole world blind." - Mahatma Gandhi
|
|
|
|
|
Using "Copy Project" has it's drawbacks. It's nice if I only have very simple web pages without any database. Now in real world asp programming, that is using databases and web forms, the "Copy Project" will not reconnect any database connections or even ask me. Seems like a rather dumb idea by Microsoft.
Any suggestions on simply transfering a finished web from a development server to an Internet web server???
ps. What's the point in having "automatic" features like "server explorer" to write all the code behind the scenes if its nearly impossible to make a simple web transfer???
The old fashioned way of coding all languages on a single asp page seems more dependable and easier to move.
|
|
|
|
|
For any serious project, don't use the drag and drop features to build queries or views / datasets etc, or at least, only use it to get an idea of what code you will need to add, then remove it and add it back yourself by hand.
Reason being that I've found from experience is that the code built for database access (other than the connection string itself)by the wizard is often very strange and in many cases badly inefficient.
In terms of moving a web that accesses a database server, all the normal things that apply to deploying any database project (web based or not) have to be taken into consideration. For example your app would normally build all the database objects it requires when it first connects to the server or as part of the installation script.
In terms of a web built (for example in c# and asp.net with vs 2002/2003) I've never come across a case where it couldn't be simply copied to the production web server so I don't agree with you there.
"Things are more like they are now than they ever were before."
-- Dwight Eisenhower
|
|
|
|
|
J Cardinal wrote:
For any serious project, don't use the drag and drop features to build queries or views / datasets etc
Good advice. BTW, those things actually confuse me, so I head straight for the SQL view. Nothing like good ol' code! 
"Do unto others as you would have them do unto you." - Jesus
"An eye for an eye only makes the whole world blind." - Mahatma Gandhi
|
|
|
|
|
Indeed this is the most insane software I've ever used. You see, when I move a perfectly running asp.net web project to another server, the computer names are different. So I changed those to make sure the database connections are correct. However upon this attempt nothing works. Oh yes the server is quite capable of running asp.net pages.
I suppose I would have to either develop all web projects on the actual Internet server to begin with or go back to the old fashioned way of writing all the code on a single .asp form including all c# scripts.
I'm very dissapointed after spendng countless hours learning various drag-n-drop features only to find it's too complicated to be moved.
|
|
|
|
|
kvnsdr wrote:
You see, when I move a perfectly running asp.net web project to another server, the computer names are different. So I changed those to make sure the database connections are correct. However upon this attempt nothing works.
It's normal practice to put the connection string in an externally configurable place if it's production software or at least in only one place in your code if it's internal use for just this reason. Then you only need to update one place.
As you found out, when you use the drag and drop features it will happily make a new connection string over and over and over again.
I think that the drag and drop is an excellent way for a beginner to learn what is required to get the task accomplished, but then most would quickly move beyond that if they are programming more than a very simple application.
"Things are more like they are now than they ever were before."
-- Dwight Eisenhower
|
|
|
|
|
J Cardinal wrote:
I think that the drag and drop is an excellent way for a beginner to learn what is required to get the task accomplished, but then most would quickly move beyond that if they are programming more than a very simple application.
Goes for most visual designers). I always like to learn the ropes by doing things with the designers and then looking at the code they generate.  That gives you a beginning foundation, but then of course you'll always need to go far beyond that.
"Do unto others as you would have them do unto you." - Jesus
"An eye for an eye only makes the whole world blind." - Mahatma Gandhi
|
|
|
|
|
I want to allow access to a directory by comparing a value in a table with the "HTTP_REFERER" Example: If I want to allow access via a hyperlink from any page on this site, then my entry in the table would be "thecodeproject.com" and my WHERE statement is checking to see if "thecodeproject.com" is contained in the "HTTP_REFERER"
I'm connecting to the database when I use "=" and I enter a value in the table that matches "HTTP_REFERER" exactly.
What is wrong with the sQuery line? I use query analyzer all day long but this is my first attempt to use sql along with ASP.
referer=lcase(request.servervariables("HTTP_REFERER"))
sQuery = "SELECT * FROM pbs WHERE orgin like " & "'" & referer & "'"
Also, what would I need to place on each page that would continue to allow the linked visitor to navigate as long as they are allow permission initially. That is if you type the url in the address bar or link from an unauthorized site, then you would get redirected to a "you can't get here from there" page.
|
|
|
|
|
You didn't say what the problem was, (i.e. are you getting an error on that query etc), so I'm guessing you mean that the query is bad and you don't know why, or it's simply not matching an hits when it should.
Run your program, set a breakpoint on the line that sets sQuery, put it in the watch window and copy the query text exact into a view in your SQL database or query analyser and confirm that it will work. Most likely you might need to either add a wildcard character on one or both sides or it's your lower case (lcase) that is mangling the original data so that it no longer matches.
For the second part, it's off topic for this forum, but there is an excellent article on forms based authentication on this site and it works very well and is easy to implement.
"Things are more like they are now than they ever were before."
-- Dwight Eisenhower
|
|
|
|
|
- You haven't specified any wildcards in your
LIKE statement, so it's the same as an equality test; - Since you want to test if the
referer contains the origin, the comparison should be the other way round;
Try:
sQuery = "SELECT * FROM pbs WHERE '" & referer & "' like '%' + orgin + '%'"
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
|
|
|
|
|
Please Help....I've been given the following problem, and I'm trying to get some information about technology/techniquest to solve it:
1. System model uses a dbms with a limited number of connections avaiable. Therefore, all communications to the dbms must go through a shared connection pool.
2. Various processes within the system will need to execute queries against the dbms through a pooled connection and then operate directly on the recordsets.
Now, the last time I did a connection pool to a dbms it was a set of classes that were compiled into a process and used ODBC to make the connections to the dbms. All updates to the dbms went through this single process.
What's confusing me is that some developers here are trying to convince me that I can make a connection pool in one central-process, then pass these connections out to other processes that will "do work" and then release the connection back into the free pool held in the central-process. I just don't see how this can work.....Can anyone help point me in a direction where this kind of thing has been done before?
Thankx!!!!
Just trying to keep the forces of entropy at bay
|
|
|
|
|
Does anyone know where I can get the layout for the ldf transaction logs that sql server writes? I want to read some information out of it for an internal program here at work. I can't find any info on this. I know of 2 products that do full log file reading and parsing, so I know the info is available somewhere.
|
|
|
|
|
I have a web app (ASP .NET) that runs a SQL query at the click of a button. I am trying to find if there is a way to check the progress of that query and re-post some information back to the user while it is still running. Any ideas?
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
Hi.
This is a general discussion post, that i've posted in the System Administration forum as well.
I've come to a point where i have a finished ASP.NET web application that i want to distribute. It use ASP.NET web as a front-end, .NET Windows as a front-end for the SQL Server backend db.
I want to create some sort of installation package for my application. I should do the following things:
1) Create FTP login
2) Create Virtual Directory in IIS
3) Copy ASP.NET files to virtual directory
4) Duplicate SQL database
5) Update web.config file in ASP.NET app.
At the moment i have some issues on point 4. What is the best approatch on duplicating SQL server databases programaticly. Are there any pre-made serverinstaller packages? Got any good articles/guitlines on the subject?
In general, i need some info on what is the best approatch to take when distributing a server app.
-Jonas
|
|
|
|
|
Server installation packages usually script out the database objects to a sql script file and bcp the necessary data out to a bcp file. When the installation is run on the target server, this script file is applied to the target database and the data bcp'd back into the target database.
It's also a good idea to let the DBA of the server database review the script file before installation so that he knows what's being done to his database server.
|
|
|
|
|
I using ADO to execute a stored procedure that will return a recordset and some parameters, but if I get the recordset will cause output parameters to be empty.
rs = cmd->Execute( NULL, NULL, NULL );
otherwise, I remove rs then the output parameters will be right.
cmd->Execute( NULL, NULL, NULL );
any idea?
|
|
|
|
|
For SQL-Server, the output parameters are only available after you have closed the recordset.
|
|
|
|
|
Hi
I have a problem with one of my fields in a table.
whenever the value of this field is going to be showed it must be converted to some other value .and when the user inserts a new value (in the second format, because the user knows nothing about the real format of the field) it must automaticly be converted to
the first format before saving.
In Delphi I do it with GetValue and SetValue Events of each field. But in .Net environment I don’t know how can I do that.
Thanx.
With best regards
|
|
|
|
|
I am doing a project in SQLJ and have the source as below:
This code will construct a tree structure from two SQL query using the connect by ... PRIOR statements.
The first SQL query will create the parent.
The Second SQL query will create the childern based on the parent value.A parameter is passed from Query1 to Query2 to establish a relationship between the parent and the child.
My problem now is to construct a Tree using JTree in java based on the above results of the SQL query.i,e the parent & children hierarchy has to be the same. I have tried to use a Hashtable but I am finding it vey difficult.IF SOMEONE HAS A SOLUTION, PLEASE HELP ME WITH AN EXAMPLE.
import java.sql.SQLException;
import oracle.sqlj.runtime.Oracle;
import sqlj.runtime.ref.DefaultContext;
import java.util.*;
#sql iterator iter1(String id,String fdn,int level);
#sql iterator iter2(String clli);
public class GetTree2
{
public GetTree2(String user_name,String password) throws SQLException
{
new TpSess1(user_name,password);
{
usingNamed();
}
finally
{
Oracle.close();
}
}
public GetTree2() throws SQLException
{
new TpSess1();
try
{
usingNamed();
}
finally
{
Oracle.close();
}
}
public static void main(String args[]) throws SQLException
{
new GetTree2(args[0],args[1]);
}
public void usingNamed() throws SQLException
{
iter1 it1=null;
iter2 it2=null;
String temp=null;
String pare1=null;
String par2=null;
String pare3=null;
String strcll1=null;
String strcll2=null;
String strcll3=null;
Vector all = new Vector();
Hashtable lev1 = new Hashtable();
Hashtable lev2 = new Hashtable();
Hashtable lev3 = new Hashtable();
Hashtable lev4 = new Hashtable();
Hashtable cll1 = new Hashtable();
Hashtable cll2 = new Hashtable();
Hashtable cll3 = new Hashtable();
Hashtable cll4 = new Hashtable();
#sql it1={select id,fdn,level from groups connect by prior fdn=parent_fdn
start with fdn='/' };
while(it1.next())
{
all.addElement(it1.id());
switch(it1.level())
{
case 1: lev1.put(all.elementAt(0),it1.id());
pare1 = it1.id();
break;
case 2: lev2.put(pare1,it1.id());
par2 = it1.id();
break;
case 3: lev3.put(par2,it1.id());
pare3 = it1.id();
break;
case 4: lev4.put(pare3,it1.id());
break;
}
temp=it1.fdn();
#sql it2= {select clli from nes where parent_fdn=:temp order by clli };
while(it2.next())
{
switch(it1.level())
{
case 1: cll1.put(all.elementAt(0),it2.clli());
break;
case 2: strcll2 = it1.id();
cll2.put(strcll2,it2.clli());
break;
case 3: strcll3 = it1.id();
cll3.put(strcll3,it2.clli());
break;
case 4: strcll4 = it1.id();
cll4.put(strcll4,it2.clli());
break;
}
}
}
it1.close();
it2.close();
}
}
|
|
|
|
|
Parameters.Refresh() method created all parameters that are stored process provide. but I can't obtain the value of output parameter after execute.
Thank you very much.
|
|
|
|
|
Hi,
Our company writes simulation and optimisation software. Because of that, we need:
1. a consistent view of the data
2. fast access to the data
Therefore all relevant data is loaded from the database at once, and all optimisation and simulation is performed on the data in memory.
We do this since the late 80s on databases like Vax/Rdb, Ingres and Oracle. All of these databases offer the possibility of starting a Read-Only Transation, i.e. all queries performed in the transaction 'see' the data like it was at the start of the transaction, even if it was modified by some other user in the mean time.
We are now considering Microsoft's SQL Server, but apparently it does not offer the possibility of Read-Only transactions. This means that if our application starts up and wants to load all relevant data of the database, it has to use a 'serializable' transaction (actually putting a Read lock on all the tables) to be sure that consistent data is read.
This means that:
- during a start-up, nobody can save
- during a save, nobody can start-up
I'm stunned to see that SQL Server does not offer Read-Only transactions.
Probably most of the [database] world is developing OLTP-type applications, but somewhere there must be somebody writing applications that need to load big parts of the database.
How do these people solve that problem?
Did anybody encounter this problem also and has found a solution for it?
Thanks in advance.
Enjoy life, this is not a rehearsal !!!
My Articles:
- Implementing a Subject/Observer pattern with templates
- Different ways of writing class factories
- AutoRunner: a template class to automatically run start- and cleanup-code in code blocks
</
|
|
|
|
|
It looks like you need to search in Books Online for Transaction Isolation Levels. More specifically, the SERIALIZABLE isolation level that prevents dirty reads.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
