Getting a pool timeout error

I have pages which are using a master page.

An example page on my site would be the homepage, it makes 3 connections to mssql.

1) Get the keywords of the page

2) Get a list of news articles

3) Get the content of the page.

When I visual web express to debug the site it's giving me a pool error message, neither can I get the site to load directly via IIS. It says theres been a pool timeout.

I've read on the internet about making sure connections are closed when you are finished and I've checked that all database connections are closed using

finally
{
conn.Close();
}

does anyone have any idea why I would be having this problem?

How can I see what connections the site is opening, or maybe theres a limit on my server?

I'm using my own test server running windows 2003 and IIS

Don't increase the connection timeout unless there is a REAL need (15 sec by default).

Here are good links will help you hopfully:

http://blogs.msdn.com/angelsb/archive/2004/08/25/220333.aspx

http://www.15seconds.com/issue/040830.htm

http://kb.seekdotnet.com/ViewArticle.aspx?ID=35

Good luck.

|||

As far as I can see I have NO leaking connections.

And I'm sure this is relating to the bug in visual studio because this happens when I try to access the page directly via IIS.

Does anyone else have any ideas?

Is there some way I can view connections and their state when I'm debugging the site?

|||

Apply the latest service pack.

Yes, you can know the status by using one of the connection object proerity (con.status or something).

Good luck.

|||

Checkout this link:http://geekswithblogs.net/chrishan/archive/2007/07/18/114030.aspx

I hope it will help you.

Good luck.

|||

Thanks, I dont seem to be any further forward though.

One thing I have noticed is that in the Output window of Visual Studio I get the following error repeated constantly.

"A first chance exception of type 'System.Data.SqlClient.SqlException'' occurred in System.Data.Dll"

|||

Surely someone can help with this?

Like I say, I don't believe this problem is with Visual Studio, as it occurs when I run the site directly on the server.

Here is the procedure which seems to be causing the problem, when I dont include this function the site runs fine, however when I include it it wont run and I get the error in my above post in the output window.

1protected void setConfigKeywords()2 {3// Define data objects4 SqlConnection conn;5 SqlCommand comm;6 SqlDataReader reader;78// Read the connection string from web.config9string connectionString = ConfigurationManager.ConnectionStrings["AWT"].ConnectionString;1011// Initialise the connection12 conn =new SqlConnection(connectionString);1314// Create command15 comm =new SqlCommand("SELECT * FROM Config WHERE ConfigID = 1", conn);1617try18 {19// Open the connection20 //conn.Open();2122 // Execute the command23 reader = comm.ExecuteReader();2425while (reader.Read())26 {27string title = reader["ConfigMetaTitle"].ToString();28string keywords = reader["ConfigMetaKeywords"].ToString();29string description = reader["ConfigMetaDesc"].ToString();30 }3132//Page.Title = title;33 //HtmlHead head = (HtmlHead)Page.Header;34 //Cls_Meta.setHeaderInfo(head, description, keywords);3536 // Close the reader37 reader.Close();3839 }40catch (Exception ex)41 {42string errorPage = ConfigurationManager.AppSettings["errorPage"];43string errorMsg ="Problem getting general keywords from database : " + ex;44 Server.Transfer(errorPage +"?errormsg=" + errorMsg);45 }46finally47 {48// Close the connection49 conn.Close();50 }51 }

getdate() problem: where is the time taken from ?

Hi,

I have a funny situation.
Within: MSSQL 2000 SP3, everything below described is running on same
PC.

there is a program running, which sends information to two other
programs.
This information is a timestamp of the program in datetime format,
which has it's own clock.
The clock is incremented each 5 seconds of the program, which
corespondes to aprox. one second of the real time.
It means, each on second of real time, the computer time is updated +5
seconds.

Now, two other applications, are getting this information at the same
moment.
FIRST of this applications, updates local time of the computer with
the time recieved.
SECOND application, writes a protocol to file, with timestamp read at
moment of writing from operating system.
Until now, all times are equal (the differences are not biger that
ms).

Now, the SECOND application, after writing a log into file (with
proper timestamp), calls SP in database.
It passes as input prm. the time recieved from very first program,
which is the same time as the current system time, which is the same
time the SECOND application writes to the log file.

This SP (besides other things) at the very beginning writes a log into
table, where two times are logged:
- getdate() to first column,
- timestamp recieved as input parameter.

Now the funny thing.
I would expect, the times are equal.
getdate() = '2007.04.25 10:00:00.000'
prm_recieved = '2007.04.25 10:00:00.000'

I would expect, that the time from getdate() will be shifted with
miliseconds (because of call etc).
getdate() = '2007.04.25 10:00:00.123'
prm_recieved = '2007.04.25 10:00:00.000'

I would even expect, that the time is shifted 5 seconds ahead:
getdate() = '2007.04.25 10:00:05.000'
prm_recieved = '2007.04.25 10:00:00.000'

or, 5 seconds and some miliseconds:
getdate() = '2007.04.25 10:00:05.123'
prm_recieved = '2007.04.25 10:00:00.000'

What I can not UNDERSTAND, why sometimes the time is equal, or
sometimes is ALMOST equal (within the diff of miliseconds), and why
sometimes the time is like this(!!!) :

getdate() = '2007.04.25 10:59:55.000'
prm_recieved = '2007.04.25 10:00:00.000'

It seams to me, the getdate is getting somehow the PERVIOUS local
system time, which was acctualy already upgraded ! Becasue all other
app's are having the proper value.

All other apps are writen in C++ and are very simple.
I was trying to set the SQLServer running property higher - with no
result.
I need to mention, there is SQLServer Agent running, and one procedure
with endless loop, with waitfor delay equal 2 seconds.
But non of them (changing the waitfor delay to other value, disabling
SQLAgent) fixes the problem.

Can somebody then tell me, where from is the time taken, or what is
the root problem of this issue?
Or what can it be?

Best regards,

MatikMatik (marzec@.sauron.xo.pl) writes:

Quote:

Originally Posted by

there is a program running, which sends information to two other
programs.
This information is a timestamp of the program in datetime format,
which has it's own clock.
The clock is incremented each 5 seconds of the program, which
corespondes to aprox. one second of the real time.
It means, each on second of real time, the computer time is updated +5
seconds.

So you have an application that modifies the computer clock every
second, and now you are asking why:

Quote:

Originally Posted by

What I can not UNDERSTAND, why sometimes the time is equal, or
sometimes is ALMOST equal (within the diff of miliseconds), and why
sometimes the time is like this(!!!) :
>
getdate() = '2007.04.25 10:59:55.000'
prm_recieved = '2007.04.25 10:00:00.000'

getdate() does not always reflect you recently updated system time.

I guess the answer is that there is not really reason that Windows and
SQL Server would behave the way you may want it to in this very special
scenario.

One reason that getdate() apparently lags behind is that getdate() has
a resolution of 3.33 ms which after all is quite a long time in a computer.
Assuming that SQL Server reads the system clock every 3.33 ms, getdate()
could seemingly lag behind your manipulated time.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||First, thanks Erland for the answer!

Quote:

Originally Posted by

So you have an application that modifies the computer clock every
second, and now you are asking why:

:) No ... of course, I'm not confused about the time changes :)
I'm expecting them ...
Confused for me, was this:

Quote:

Originally Posted by

Quote:

Originally Posted by

getdate() = '2007.04.25 10:59:55.000'
prm_recieved = '2007.04.25 10:00:00.000'

>
getdate() does not always reflect you recently updated system time.
>
I guess the answer is that there is not really reason that Windows and
SQL Server would behave the way you may want it to in this very special
scenario.
>
One reason that getdate() apparently lags behind is that getdate() has
a resolution of 3.33 ms which after all is quite a long time in a computer.
Assuming that SQL Server reads the system clock every 3.33 ms, getdate()
could seemingly lag behind your manipulated time.

Ok.
So ... that means for me as fallow:
The getdate() is not taking the current system time, only the buffered
SQLServer system time.
That means as well, that the time between changing system time,
writing into log (application) and calling procedure in DB, until this
position where the getdate() is called, MUST be shorter than a maximum
time of 3.33 ms.

Well, this is not I was thinking getdate() is doing:(
Is the current_timestamp function behaviour exactly in this way?
(probably yes, since in BOL says that this is the same as getdate())

Thank's Erland again for help.

Matik|||Matik (marzec@.sauron.xo.pl) writes:

Quote:

Originally Posted by

So ... that means for me as fallow:
The getdate() is not taking the current system time, only the buffered
SQLServer system time.

I like to stress that is my own speculation of how the internals work.

Quote:

Originally Posted by

That means as well, that the time between changing system time,
writing into log (application) and calling procedure in DB, until this
position where the getdate() is called, MUST be shorter than a maximum
time of 3.33 ms.

If my theory is correct, yes, this appears to be a correct conclusion.

Quote:

Originally Posted by

Well, this is not I was thinking getdate() is doing:(
Is the current_timestamp function behaviour exactly in this way?
(probably yes, since in BOL says that this is the same as getdate())

I would expect that CURRENT_TIMESTAMP to be just a synonym fot getdate(). It
would be funny if two equivalent functions are implemented in different
ways.

I also like to point out that this kind of behaviour that could be different
in different versions of SQL Server, or even in different service packs.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||

Quote:

Originally Posted by

Quote:

Originally Posted by

The getdate() is not taking the current system time, only the buffered
SQLServer system time.

>
I like to stress that is my own speculation of how the internals work.
>

Quote:

Originally Posted by

That means as well, that the time between changing system time,
writing into log (application) and calling procedure in DB, until this
position where the getdate() is called, MUST be shorter than a maximum
time of 3.33 ms.

>
If my theory is correct, yes, this appears to be a correct conclusion.

I was thinkig about one more thing ...
Is there any way, to force sql server to refresh it's time?
Let's say, that by the procedure call, I will force him, to refresh
it's time ... will it be possible somehow?

Matik|||Matik (marzec@.sauron.xo.pl) writes:

Quote:

Originally Posted by

I was thinkig about one more thing ...
Is there any way, to force sql server to refresh it's time?
Let's say, that by the procedure call, I will force him, to refresh
it's time ... will it be possible somehow?

Since all this is about behaviour that is strictly internal to SQL Server,
the likelyhood that there is a interface, documented or undocumented,
to affect this behaviour is about nil. Who knows, maybe there is a trace
flag, but don't stay up all night looking for it.

If you are on SQL 2005, you could write a CLR function which retrieves
the system time from Windows, with the regular 100 ns precision.

If you are on SQL 2000, you would have to write an extended stored
procedure, which may not be performant enough. (There is quite a cost
for the eontext switch.)

But getdate() seems dead in the water when you are living in the fast lane
like you do.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||

Quote:

Originally Posted by

to affect this behaviour is about nil. Who knows, maybe there is a trace
flag, but don't stay up all night looking for it.

I wont :) Have other things to do as well :)

Quote:

Originally Posted by

If you are on SQL 2005, you could write a CLR function which retrieves

I'm on 2000 ... right now ...

Quote:

Originally Posted by

If you are on SQL 2000, you would have to write an extended stored

This is what I was thinking of...

Quote:

Originally Posted by

procedure, which may not be performant enough. (There is quite a cost
for the eontext switch.)

This is what I was afraid of :(

Quote:

Originally Posted by

But getdate() seems dead in the water when you are living in the fast lane
like you do.

I just need to change aproach probably, and try to solve it in
different way.
Probably the solution is what I did right now ...
Just, a the beginning, I'm sending procedure to sleep (waitfor delay).
It is not nice, and slows everything down, but maybe ... will be fast
enougth...
Otherwise ... try to do smth. else.

Thanks Erland again for your help and patience.

Best regards

Matik

getDate not working in function

Hi,
Using MSSQL 2000
How can I get hold of the current datetime inside a function?
declare @.dt datetime;
select @.dt = getdate();
gives an "Invalid use of 'getdate' within a function" error.
Any help will be greatly appreciated.
Regards,
arnoudgot it
select @.dt = dbo.GETDATE();
does the job.
"arnoud oortwijk" <abc> wrote in message
news:ONNpGsWzFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Using MSSQL 2000
> How can I get hold of the current datetime inside a function?
> declare @.dt datetime;
> select @.dt = getdate();
> gives an "Invalid use of 'getdate' within a function" error.
> Any help will be greatly appreciated.
> Regards,
> arnoud
>|||No. That would reference a user-defined function called dbo.GETDATE. I
don't think that's what you wanted.
You can't use the system function GETDATE() in a user-defined function.
Easiest method is to pass the time as a parameter.
David Portas
SQL Server MVP
--|||David
> Easiest method is to pass the time as a parameter
I think you meant
Easiest method is to pass the GETDATE() function as a parameter
:-)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128929659.370492.41730@.g14g2000cwa.googlegroups.com...
> No. That would reference a user-defined function called dbo.GETDATE. I
> don't think that's what you wanted.
> You can't use the system function GETDATE() in a user-defined function.
> Easiest method is to pass the time as a parameter.
> --
> David Portas
> SQL Server MVP
> --
>

get triggers latest update date

Is there a way in MSSQL 2000 to get trigger's latest update date?
sysobject table only has creation date of a trigger and I've been using ALTER TRIGGER command to modify it.
Thanks,
IgorUnfortunately, no. You can't get the last modified date for any SQL Server objects. You might instead do a DROP and CREATE when modifying your triggers.

Terri

get the data directory

Hi,

I am searching for how getting the data directory where default mdf files are based.

(C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data)

My goal is to deploy mdf file in this folder during installation.

Thanks

hi,

I'm little in trouble giving advieces as well as registry values are not honored as the seem..

you can query, via a NON documented extended stored procedure the Windows registry for some info..

you can see a HKLM\Microsoft\Microsoft SQL Server key..

SQL Server 2005 registers instances as MSSQL.1, MSSQL.n, and this for every engine type, like Report server, Olap engine, ...

but you can get the MSSQL.x value querying the \Instance Names\SQL key as well

something like

SET NOCOUNT ON;

DECLARE @.test varchar(256);

DECLARE @.instance VARCHAR(128);

DECLARE @.regKey VARCHAR(128);

SELECT @.instance = CONVERT(varchar, SERVERPROPERTY('InstanceName'));

IF @.instance IS NULL

SET @.regKey = 'MSSQLServer';

ELSE

SET @.regKey = @.instance;

SELECT @.regKey AS [Instance name];

EXEC master..xp_regread @.rootkey='HKEY_LOCAL_MACHINE',

@.key = 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL\',

@.value_name = @.regKey,

@.value = @.test OUTPUT;

SELECT @.test AS [base instance directory];

SET @.regKey = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @.test + '\Setup';

EXEC master..xp_regread @.rootkey='HKEY_LOCAL_MACHINE',

@.key = @.regKey ,

@.value_name = 'SQLDataRoot',

@.value = @.test OUTPUT;

SELECT @.test AS [SQL Path as per Setup key];

--<-

Instance name

MSSQLServer

base instance directory

MSSQL.1

SQL Path as per Setup key

--

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL

but here problems arise... as usually data folder is in \...\MSSQL.1\MSSQL\Data , but that Data part is not shown anywhere.. .you can "perhaps" going littbe bit further, querying the \Parameters\SQLArg0 , which reports the full path of the master.mdf file formatted as the startup parameter required by SQL Server, thats to say

SQLArg0 = "-dC:\Program Files\..here full path.......\master.mdf"

and parse the result to extract the required "path".. or just hope no one changes that path and hardcode a + '\Data\' addition...

but, again, this whole post is in complete undocumented and unsupported mode..

regards

|||Thanks for your help Andrea