Process crashes calling stored procedure several times.

[boostie]

Hi all,

I have an issue where node process crashes when executing a stored procedure several times in the row (in particular 8). I started using tedious to work around the same crash issue with node-mssql, but started experiencing the same issue which could point to TDS. Now, this issue is weird to me but maybe someone can provide an understanding if something is done incorrectly or if this is an actual problem in both packages and needs to be fixed. Here are some facts:

1. The stored procedure is using a temporary table to build complex data set (#table), after which a dynamic query is executed using exec() with something like (select * from #table). This works and returns correct result set in Administrative console and VBScript without issues.

2. First when using node-mssql I have noticed that calling this particular stored procedure would crash the node process. I then tried to use a streaming interface, only to notice a problem at 160+ rows, which cause a crash.

3. Migrated to tedious and changed the stored procedure to return a range of rows (20) instead of many rows as previously. Calling the stored procedure with the following code. Without changing the row range (not a data issue), I experience the crash when calling the function 8 times in the row, which should be fine, imaging paging (8x20 rows = 160). Following code executes the stored procedure:

var self = this;
var connection = new Connection(dbConfig);
connection.on('connect', function(err) {
  if (err) {
    return c(err);
  }

  var results = [], dbError = null;
  var types = self.inputTransform.getNotificationTypes(query.type);
  var request = new Request('sp_MyStoredProcedure', function(err, rowCount) {
    if (err) {
      return c(err);
    }

    var alarmInstances = self.transform.wrapAlams(results, query.id);
    var response = {
      forDate: "Today, Wed 04 March 2015", 
      type: types.type,
      status: types.status, 
      actionToTake: 'Test',
      instances: alarmInstances
    };

    connection.close();
    return c(null, {
      alarms: response
    });
  });

  request.on('row', function (columns) {
    results.push(columns);
  });       

  request.addParameter('Site', TYPES.VarChar, query.site);
  request.addParameter('Product', TYPES.VarChar, query.name);
  request.addParameter('ProductCode', TYPES.VarChar, (query.code || 'all'));
  request.addParameter('Period', TYPES.VarChar, 'NA');
  request.addParameter('SelectedType', TYPES.VarChar, types.type);
  request.addParameter('unacknowledgedonly', TYPES.Char, types.acknowledged);
  connection.callProcedure(request);

});

Enabling the debug gives me the following (last few lines) before processes crashes:

connected to localhost:5134
State change: Connecting -> SentPrelogin
State change: SentPrelogin -> SentLogin7WithStandardLogin
Packet size changed from 4096 to 4096
State change: SentLogin7WithStandardLogin -> LoggedInSendingInitialSql
State change: LoggedInSendingInitialSql -> LoggedIn
State change: LoggedIn -> SentClientRequest

after which node.js process exits. No errors, no explanation that I could find online. Please let me know if there is a potential issue or if something isn't done correctly.

Thank you,
K

[boostie]

Table-variables and temporary tables have the same effect. After calling a procedure several times in the row, the process crashes on the same state as above. This makes the module unusable for our scenario, please comment if this is a known issue, possible work-around, or other npm modules that could accomplish the task of accessing MS SQL data without such issues. Much appreciated.

[arthurschreiber]

@boostie Could you provide a more complete test case to reproduce this? I'd need the source code of the stored procedure as well to be able to reproduce this.

[IanChokS]

Since it's been a while, this issue might have already been resolved with the latest tedious version. @boostie if you are still experience this same problem, please re-open this issue.