Wednesday 27 February 2019

node mssql temp table lost - RequestError: Invalid object name '#myTempTable'

I'm using node-mssql and connecting to a the 2017 sql server from docker.

Issue

Throughout my script, I've put

var test = await dbRequest().batch("SELECT * FROM #myTestTable");

Intermittently, I get the error RequestError: Invalid object name '#myTempTable'. So I put some watches on my sql.ConnectionPool to identify what conditions occur to cause the error. Turns out on most lines of execution, the pool.pool.available variable is 1, as per below.

enter image description here

Whenever the error happens, immediately before stepping through to the next line, pool.pool.available will be 0 as so:

enter image description here

If this happens immediately before running var test = await dbRequest().batch("SELECT * FROM #myTestTable");

It will fail with RequestError: Invalid object name '#myTempTable'

What I've tried

I've tried sending a pool config through to Tedious with {min: 100, max: 1000, log: true}, but unfortunately it appears to be ignored (https://www.npmjs.com/package/mssql#connection-pools)

var sqlServerProperty = {
    user: '',
    password: '',
    server: '192.168.1.13',
    database: 'CCTDB',
    pool: {min: 1, 
        max: 100, 
        idleTimeoutMillis: 30000}
};

I've also tried just the one pool.request() and running all dbRequests from that. No dice!

Any help appreciated!

What does dbRequest() look like?

// the entire script is wrapped in an async function
        var pool = new sql.ConnectionPool(sqlServerProperty);
        await pool.connect();
        var dbRequest = ( () => { return pool.request() });



from node mssql temp table lost - RequestError: Invalid object name '#myTempTable'

No comments:

Post a Comment