Sometimes, the Node-RED function in MSSQL shows the error "ConnectionError: Connection is closed." and stops working at that moment. It then resumes processing with the next task, skipping the one where the error occurred.
Which mssql package are you using? (Look at installed nodes on palette manager)
As a maintainer of the well supported and feature rich node-red-contrib-mssql-plus I would recommend you use that over this unknown new node.
You can. You have 2 choices
- Manual installation (no need to alter flows)
- Stop node-red
- Open terminal
- Run
npm remove node-red-contrib-mssql-yuzhi - Run
npm install node-red-contrib-mssql-plus - Start node-red
- Via node-red palette manager
- Remove all mssql nodes from the flow (including config nodes) and deploy
- Install new node
- Add mssql nodes back into your flow.
1 Like
Should that be
Run npm install node-red-contrib-mssql-plus
Thanks. Never a good idea to post from phone. Updated.
Hi, I install new mssql plus already. You can recommend to use mssql?
I am getting the error as shown in the picture.
Add a debug node to the end of the function node and show me what it is outputting.
Also, select those three nodes and export (ctrl+e) the flow & paste it into a reply.
I was able to connect to MSSQL, but after connecting and leaving it running for a while, the connection is closed, as shown in the image.
[
{
"id": "f93acf9b13cc7c11",
"type": "http in",
"z": "bbfe1ed3bbfa638d",
"name": "",
"url": "/api/Wastewater",
"method": "get",
"upload": false,
"swaggerDoc": "",
"x": 120,
"y": 140,
"wires": [
[
"5d18bcb7e2481ad7"
]
]
},
{
"id": "e436a72102184fde",
"type": "http response",
"z": "bbfe1ed3bbfa638d",
"name": "",
"statusCode": "",
"headers": {},
"x": 850,
"y": 140,
"wires": []
},
{
"id": "84446ff67f182ceb",
"type": "MSSQL",
"z": "bbfe1ed3bbfa638d",
"mssqlCN": "4edeb9464c29eace",
"name": "MSSQL1",
"outField": "payload",
"returnType": "0",
"throwErrors": "0",
"query": "SELECT [Name]+Attribute as [Name], [Value]\nFROM (\n SELECT [Name], [Flow_Data], [Flow_Meter], [Network_status]\n FROM (\n SELECT [Name], [Flow_Data], [Flow_Meter]\n\t\t,CASE WHEN [Name] IN ('MW1','MW2','MW3') THEN [Status_Pump] ELSE [Network_status] END [Network_status]\n\t\t, [InsertDate],ROW_NUMBER() OVER(PARTITION BY [Name] ORDER BY [InsertDate] DESC) AS R\n FROM [dbIoT].[dbo].[Scada_Wastewater_FlowData] D\n\t\tLEFT JOIN (\n\t\t\tSELECT FAC,CAST([Status_Pump] AS NVARCHAR) [Status_Pump]\n\t\t\tFROM(\n\t\t\t\tSELECT *,ROW_NUMBER() OVER(PARTITION BY FAC ORDER BY [Status_Pump] DESC) AS R\n\t\t\t\tFROM(\n\t\t\t\t\tSELECT \n\t\t\t\t\t [Status_Pump]\n\t\t\t\t\t ,CASE WHEN [Name] IN ('L3','L4') THEN 1 \n\t\t\t\t\t WHEN [Name] IN ('L5','L6') THEN 2\n\t\t\t\t\t WHEN [Name] IN ('L7','L8') THEN 3\n\t\t\t\t\t ELSE 0 END AS FAC\t \n\t\t\t\t FROM [dbIoT].[dbo].[Scada_WasteWater_PumpData]\n\t\t\t\t WHERE [Name] BETWEEN 'L3' AND 'L8'\n\t\t\t\t AND [InsertDate] >= DATEADD(MINUTE, -5, GETDATE())\n\t\t\t\t ) A \n\t\t\t\t )B WHERE R = 1\n\t\t)P ON 'MW'+CAST(P.FAC AS NVARCHAR) = D.[Name]\n WHERE [InsertDate] >= DATEADD(MINUTE, -15, GETDATE())\n ) A\n WHERE R = 1\n) B\nUNPIVOT (\n Value FOR Attribute IN ([Flow_Data], [Flow_Meter], [Network_status])\n) AS unpvt;",
"modeOpt": "",
"modeOptType": "query",
"queryOpt": "",
"queryOptType": "editor",
"paramsOpt": "",
"paramsOptType": "none",
"rows": "",
"rowsType": "msg",
"parseMustache": true,
"params": [],
"x": 480,
"y": 140,
"wires": [
[
"14e68676e6aeedfc"
]
]
},
{
"id": "14e68676e6aeedfc",
"type": "function",
"z": "bbfe1ed3bbfa638d",
"name": "function convert1",
"func": "var dataObject = {};\n\n// Loop through the array of data\nfor (var i = 0; i < msg.payload.length; i++) {\n // Check the name of the data and add appropriate units\n if (msg.payload[i].Name.includes(\"Data\")) {\n if (msg.payload[i].Name.includes(\"TB\")) {\n dataObject[msg.payload[i].Name] = msg.payload[i].Value;\n } else {\n dataObject[msg.payload[i].Name] = msg.payload[i].Value + \" mÂł/h\";\n }\n } else if (msg.payload[i].Name.includes(\"Meter\")) {\n dataObject[msg.payload[i].Name] = msg.payload[i].Value + \" mÂł\";\n } else {\n dataObject[msg.payload[i].Name] = msg.payload[i].Value; \n }\n}\n\n// Send the object back to the output\nmsg.payload = dataObject;\nreturn msg;\n\n",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 670,
"y": 140,
"wires": [
[
"e436a72102184fde"
]
]
},
{
"id": "5d18bcb7e2481ad7",
"type": "delay",
"z": "bbfe1ed3bbfa638d",
"name": "",
"pauseType": "delay",
"timeout": "30",
"timeoutUnits": "seconds",
"rate": "1",
"nbRateUnits": "1",
"rateUnits": "second",
"randomFirst": "1",
"randomLast": "5",
"randomUnits": "seconds",
"drop": false,
"outputs": 1,
"x": 320,
"y": 140,
"wires": [
[
"84446ff67f182ceb"
]
]
},
{
"id": "4edeb9464c29eace",
"type": "MSSQL-CN",
"tdsVersion": "7_4",
"name": "db",
"server": "",
"port": "1433",
"encyption": false,
"trustServerCertificate": false,
"database": "db",
"useUTC": false,
"connectTimeout": "",
"requestTimeout": "",
"cancelTimeout": "",
"pool": "",
"parseJSON": false,
"enableArithAbort": true,
"readOnlyIntent": false
}
]
How long is a while?
How many requests were made?
You know the delay node delays every message by exactly the amount you said
e.g. if you send 1000 requests, nothing will happen for 30s then all 1000 will be released at the same time after 30s!
Did you mean to rate limit (the delay node can be changed to perform rate limiting)
As for why...
Add a catch node pointed at the MSSQL node and link that to a debug node (set debug node to show the full msg)
Add a status node pointed at the MSSQL node and link that to a debug node (set debug node to show the full msg)
show use what the full message shows for the status and the catch
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
