MSSQL-PLUS node error

General
1

I am trying to execute a stored procedure on a MSSQL database. I have attempted to closely follow the instructions here. Though I don't understand all the specifics.

Here is how the MSSQL node is configured currently:

image
image1239Ă—1015 145 KB

I have the connection configured and it doesn't give me any obvious warnings there.
But I get this in the debug.

image
image585Ă—579 58.7 KB

My skillset is not yet sharpened enough to that I know where to start looking.
I don't manage the db or the stored procedure, but I have been giving the name and parameters.

Appreciate your input and let me know if you need more information!! Thanks so much.

2

Are you sure the values are in msg. and not msg.payload.?

Add a debug node BEFORE the SQL node and show us the output.

PS: to be sure you use the correct path to an item, use the tools provided:

There’s a great page in the docs (Working with messages : Node-RED) that will explain how to use the debug panel to find the right path to any data item.

Pay particular attention to the part about the buttons that appear under your mouse pointer when you over hover a debug message property in the sidebar.

BX00Cy7yHi

3

Yes, Here is the function ahead of the MSSQL node and I'm returning mtr1.

var mtr1 = {WaterMeterId: 1, 
            WaterMeterPanelId: 1,
            GallonsPerMinute: meter1};

But I have solved the issue. I believe the error was there from before I had the connection configured, and although I had redeployed, I had failed to retrigger the node itself.

I have an issue where it doesn't resolve the name of the server, I need to use the IP address. Does it not use the DNS server that the Pi itself is using? I did not enter anything under Domain in the connection settings because I was unable to find documentation of how its used.

Another question, if the MSSQL node receives multiple messages simultaneously, does it queue them and process them gracefully. It appears to be working fine, but I was wondering if that's a good practice.

4

Good Morning,
This is my main question remaining:

Does any one have any thoughts? Is this good practice from the perspective of the MSSQL node itself and from the DB?

5

I forget if there is anything inside the node that would potentially cause issues with concurrency. I do know if you hammer it you can reach your connection limit.

What i always did with SQL (and many other things that are async/external like ModBus and PLC comms) is to create a kinda streaming gateway - far less complex than it sounds.

Essentially, I would create a subroutine with a small rate limit of say 0.1sec just to throttle moments of madness.

e.g
image

(just change HTTP to MSSQL-PLUS - its the same pattern)

1 Like
6

The rate limit node is a good idea. I am sending a bunch of messages every 2 seconds and using that node I can effectively spread them out over the wait time.

Thanks!

7

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