SQLXML Issues

microsoft.public.dotnet.framework.adonet

9 Nov 2005 10:08 PM

Joseph George

We have the following code in the code behind of an ASPX page.

SqlXmlCommand cmd = new SqlXmlCommand(strSQLXMLConString);
cmd.BasePath    =    ConfigurationSettingsHelper.AppSettings("BasePathForSQLXML837D");
cmd.CommandType    = SqlXmlCommandType.Template ;
//...intermediate code ommited for clarity//
cmd.CommandStream = stream;
s = cmd.ExecuteStream();

PostProcess(s,strFileName);
s.Close();
stream.Close();

There are a large number of users hitting this page. The connection
string is stored in the web.config as -->
"Provider=SQLOLEDB;Server=ipaddress,1433;uid=username;
pwd=password;database=dbname"

What we're seeing is that connection pooling is not fully working with
this code.

When I run the following query on the database at 2:57PM :

Select
login_time, last_batch
from sysprocesses
where
dbid = 5
and ....
order by login_time desc

I get these results (edited for clarity):

login_time                      last_batch
~~~~~~~~~~~~~~~~~   ~~~~~~~~~~~~~~~~~~
2005-11-09 14:57:26.827 2005-11-09 14:57:33.700
2005-11-09 14:57:21.530 2005-11-09 14:57:29.107
2005-11-09 14:57:17.670 2005-11-09 14:57:25.233
2005-11-09 14:57:16.857 2005-11-09 14:57:25.890
2005-11-09 14:57:10.547 2005-11-09 14:57:31.217
2005-11-09 14:57:09.890 2005-11-09 14:57:29.500
2005-11-09 14:57:00.700 2005-11-09 14:57:26.623
2005-11-09 14:56:35.607 2005-11-09 14:57:06.373
2005-11-09 14:56:26.967 2005-11-09 14:57:29.187
2005-11-09 14:56:14.420 2005-11-09 14:57:22.343
2005-11-09 14:56:08.230 2005-11-09 14:57:23.577
2005-11-09 14:56:08.170 2005-11-09 14:57:29.280
2005-11-09 14:55:37.700 2005-11-09 14:57:23.233
2005-11-09 14:55:28.513 2005-11-09 14:57:22.343
2005-11-09 14:55:23.183 2005-11-09 14:57:30.903
2005-11-09 14:54:45.700 2005-11-09 14:57:28.797
2005-11-09 14:54:44.937 2005-11-09 14:57:32.547
2005-11-09 14:54:39.687 2005-11-09 14:57:33.093
2005-11-09 14:54:39.687 2005-11-09 14:57:30.060
2005-11-09 14:50:43.373 2005-11-09 14:57:19.513
2005-11-09 14:50:09.170 2005-11-09 14:55:22.293
2005-11-09 14:49:38.653 2005-11-09 14:57:07.170
2005-11-09 14:49:06.077 2005-11-09 14:57:23.717
2005-11-09 14:48:51.920 2005-11-09 14:57:20.297
2005-11-09 14:48:49.843 2005-11-09 14:57:09.000
2005-11-09 14:48:26.043 2005-11-09 14:57:28.170
2005-11-09 14:47:50.483 2005-11-09 14:57:14.060
2005-11-09 14:47:31.513 2005-11-09 14:57:22.733
2005-11-09 14:46:42.857 2005-11-09 14:57:21.640
2005-11-09 14:46:24.687 2005-11-09 14:56:34.107
2005-11-09 14:46:21.967 2005-11-09 14:57:20.483
2005-11-09 14:45:18.297 2005-11-09 14:57:25.200
2005-11-09 14:45:07.280 2005-11-09 14:55:36.170
2005-11-09 14:43:26.967 2005-11-09 14:57:28.263
2005-11-09 14:37:39.950 2005-11-09 14:56:08.497

Why is it that some connections are re-used while new connections are
also being generated all the time? I see that new connections are
getting created every minute. This is choking the database server and
we're getting "General network error" and "Timeout expired" errors on
the website. I'm also seeing that for all of the above connections,
the sysprocesses.hostname is = "Internet Information Services" !!

Can anyone please provide insights on this?

Some general Info: We use windows 2003, .net fx 1.1SP1, SQLXML SP3

Thanks,
JGP