Odd ExecuteReader Error

Finally run out of ideas on this one....

Just to get it outta the way, the environment:
2 NLB Web servers (each Win2003, IIS6, 2G memory, 2 physical/4 logical
CPU, 2 NICs, Unicast)
1 DB Server (Win2K, SQL2K, 2G memory, 2 physical/logical.. no
hyperthreading)

I'm periodically getting a "Timeout Expired..." error on
SqlCommand.ExecuteReader for VERY simply INSERTs and UPDATEs (no
stored procedures) ex. INSERT INTO MyTable (fields) VALUES(values),
UPDATE MyTable SET... WHERE MyTableKey=@MyKeyValue

It happens on a variety of tables, none of which have triggers. The
SQLCommand timeout value is set to 60. SQL Server is showing no
blocks, long lock wait times, or anything else that would indicate a
problem on the DB server. No backups or replication snapshots at
these time either. In fact, the DB server is humming along nicely.

Also, using non-pooled connections or pooled connections does not seem
to make a difference.

There are some strange things:

First, only seeing it at the top of the hour, but there is nothing
specifically running at the top of the hour I can find. (no jobs,
recycling of app pool, other services running) Doesn't seem to be
related to load (sometimes happens at peak, other times off peak (ex.
8pm)

Second, only happens on sites that are NLB... there is an asp.net
site on one of the servers (same DB, similar C# code) which does NOT
use NLB and does not see the errors.

The only thing I can see that COULD be a problem is that the DB
Server's Subnet is the same as Web Servers Virtual and Dedicated IPs
on NIC #1 (versus our other subnet on Web Servers' NIC #2) I'd love
to blame this, but doesn't explain the consistent time of day when I
see this.

Any ideas?
Thanks,
-- JD

Carol
Tue Oct 28 15:53:46 CST 2003

Hi,
The ExecuteReader method of the Command object does only
one thing: Retrieves data based on an adhoc SQL statement
or a stored procedure and places it in the SQLDataReader
object to read forward only. No updating occurs with this
method. Also, you need to close it immediately when you
are done reading the data to release the connection object.

The ExecuteNonQuery method is probably what you are
looking for.

I hope this helps. My email has 2 "k"s in it. Carol

>-----Original Message-----
>Finally run out of ideas on this one....
>
>Just to get it outta the way, the environment:
>2 NLB Web servers (each Win2003, IIS6, 2G memory, 2
physical/4 logical
>CPU, 2 NICs, Unicast)
>1 DB Server (Win2K, SQL2K, 2G memory, 2
physical/logical.. no
>hyperthreading)
>
>I'm periodically getting a "Timeout Expired..." error on
>SqlCommand.ExecuteReader for VERY simply INSERTs and
UPDATEs (no
>stored procedures) ex. INSERT INTO MyTable (fields) VALUES
(values),
>UPDATE MyTable SET... WHERE MyTableKey=@MyKeyValue
>
>It happens on a variety of tables, none of which have
triggers. The
>SQLCommand timeout value is set to 60. SQL Server is
showing no
>blocks, long lock wait times, or anything else that would
indicate a
>problem on the DB server. No backups or replication
snapshots at
>these time either. In fact, the DB server is humming
along nicely.
>
>Also, using non-pooled connections or pooled connections
does not seem
>to make a difference.
>
>There are some strange things:
>
>First, only seeing it at the top of the hour, but there
is nothing
>specifically running at the top of the hour I can find.
(no jobs,
>recycling of app pool, other services running) Doesn't
seem to be
>related to load (sometimes happens at peak, other times
off peak (ex.
>8pm)
>
>Second, only happens on sites that are NLB... there is
an asp.net
>site on one of the servers (same DB, similar C# code)
which does NOT
>use NLB and does not see the errors.
>
>The only thing I can see that COULD be a problem is that
the DB
>Server's Subnet is the same as Web Servers Virtual and
Dedicated IPs
>on NIC #1 (versus our other subnet on Web Servers' NIC
#2) I'd love
>to blame this, but doesn't explain the consistent time of
day when I
>see this.
>
>Any ideas?
>Thanks,
>-- JD
>.
>

jdrewes
Wed Oct 29 10:29:54 CST 2003

Thanks for the response.

We use ExecuteReader for our inserts/updates so that we can return
primary keys or other pertinent information... example INSERT
INTO..... SELECT SCOPE_IDENTITY(). ExecuteNonQuery only returns the
number of rows, no? Probably wouldn't work in our current
application.

An update... we have seen the same error in a non NLB application.
It was probably dumb luck that it hasn't happened in the other app.
So still perplexed about the time of day, but clearly SOMETHING must
be happening at the top of the hour that I haven't been able to
discover.

-- JD

"Carol Torkko" <ctorkkko@mindspring.com> wrote in message news:<033701c39d9d$f65e3020$a501280a@phx.gbl>...
> Hi,
> The ExecuteReader method of the Command object does only
> one thing: Retrieves data based on an adhoc SQL statement
> or a stored procedure and places it in the SQLDataReader
> object to read forward only. No updating occurs with this
> method. Also, you need to close it immediately when you
> are done reading the data to release the connection object.
>
> The ExecuteNonQuery method is probably what you are
> looking for.
>
> I hope this helps. My email has 2 "k"s in it. Carol
>
> >-----Original Message-----
> >Finally run out of ideas on this one....
> >
> >Just to get it outta the way, the environment:
> >2 NLB Web servers (each Win2003, IIS6, 2G memory, 2
> physical/4 logical
> >CPU, 2 NICs, Unicast)
> >1 DB Server (Win2K, SQL2K, 2G memory, 2
> physical/logical.. no
> >hyperthreading)
> >
> >I'm periodically getting a "Timeout Expired..." error on
> >SqlCommand.ExecuteReader for VERY simply INSERTs and
> UPDATEs (no
> >stored procedures) ex. INSERT INTO MyTable (fields) VALUES
> (values),
> >UPDATE MyTable SET... WHERE MyTableKey=@MyKeyValue
> >
> >It happens on a variety of tables, none of which have
> triggers. The
> >SQLCommand timeout value is set to 60. SQL Server is
> showing no
> >blocks, long lock wait times, or anything else that would
> indicate a
> >problem on the DB server. No backups or replication
> snapshots at
> >these time either. In fact, the DB server is humming
> along nicely.
> >
> >Also, using non-pooled connections or pooled connections
> does not seem
> >to make a difference.
> >
> >There are some strange things:
> >
> >First, only seeing it at the top of the hour, but there
> is nothing
> >specifically running at the top of the hour I can find.
> (no jobs,
> >recycling of app pool, other services running) Doesn't
> seem to be
> >related to load (sometimes happens at peak, other times
> off peak (ex.
> >8pm)
> >
> >Second, only happens on sites that are NLB... there is
> an asp.net
> >site on one of the servers (same DB, similar C# code)
> which does NOT
> >use NLB and does not see the errors.
> >
> >The only thing I can see that COULD be a problem is that
> the DB
> >Server's Subnet is the same as Web Servers Virtual and
> Dedicated IPs
> >on NIC #1 (versus our other subnet on Web Servers' NIC
> #2) I'd love
> >to blame this, but doesn't explain the consistent time of
> day when I
> >see this.
> >
> >Any ideas?
> >Thanks,
> >-- JD
> >.
> >