Re: 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. - C# Discussion Boards

Re: 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.

9-Jan-07 7:44

Hi All,

I am trying to connect to Oracle database via C# service application. When I try to open the connection i get the exception "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." This happens at very first instance(not after some interval...)

I have tried to search this on the net, but all in vain. Most of them have given the answere as to check with the connection. Below is the code snippit I am using to connect to database.

string csQuery = "prc_ins_sample";
string strConnection = "User ID=xxxx;Password=xxxx;Data Source=xxxxx;";
OracleConnection OraConn = new OracleConnection();
try
{
OraConn.ConnectionString = strConnection;
HMLog.WriteEntry("OraConn.Open();");
OraConn.Open();
OracleCommand OraCmd = new OracleCommand(csQuery, OraConn);
OraCmd.CommandType = CommandType.StoredProcedure;
HMLog.WriteEntry("OraConn.Open()");

OracleParameter arg_month = new OracleParameter();
arg_month.OracleType = OracleType.Number;
arg_month.Direction = ParameterDirection.Input;
arg_month.ParameterName = "arg_month";
arg_month.Value = 10;
OraCmd.Parameters.Add(arg_month);

arg_month = new OracleParameter();
arg_month.ParameterName = "arg_mybal";
arg_month.Direction = ParameterDirection.Output;
arg_month.OracleType = OracleType.Number;
OraCmd.Parameters.Add(arg_month);

HMLog.WriteEntry("Before execution");
int iRecordCount = OraCmd.ExecuteNonQuery();
if ( iRecordCount > 0 )
{

}
OraConn.Close();
OraConn.Dispose();
}
catch(Exception ex)
{
HMLog.WriteEntry(ex.Message);
if (OraConn.State != ConnectionState.Closed ) OraConn.Close();
OraConn.Dispose();
}

Does any one know to get out of this problem.

Srinath

Dave Kreskowiak9-Jan-07 7:55

Re: 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.

9-Jan-07 7:55

Do you have the Oracle client software installed, version 8i release 3 or better?

Depending on your Oracle installation, you might have to add the Integrated Security=no; option to your connection string.

@"Data Source=xxxxx;User ID=xxxxassword=xxxx;Integrated Security=no;";

Dave Kreskowiak
Microsoft MVP - Visual Basic

btsrinath9-Jan-07 8:55

Re: 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.

9-Jan-07 8:55

Thanks for the reply.

Your hint did not help me. This is a strange behaviour, If I connect to the same database from a desktop application developed in C# with the same connection string, it does executes the procedure. But the same tried from C# service application it hangs for a min and then throw this exception.
Let me know if am doing somthing wrong... Frown | :(

I am in a pretty bad state with this exception.

Srinath

Dave Kreskowiak9-Jan-07 8:59

Re: 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.

9-Jan-07 8:59

As a test, go into the Services manager (Start/Run Services.msc), stop your service and change the userid and password that the service runs under to your id and password. Then restart the service and see what happens.

Dave Kreskowiak
Microsoft MVP - Visual Basic

btsrinath9-Jan-07 9:37

Re: 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.

9-Jan-07 9:37

Even this did not help me. Is there any initial setup that has to be done on my system even before accessing the database from service applications ? like user creation, giving persmissions etc..etc...?

Srinath

Dave Kreskowiak9-Jan-07 9:43

Re: 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.

9-Jan-07 9:43

Nope. It would appear that the database code is not the same as what you've tested in the non-service version.

Dave Kreskowiak
Microsoft MVP - Visual Basic

btsrinath9-Jan-07 9:55

Re: 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.

9-Jan-07 9:55

Code is very much the same. I am executing that in a console application. While carrying out your previous instruction I was given the permission to execute services(Log on services). So I restarted the machine and then carried out your instruction. Now I have strong feeling that some permission has to be given to my USERID and PWD on the local machine. And the serive was installed using "LocalSystem" property.
More over I am logging to a domain. Keeping this in mind I asked you about those question. Let me know if you need more info from my side.

Srinath

btsrinath10-Jan-07 5:31

Re: 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.

10-Jan-07 5:31

Do i have to impersionate the user...while accessing the data base ?

Srinath

Luc Pattyn9-Jan-07 9:50

Re: 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.

9-Jan-07 9:50

Could it be that (part of) your Oracle is installed "for this user only" instead of
"for all users" ?
As a test: use another user account on the same machine and try again.

Smile | :)

Luc Pattyn

btsrinath9-Jan-07 10:25

Re: 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.

9-Jan-07 10:25

Your are right. If I log into machine as a local user i am not able to access the database. Another strange problem, I right click on my console application and then select "Run As" option. Here I am using loginID and PWD, which I have been using so far and working fine. But the application throws an error

OCI.DLL
The specified module could not be found.
Could not create an environment: OCIEnvCreate returned -1.

But the same application if double clicked returns the correct answere from the procedure.

Srinath

Luc Pattyn9-Jan-07 10:50

Re: 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.

9-Jan-07 10:50

Googling oci.dll I found
http://www.dll-files.com/dllindex/dll-files.shtml?oci[^]

which seems to indicate the file somehow could have been installed on your machine
separate from the normal Oracle files, so all is not lost.
Search your PC for it, and move or copy it to a more appropriate location (where ever
that might be).

Smile | :)

Luc Pattyn

btsrinath9-Jan-07 11:41

Re: 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. [modified]

9-Jan-07 11:41

Actually all those dlls are picked up from my env path variables.
So that seems to be ok.... Smile | :)

But the acutal problem still exists.

Srinath

Luc Pattyn9-Jan-07 11:54

Determining time of query end

9-Jan-07 11:54

Just checking, is your env path the same for everyone ?

And is it set before the service gets started ?

-- modified at 18:04 Tuesday 9th January, 2007

Luc Pattyn

sopho249-Jan-07 7:07

sopho24

9-Jan-07 7:07

How can I code for controlling end of a database query and command? Assume I must run a messagebox when query finished certainly. How can I be sure query was finished (in code) ?

Re: Determining time of query end

btsrinath9-Jan-07 12:00

9-Jan-07 12:00

Do u mean to say you need to collect the time stamp ? before and after query execution ?

writing in a file

netJP12L9-Jan-07 6:12

netJP12L

9-Jan-07 6:12

I have a class inhertied from PrintDocument in that I am trying to write in a file while am in printpage event handler. The problem is that the file gets created but there is not text/data. Is it even possible to do.

protected void ThePrintDoc_PrintPage (object sender,PrintPageEventArgs ev)
{
StreamWriter sw =new StreamWriter(new FileStream("path.txt", FileMode.Create, FileAccess.Write));
sw.Write("hello");
sw.Close();

}

WillemM9-Jan-07 6:17

WillemM

9-Jan-07 6:17

Could be because the file doesn't get closed properly. Does the data appear after the application exits?

WM.

What about weapons of mass-construction?
"You can always try to smash it with a wrench to fix that. It might actually work" - WillemM

netJP12L9-Jan-07 6:29

netJP12L

9-Jan-07 6:29

no. if i apply the same codes in a non inherited PrintDocument then i can see the file with data but if i wrote same in PrintDocument class means
(public class TextPrintDocument : PrintDocument) then i don't see
Thanks

WillemM9-Jan-07 7:04

WillemM

9-Jan-07 7:04

Did you do the following inside your class??

public class TextDocument: PrintDocument {
public TextDocument()
:base()
{
PrintPage += new PrintPageEventHandler(ThePrintDoc_PrintPage);
}

protected void ThePrintDoc_PrintPage (object sender,PrintPageEventArgs ev)
{
StreamWriter sw =new StreamWriter(new FileStream("path.txt", FileMode.Create, FileAccess.Write));

sw.Write("hello");
sw.Close();
}
}

It could be that your method wasn't hooked up to the printpage event of the PrintDocument class, which you inherit from.

WM.

What about weapons of mass-construction?
"You can always try to smash it with a wrench to fix that. It might actually work" - WillemM

Dave Kreskowiak9-Jan-07 6:28

9-Jan-07 6:28

Try calling your StreamWriter's Flush() method before you try and read the file:

protected void ThePrintDoc_PrintPage (object sender,PrintPageEventArgs ev)
{
    StreamWriter sw =new StreamWriter(new FileStream("path.txt", FileMode.Create, FileAccess.Write));
    sw.Write("hello");
    sw.Flush();
    sw.Close();
}

Dave Kreskowiak
Microsoft MVP - Visual Basic

Luc Pattyn9-Jan-07 7:21

9-Jan-07 7:21

Hi,

your 3 lines of StreamWriter code work fine inside one of my PrintPage methods.

some possibilities:
- your file already exists and is read-only or locked by another process
- you ar looking at the wrong file (using a relative path, and current directory maybe changed)
- you are getting an exception but missed it somehow

Smile | :)

Luc Pattyn

netJP12L9-Jan-07 8:43

netJP12L

9-Jan-07 8:43

Thank you all, It was putting in a wrong folder. so i just have the absolute path now.
This leads to my next question which is if i try to write in a file then there is no data in a file but if comment the loop then there is something.
I want to write in a file until i<5. is there something i am not doing right.

for(int i=0;i<5;i++)
{
sw = new StreamWriter(@"C:\myfolder\path.txt");
sw.writeLine(i.ToString());
sw.flush();

}
sw.close;

Thanks

Martin#9-Jan-07 9:09

Martin#

9-Jan-07 9:09

Hello,

I'm not sure if it's usefull to create a new instance of StreamWriter (in youre case 5) inside the for loop.
If yes, you would have to call the close method also in the loop, otherwise only the last StreamWriter instance would be closed (disposed).
If not, you should do this:

sw = new StreamWriter(@"C:\myfolder\path.txt");
for(int i=0;i<5;i++)
{
    sw.WriteLine(i.ToString());
    sw.Flush();
}
sw.Close();

All the best

Martin

Luc Pattyn9-Jan-07 9:47