# Interval connection error

**URL:** <https://discourse.nodered.org/t/interval-connection-error/96848>\
**Category:** Developing Nodes\
**Tags:** function-node\
**Created:** [2 May 2025 08:01 UTC](https://discourse.nodered.org/t/interval-connection-error/96848 "2025-05-02T08:01:24Z")\
**Posts on this page:** 18\
**Page:** 1

<div class="post-metadata">

**Author:** ![Dar](https://avatars.discourse-cdn.com/v4/letter/d/5f8ce5/32.png) [@Dar](https://discourse.nodered.org/u/Dar)\
**Post date:** [2 May 2025 08:01 UTC](https://discourse.nodered.org/t/interval-connection-error/96848/1 "2025-05-02T08:01:24Z")

</div>

Hello, I need some help

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/8/4893fdbcf030b5a0a4fd56622bc1e36d6d3d4470.png)

I am an Intern working on this project where I have to develop a dashboard to read mssql data for plc in node red, I just started learning node red and I'm stuck at this problem, the issue is for this particular flow, there is an error

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/3/73891b1436809f20f90d78dd5452bed573c11b27.png)

When I inject the data manually it shows the output is fine, but when I set the interval it shows this error.

I used node-red-contrib-mssql

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [2 May 2025 08:11 UTC](https://discourse.nodered.org/t/interval-connection-error/96848/2 "2025-05-02T08:11:38Z")

</div>

Welcome to the forum Dar.

Can you share some more info?

What is the interval you are using?

Are you using a prepared SQL statement? How complex is the statement?

---

<div class="post-metadata">

**Author:** ![Dar](https://avatars.discourse-cdn.com/v4/letter/d/5f8ce5/32.png) [@Dar](https://discourse.nodered.org/u/Dar)\
**Post date:** [2 May 2025 08:16 UTC](https://discourse.nodered.org/t/interval-connection-error/96848/3 "2025-05-02T08:16:30Z")

</div>

Thank you for repying  
I don't know if this will help, please tell me if I need to share some more.

```auto
[
    {
        "id": "630bdb1c4f13eafc",
        "type": "inject",
        "z": "8a3e2aefb4cecb49",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "10",
        "crontab": "",
        "once": true,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 150,
        "y": 220,
        "wires": [
            [
                "f8dc1780c5b633ec"
            ]
        ]
    },
    {
        "id": "51b0c07ad2c67bb5",
        "type": "debug",
        "z": "8a3e2aefb4cecb49",
        "name": "debug 2",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 880,
        "y": 220,
        "wires": []
    },
    {
        "id": "f8dc1780c5b633ec",
        "type": "function",
        "z": "8a3e2aefb4cecb49",
        "name": "function 10",
        "func": "msg.payload = \"SELECT TOP (10) * FROM dbo.SA03 WHERE Tag = 'CH.DV01.MAIN01.Speed' ORDER BY DateTime DESC\";\n\nreturn msg;",
        "outputs": 1,
        "timeout": 0,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 350,
        "y": 220,
        "wires": [
            [
                "07853a5498b3965f"
            ]
        ]
    },
    {
        "id": "5483a2755cbf1c1f",
        "type": "ui_table",
        "z": "8a3e2aefb4cecb49",
        "group": "eb8ac8c960fbfe51",
        "name": "",
        "order": 1,
        "width": 30,
        "height": 6,
        "columns": [],
        "outputs": 0,
        "cts": false,
        "x": 890,
        "y": 320,
        "wires": []
    },
    {
        "id": "07853a5498b3965f",
        "type": "MSSQL",
        "z": "8a3e2aefb4cecb49",
        "mssqlCN": "aba6c4089a8cbd85",
        "name": "Speed",
        "query": "",
        "outField": "payload",
        "x": 550,
        "y": 220,
        "wires": [
            [
                "51b0c07ad2c67bb5",
                "5483a2755cbf1c1f"
            ]
        ]
    },
    {
        "id": "eb8ac8c960fbfe51",
        "type": "ui_group",
        "name": "Speed",
        "tab": "959e32e81beb4ff7",
        "order": 2,
        "disp": true,
        "width": 30,
        "collapse": false,
        "className": ""
    },
    {
        "id": "aba6c4089a8cbd85",
        "type": "MSSQL-CN",
        "name": "i4.0",
        "server": "Delta`Preformatted text`",
        "encyption": true,
        "database": "i4.0"
    },
    {
        "id": "959e32e81beb4ff7",
        "type": "ui_tab",
        "name": "Delta SA 03",
        "icon": "dashboard",
        "disabled": false,
        "hidden": false
    }
]

```

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [2 May 2025 08:26 UTC](https://discourse.nodered.org/t/interval-connection-error/96848/4 "2025-05-02T08:26:44Z")

</div>

So you are asking the db server every 10 seconds(?) to dynamically calculate the "top 10" entries from the db and return all of the fields?

I suspect that you are hitting some kind of processing limit.

If "top 10" means the newest 10 entries, you've already sorted by date/time so you only need to "LIMIT 10". I assume that the DateTime field is indexed though because if it isn't, that is still a fair load on the server, especially if the table is large.

You should also list the fields you want rather than making the server work it out each time.

Better still, you should use a prepared statement with the above optimisations so that the server hasn't got to compile the query each time.

My SQL is rather rusty so I think I've got this right and I don't know if it will actually fix the issue but it should at least help.

If it doesn't, you will have to slow down the interval until you stop getting the errors.

BTW, using a straight SQL table for processing timeseries data is sub-optimal. Not sure if MS SQL has a timeseries optimised table format, I believe Postgres does. Most people use a dedicated timeseries db server though.

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [2 May 2025 08:41 UTC](https://discourse.nodered.org/t/interval-connection-error/96848/5 "2025-05-02T08:41:33Z")

</div>

How many records are in the SA03 table?  
What indexes are defined for this table?

---

<div class="post-metadata">

**Author:** ![Dar](https://avatars.discourse-cdn.com/v4/letter/d/5f8ce5/32.png) [@Dar](https://discourse.nodered.org/u/Dar)\
**Post date:** [2 May 2025 08:54 UTC](https://discourse.nodered.org/t/interval-connection-error/96848/6 "2025-05-02T08:54:25Z")

</div>

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/9/d9868d451af012dfd634e6bfbb31e47836b14eb9.jpeg)

As an intern, I don't really have that much access to the database, so this is all I have

---

<div class="post-metadata">

**Author:** ![Dar](https://avatars.discourse-cdn.com/v4/letter/d/5f8ce5/32.png) [@Dar](https://discourse.nodered.org/u/Dar)\
**Post date:** [2 May 2025 08:55 UTC](https://discourse.nodered.org/t/interval-connection-error/96848/7 "2025-05-02T08:55:56Z")

</div>

I'll give it a try but for the interval, I already adjusted it but it is not working

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [2 May 2025 09:42 UTC](https://discourse.nodered.org/t/interval-connection-error/96848/8 "2025-05-02T09:42:20Z")

</div>

Top 10 doesn't seem to make much sense looking at the data. Too many entries with the same timestamp.

As JBudd say's you really need to understand what indexes have been applied. With any db, indexes are make-or-break for performance.

> [@Dar](#):
>
> I'll give it a try but for the interval, I already adjusted it but it is not working

I don't use MS SQL - does the driver have a connection pooling option?

How big did you make the interval? Did you try a few minutes for example? Just to see if you are hitting db server limits.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [2 May 2025 09:49 UTC](https://discourse.nodered.org/t/interval-connection-error/96848/9 "2025-05-02T09:49:54Z")

</div>

> [@Dar](#):
>
> I used node-red-contrib-mssql

I would strongly recommend you do not use that node.  
I would suggest you switch to `node-red-contrib-mssql-plus`  
Here is why:

> [@Mssql not sending data to database using mustache format](https://discourse.nodered.org/t/mssql-not-sending-data-to-database-using-mustache-format/83527/4):
>
> That node is over 7 years old, uses old libraries (with CVE issues) and has a number of known bugs and last, but not least, does not have the additional features that make mssql-plus the best SQL node in the catalog. I suggest you remove that and add the newer/better one and use parameters. You have 2 choices to acheive that ^ remove all traces of mssql from your flows remove node-red-contrib-mssql via palette manager install node-red-contrib-mssql-plus via palette manager restart node-red a…

> [@TotallyInformation](#):
>
> I don't use MS SQL - does the driver have a connection pooling option?

The OP is using the (very) old contrib. `node-red-contrib-mssql-plus` does support pooling (and stored procs and prepared statements)

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [2 May 2025 09:51 UTC](https://discourse.nodered.org/t/interval-connection-error/96848/10 "2025-05-02T09:51:14Z")

</div>

Thanks Steve, sage advice 🙂

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [2 May 2025 10:33 UTC](https://discourse.nodered.org/t/interval-connection-error/96848/11 "2025-05-02T10:33:31Z")

</div>

I'm not an mssql user so i don't really know how to interpret the picture you posted.

To find out how many records are in the table you could run (from Node-red)

```auto
SELECT COUNT(*) FROM dbo.SA03

```

BUT (according to [https://codingsight.com/how-to-count-number-of-rows-in-sql-server-table/](https://codingsight.com/how-to-count-number-of-rows-in-sql-server-table/) this will lock the table until it returns (surely only a millisecond or two?) so not ideal on a production database.  
Instead, they suggest (option 4) you can get the row count from SQL Server Management Studio, which _may be_ the app you posted a screenshot from.  
The link also discusses how to see the number of logical reads used by a query. This is essential information for tuning your query.

The screenshot does show on the left a category "Indexes" which will show you how the table is indexed.

I am pretty sure that you **do** need the `ORDER BY` clause, otherwise the 10 records returned cannot be relied on as the most recent.  
The example top 1000 statement in your picture though omits this clause. Seems bad form to me but as I said, I don't use mssql.  
And @TotallyInformation's `LIMIT 10` is invalid syntax for mssql (?)

A minor point - you show that the query was run at 3:57:09, 3:57:10 and 3:57:20.  
If you are running it every 10 seconds, you don't _also_ need "Inject once after 0.1 seconds" ticked.

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [2 May 2025 11:47 UTC](https://discourse.nodered.org/t/interval-connection-error/96848/12 "2025-05-02T11:47:35Z")

</div>

> [@jbudd](#):
>
> And @TotallyInformation's `LIMIT 10` is invalid syntax for mssql (?)

Doh!

OK, try something like this:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/c/7cb1c160b78b825862ba0bb841cdc731b1e5914e.png)

---

<div class="post-metadata">

**Author:** ![Dar](https://avatars.discourse-cdn.com/v4/letter/d/5f8ce5/32.png) [@Dar](https://discourse.nodered.org/u/Dar)\
**Post date:** [5 May 2025 00:23 UTC](https://discourse.nodered.org/t/interval-connection-error/96848/13 "2025-05-05T00:23:49Z")

</div>

Thank you for the reply steve, I did tried to use the node-red-contrib-mssql-plus node a few times but I failed to get the node to even make a connection to the MSSQL database, I probably made a mistake somewhere when filling in the information. The tutorial from youtube don't have much about the plus version, I might need some guidance on how to convert from old node to the new one, If you can give some guidance that would be very much appreciated.

---

<div class="post-metadata">

**Author:** ![Dar](https://avatars.discourse-cdn.com/v4/letter/d/5f8ce5/32.png) [@Dar](https://discourse.nodered.org/u/Dar)\
**Post date:** [5 May 2025 00:25 UTC](https://discourse.nodered.org/t/interval-connection-error/96848/14 "2025-05-05T00:25:20Z")

</div>

@jbudd Thank you for the information, I want to ask If I made a separate table in mssql solely for speed tag, do you think it will help?

@TotallyInformation thank you, I'll give it a try

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [5 May 2025 00:38 UTC](https://discourse.nodered.org/t/interval-connection-error/96848/15 "2025-05-05T00:38:25Z")

</div>

No I don't but as I said before, I'm not an mssql user.

Did you find out record counts and indexes?

---

<div class="post-metadata">

**Author:** ![Dar](https://avatars.discourse-cdn.com/v4/letter/d/5f8ce5/32.png) [@Dar](https://discourse.nodered.org/u/Dar)\
**Post date:** [5 May 2025 01:04 UTC](https://discourse.nodered.org/t/interval-connection-error/96848/16 "2025-05-05T01:04:21Z")

</div>

It was my off day before so I don't get the chance, but I will be meeting the admin today and I will get back to you later

---

<div class="post-metadata">

**Author:** ![Dar](https://avatars.discourse-cdn.com/v4/letter/d/5f8ce5/32.png) [@Dar](https://discourse.nodered.org/u/Dar)\
**Post date:** [5 May 2025 01:40 UTC](https://discourse.nodered.org/t/interval-connection-error/96848/17 "2025-05-05T01:40:14Z")

</div>

> [@jbudd](#):
>
> A minor point - you show that the query was run at 3:57:09, 3:57:10 and 3:57:20.  
> If you are running it every 10 seconds, you don't _also_ need "Inject once after 0.1 seconds" ticked.

So I tried doing this and it worked but once in a while the debug output shows "ConnectionError: Connection is closed." it was probably still the issue with the database, I will try to find out about the record counts and index.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [19 May 2025 01:40 UTC](https://discourse.nodered.org/t/interval-connection-error/96848/18 "2025-05-19T01:40:22Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.