# SQL SERVER Node

**URL:** <https://discourse.nodered.org/t/sql-server-node/92155>\
**Category:** General\
**Tags:** node-red-contrib-mssql-plus\
**Created:** [3 October 2024 16:21 UTC](https://discourse.nodered.org/t/sql-server-node/92155 "2024-10-03T16:21:34Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![LucasPelu](https://avatars.discourse-cdn.com/v4/letter/l/977dab/32.png) [@LucasPelu](https://discourse.nodered.org/u/LucasPelu)\
**Post date:** [3 October 2024 16:21 UTC](https://discourse.nodered.org/t/sql-server-node/92155/1 "2024-10-03T16:21:34Z")

</div>

Hello, I am working with SQL Server and I need to make a query using Node Red so that it brings me information from a table.  
my script is the following:

select \* from alc.ActivityType where ActivityTypeName = 'variable'

where it says "variable" I want to enter the values ​​that come from another node.  
I downloaded node-red-contrib-mssql-plus.  
I configured it correctly since when I add the script with a fixed variable it brings me the table information. But I can't find a way to add the variable coming from another node.

---

<div class="post-metadata">

**Author:** ![marcus-j-davies](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/marcus-j-davies/32/103435_2.png) [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)\
**Post date:** [3 October 2024 16:35 UTC](https://discourse.nodered.org/t/sql-server-node/92155/2 "2024-10-03T16:35:47Z")

</div>

Something like this should work (Note: I don't use this Node, so may not be 100%)

**WHEN** Query -\> **Editor**

* * *

```auto
select * from alc.ActivityType where ActivityTypeName = '{{{msg.filterValue}}}'

```

Your `msg`

```auto
msg.filterValue = 'Foo-Bar'

```

**WHEN** Query -\> **msg** (lets assume `topic`, and `Parameters` option is set to `payload`)

* * *

Your `msg`

```auto
msg.topic = select * from alc.ActivityType where ActivityTypeName = '@filterValue'
msg.payload = {
   filterValue: 'Foo-Bar'
}

```

I think @Steve-Mcl may correct me here 😇

---

<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:** [3 October 2024 16:44 UTC](https://discourse.nodered.org/t/sql-server-node/92155/3 "2024-10-03T16:44:32Z")

</div>

> [@marcus-j-davies](#):
>
> I think @Steve-Mcl may correct me here 😇

Somewhat

mssql-plus is a lot easier to use (in theory) and safer by default - when using it properly.

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

---

<div class="post-metadata">

**Author:** ![LucasPelu](https://avatars.discourse-cdn.com/v4/letter/l/977dab/32.png) [@LucasPelu](https://discourse.nodered.org/u/LucasPelu)\
**Post date:** [3 October 2024 17:08 UTC](https://discourse.nodered.org/t/sql-server-node/92155/4 "2024-10-03T17:08:45Z")

</div>

It didn't work for me, in the function node I have the msg:

```auto
msg.filterValue = 'intervencion\Reparación\Cañerias AG\.\PVC\90 mm. (Trabajo)'

```

the SQL node y set it like This:

 ![nodo](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/1/81c782958548b8795c5a1736ea3cc95d81bbaca8.png)

---

<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:** [3 October 2024 17:57 UTC](https://discourse.nodered.org/t/sql-server-node/92155/5 "2024-10-03T17:57:40Z")

</div>

Set the output to driver mode.  
Add a debug (set to show complete msg object)  
Show me the debug output (expand all properties)  
Show me your table with a manual query using that string in the where clause.

---

<div class="post-metadata">

**Author:** ![LucasPelu](https://avatars.discourse-cdn.com/v4/letter/l/977dab/32.png) [@LucasPelu](https://discourse.nodered.org/u/LucasPelu)\
**Post date:** [3 October 2024 18:04 UTC](https://discourse.nodered.org/t/sql-server-node/92155/6 "2024-10-03T18:04:38Z")

</div>

this is the debug

 ![nodo](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/c/9c65b111cdbc90e33d4f4afbc4343d625d5238e0.png)  
And this is the debug whit the response of the SQL node with de script on the query editor

 ![nodo 2](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/1/c123ac6d26971f07576e9d1d7f9a4b815962b9d0.png)

and this is the clipboard of this nodes.

```auto
[
    {
        "id": "8a7a4f463020edb6",
        "type": "MSSQL",
        "z": "4ab6a4fb7cf2f03a",
        "mssqlCN": "2b06f502f12b11a7",
        "name": "",
        "outField": "payload",
        "returnType": "1",
        "throwErrors": "0",
        "query": "select * from alc.ActivityType where ActivityTypeName = @type",
        "modeOpt": "",
        "modeOptType": "query",
        "queryOpt": "",
        "queryOptType": "editor",
        "paramsOpt": "",
        "paramsOptType": "editor",
        "rows": "rows",
        "rowsType": "msg",
        "parseMustache": false,
        "params": [
            {
                "output": false,
                "name": "type",
                "type": "VarChar",
                "valueType": "msg",
                "value": "filterValue",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            }
        ],
        "x": 820,
        "y": 1900,
        "wires": [
            [
                "516b732d290c57d9"
            ]
        ]
    },
    {
        "id": "516b732d290c57d9",
        "type": "debug",
        "z": "4ab6a4fb7cf2f03a",
        "name": "debug 456",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 1030,
        "y": 1900,
        "wires": []
    },
    {
        "id": "2b739a5e48d3eae0",
        "type": "inject",
        "z": "4ab6a4fb7cf2f03a",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 480,
        "y": 1900,
        "wires": [
            [
                "16702c357e386af0"
            ]
        ]
    },
    {
        "id": "16702c357e386af0",
        "type": "function",
        "z": "4ab6a4fb7cf2f03a",
        "name": "function 73",
        "func": "//msg.query = \"select * from alc.ActivityType where ActivityTypeName = 'intervencion\\Reparación\\Cañería AG\\.\\PVC\\90 mm. (Trabajo)'\"\n//msg.topic = \"select * from alc.ActivityType where ActivityTypeName = '@filterValue'\"\nmsg.filterValue = 'intervencion\\Reparación\\Cañería AG\\.\\PVC\\90 mm. (Trabajo)'\n\n\nreturn msg",
        "outputs": 1,
        "timeout": 0,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 650,
        "y": 1900,
        "wires": [
            [
                "8a7a4f463020edb6"
            ]
        ]
    },
    {
        "id": "2b06f502f12b11a7",
        "type": "MSSQL-CN",
        "tdsVersion": "7_4",
        "name": "",
        "server": "10.40.2.8",
        "port": "13341",
        "encyption": true,
        "trustServerCertificate": true,
        "database": "WLM",
        "useUTC": true,
        "connectTimeout": "15000",
        "requestTimeout": "15000",
        "cancelTimeout": "5000",
        "pool": "5",
        "parseJSON": false,
        "enableArithAbort": true,
        "readOnlyIntent": false
    }
]

```

---

<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:** [3 October 2024 19:27 UTC](https://discourse.nodered.org/t/sql-server-node/92155/7 "2024-10-03T19:27:59Z")

</div>

You haven't shown me a manual query that returns a row containing that exact string.

> [@Steve-Mcl](#):
>
> Show me your table with a manual query using that string in the where clause

---

<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:** [3 October 2024 19:29 UTC](https://discourse.nodered.org/t/sql-server-node/92155/8 "2024-10-03T19:29:09Z")

</div>

> [@LucasPelu](#):
>
> ![nodo 2](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/1/c123ac6d26971f07576e9d1d7f9a4b815962b9d0.png)

Wait, is that your data I see?

---

<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:** [4 October 2024 06:34 UTC](https://discourse.nodered.org/t/sql-server-node/92155/9 "2024-10-04T06:34:54Z")

</div>

ok, a second look - I know what is happening.

As you are setting the filter value in a function node AND it has `\` backslashes, they are acting as escapes.

Your filter value `intervencion\Reparación\Cañería AG\.\PVC\90 mm. (Trabajo)` is getting converted to `intervencionReparaciónCañería AG.PVC90 mm. (Trabajo)`  
proof: ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/8/98147f49172588503080f28d346c4a59b0fda328.png) before it even reaches the MSSQL node. A simple debug node BEFORE the MSSQL node would have highlighted this.

To escape a backslash in JavaScript string, you need to use double `\\` backslash

---

<div class="post-metadata">

**Author:** ![LucasPelu](https://avatars.discourse-cdn.com/v4/letter/l/977dab/32.png) [@LucasPelu](https://discourse.nodered.org/u/LucasPelu)\
**Post date:** [4 October 2024 14:05 UTC](https://discourse.nodered.org/t/sql-server-node/92155/10 "2024-10-04T14:05:22Z")

</div>

> [@LucasPelu](#):
>
> node-red-contrib-mssql-plus

Yes was that!!! Thank you so much!!!!

---

<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:** [18 October 2024 14:05 UTC](https://discourse.nodered.org/t/sql-server-node/92155/11 "2024-10-18T14:05:22Z")

</div>

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