Node-red returns empty when sending query to Microsoft SQL Server

Hi, i’m having a problem using Microsoft SQL server with node-red and i hope someone can help me, also sorry if i'm not using the correct format for the post. I’m using the mssql-plus pallete and it Works in general, i send a string with the query and i’m returned with the result, but when i try some commands it just returns empty. I have already tried the same queries directly in SQL server and it Works just fine, but not when i do it through node-red. I was initialy suspecting that the return was too long or maybe the request time was too long, but nothing changed after increasing these in the mssql-plus settings, and later i proved that it was not the case, since i did a query with a very big return and it worked, but when i try a small query with some specific commands it just don’t work.

Example that don’t work:

Example that work:

The two examples are the same query, just changing somethings, the first example don’t work, but when i do the same query with minus one “where”, it Works, and when i do the same query with no “where” at all it also Works (i could not send the third image because new users can only send two images). The query with no “where” takes a larger amount of time and have a larger return than the one that don’t work, so it is not a time out or a limit in the return size. Also i can't send the node-red JSON because it only works with the database and i'm not autorized to share the database, i hope you guys can help me with the limited information i'm providing. If something is not clear just ask and i will gladly do my best to explain. Thanks in advance.

Hi @Davih_andrade - Welcome to forums

The only difference I see is the year clause.
Therefore, are you sure you have records that have dates that

  • Has the Day of 10
  • Has the Month of 7
  • Has the Year of 2022

If omitting the year clause yields records, then it would seem to me that there are no records of that date in 2022 that match the criteria :man_shrugging:

Hi, thanks for the response! I don't think it was 100% clear in my post, but the query returns just fine when i run the code directly in the SQL server, and just when i do it through node-red it returns empty.

Ah right ok...

the second query that works - are the 2 datasets identical? Node RED vs SSMS
I wonder if it's an international format thing with the date?

can you try YYYY-MM-DD?

That was exactly the problem, thank you very much!

1 Like

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