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 }

Getting a list of user access to which databases - help

Hi ,
i know there's a view "sxyslogin" in Master database that is able to show a
list of user with the default database that they have access to
however , i like to get a list of users with all the databases that they are
able to access, how can i do that with the rights that they have in these
databases as well ?
for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
has the access to these users
appreciate any advise
tks & rdgs
Hi,
Execute the system procedure sp_helplogin to get all the users with
associated access to databases.
For displaying object level previlages for the user , execute sp_helprotect.
See the reference of both procedures in books online.
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:455416BC-1ED3-4F69-B9DB-E17A078A7C79@.microsoft.com...
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they
are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that
userA
> has the access to these users
> appreciate any advise
> tks & rdgs
|||You can check the stored procedure sp_helplogins
best Regards,
Chandra
http://chanduas.blogspot.com/
"maxzsim" wrote:

> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
> has the access to these users
> appreciate any advise
> tks & rdgs
|||Here you go this query will match up the sysdatabases which is the list of
databases you need with the sysusers information which will give you a
results set of databases and the user for that database. Then if you want to
go a little further and match that sid with sysxlogins if you need some
information from there. The in clause makes it where you dont have to see
all the user information for sql internal usage.
Hope this helps.
Select a.name,
b.[name],
b.[UID],
b.[SID],
b.[ISSQLROLE],
CASE WHEN b.[ISSQLUSER] = 1
THEN 1
ELSE 0
END AS issqluser
from [master].[dbo].[sysdatabases] a ,
[master].[dbo].[sysusers] b
WHERE b.[name] NOT IN (
'public',
'db_owner',
'db_accessadmin',
'db_securityadmin',
'db_ddladmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_denydatareader',
'db_denydatawriter',
'dbo',
'guest',
'INFORMATION_SCHEMA',
'system_function_schema'
)
"maxzsim" wrote:

> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
> has the access to these users
> appreciate any advise
> tks & rdgs

Getting a list of user access to which databases - help

Hi ,
i know there's a view "sxyslogin" in Master database that is able to show a
list of user with the default database that they have access to
however , i like to get a list of users with all the databases that they are
able to access, how can i do that with the rights that they have in these
databases as well ?
for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
has the access to these users
appreciate any advise
tks & rdgsHi,
Execute the system procedure sp_helplogin to get all the users with
associated access to databases.
For displaying object level previlages for the user , execute sp_helprotect.
See the reference of both procedures in books online.
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:455416BC-1ED3-4F69-B9DB-E17A078A7C79@.microsoft.com...
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they
are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that
userA
> has the access to these users
> appreciate any advise
> tks & rdgs|||You can check the stored procedure sp_helplogins
--
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"maxzsim" wrote:
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
> has the access to these users
> appreciate any advise
> tks & rdgs|||Here you go this query will match up the sysdatabases which is the list of
databases you need with the sysusers information which will give you a
results set of databases and the user for that database. Then if you want to
go a little further and match that sid with sysxlogins if you need some
information from there. The in clause makes it where you dont have to see
all the user information for sql internal usage.
Hope this helps.
Select a.name,
b.[name],
b.[UID],
b.[SID],
b.[ISSQLROLE],
CASE WHEN b.[ISSQLUSER] = 1
THEN 1
ELSE 0
END AS issqluser
from [master].[dbo].[sysdatabases] a ,
[master].[dbo].[sysusers] b
WHERE b.[name] NOT IN (
'public',
'db_owner',
'db_accessadmin',
'db_securityadmin',
'db_ddladmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_denydatareader',
'db_denydatawriter',
'dbo',
'guest',
'INFORMATION_SCHEMA',
'system_function_schema'
)
"maxzsim" wrote:
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
> has the access to these users
> appreciate any advise
> tks & rdgs

Getting a list of user access to which databases - help

Hi ,
i know there's a view "sxyslogin" in Master database that is able to show a
list of user with the default database that they have access to
however , i like to get a list of users with all the databases that they are
able to access, how can i do that with the rights that they have in these
databases as well ?
for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
has the access to these users
appreciate any advise
tks & rdgsHi,
Execute the system procedure sp_helplogin to get all the users with
associated access to databases.
For displaying object level previlages for the user , execute sp_helprotect.
See the reference of both procedures in books online.
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:455416BC-1ED3-4F69-B9DB-E17A078A7C79@.microsoft.com...
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they
are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that
userA
> has the access to these users
> appreciate any advise
> tks & rdgs|||You can check the stored procedure sp_helplogins
--
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"maxzsim" wrote:

> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they a
re
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that user
A
> has the access to these users
> appreciate any advise
> tks & rdgs|||Here you go this query will match up the sysdatabases which is the list of
databases you need with the sysusers information which will give you a
results set of databases and the user for that database. Then if you want t
o
go a little further and match that sid with sysxlogins if you need some
information from there. The in clause makes it where you dont have to see
all the user information for sql internal usage.
Hope this helps.
Select a.name,
b.[name],
b.[UID],
b.[SID],
b.[ISSQLROLE],
CASE WHEN b.[ISSQLUSER] = 1
THEN 1
ELSE 0
END AS issqluser
from [master].[dbo].[sysdatabases] a ,
[master].[dbo].[sysusers] b
WHERE b.[name] NOT IN (
'public',
'db_owner',
'db_accessadmin',
'db_securityadmin',
'db_ddladmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_denydatareader',
'db_denydatawriter',
'dbo',
'guest',
'INFORMATION_SCHEMA',
'system_function_schema'
)
"maxzsim" wrote:

> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they a
re
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that user
A
> has the access to these users
> appreciate any advise
> tks & rdgs

GetDate()

I would like to exec master..xp_fixeddrives which will give you the list of
physical drives sqlserver sees. Output is Drives, FreeSpace from
xp_fixeddrives.
I would like to write this information to a table with Drive,
FreeSpace,SampleTime.
The SampleTime will be the getdate() function.
Please help me with this issue.
ThanksCREATE TABLE #DriveData
(
Drive CHAR(2),
FreeSpace BIGINT,
SampleTime DATETIME NOT NULL DEFAULT GETDATE()
);
INSERT #DriveData
(
Drive,
FreeSpace
)
EXEC master..xp_fixeddrives;
SELECT Drive, FreeSpace, SampleTime
FROM #DriveData;
DROP TABLE #DriveData;
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:23A81366-D8DD-4833-B25C-96DF0561090D@.microsoft.com...
>I would like to exec master..xp_fixeddrives which will give you the list of
> physical drives sqlserver sees. Output is Drives, FreeSpace from
> xp_fixeddrives.
> I would like to write this information to a table with Drive,
> FreeSpace,SampleTime.
> The SampleTime will be the getdate() function.
> Please help me with this issue.
> Thanks
>
>|||CREATE TABLE #diskspace(
drivename VARCHAR(50),
MBFree INT)
INSERT INTO #diskspace(drivename,MBFree) exec master..xp_fixeddrives
SELECT * FROM #diskspace|||CREATE TABLE #diskspace(
drivename VARCHAR(50),
MBFree INT)
INSERT INTO #diskspace(drivename,MBFree) exec master..xp_fixeddrives
SELECT * FROM #diskspace|||Note the addition of the date field to the table.
CREATE TABLE #diskspace(
drivename VARCHAR(50),
MBFree INT,
sampledate DATETIME default getdate()
)sql

Get MIN ROW from two tables

I have two tables 'table1' and 'table2' which have a common column column as master child relationship.

Table1 Table2
cust_id cust_id
cust_name idx
info

Please suggest query that joins these tables but returns only min(idx) from the 'table2' for each cust_id (one row each).

Thanks
Vinnieselect t1.cust_id, idx = min(t2.idx)
from Table1 t1
join Table2 t2
on t1.cust_id = t2.cust_id
group by t1.cust_id

If some don't have children then just change join to left outer join