Has anyone found a workaround for this MSSQL error/restriction "ConnectionError: Connection is closed."
I am using NodeRED to log data from various PAC based systems to an Aure MSSQL DB.
All is well when I run one flow at a time. The problem occurs when you try to run multiple flows concurrently referencing different tables in the same DB. In other words I have a DB for the customer with different tables for each PAC based process.
Once the connection to the DB is opened by the first flow the others get this error. So far the only way I have found to make it work is to string all the flows together sequentially and let them write to the DB one after the other. However this does not make for a very elegant organization of the flows.
I have tried looking for a different MSSQL Node and played with a couple but no luck yet. From what I have read this seems to be more of a MSSQL server issue, but I am looking for ideas.
No, not that I know of. All of the demos that Opto 22 has published and I have reviewed seem to only write one table in a given db. So I have stuck with stringing my nodes together so both tables can get updated. Below is an example of the Batch and HTST tables getting updated in the same db.
Hey @torchard do you have any suggestions on this? If you have two different nodes that use different tables in a MS SQL db you get a connection closed error. This is very limiting. Any ideas would be appreciated.
I haven’t experimented with this type of sequential connection before, but would it be possible to add a delay node somewhere between the two MSSQL nodes?
Just based off my experience with the MSSQL nodes it’s pretty likely the connection on the first one is still open or in the process of closing when the second node attempts to connect, which could be causing all this trouble. Node-RED timing is a little finicky sometimes, so a short delay to give the SQL connection a little breathing room might be all it needs.
Have you tried created a separate connection for each one? I haven’t tried this, but wondering if that solves it. I typically batch my statements together and send it all at once as follows:
If your logic makes it possible, you can send more than one SQL statement at a time. Store your SQL in a string and concatenate the next statement separated by a semicolon, then send it one time.
It does not seem to be a timing issue so I will try a different connection. Now all I have to do is figure out what nuances make a connection “different”, but I’ll start with just a different named connection in Node-Red. I appreciated the help, thanks.
