Procedure stops in .NET application

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Procedure stops in .NET application

Author

Topic

martind1
Starting Member

28 Posts

Posted - 2012-08-10 : 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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-08-10 : 05:01:15

best advice is to fire up sql serverprofiler on the sql server then run the application version of the update.

http://www.google.co.uk/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&sqi=2&ved=0CCEQFjAA&url=http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms181091.aspx&ei=ys0kUKiDJInT0QWSq4DICg&usg=AFQjCNHqAcM2nSfDP81KaUhYDgeRk6pfDA

You'll see exactly what is issued to the db.

My guess is that you have contention / blocking caused by some connection. Possibly you are trying to read from and update the table(s) at the same time.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

martind1
Starting Member

28 Posts

Posted - 2012-08-10 : 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

28 Posts

Posted - 2012-08-10 : 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
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-08-10 : 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

28 Posts

Posted - 2012-08-10 : 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
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-08-10 : 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

28 Posts

Posted - 2012-08-11 : 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
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-08-14 : 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

28 Posts

Posted - 2012-08-15 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources