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

Question

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.

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

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() });

Answer 1

That error message is just a sign that initial session (or connection) was closed and new one started. Temporary table has a lifetime of the session. In order to fix this issue make sure that your logic can retain open connections untill #temp table is not necessary anymore

I am not an expert in nodejs, but quick research tells:

There is a clear way to do it - simply initialize Connection with pool.min option set to 1 (or higher). It means, that there must be at least one active connection in the pool. Higher number means more concurrent active connections.

Have you tried something like?:

pool: {min: 20, 
        max: 100, 
        idleTimeoutMillis: 30000}

In this case, more connections will be established, so more of them available at some single point of time and the chance that your session is closed because it to be used by something else will be reduced

Answer 2

To insure all batches in a series are executed under the same connection, you can also use Transactions: https://www.npmjs.com/package/mssql#transaction

Note that transactions will lock records until committed, which may cause blocking in the database.