| Author |
 Topic  |
|
|
martind1
Starting Member
United Kingdom
28 Posts |
 Posted - 08/10/2012 : 03:59:52
|
Hi,
I have got a procedure setup and when I call it from SQL managerment studio, it runs absolutely fine in under 2 seconds.
The procedure performs lots of updates on a table, runs fine in Management studio.
However, when its called from a .NET application, it just hangs for over 6 hours, and never completes.
The procedure has no parameters.
I have tried adding WITH RECOMPILE to the procedure but had no effect.
I have looked into the running query stats and it isn't making any more reads or writes after a certain amount of time.
I have also looked into sp_who2 and it has 2 UPDATE commands marked as RUNNABLE and 3 marked as SUSPENDED.
Its really got me stumped.
Any suggestions?
--
http://www.tutorial-resource.com - Free Web Development Resources |
Edited by - martind1 on 08/10/2012 04:04:42
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3438 Posts |
|
|
martind1
Starting Member
United Kingdom
28 Posts |
Posted - 08/10/2012 : 05:08:36
|
I will take a look at the trace now and let you know my results :)
--
http://www.tutorial-resource.com - Free Web Development Resources |
 |
|
|
martind1
Starting Member
United Kingdom
28 Posts |
Posted - 08/10/2012 : 07:35:55
|
Trace revealed no results to the problem.
Going to try it on another server see if it works then.
--
http://www.tutorial-resource.com - Free Web Development Resources |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3438 Posts |
Posted - 08/10/2012 : 08:48:50
|
if it's hanging and not doing anything there will be something happening....
You could trigger the change (via application)
and then in management studio run
EXEC sp_who2
and look for blocking jobs?
Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
|
martind1
Starting Member
United Kingdom
28 Posts |
Posted - 08/10/2012 : 09:02:45
|
Ive ran sp_who2 while the procedure runs or hangs, and there is no blocking queries. Its just very odd.
--
http://www.tutorial-resource.com - Free Web Development Resources |
|
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1608 Posts |
Posted - 08/10/2012 : 13:18:55
|
post the .net code from connection string onward
<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion |
|
|
|
martind1
Starting Member
United Kingdom
28 Posts |
Posted - 08/11/2012 : 03:08:24
|
Here is my c# code. All other procedures called from the application use exactly the same code, just a different procedure name.
string connectionString = ConfigurationManager.AppSettings["DatabaseCS"].ToString();
SqlConnection sqlConnection = new SqlConnection(connectionString);
SqlCommand sqlCommand = new SqlCommand("DisableDuplicatePackages", sqlConnection);
sqlCommand.CommandTimeout = 0;
sqlCommand.CommandType = CommandType.StoredProcedure;
try
{
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
Exceptions.LogException(ex);
}
finally
{
sqlConnection.Close();
}
--
http://www.tutorial-resource.com - Free Web Development Resources |
|
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1608 Posts |
Posted - 08/14/2012 : 12:18:14
|
if they all use the same c# call then the issue is most definitely in your sproc. if you are able to post that or show us the pseudocode for it
<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion |
|
|
|
martind1
Starting Member
United Kingdom
28 Posts |
Posted - 08/15/2012 : 05:01:13
|
Hi,
I have solved the issue by re-writing the stored procedure to do it a different way. It was updating a table and doing a select at the same time. Now I'm just grouping to get a unique id instead. Still doesn't explain why it worked in management studio and not in the .net application.
Anyway, problem solved, kind of lol :)
Cheers
--
http://www.tutorial-resource.com - Free Web Development Resources |
|
|
| |
Topic |
|
Procedure stops in .NET application
