Strange Update 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

Transact-SQL (2005)

Strange Update Problem

Author

Topic

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-11-06 : 09:24:38

Hi All,

I have a strange problem in a SP. First i ll give details

My table structure is
CREATE TABLE [dbo].[COMN_WBI](
[XML_Id] [int] NULL,
[COMN_XML] [xml] NOT NULL,
[Transaction_Type] [nchar](50) NOT NULL,
[Created_DT] [datetime] NOT NULL,
[Processed_DT] [datetime] NULL,
[Successful_YN] [bit] NULL,
[Failure_Reason] [varchar](2000) NULL
) ON [PRIMARY]

My SP contains the following code

ALTER PROCEDURE [dbo].[USP_UPDATE_XMLFiles_INFO]
(
@XML_Id INT,
@Processed_Date DATETIME,
@Successful_YN BIT,
@Failure_Reason VARCHAR(2000)
)
AS
BEGIN

UPDATE COMN_WBI SET
Processed_DT = @Processed_Date,
--Processed_Time = @Processed_Time,
Successful_YN = @Successful_YN,
Failure_Reason = @Failure_Reason
WHERE XML_Id = @XML_Id

END

my prblem is when i execte the sp in SSMS it is fine and in 0 sec completes, but when i try to execute the same from Front end (c#.net)
it gives me time out expired.

Note : no inline queries are used in .Net
also the table is used in this sp only.
There are currently 6 records in the table

why is so?

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-06 : 09:42:57

My be you are messed up with multiple connections from your app and locked yourself?

What I mean, a common scenario of a problem:

Conn1: begin tran
Conn1: update tab X
Call some proprty of a some object. inside, it opens a new connection
Conn2: select * from X --- LOCKED !!!!!!!!!!!!!!!!!!!!!
Conn1: commit tran

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources