How to create and read from the same temp SQL table using node js

Question

I am trying to create a temp table and read from it in the same connection using node js and mssql.

Query1: Selects from a bunch of different places and creates '#mrpSalesHistory'
Query2: Truncates the real table
Query3: Inserts the the data from '#mrpSalesHistory' to the real table
Query4: Selects count from the real table
Query5: Merges into the real table using '#mrpSalesHistory' as source
Query6: Selects All from real table

Code Snip:

sql.connect(config).then(function() {
    new sql.Request().batch(query1, (err, result) => {
        if(err){
            console.log('Q1');
            console.log(err);
        }
        console.log('Q1');
        console.log(result)
    })

}).then(function() {

    new sql.Request().batch(query2, (err, result) => {
        if(err){
            console.log('Q2');
            console.log(err);
        }
        console.log('Q2');
        console.log(result)
    })

}).then(function() {

    new sql.Request().batch(query3, (err, result) => {
        if(err){
            console.log('Q3');
            console.log(err);
        }
        console.log('Q3');
        console.log(result)
    })

}).then(function() {

    new sql.Request().batch(query4, (err, result) => {
        if(err){
            console.log('Q4');
            console.log(err);
        }
        console.log('Q4');
        console.log(result)
    })

}).then(function() {

    new sql.Request().batch(query5, (err, result) => {
        if(err){
            console.log('Q5');
            console.log(err);
        }
        console.log('Q5');
        console.log(result)
    })

}).then(function() {

    new sql.Request().batch(query6, (err, result) => {
        if(err){
            console.log('Q6');
            console.log(err);
        }
        console.log('Q6');
        console.log(result)
    })

}).catch(function(err) {

    console.log(err)
});

After running the following code I get

RequestError: Invalid object name '#mrpSalesHistory'. on the 3rd and 5th query.

Full log output:

Q3
{ RequestError: Invalid object name '#mrpSalesHistory'.
    at StreamEvents.req.once.err (C:\wamp64\www\mrp\updateNode\node_modules\mssql\lib\msnodesqlv8.js:532:17)
    at Object.onceWrapper (events.js:315:30)
    at emitOne (events.js:116:13)
    at StreamEvents.emit (events.js:211:7)
    at routeStatementError (C:\wamp64\www\mrp\updateNode\node_modules\msnodesqlv8\lib\reader.js:27:18)
    at C:\wamp64\www\mrp\updateNode\node_modules\msnodesqlv8\lib\reader.js:229:15
    at Object.cbFreeStatement [as end] (C:\wamp64\www\mrp\updateNode\node_modules\msnodesqlv8\lib\driver.js:202:9)
    at onInvoke (C:\wamp64\www\mrp\updateNode\node_modules\msnodesqlv8\lib\reader.js:228:26)
    at onQuery (C:\wamp64\www\mrp\updateNode\node_modules\msnodesqlv8\lib\driver.js:114:11)
  code: 'EREQUEST',
  number: 208,
  state: '42S02',
  originalError: { Error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name '#mrpSalesHistory'. sqlstate: '42S02', code: 208 },
  name: 'RequestError' }
Q3
undefined
Q2
{ recordsets: [],
  recordset: undefined,
  output: {},
  rowsAffected: [ -1 ] }
Q5
{ RequestError: Invalid object name '#mrpSalesHistory'.
    at StreamEvents.req.once.err (C:\wamp64\www\mrp\updateNode\node_modules\mssql\lib\msnodesqlv8.js:532:17)
    at Object.onceWrapper (events.js:315:30)
    at emitOne (events.js:116:13)
    at StreamEvents.emit (events.js:211:7)
    at routeStatementError (C:\wamp64\www\mrp\updateNode\node_modules\msnodesqlv8\lib\reader.js:27:18)
    at C:\wamp64\www\mrp\updateNode\node_modules\msnodesqlv8\lib\reader.js:229:15
    at Object.cbFreeStatement [as end] (C:\wamp64\www\mrp\updateNode\node_modules\msnodesqlv8\lib\driver.js:202:9)
    at onInvoke (C:\wamp64\www\mrp\updateNode\node_modules\msnodesqlv8\lib\reader.js:228:26)
    at onQuery (C:\wamp64\www\mrp\updateNode\node_modules\msnodesqlv8\lib\driver.js:114:11)
  code: 'EREQUEST',
  number: 208,
  state: '42S02',
  originalError: { Error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name '#mrpSalesHistory'. sqlstate: '42S02', code: 208 },
  name: 'RequestError' }
Q5
undefined
Q6
{ recordsets: [ [] ],
  recordset: [],
  output: {},
  rowsAffected: [] }
Q4
{ recordsets: [ [ [Object] ] ],
  recordset: [ { '': 0 } ],
  output: {},
  rowsAffected: [] }
Q1
{ recordsets: [],
  recordset: undefined,
  output: {},
  rowsAffected: [ 77938 ] }

Thank you.

If each sql.Request is a separate session (I assume this is the case but I'm not an expert in node) then the temp tables won't exist between calls. Is there a specific reason that this can't all be done in a single query? Or can you combine them into a single batch? — Jacob H
– Jacob H, Commented Feb 16, 2018 at 21:05
@JacobH I tried to do it all with one query, and it didn't work, but I looked on the github page for mssql and there is something to enable multiple requests request.multiple = true. But i'm not sure where to put it. — kaydrae
– kaydrae, Commented Feb 16, 2018 at 21:26

Bennett Ellis · Accepted Answer · 2018-03-28 11:37:24Z

1

Every one of your new sql.Request().batch( calls should be return new sql.Request().batch(. You're using promises. You have to return the promise in order to actually wait for the promise to be fulfilled. What's really going on in your code is that you're starting all 5 of these sql requests in rapid succession without waiting for any to complete, then their being fullfilled over time with no guaranteed order. Notice the random sequence of output from each query.

answered Mar 28, 2018 at 11:37

Bennett Ellis

262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create and read from the same temp SQL table using node js

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related