September 12, 2011 at 10:04 am
Hi,
This is not related completely to the sql server. But I am hoping someone might be able to help.
we have a stored procedure that inserts the data into a table. we wrapped the insert statement inside a transaction. And we call this stored procedure from visual basic code. The timeout is set out to 30 seconds in visual basic. So, when the visual basic runs the stored procedure, sometimes it times out. Since the transaction has started inside the stored procedure and didn't complete, how could we rollback this from visual basic?
Should we have a begin tran and commit/rollback transaction inside visual basic also?
Thanks,
Sridhar.
September 12, 2011 at 10:33 am
Is the insert statement the only statement involved in the transaction? If so, and the query timer times out, the insert will be rolled back.
If other statements are involved the results can vary. You should probably use try - catch.
Please provide more information about this procedure we may be able to help further.
The probability of survival is inversely proportional to the angle of arrival.
September 12, 2011 at 12:26 pm
The insert statement is the only one inside the stored procedure. I thought that when Visual Basic calls the stored procedure, it will wait for 30 seconds (time out value) and if it didn't get response from sql server, it will throw the message. I am not sure what happens to the stored procedure that is called? Does it continue to finish or does it stop with out rolling back causing the lock?
September 12, 2011 at 12:50 pm
Hi Sridhar
Sridhar-137443 (9/12/2011)
The insert statement is the only one inside the stored procedure.
You got a procedure that does nothing but one INSERT statement and takes more than 30 seconds? You need a new box 😀
I thought that when Visual Basic calls the stored procedure, it will wait for 30 seconds (time out value) and if it didn't get response from sql server, it will throw the message.
Are we talking about VB.NET or classic VB or VBA?
In both cases an exception will be thrown, but needs to be differently handled.
I am not sure what happens to the stored procedure that is called? Does it continue to finish or does it stop with out rolling back causing the lock?
Usually your data provider will tell SQL Server to cancel the execution but the transaction will not be affected by this. To be safe you should implement an error handling that explicitly rolls back the transaction.
Greets
Flo
September 12, 2011 at 3:20 pm
Sridhar-137443 (9/12/2011)
The insert statement is the only one inside the stored procedure. I thought that when Visual Basic calls the stored procedure, it will wait for 30 seconds (time out value) and if it didn't get response from sql server, it will throw the message. I am not sure what happens to the stored procedure that is called? Does it continue to finish or does it stop with out rolling back causing the lock?
Could you post the actual execution plan. I'm pretty sure we're missing something obvious, or there's blocking somewhere.
September 12, 2011 at 9:18 pm
Hi Florian,
Thank you for the detailed response. We are using the Classic Visual Basic 6.0. we are thinking that the insert statement is timing out because the table has a lock caused by some other query in another program. I am not sure how we could look at the query (not just the processid) that is causing the block.
Also, since the transaction statement is inside the stored procedure, how would I rollback from classic visual basic? Should I have transaction statement in visual basic too? If I create a transaction in classic visual basic, does it lock the whole database? If you could post a sample code to do this, it would be a great help.
Thanks,
Sridhar.
September 13, 2011 at 2:15 am
Hi Sridhar
Sridhar-137443 (9/12/2011)
We are using the Classic Visual Basic 6.0
Ugh... I'll do my best but it's more than one decade since my last VB6 attempts.
I am not sure how we could look at the query (not just the processid) that is causing the block.
A good start point to see what is going on is Adam Machanic's procedure Who Is Active v10.0.
Also, since the transaction statement is inside the stored procedure...
I'd (almost) always surround data manipulating code with a TRY...CATCH (Transact-SQL) within your procedure. However, if your INSERT is the only statement inside of your proc you can leave this away - even if some error logging into a database table could be helpful.
how would I rollback from classic visual basic? Should I have transaction statement in visual basic too?
Yup. There are two levels of transaction handling. A procedure ensures that all data manipulated within its call are either written correct or not. On the other hand, the procedure does usually not know if there will be any other data manipulations send from the client. I'd always use something like this (sorry for my lousy VB6 code..)
On Error Goto ErrSQL
Dim cn As New ADODB.Connection
cn.Provider = "SQLOLEDB.1"
cn.ConnectionString = "myCnStr"
cn.Open
cn.BeginTrans
' do your data manipulations
cn.CommitTrans
ErrSQL:
cn.RollbackTrans
' log the error
If I create a transaction in classic visual basic, does it lock the whole database?
Nope. Transaction locks and lock escalation will be generally equal to the locking inside of the procedure. The only difference is, if you send more than one manipulation in one transaction you will lock more resources on database level until your client transaction becomes committed or rolled back, but a database lock is rare and will usually hint any other problems.
If you could post a sample code to do this, it would be a great help.
I tried my best 😛
Greets
Flo
September 13, 2011 at 7:40 am
Thank you Florian.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply