CODEWITHSUNDEEP
c#sql-servernetwork-programmingvb6
Christian Edel
Christian Edel

Reputation: 136

Whats does MSSQL, when connection is lost, after SP call?

I have a software operating via WLAN mounted on a moving device. At the moment transactions are opened and closed in code. Between the businesslogic is happening. Now i'm suffering of lost connection and staying open transactions on the database. (MSSQL 2012)

My solution was to move all transactions/logic to a sp.

So the client only calls the sp and transations are handled inside.

My question here is: What happens to a sp wenn the connection is lost? Does it run to the end?

Upvotes: 1

Views: 146

Answers (1)

Thom A
Thom A

Reputation: 95830

This is covered in the documentation Controlling Transactions (Database Engine), specifically in the Errors During Transaction Processing section:

If an error prevents the successful completion of a transaction, SQL Server automatically rolls back the transaction and frees all resources held by the transaction. If the client's network connection to an instance of the Database Engine is broken, any outstanding transactions for the connection are rolled back when the network notifies the instance of the break. If the client application fails or if the client computer goes down or is restarted, this also breaks the connection, and the instance of the Database Engine rolls back any outstanding connections when the network notifies it of the break. If the client logs off the application, any outstanding transactions are rolled back.

I've emphasised the relevant section.

So, moving the transactions to the SP won't stop the transaction being rolled back if your connection drops. I would suggest finding out why your connection is unstable and fixing that. Otherwise you'll need to work out a way to run the query locally on the instance (perhaps, using SQL Agent).

Upvotes: 1

Related Questions