MSSQL 2 instances same server, 2 different databases

General
1

Hello Guys,

I have a problem with MSSQL-Plus node. I have 2 separate flows that are adding values to same MSSQL server, but on 2 different databases, with 2 different users and passwords.

Even if i select the correct connection for the MSSQL node, he is not able to do the switch between them, he is always checking the table name in the other database. Is like he remain connected to one of the databases and not checking the connection config each time when values are entering the MSSQL node.

I am not able to use 2 different databases on the same node-red server.

Did anyone encounter this error before? Do you have a solution for this?

Thank you!

2

I have no problems accessing tables from two different databases in two different flows. The mysql is installed on the same machine as I am testing on using node-red-contrib-mysql

using a simple select:

  1. if you only have database/tab/e A in the flows, does it work?
  2. If you swap out database/tab/e A for database/tab/e B in the flows, does it work?
  3. what is the full name of the node you are using?
    If you still have a problem, please provide your flows but read this first: How to share code or flow json
3

@zenofmud the Q is about MSSQL not Mysql...

4

Hello zenofmud,

I have flow A, that is processing a set of data and a "node-red-contrib-mssql-plus" node which is configured to add data to database WeatherST, table cStoreWeatherStations.
And flow B, that is processing another set of data (the source is different from the flow A) and after processing the data i have another "node-red-contrib-mssql-plus" node which is configured to add data to database IoT, table GatewayStats.

Now what happens is very strange, the first sets of data that comes in are putted in the correct databases and tables, the next set of data is randomly selecting only one of the databases connection.

What i have done to test is to create table GatewayStats, into database WeatherST and surprisingly informations are added there, even if the MSSQL node is correctly configured with the connection of IoT database.

So the SQL connection remains connected to WeatherST database and is adding there all the info from both of the flows (if he find the correct table name and column settings). The node is not even checking again if the SQL connection is different.

Yes, dceejay, is about MSSQL not MySQL.

Thank you!

5

As @zenofmud suggested I think pasting a portion of your flows here would be worthwhile. Just the db access nodes and a few nodes before them. It doesn't have be enough to work, just to see the config.
Have you checked on the node's github repository to see if there are any similar issues reported there?

6

Head slap!

7

Hello, the Nodered server is not on the same machine as the SQL server.
On Github is nothing related with this error.
Now for the flows:

Flow A:

[
    {
        "id": "15b82b89.7ee7dc",
        "type": "function",
        "z": "b4f95417.221508",
        "name": "Split String",
        "func": "var msg = msg.payload;\nvar DevID={ payload: parseInt(msg.slice(40,42),16)};\nvar Pluv1={ payload: parseInt(msg.slice(0,4),16)/100};\nvar Pluv2={ payload: parseInt(msg.slice(4,8),16)/100};\nvar Pluv3={ payload: parseInt(msg.slice(8,12),16)/100};\nvar Wind={ payload: parseInt(msg.slice(12,18),16)};\nvar Anemo={ payload: parseInt(msg.slice(18,22),16)/100};\nvar Tempsign={ payload: parseInt(msg.slice(22,24),16)};\nvar Temp={ payload: parseInt(msg.slice(24,28),16)/100};\nif (Tempsign.payload == 1) (Temp = { payload: (Temp.payload * (-1))});\nvar Hum={ payload: parseInt(msg.slice(28,32),16)/100};\nvar Press={ payload: parseInt(msg.slice(32,38),16)};\nvar Bat={ payload: parseInt(msg.slice(38,40),16)};\nreturn [DevID,Pluv1,Pluv2,Pluv3,Wind,Anemo,Temp,Hum,Press,Bat];",
        "outputs": 10,
        "noerr": 0,
        "x": 1050,
        "y": 420,
        "wires": [
            [
                "c46309a1.359578",
                "f7e42d18.d1804"
            ],
            [
                "73bf16e0.ee2438",
                "f7e42d18.d1804"
            ],
            [
                "23cc97.366abb6a",
                "f7e42d18.d1804"
            ],
            [
                "1bfe6a6e.ba4ee6",
                "f7e42d18.d1804"
            ],
            [
                "47911896.8df1b8",
                "f7e42d18.d1804"
            ],
            [
                "e60d01e1.adf32",
                "f7e42d18.d1804"
            ],
            [
                "29ed72f4.c4c06e",
                "f7e42d18.d1804"
            ],
            [
                "344664c2.26020c",
                "f7e42d18.d1804"
            ],
            [
                "721cbc1b.9ca88c",
                "f7e42d18.d1804"
            ],
            [
                "dfff80a0.04af5",
                "f7e42d18.d1804"
            ]
        ]
    },
    {
        "id": "f7e42d18.d1804",
        "type": "join",
        "z": "b4f95417.221508",
        "name": "",
        "mode": "custom",
        "build": "array",
        "property": "payload",
        "propertyType": "msg",
        "key": "topic",
        "joiner": "\\n",
        "joinerType": "str",
        "accumulate": false,
        "timeout": "",
        "count": "10",
        "reduceRight": false,
        "reduceExp": "",
        "reduceInit": "",
        "reduceInitType": "",
        "reduceFixup": "",
        "x": 1210,
        "y": 420,
        "wires": [
            [
                "be047bfa.63f268"
            ]
        ]
    },
    {
        "id": "be047bfa.63f268",
        "type": "simple-queue",
        "z": "b4f95417.221508",
        "name": "Insert to DB queue",
        "firstMessageBypass": true,
        "bypassInterval": "0",
        "x": 1390,
        "y": 420,
        "wires": [
            [
                "a802c590.5c8848"
            ]
        ]
    },
    {
        "id": "a802c590.5c8848",
        "type": "MSSQL",
        "z": "b4f95417.221508",
        "mssqlCN": "989023f3.29ffc8",
        "name": "WeatherStationsReads",
        "query": "DECLARE @StoreTime as DATETIME;\n\nSET @StoreTime = CURRENT_TIMESTAMP;\n\nINSERT INTO cStoreWeatherStations\n(\n[ReadTime]\n,[DevID]\n,[CurrentHAccRainfall]\n,[PreviousHAccRainfall]\n,[Last24HAccRainfall]\n,[WindDirection]\n,[Anemometer]\n,[Temperature]\n,[Humidity]\n,[Pressure]\n,[BatteryLevel]\n)\nVALUES\n    (\n     @StoreTime,\n     {{{payload.0}}},\n     {{{payload.1}}},\n     {{{payload.2}}},\n     {{{payload.3}}},\n     {{{payload.4}}},\n     {{{payload.5}}},\n     {{{payload.6}}},\n     {{{payload.7}}},\n     {{{payload.8}}},\n     {{{payload.9}}}\n    )",
        "outField": "payload",
        "x": 1610,
        "y": 420,
        "wires": [
            [
                "4c299039.e10c58"
            ]
        ]
    },
    {
        "id": "989023f3.29ffc8",
        "type": "MSSQL-CN",
        "z": "",
        "tdsVersion": "7_4",
        "name": "WeatherStations",
        "server": "10.80.x.x",
        "port": "1433",
        "encyption": false,
        "database": "WeatherST",
        "useUTC": false,
        "connectTimeout": "15000",
        "requestTimeout": "15000",
        "cancelTimeout": "5000",
        "pool": "10"
    }
]

Flow B:

[
    {
        "id": "bd3d408c.87daf",
        "type": "function",
        "z": "374f65b3.b9a9ea",
        "name": "Split String",
        "func": "var i;\nfor (i = 0; i < msg.payload.length; i++)\n\nvar UpTimeEpoch = { payload: parseInt(msg.payload[1].slice(9,19))};\n//var UpTime = { payload: parseInt(msg.payload[1].slice(9,19))};\n//var days = Math.trunc(UpTime.payload / (24*3600));\n//var time = (new Date((UpTime.payload - days*24*3600) * 1000)).toUTCString().match(/(\\d\\d:\\d\\d:\\d\\d)/)[0];\n//UpTime.payload = days + \":\" + time;\nvar Load1min = { payload: parseFloat(msg.payload[2].slice(8,13))};\nvar Load5min = { payload: parseFloat(msg.payload[3].slice(0,5))};\nvar Load15min = { payload: parseFloat(msg.payload[4].slice(0,5))};\nvar UsedMem = { payload: parseInt(msg.payload[5].slice(14,20))};\nvar FreeMem = { payload: parseInt(msg.payload[6].slice(7,13))};\nvar GatewayID = { payload: parseInt(msg.payload[8])};\n\nreturn [UpTimeEpoch,Load1min,Load5min,Load15min,UsedMem,FreeMem,GatewayID];",
        "outputs": 7,
        "noerr": 0,
        "x": 890,
        "y": 300,
        "wires": [
            [
                "1b351a9c.1e2ef5"
            ],
            [
                "1b351a9c.1e2ef5"
            ],
            [
                "1b351a9c.1e2ef5"
            ],
            [
                "1b351a9c.1e2ef5"
            ],
            [
                "1b351a9c.1e2ef5"
            ],
            [
                "1b351a9c.1e2ef5"
            ],
            [
                "1b351a9c.1e2ef5"
            ]
        ]
    },
    {
        "id": "1b351a9c.1e2ef5",
        "type": "join",
        "z": "374f65b3.b9a9ea",
        "name": "",
        "mode": "custom",
        "build": "array",
        "property": "payload",
        "propertyType": "msg",
        "key": "topic",
        "joiner": "\\n",
        "joinerType": "str",
        "accumulate": false,
        "timeout": "",
        "count": "7",
        "reduceRight": false,
        "reduceExp": "",
        "reduceInit": "",
        "reduceInitType": "",
        "reduceFixup": "",
        "x": 1050,
        "y": 300,
        "wires": [
            [
                "84611128.5813f"
            ]
        ]
    },
    {
        "id": "84611128.5813f",
        "type": "simple-queue",
        "z": "374f65b3.b9a9ea",
        "name": "Insert to DB queue",
        "firstMessageBypass": true,
        "bypassInterval": "5000",
        "x": 1210,
        "y": 300,
        "wires": [
            [
                "8cf86df0.72483",
                "abca801e.ac13d8"
            ]
        ]
    },
    {
        "id": "abca801e.ac13d8",
        "type": "MSSQL",
        "z": "374f65b3.b9a9ea",
        "mssqlCN": "cd65dbe6.c215d",
        "name": "GatewayStats",
        "query": "DECLARE @StoreTime as DATETIME;\n\nSET @StoreTime = CURRENT_TIMESTAMP;\n\nINSERT INTO GatewayStats\n(\n[ReadTime]\n,[GatewayID]\n,[UpTime]\n,[LoadOne]\n,[LoadFive]\n,[LoadFifteen]\n,[RamUsed]\n,[RamFree]\n)\nVALUES\n    (\n     @StoreTime,\n     {{{payload.6}}},\n     {{{payload.0}}},\n     {{{payload.1}}},\n     {{{payload.2}}},\n     {{{payload.3}}},\n     {{{payload.4}}},\n     {{{payload.5}}}\n    )\n    ",
        "outField": "payload",
        "x": 1420,
        "y": 300,
        "wires": [
            []
        ]
    },
    {
        "id": "cd65dbe6.c215d",
        "type": "MSSQL-CN",
        "z": "",
        "tdsVersion": "7_4",
        "name": "IoT",
        "server": "10.80.x.x",
        "port": "1433",
        "encyption": false,
        "database": "IoT",
        "useUTC": false,
        "connectTimeout": "15000",
        "requestTimeout": "15000",
        "cancelTimeout": "5000",
        "pool": "10"
    }
]
8

any help?

9

All MSSQL nodes has problem with connect two or more connection to different MSSQL servers because connection is set to flow node not disconnect connection and remember old data to connect.

Sorry,
New version MSSQL plus looks like goods.

10

I am using the MSSQL Plus. you recommand to uninstall MSSQL Plus and reinstall it again?

11

Yes, but I'm not sure will be working.

12

MSSQL PLUS works great if you only connect to a single database. It keeps the connection live and if you connect to another source it will most likely connect to that point as well. The problem comes when you need to query or insert to the first connection. It's apparently not designed fully to connect, apply some query and then disconnect. Otherwise you should be able to make a connection to anything available and multiple connections. It's just a simple implementation and not fully tested to do much more than connect to a single database and query something. If you only need to query a single MSSQL node, your fine, otherwise you have to use another compatible mssql connector that you can install, and hope that will maintain more more than a single connection too. Be careful, if the nodes use the same filenames they may cause conflicts and errors. Node-RED should warn you.