Min pool size

Hello Experts,

I make a connection with .Net Sqlclient Data Provider to
my server and I adjust the min pool size = 0, but when I
check the sysprocesses I see that 2 connections have been
made. Why is that? am I missing something here?

Conn. string
"server=MYSERVER;user id=sa;database=MYDB;min pool
size=0;max pool size=10;packet size=3072;Application
Name=MYAPP"

Thanks in advance

Michas Konstantinos
SQL Sewrver Developer, DBA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Konstantinos Michas (k_michas@.hotmail.com) writes:
> I make a connection with .Net Sqlclient Data Provider to
> my server and I adjust the min pool size = 0, but when I
> check the sysprocesses I see that 2 connections have been
> made. Why is that? am I missing something here?
> Conn. string
> "server=MYSERVER;user id=sa;database=MYDB;min pool
> size=0;max pool size=10;packet size=3072;Application
> Name=MYAPP"

Min pool size = 0 is the default, so getting two connections is
nothing strange. If you would set Max pool size = 1, you would get
slapped in the face, when you try to get a second connection.

If you have two connections, but you are only expecting one, you are
probably not closing the connection properly. Be sure if you use
ExecuteReader to get all rows and all result sets.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Miltivalue Parameters with Max Pool connections

I am trying to write a report with many different records needed (way the database was designed). Client will need multivalue parameter and I have reached max pool connections(can anyone please give me the number).

Normally I would handle this with a stored procedure to create a temp table with the needed information for each section(one row per section), but this will not work with the multivalued parameters(more than one in this report).

Any help on this issue would be appreciated.

Thanks!

Terry

I'm not sure I understand the issue why you are running out of connections, but if all datasets are based on the same data source, you could select the "Use Single Transaction" checkbox on the data source dialog. In that case, all datasets running against that data source will use the same connection. See also: http://msdn2.microsoft.com/en-us/library/ms181198.aspx

-- Robert

|||

I have found the data source, but not the single transaction area. Any help in this area would be appreciated. Will have to solve the multivalue parameter issue later. Idea without multivalue parameter would be a stored procedure to fix this.

Have one other idea with @.t table useage.

Thanks for the information.

Terry

|||

The "Use single transaction" checkbox is only available in report designer. You cannot change this setting once the report is published.

-- Robert

|||

That answered the question. I will have to find it at the start of the development for a new system.

Thanks!

Terry