title: How to perform looped queries in transactions properly with node.js, mssql and msnodesqlv8
body: <p>I am attempting to do what I've previously done in c# and asp.net webforms and execute a number of insert stored procedures, some of which are in loops, in a transaction.  I am now trying to do so in my angular application through node.js with the mssql package.  What I have so far is as follows.</p>
  
  <pre><code>    //Insert Change Record
  router.post('/insertChange', (req, res) =&gt; {
      const transaction = new sql.Transaction(conn);
  
      transaction.begin(err =&gt; {
          let rolledBack = false;
          transaction.on('rollback', aborted =&gt; {
              rolledBack = true;
          })
          const request = new sql.Request(transaction);
          request.input('ChangeTitle', req.body.changeTitle);
          request.input('TypeId', req.body.typeId);
          request.input('DateSubmitted', req.body.dateSubmitted);
          request.input('TargetDate', req.body.targetProductionDate);
  
          request.input('ChangeSponsor', req.body.changeSponsor);
          request.input('UATDate', req.body.dateReadyUAT);
          request.input('ChangeSponsorEmail', req.body.changeSponsorEmail);
          request.input('ClarityId', req.body.clarityId);
          request.input('ChangeDescription', req.body.changeDescription);
  
          request.input('ComponentName', req.body.componentName);
          request.input('ComponentDescription', req.body.componentDescription);
  
          request.input('ReasonForChange', req.body.reasonForChange);
          request.input('ComponentREplacing', req.body.componentReplacing);
          request.input('DependentChange', req.body.dependentChange);
  
          request.input('InstallOption', req.body.installOption);
          request.input('RebootRequired', req.body.rebootRequired);
          request.input('UserIntervention', req.body.userIntervention);
          request.input('Activation', req.body.activation);
  
          request.input('ContingencyPlan', req.body.contingencyPlan);
          request.input('AdditionalInformation', req.body.additionalInformation);
  
          request.input('PerformanceImpact', req.body.applicationPerformance);
          request.input('IsPCoERequired', req.body.pcoeTesting);
          request.input('IsCanadianRetailBranch', req.body.pbsTesting);
  
          request.input('BusinessAppImpact', req.body.businessApplication);
          request.input('NetworkImpact', req.body.networkPerformance);
          request.input('NewInfrastructure', req.body.newInfrastructure);
          request.input('LogonTime', req.body.logonTime);
          request.input('AdditionalTechnicalInformation', req.body.additionalAssessmentInfo);
  
          request.input('IsProdIssue', req.body.isProdIssue);
          request.input('ProdIssue', req.body.productionIssue);
          request.input('ProdIncidentNum', req.body.incidentNumbers);
          request.input('IsMajorChange', req.body.isMajorChange);
          request.input('HasRequiredTesting', req.body.hasRequiredTesting);
          request.input('HasPackageSubmit', req.body.hasPackageSubmit);
          request.input('SignOffETA', req.body.signoffETA);
          request.input('SpecificTesting', req.body.specificTesting);
          request.input('SpecificPilot', req.body.specificPilot);
          request.input('PilotInfo', req.body.pilotTransits);
  
          request.input('UserChanges', req.body.userDifferences);
          request.input('ServiceDeskProcedure', req.body.procedureSupport);
          request.input('SupportCallFlowId', req.body.supportCallFlow);
  
          request.input('OverallChangeStatus', 1);
  
          let changeId = request.output('changeId', sql.Int);
  
          request.execute('dbo.InsertChange').then(function (result) {
              console.dir(result);
          }).catch(function (err) {
              console.dir(err);
          });
  
          async.each(
              req.body.changeType
              ,function iterator(item, next) {
              const requestCT = new sql.Request(transaction);
              requestCT.input('ChangeId', changeId);
              requestCT.input('TypeOfChangeId', item.typeOfChangeId);
              requestCT.input('Description', item.description);
              requestCT.execute('dbo.InsertTypeOfChange').then(function (result) {
                  console.dir(result);
                  next();
              }).catch(function (err) {
                  console.dir(err);
              });
          })
          async.each(
              req.body.serviceImpacted
              ,function iterator(item, next) {
              const requestSI = new sql.Request(transaction);
              requestSI.input('ChangeId', changeId);
              requestSI.input('ServicesImpactedId', item.serviceImpactedId);
              requestSI.execute('dbo.InsertImpactedService').then(function (result) {
                  console.dir(result);
                  next();
              }).catch(function (err) {
                  console.dir(err);
              });
          })
          async.each(
              req.body.businessImpacted
              ,function iterator(item, next) {
              const requestBI = new sql.Request(transaction);
              requestBI.input('ChangeId', changeId);
              requestBI.input('BusinessImpactedId', item.businessImpactedId);
              requestBI.execute('dbo.InsertImpactedBusiness').then(function (result) {
                  console.dir(result);
                  next();
              }).catch(function (err) {
                  console.dir(err);
              });
          })
          async.each(
              req.body.criticalApp
              ,function iterator(item, next) {
              const requestCA = new sql.Request(transaction);
              requestCA.input('ChangeId', changeId);
              requestCA.input('CriticalBankingId', item.criticalId);
              requestCA.input('Description', item.description);
              requestCA.execute('dbo.InsertCriticalBankingApp').then(function (result) {
                  console.dir(result);
                  next();
              }).catch(function (err) {
                  console.dir(err);
              });
          })
          async.each(
              req.body.testStages
              ,function iterator(item, next) {
              const requestTS = new sql.Request(transaction);
              requestTS.input('ChangeId', changeId);
              requestTS.input('TestStageId', item.testStageId);
              requestTS.input('TestDate', item.testDate);
              requestTS.input('Description', item.description);
              requestTS.execute('dbo.InsertChangeTest').then(function (result) {
                  console.dir(result);
                  next();
              }).catch(function (err) {
                  console.dir(err);
              });
          })
  
          if (err) {
              if (!rolledBack) {
                  transaction.rollback(err =&gt; {
                      console.dir(err);
                      // ... error checks
                  })
              }
          } else {
              transaction.commit(err =&gt; {
                  console.dir(err);
                  // ... error checks
  
              })
          }
      })
  });
  </code></pre>
  
  <p>There really isn't a heck of a lot of material online anywhere for processing transactions with stored procedures, and even less for big transactions like this.  But the error I'm getting is the following multiple times in my terminal log.</p>
  
  <pre><code>{ TransactionError: Can't acquire connection for the request. There is another request in progress.
  at Transaction.acquire (C:\Users\Redirection\meecd26\Documents\GitRepo\td-angular-starter\node_modules\mssql\lib\base.js:740:30)
  at Immediate._query.err [as _onImmediate] (C:\Users\Redirection\meecd26\Documents\GitRepo\td-angular-starter\node_modules\mssql\lib\msnodesqlv8.js:417:19)
  at runCallback (timers.js:781:20)
  at tryOnImmediate (timers.js:743:5)
  at processImmediate [as _immediateCallback] (timers.js:714:5) code: 'EREQINPROG', name: 'TransactionError' }
  </code></pre>
  
  <p>This certainly suggests that I'm performing the transaction improperly.</p>
  
  <p>Would someone be able to help guide me as to how I can improve and fix this?</p>
  
  <p>Edit: Made changes to my code to reflect what I found <a href="https://github.com/patriksimek/node-mssql/issues/113" rel="nofollow noreferrer">here</a>, as well as a few changes to my first insert.  While my first insert does work, I'm still getting the repeated errors above, so now I know my problems are with how I'm implementing the inserts inside of loops.  Is there a way I can close the connection upon each insert?  Would that be the approach I should try and take?</p>