Problem running SQL stored procedure

Using node-red-contrib-mssql-plus to connect to SQL server
Have got Select query running but getting an error when trying to execute a stored procedure, failing with error

Could not find stored procedure ''.

image

Both the flows use the same connection (should take out issues of Connection errors)
Have logged into the DB using SSME and run the SP as the user defined in the Connection and runs OK (Should take out issues of permissions to run SP's

Any pointers guys n gals?

Exported working Select node

[
    {
        "id": "9e3f82a78ffb4c36",
        "type": "MSSQL",
        "z": "2e8c441f11087307",
        "mssqlCN": "a51e405c.10f64",
        "name": "",
        "outField": "payload",
        "returnType": 0,
        "throwErrors": 1,
        "query": "SELECT TOP 5 *\n  FROM [MATTEC_PROHELP_TEST].[dbo].[DownCodes]",
        "modeOpt": "",
        "modeOptType": "query",
        "queryOpt": "",
        "queryOptType": "editor",
        "paramsOpt": "",
        "paramsOptType": "none",
        "rows": "rows",
        "rowsType": "msg",
        "params": [],
        "x": 390,
        "y": 920,
        "wires": [
            [
                "56e830ba1e27af4d"
            ]
        ]
    },
    {
        "id": "a51e405c.10f64",
        "type": "MSSQL-CN",
        "tdsVersion": "7_4",
        "name": "Mattec Test Server",
        "server": "dc1nwndbs028.nr.ad.newellco.com\\EPICORTEST",
        "port": "4080",
        "encyption": false,
        "trustServerCertificate": false,
        "database": "mattec_prohelp_test",
        "useUTC": false,
        "connectTimeout": "15000",
        "requestTimeout": "15000",
        "cancelTimeout": "5000",
        "pool": "5",
        "parseJSON": false,
        "enableArithAbort": false,
        "credentials": {}
    }
]

Exported failing SP Exec node

[
    {
        "id": "f62c41c8672c3a91",
        "type": "MSSQL",
        "z": "2e8c441f11087307",
        "mssqlCN": "a51e405c.10f64",
        "name": "",
        "outField": "payload",
        "returnType": 0,
        "throwErrors": 1,
        "query": "[MATTEC_PROHELP_TEST].[dbo].[MiuScrapEntry] \n",
        "modeOpt": "",
        "modeOptType": "execute",
        "queryOpt": "",
        "queryOptType": "editor",
        "paramsOpt": "",
        "paramsOptType": "editor",
        "rows": "rows",
        "rowsType": "msg",
        "params": [
            {
                "output": false,
                "name": "MachNo",
                "type": "int",
                "valueType": "num",
                "value": "120",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "SonID",
                "type": "int",
                "valueType": "num",
                "value": "0",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "PrimaryReason",
                "type": "int",
                "valueType": "num",
                "value": "4",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "SecondaryReason",
                "type": "int",
                "valueType": "num",
                "value": "4",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "Quantity",
                "type": "int",
                "valueType": "num",
                "value": "10",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "PQ_Flag",
                "type": "int",
                "valueType": "num",
                "value": "0",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "TimeLogged",
                "type": "int",
                "valueType": "msg",
                "value": "payload.timestamp",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "UnscrapCode",
                "type": "NVarChar(20)",
                "valueType": "num",
                "value": "0",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            }
        ],
        "x": 410,
        "y": 1080,
        "wires": [
            [
                "1988a7a017a51b1b"
            ]
        ]
    },
    {
        "id": "a51e405c.10f64",
        "type": "MSSQL-CN",
        "tdsVersion": "7_4",
        "name": "Mattec Test Server",
        "server": "dc1nwndbs028.nr.ad.newellco.com\\EPICORTEST",
        "port": "4080",
        "encyption": false,
        "trustServerCertificate": false,
        "database": "mattec_prohelp_test",
        "useUTC": false,
        "connectTimeout": "15000",
        "requestTimeout": "15000",
        "cancelTimeout": "5000",
        "pool": "5",
        "parseJSON": false,
        "enableArithAbort": false,
        "credentials": {}
    }
]

Hi Greg, welcome to the forum.

Everything looks ok apart from...

Try removing the carriage return from the query.

Does that fix your issue?

Doh!!!!!
I never read through the export, wasn't expecting to see anything I didn't already know :slight_smile:

Many thanks for the quick reply and nailing it 1st time

1 Like

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