asked on

Ms Sql .Net - timeout

Hello,
I have a stored procedure. When I execute it in Sql Server Management studio it takes less then 1 second to return data.
It returns 21 rows and 13 columns.
When I call this procedure from .Net I get TimeOut, even though I extended the CommandTimout to 60 seconds.
I have a parameter in the stored procedure and when set to true it returns the first 7 columns instead of 13.
The strange thing is that when I only get the first 7 columns I do not get timeout and it takes very short time to load RadGrid with the data. I have no idea why this can happens. I hope someone can help me with this.

Attached is an excel document with the result from the query.

Greetings
sql_result.xlsx

ASKER CERTIFIED SOLUTION

Russ Suter

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ste5an

The problem is the changing output format. This means that your procedure needs to be recompiled. Force that recompile:

CREATE PROC foo
(
  @PassedVariable varchar(50)
)
WITH RECOMPILE
AS
  SET NOCOUNT ON;
  -- your code.

Open in new window

Éric Moreau

just like Russ, I am convinced that your issue is related to parameter sniffing. If you want to read more about this, have a look at http://www.sqlpointers.com/2006/11/parameter-sniffing-stored-procedures.html

johnson1

ASKER

Thank you very much. You saved the day.I would never have thought of this:-)