Ignition creates a connection pool for each database connection you have. By default, the pool is fixed to 8 (JDBC) connections. Those JDBC connections likely map 1:1 with what MSSQL docs are calling ‘Sessions’ - so what’s probably happening is that you create a temp table on one session (by chance) then don’t borrow that same session from the pool for some period of time. Eventually you’ll get the same JDBC connection, and you’ll get the error.
I’m not an expert in MSSQL, but is there no way to explicitly discard the temporary table when you’re done with it? Or namespace it on whatever operation you’re doing - maybe name each table ${clientId}Results or something goofy like that?