TSQL doesn't handle multiple transactions very well. In fact it doesn't handle it at all. So what do you do when you have nested stored procedures, all which require transaction handling?
TSQL has a built in function that will return the number of BEGIN TRANSACTION statements that have been called on the current connection.
Using this you can choose whether to begin a transaction or not based on the value it returns. If it returns a number greater than 0 then you are currently in a transaction.
However if it returns a 0 then there were no other transactions and you are free to start one. The best way to do this is to set a variable that indicates if a transaction is needed, since when you start your new transaction and you try to check again when you need to do a rollback or commit, @@TRANCOUNT will not return 0 since you recently started a transaction.
You can easily do it like this:
IF (@@TRANCOUNT = 0)
SET @transReq = 1
SET @transReq = 0
IF (@transReq = 1)
--Insert Code Here
--If no error
IF (@transReq = 1)
IF (@transReq = 1)
With this you can have multiple nested stored procedures and you won't have to worry about nested transactions errors.
By: Dustin Yee, Application Developer, WebSan Solutions Inc. a Microsoft Dynamics GP Partner and 2013 Canadian Channel Elite Awards Finalist.
On June 2nd 2013, WebSan Solutions took part in Becel’s 26th annual Ride for Heart. More than 13,000 cyclists rode their bikes across the Gardiner Expressway and Don Valley Parkway. The event raised a record of $5.5 million, which is $500,000 more than it did in 2012.
We are glad that we could make a positive impact in eradicating heart disease and stroke! It has been a proud moment for all the employees here at the office.
To view our photo and the July edition of Snap North York, click here: http://www.snapnorthyork.com/index.php?option=com_sngevents&id=520947
By: Natalie Williams, Marketing Coordinator, WebSan Solutions Inc.
Implementing a new ERP Software is a time for new learning experiences and challenges. There are some common mistakes that you want to avoid when implementing ERP software, remember this list so you can become a hero and not a victim of poor planning.
Mistake 1: Not selecting the right vendor
Browsing through a vendor’s website can’t determine if they are right for you. Call the company and speak with someone, this gives you the chance to ask questions about the company and their services. Don’t forget to ask for references so you can hear more than one opinion of the vendor.
Mistake 2: Not taking advantage of all the features that you are paying for!
For any kind of service you pay for, be mindful of what’s included. If you are unaware of a feature that you have but never use, just ask!
Mistake 3: Underestimating the time and resources required
The estimated time for the scope of a project can go two ways: Either the scope is nowhere accurate (underestimating) or the scope is close to being accurate (educated estimating). It’s your job to review the implementation plan. If things are getting out of hand, use the following steps to help you solve a problem: review the problem, resolve the problem and remember the problem so it doesn’t happen again.
Mistake 4: Not properly investing in training and change management
Encouraging questions decreases a number of misunderstandings that occur with your staff. Have other resources available such as, review videos and webinars. Fill out change order requests to make sure your change is on record and not forgotten. Verbal communication doesn’t cut it anymore.
Mistake 5: Not having an active load testing environment
You won’t see the results of your changes based on a couple of “test users”. You must be able to mimic your true user load to see the real effect of changes and to watch out for costly unplanned downtime.
Natalie Williams, Marketing Coordinator, WebSan Solutions Inc., a Canadian Certified Microsoft Partner