# Can't upload some values to MSSQL

**URL:** <https://discourse.nodered.org/t/cant-upload-some-values-to-mssql/53931>\
**Category:** General\
**Tags:** database\
**Created:** [18 November 2021 10:50 UTC](https://discourse.nodered.org/t/cant-upload-some-values-to-mssql/53931 "2021-11-18T10:50:06Z")\
**Posts on this page:** 18\
**Page:** 1

<div class="post-metadata">

**Author:** ![evanic14](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/evanic14/32/23480_2.png) [@evanic14](https://discourse.nodered.org/u/evanic14)\
**Post date:** [18 November 2021 10:50 UTC](https://discourse.nodered.org/t/cant-upload-some-values-to-mssql/53931/1 "2021-11-18T10:50:06Z")

</div>

hi, when i try to upload a value to my database I get this message in my debug window:

> RequestError: Could not find stored procedure 'true'.

has anyone got a idea?

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [18 November 2021 12:01 UTC](https://discourse.nodered.org/t/cant-upload-some-values-to-mssql/53931/2 "2021-11-18T12:01:55Z")

</div>

too little information. pls share the code

---

<div class="post-metadata">

**Author:** ![evanic14](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/evanic14/32/23480_2.png) [@evanic14](https://discourse.nodered.org/u/evanic14)\
**Post date:** [18 November 2021 12:20 UTC](https://discourse.nodered.org/t/cant-upload-some-values-to-mssql/53931/3 "2021-11-18T12:20:44Z")

</div>

[database flow.txt](https://discourse.nodered.org/uploads/short-url/1z9Wt4BQgGEdajS4hLJkoCIBPXA.txt) (69.2 KB)  
i had to put the flow in a text flow

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [18 November 2021 12:46 UTC](https://discourse.nodered.org/t/cant-upload-some-values-to-mssql/53931/4 "2021-11-18T12:46:12Z")

</div>

I am so sorry, i am not fluent with MSSQL, i use MySQL and had working examples  
Someone else will definitely help, keep watching this space.

---

<div class="post-metadata">

**Author:** ![evanic14](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/evanic14/32/23480_2.png) [@evanic14](https://discourse.nodered.org/u/evanic14)\
**Post date:** [18 November 2021 12:57 UTC](https://discourse.nodered.org/t/cant-upload-some-values-to-mssql/53931/5 "2021-11-18T12:57:43Z")

</div>

No worries. Thanks for taking a look at my code.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [18 November 2021 15:18 UTC](https://discourse.nodered.org/t/cant-upload-some-values-to-mssql/53931/6 "2021-11-18T15:18:57Z")

</div>

If you edit the title of the thread and include MSSQL in it then it will be more likely that someone who knows about that will see it. I don't use it, but I understand that recommended node to use is node-red-contrib-mssql-plus (or a similar name).

---

<div class="post-metadata">

**Author:** ![evanic14](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/evanic14/32/23480_2.png) [@evanic14](https://discourse.nodered.org/u/evanic14)\
**Post date:** [18 November 2021 16:04 UTC](https://discourse.nodered.org/t/cant-upload-some-values-to-mssql/53931/7 "2021-11-18T16:04:24Z")

</div>

hi, thanks for the tip, I changed the titel. I've got this library installed:

> node-red-contrib-mssql

And i can upload some values to the database but i can't upload everything. I suspect an error in my query but i'm not entirely sure.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [18 November 2021 16:19 UTC](https://discourse.nodered.org/t/cant-upload-some-values-to-mssql/53931/8 "2021-11-18T16:19:39Z")

</div>

Look at the node's page in the [flows site](https://flows.nodered.org/node/node-red-contrib-mssql) and you will see that it is ancient and, by going to the github page linked there you will see that no longer even exists.

---

<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:** [18 November 2021 23:03 UTC](https://discourse.nodered.org/t/cant-upload-some-values-to-mssql/53931/9 "2021-11-18T23:03:53Z")

</div>

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

Pretty sure that doesnt support stored procedures. (and its old and has known bugs)

`node-red-contrib-mssql-plus` has direct support for stored procedures AND parameters (to reduce chances of SQL injection).

Here is an example: [MSSQL-PLUS - passing array to UDT parameter (TVP param in a stored procedure) - #22 by Steve-Mcl](https://discourse.nodered.org/t/mssql-plus-passing-array-to-udt-parameter-tvp-param-in-a-stored-procedure/30049/22)

---

<div class="post-metadata">

**Author:** ![evanic14](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/evanic14/32/23480_2.png) [@evanic14](https://discourse.nodered.org/u/evanic14)\
**Post date:** [19 November 2021 12:10 UTC](https://discourse.nodered.org/t/cant-upload-some-values-to-mssql/53931/10 "2021-11-19T12:10:38Z")

</div>

right. have installed the library you recommended and it is doing something. however i think im doing something wrong. i usume i need to format the data as you showed in the thread you linked in your comment but i don't really know where i should start.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/1/71a85ec4ffb384d414add5c47ec9fa151d68771d.png)

EDIT: i changed the input value from a varchar to an int and now the `Invalid template! Template should be a "string" but "number" was recieved` error code is gone but the other ones remain.

---

<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:** [19 November 2021 16:47 UTC](https://discourse.nodered.org/t/cant-upload-some-values-to-mssql/53931/11 "2021-11-19T16:47:24Z")

</div>

You will have to share what is entered in the MSSQL-PLUS node and put a debug node BEFORE and AFTER the MSSQL node for me to be able to have any idea.

---

<div class="post-metadata">

**Author:** ![evanic14](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/evanic14/32/23480_2.png) [@evanic14](https://discourse.nodered.org/u/evanic14)\
**Post date:** [20 November 2021 18:49 UTC](https://discourse.nodered.org/t/cant-upload-some-values-to-mssql/53931/12 "2021-11-20T18:49:38Z")

</div>

MSSQL-PLUS node:

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

setup:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/5/45c909120dc84c5e5dc794e77bd1a3b39be75b37.png)

output:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/8/78ccbd724f3a1fcd7b380e954cd5df2239099b02.png)

im still getting a formatting error for some reason. I solved this error the other day. but i don't think solving that will solve all of my problems.  
ill show you a picture of my database setup aswell:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/c/dcf945d881ef6a9d296b70bb20caf950d161bb96.png)

if you need me to translate some of the dutch words I used, just tell me.

---

<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:** [20 November 2021 19:43 UTC](https://discourse.nodered.org/t/cant-upload-some-values-to-mssql/53931/13 "2021-11-20T19:43:49Z")

</div>

> [@evanic14](#):
>
> MSSQL-PLUS node:
> 
> ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/d/cdb27ec7d8f56ccaaf591d2de406e218404c38b7.png)
> 
> setup:
> 
> ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/5/45c909120dc84c5e5dc794e77bd1a3b39be75b37.png)
> 
> output:

So it looks like you are generating a dynamic SQL command in the function node however you have the query set to editor.

It would make more sense to enter the SQL in the SQL node and make use of parameters (to negate any SQL injection) however for the sake of this simplicity, you need to set the query to `msg.payload` or `msg.topic` (to match wherever you set the query in your function node)

---

<div class="post-metadata">

**Author:** ![evanic14](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/evanic14/32/23480_2.png) [@evanic14](https://discourse.nodered.org/u/evanic14)\
**Post date:** [21 November 2021 17:20 UTC](https://discourse.nodered.org/t/cant-upload-some-values-to-mssql/53931/14 "2021-11-21T17:20:00Z")

</div>

this is what i came up with:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/9/190af07ad10472389f4de8558ce1bdad25978c2f.png)  
I put the query in the sql node like you said and i seperated the if statement:  
BEFORE:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/e/befc50ba67c3d3420f4d2687a2a4205675137cfa.png)  
AFTER:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/2/420393c856dd6e0365e1537080692b05d3b49d18.png)  
but the if statement doesn't work.

i've got a series of buttons and tabs in my dasboard which collect data, this data needs to be pushed to the database IF button 'toevoegen' (this means 'add' in dutch) is pressed.

this is how i define the 'toevoegen' button:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/3/d35db6223af5d20c4700fbc8beca36a6050170cc.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:** [21 November 2021 19:05 UTC](https://discourse.nodered.org/t/cant-upload-some-values-to-mssql/53931/15 "2021-11-21T19:05:52Z")

</div>

If you have the `query` set to `editor` you cannot pass a query into the node.

What I was saying in the previous post was to use the parameters.

Here is an example that explains how it works...

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

---

<div class="post-metadata">

**Author:** ![evanic14](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/evanic14/32/23480_2.png) [@evanic14](https://discourse.nodered.org/u/evanic14)\
**Post date:** [21 November 2021 20:16 UTC](https://discourse.nodered.org/t/cant-upload-some-values-to-mssql/53931/16 "2021-11-21T20:16:50Z")

</div>

This seems to be working. But before I'll mark your reply as the solution I'll have to do some tests. But for now thanks a million for your time and patience.

I'll let you know when I'm done.

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

---

<div class="post-metadata">

**Author:** ![evanic14](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/evanic14/32/23480_2.png) [@evanic14](https://discourse.nodered.org/u/evanic14)\
**Post date:** [22 November 2021 08:31 UTC](https://discourse.nodered.org/t/cant-upload-some-values-to-mssql/53931/17 "2021-11-22T08:31:37Z")

</div>

right, the database side is working as far as I can see. But the if statement doesn't work.

this is the code in the if stat node  
`if(flow.get('Toevoegen')==true) { msg.payload = true; } return msg;`  
and this is what the flow looks like:

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

---

<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:** [8 December 2021 10:27 UTC](https://discourse.nodered.org/t/cant-upload-some-values-to-mssql/53931/18 "2021-12-08T10:27:16Z")

</div>

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