1

I have a SP which takes 20 seconds in SqlServer environment but sometimes in my ASP.NET page when I run the SP I get SqlServer timeout excaption. I event set CommandTimeout and ConnectionTimeout to 60 but I still get the exception. I would appreciate to help me.

Pers
  • 31
  • 2
  • one more thing I don't have any transaction.when I get this error I can run the SP in SqlServer management studio. – Pers Aug 09 '12 at 06:02
  • you said you sometime get this error, is this right? – NG. Aug 09 '12 at 07:57
  • yes sometimes..when I get this error I run 'Alter Proc ' command once and it works then again. – Pers Aug 09 '12 at 08:47

5 Answers5

1

Some other operation might be locking the table. set the timeout to a higher value and check.

while running the proc execute sp_lock and sp_who2 system procedure for any locking

Joe G Joseph
  • 23,518
  • 5
  • 56
  • 58
  • There is no lock on tables because when I get the exception I can run the SP in management studio but in my webpage I see timeout error and what's more after run alter SP my webpage works. – Pers Aug 09 '12 at 06:24
0

You can try 

  cmd.CommandTimeout = 0;

if you are executing a query taking long time.

Mainuddin
  • 416
  • 5
  • 15
0

1) tried something like??

SqlCommand cmd = new SqlCommand("MyReport", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandTimeout = 3660; //// or set it zero (0)

2) and this???

3) Assuming your db server and point of execution are different, is your internet/intranet connectivity fine?
4) check for vpn connection (if used)

NG.
  • 5,695
  • 2
  • 19
  • 30
  • But the SP takes 20 seconds in the worst state and I'm wondering why it doesn't return data in my ASP.NET page and if simultaneously I run the SP in management studio it works very well. – Pers Aug 09 '12 at 08:10
  • checked internet/vpn conectivity?? – NG. Aug 09 '12 at 08:33
0

Execute the query from the SSMS and save the execution plan. Then run the application and have the SQL Profiler to capture the trace and then save the execution plan from profiler as mentioned in this link.

Compare the two execution plan to find out the actual difference in execution.

Check for parameter sniffing. If you still have the issue make sure the DB statistics are updated, sometimes this might be the issue after that drop and create the procedure.

Community
  • 1
  • 1
user824910
  • 1,067
  • 6
  • 16
  • 38
0

I think the problem is the sending parameters from your application to store procedure. try it again but this time use SQL Server Profiler to trace your query execution . you can use TextData column value in SQL Server Profiler and run real executed query again to find the real problem.

Milad
  • 117
  • 1
  • 8