Uri Dimant
3/29/2007 12:25:00 PM
Hari
> Please do not enclose multiple commands inside one transaction if it is
> OLTP system as well as make sure that table has proper indexes.
Sometimes you cannot avoid it. Like having SELECT @val=MAX(col)...... and
then UPDATE .... SET col=@val
The OP has to make sure that inside BEGIN TRAN.. block he needs to no mix
up DML and first SELECT statemens and later on UPDATE/INSERT/DELETE as
well as order tables that participate with DML.
"Hari Prasad" <hari_prasad_k@hotmail.com> wrote in message
news:%23t1I1xfcHHA.3976@TK2MSFTNGP06.phx.gbl...
> Hello Pradeep,
>
> Please do not enclose multiple commands inside one transaction if it is
> OLTP system as well as make sure that table has proper indexes.
> What ever records used inside this tranactgion will be locked and other
> sessions needs to wait for the same data until the end of transaction
> is completed. So if other processes tries the same data there are chances
> for lot of wait_time and causes application time out.
>
> Thanks
> Hari
>
> "Uri Dimant" <urid@iscar.co.il> wrote in message
> news:u1BfjQecHHA.3648@TK2MSFTNGP05.phx.gbl...
>> Hi
>>
>> When you specify BEGIN TRAN .... that means all DML will be ran as one
>> transaction and depends on what are your DML users may wait till
>> transaction will be commited
>>
>>
>> "Pradeep" <Pradeep@discussions.microsoft.com> wrote in message
>> news:563A297C-911A-4CC5-9721-EA914B447284@microsoft.com...
>>> Thanks Uri,
>>>
>>> I am looking at what could be wrong in the stored procedure,I will
>>> certainly
>>> include the statement in the connection string.
>>>
>>> I am using BEGIN TRANSACTION "X" <statements> COMMIT TRANSACTION "X"
>>>
>>> Does the above syntax in any way lock the table and cause the error.
>>>
>>> Thanks for all the help.
>>>
>>> Cheers,
>>>
>>> Pradeep
>>>
>>> "Uri Dimant" wrote:
>>>
>>>> Hi
>>>>
>>>> First of all , you have to optimize your SP. That is the main problem
>>>> Secondly , specify in connection string (within VB) timeout=0
>>>>
>>>>
>>>> "Pradeep" <Pradeep@discussions.microsoft.com> wrote in message
>>>> news:70EC32AF-FF37-4060-AD28-088E7D28B77C@microsoft.com...
>>>> > Hello,
>>>> > 0
>>>> > Not sure if this question fits into this group or not. Please let me
>>>> > know
>>>> > if
>>>> > it does not so that I can post it in the Vb6 group.
>>>> >
>>>> > I have built an application with VB6.0 and SQL Server 2000. When i
>>>> > Execute
>>>> > a
>>>> > stored procedure, it fails at times with a Time Out Expired message.
>>>> > After
>>>> > i
>>>> > receive that message i am not able to execute any other stored
>>>> > procedure
>>>> > which usually execute without any problems. Once i encounter the
>>>> > message,
>>>> > attempts to execute all other stored procedures return the same
>>>> > error.
>>>> >
>>>> > out of sheer curiosity, I closed the connection, set the object to
>>>> > Nothing
>>>> > and reopened it when everything started working. Is there anything
>>>> > that i
>>>> > can
>>>> > set in the connection string itself. I tried setting the Time Out
>>>> > property
>>>> > in
>>>> > the properties window of the SQL Server but i do not access to the
>>>> > same.
>>>> >
>>>> > Any ideas of implementing the same would be highly appreciated.
>>>> >
>>>> > Cheers,
>>>> >
>>>> > Pradeep
>>>>
>>>>
>>>>
>>
>>
>
>