SP resulting in Timeout expired

[Grace]

Hello,

From a VB .net application I'm calling an SQL stored
procedure and filling the results in an SQLDataReader,
whose results are returned to an ASP .Net page.
The procedure was running fine and all of a sudden it
started giving this error:
System.Data.SqlClient.SqlException: Timeout expired. The
timeout period elapsed prior to completion of the
operation or the server is not responding.

When I run the stored procedure in Query Analyzer it takes
1 sec. I tried to monitor it in Profiler by adding the
Stored Procedure Events, and I found that it is getting
stuck at a statement that goes similar to the following:

INSERT INTO #Tempf
SELECT t.SerNo, t.Name
FROM #Temp t
WHERE t.Value=x

#Tempf and #Temp are created explicitly at the beginning
of the procedure. #Temp contains around 16,000 records.
This statement is not being completed. I have no idea
about what the error might be from and I would appreciate
any help or idea about what might be causing this error.


Thanks,
Grace

 

[William Ryan]

Grace:

How are you updating Stored Procs with a DataReader?

I think that may be your problem, b/c it's probably firing the first part of
the Proc with Select statements, but it can't update a DB so that's probably
where it's giving you an error.

Take out the update from the proc and see if it runs correctly. You may
need to split it out and fire an executeNonQuery from the command object.

Cheers,

Bill

 

[Uri Dimant]

Grace
Do you have any indexes on TEMP table ?
If you are sitting on SQL Server 2000 perhaps you need to use table variable
to improve perfomance issue.

 

[Grace]

I'm not sure if I understood you correctly, what I'm doing
in the stored procedure is selecting a number of records
inserting them into a #Temp table according to a certain
criteria by using EXEC sp_executesql. Then doing an insert
into #Tempf from #Temp according to another criteria.
Finally Selecting what is now in #Tempf. The datareader is
only reading the records returned from #Tempf. No update
is taking place.

Thanks for your help,
Grace