Unable to use Table-Valued Parameter (TVP) in query (node.js & node-mssql)

Question

Good day,

I am attempting to export some data from SQL, store it and later compare that data to the result of the same query. The simplest way I can see to do this is using TVP however it doesn't seem to work on node-mssql and it doesn't give me a TVP related error.

I run the code:

var _sql = require('mssql');
var _conn = new _sql.Connection(/*CONN STR*/,function(err) {
  if (err) {
    console.log('ERROR Unable to connect to DB:',err);
  } else {
    var oInitRequest = new _sql.Request(_conn);
    oInitRequest.query(/*DB QUERY*/)
      .then(function(oInitRS) {
        console.log('oInitData:',oInitRS);
        var oInitTable = oInitRS.toTable();
        console.log('oInitTable:',oInitTable);

        var oCheckRequest = new _sql.Request(_conn);
        oCheckRequest.input('oInitTable',_sql.TVP,oInitTable);
        oCheckRequest.query('SELECT * FROM @oInitTable')
          .then(function(oCheckRS) {
            console.log('oInitTable re-read:',oCheckRS);
          })
          .catch(function(err) {
            console.log('ERROR unable to pass oInitTable to new query',err);
          });
      })
      .catch(function(err) {
        console.log('ERROR Unable to retrieve oInitData',err);
      });
  }
});

I receive the error:

ERROR unable to pass oInitTable to new query
{
  [RequestError: Could not find stored procedure 'sp_executesql'.]
  name: 'RequestError',
  message: 'Could not find stored procedure \'sp_executesql\'.',
  code: 'EREQUEST',
  number: 2812,
  lineNumber: 1,
  state: 1,
  class: 16,
  serverName: /*REDACTED*/,
  procName: '',
  precedingErrors: []
}

Why was it able to run a query using sp_executesql successfully for the first query but the stored procedure did not exist for the second?

I tried using a prepared statement but even a standard one with no parameters did not work stating the error:

{
  [RequestError: Could not find prepared statement with handle 0.]
  name: 'RequestError',
  message: 'Could not find prepared statement with handle 0.',
  code: 'EREQUEST',
  number: 8179,
  lineNumber: 1,
  state: 4,
  class: 16,
  serverName: /*REDACTED*/,
  procName: 'sp_execute',
  precedingErrors: []
}

Does anyone know how to get a query to accept a TVP without creating a stored procedure specifically for 1 query?

Answer 1

Apologies. It seems that this is a bug in node-mssql and not something I could be assisted with