[SOLVED] Node-red-contrib-mssql-plus

General
1

Hi all.
I have problems connecting to a mssql database.
I'm using the following node "node-red-contrib-mssql-plus 0.30".
The db is accessible with other programs and when I try to run a query I get this error:
{"_msgid": "b659f22e.a68e3", "topic": "", "payload": 1569512403346, "query": "SELECT * FROM tb_TEST WHERE N = 5 AND COD = 1253 "," error ": {" code ":" ESOCKET "," message ":" Connection lost - write EPIPE "," name ":" ConnectionError "}}
Any ideas to solve?

All running on node-red 0.20.5

Thanks

2

What version of SQL server?
Have you set the correct SQL server version on the config node?

Can you post a screenshot of your config node too?

3

I have a doubt before I can use the node node-red-contrib-mssql-plus I have to install this component: $ npm install mssql
Tomorrow I will post the screenshots

Thanks for your help

4

There is no need to npm install anything. You should simply install via palette.

I am asking your version of SQL server and a screen shot of your config node to evaluate your setup.

While you are at it, please also specify your node version node -v

5

Hi
Thats the flow


[
    {
        "id": "ae2e5a45.2a7d68",
        "type": "tab",
        "label": "Test_MSSQL",
        "disabled": false,
        "info": ""
    },
    {
        "id": "f24deb49.b51e08",
        "type": "MSSQL",
        "z": "ae2e5a45.2a7d68",
        "mssqlCN": "72110aa3.1f7594",
        "name": "TestQuery",
        "query": "SELECT OPERATORE FROM tb_TEST WHERE N_PR=5 AND COD_PR=1253",
        "outField": "payload",
        "returnType": "0",
        "throwErrors": "0",
        "x": 470,
        "y": 200,
        "wires": [
            [
                "d07d615d.2ff93"
            ]
        ]
    },
    {
        "id": "bf3ebe40.551bc",
        "type": "inject",
        "z": "ae2e5a45.2a7d68",
        "name": "",
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "x": 260,
        "y": 200,
        "wires": [
            [
                "f24deb49.b51e08"
            ]
        ]
    },
    {
        "id": "d07d615d.2ff93",
        "type": "debug",
        "z": "ae2e5a45.2a7d68",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "x": 650,
        "y": 200,
        "wires": []
    },
    {
        "id": "72110aa3.1f7594",
        "type": "MSSQL-CN",
        "z": "",
        "tdsVersion": "7_2",
        "name": "TestDB",
        "server": "dbserver",
        "port": "1433",
        "encyption": true,
        "database": "DB_PR",
        "useUTC": true,
        "connectTimeout": "15000",
        "requestTimeout": "15000",
        "cancelTimeout": "15000",
        "pool": "5"
    }
]

The version of node is

node -v  
V12.2.0

MSSQL for the test is : 2003

I tried with all the versions of TDS getting the same behavior, do I have to ask the administrator of the DB to perform configurations on the mssql server?
Something like

  • Use Mixed Mode Security
  • Turn on Named Pipes and TCPIP communication using the SQL Server configuration tool

Maybe I should try with a mssql 2016 or 2013, maybe I would have more chances to make it all work

Thank you very much for your availability

38
Screenshot 2019-09-27 10.48.38.png1920Ă—1080 257 KB

6

Works for me on SQL 2003...

image
image.png951Ă—207 11.1 KB

Obvious stuff 1st.

  1. On the config node - Turn off encryption
  2. On the config node - Ensure TDS 7_1 (SQL Server 2000/2003)
  3. Can you ping dbserver from the machine running node-red?
  4. Are you certain the port is 1433
    1 How to get sql server port number (first result has decent info)
  5. Is the SQL Server install an instance or not?
    1. If it is an instance you should set the server field hostname\instance_name

See how you get on (I suspect it is the mix of encryption and TDS)

1 Like
7

Hi Steve

I suspect it is the mix of encryption and TDS

That could be the cause.

To solve the problem definitively I convinced the database administrator to switch to mssql2016 and now everything works.
Now we are seriously starting to use Node-red for our industry 4.0 applications.
Node Red is a powerful tool and the Node Red community is just as powerful.

Thank you very much Steve :smiley:

00
001920Ă—1080 331 KB

1 Like