Time Out Problem - SQL Server Forums

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 2005 Forums

SQL Server Administration (2005)

Time Out Problem

Author

Topic

sanjnep
Posting Yak Master

191 Posts

Posted - 2009-01-14 : 17:47:29

I have a store procedure and contains 2 update statements like

create procedure sp_update
as
begin tran
update table A
update table B
commit tran

When I ran it from Query Analyzer it runs fine but when I ran it from .NET I am getting timeout expire but still all the SQL command inside store procedure ran successfully. Can you help me plz to find the solution?
Thanks
ss

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-14 : 17:52:51

Could you post the actual stored procedure as it is hard for us to help with what you have posted?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sanjnep
Posting Yak Master

191 Posts

Posted - 2009-01-14 : 18:05:04

create procedure sp_fromsisyio
@id int,@nsnumber int
as

declare @ErrCode int

begin tran

update tbl_logsis set date = getdate() where id = @id

select @ErrCode = @@Error

If @ErrCode <> 0
Begin
Rollback Transaction
End

update tbl_errorcode set date = getdate() where nsmumber = @nsnumber

If @ErrCode <> 0
Begin
Rollback Transaction
End

commit Tran

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-14 : 18:07:20

How long does it take to run in Query Analyzer? How many rows does each update statement affect? What is the timeout setting in the application?

To see if you are getting a different execution plan from Query Analyzer than what the application is getting, run SQL Profiler and compare the execution plans.

If the execution plans are identical, which I suspect they aren't, then I don't see any reason why this would be happening.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources