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.
3
-
one more thing I don't have any transaction.when I get this error I can run the SP in SqlServer management studio.Pers– Pers2012-08-09 06:02:15 +00:00Commented Aug 9, 2012 at 6:02
-
you said you sometime get this error, is this right?NG.– NG.2012-08-09 07:57:34 +00:00Commented Aug 9, 2012 at 7:57
-
yes sometimes..when I get this error I run 'Alter Proc ' command once and it works then again.Pers– Pers2012-08-09 08:47:31 +00:00Commented Aug 9, 2012 at 8:47
Add a comment
|
5 Answers
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
1 Comment
Pers
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.
You can try
cmd.CommandTimeout = 0;
if you are executing a query taking long time.
Comments
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)
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.
Comments
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.
