"node-red-contrib-mssql-plus", I thought was working fine until I upgraded to NR 20.0-beta.3. But I'm not sure if the 20.0-beta.3 is the problem. It's not exactly not working, but giving me an error when I click a button to pull data from a particular database. The one with the problem is MSSQL 2008-R2 and not another older database server which is 2002. The first flow gets data from the 2008R2 to get a part number then to pull data from a SKU parts table in 2002. The first queries work fine and the system gets back all it needs. The second attempt to update on a change yields an error "Object not found" citing the table name in the 2008R2 DB while the connection is still opened. Has anyone run into this with "node-red-contrib-mssql-plus"? Which MSSQL nodes are best? and does anyone have a favourite? Has anyone run into this sort of thing using the new beta.3? Thanks.
As I posted when you asked this last time:
Show us the query you are running and the exact error message please. Assuming you are feeding the query in a message then put a debug node in to show the query.
Colin,
I upgraded my Node-RED nodes. The MSSQL was a newer version posted 2 months ago. On the new version 02.0.Beta-3 I upgraded all of my nodes. As stated, it's probably not a factor on the Node-RED version, but the "node-red-contrib-mssql-plus" version which is causing this problem.
I meant to send the query, but got in between other things going on, I apologize. I also noted that my results are dependent on the type of MSSQL Server that my query is tied to. For Example the options are...
7_1(SQL Server 2000),
7_2 (SQL Server 2005),
7_3_A (SQL Server 2008),
7_3_B (SQL Server 2008R2),
7_4 (SQL Server 2012/2014)
The queries work! The subsequent queries which are just a button press cause the connection to 7_3_B (SQL Server 2008R2) fail with an error. NOT THE FIRST TIME AROUND, but only subsequent button presses... This did not happen before. There were no changes to the queries, but only updates to the node version.
There are 2 databases which I am connecting and getting data.
The sysetm has no problem connecting and querying 7_1(SQL Server 2000) at all, multiple tries, not a problem at all... but the problem is only on subsequent button presses to query the 7_3_B (SQL Server 2008R2). The SECOND button press yields this. "RequestError: Invalid object name 'myDB.dbo.myTable'."
To remedy the problem I tried "node-red-contrib-sqldbs" to get data from (2008R2). IT WORKS!
Going back and trying some things, Selections on the MSSQL node and Connection attributes. I cannot locate anything which I can change on the client side which will make a difference. Except to use another SQL node like I did.
SELECT TOP 1 j.[Capture02] as Product
FROM [myDB].[dbo].[tJbSysActual] as jsa
JOIN [myDB].[dbo].[tJb] as j ON jsa.JobID = j.ID
WHERE SystemID = 22 ORDER BY j.[ID] DESC
Colin, To reply to your note, Thank you for the interest in my problem. I re-posted here in order to comply with the request to not have it on the node-red beta.3 topic... As it may or may not be related to the beta release. However, something is making this MSSQL node behave strangely. I have had to use other means to get the results I had prior to the update. I will try to post something on NPM.
I am seeing the same problem. Looks like if I have two mssql-nodes for different servers the first one contacted gets the connection. All queries for the second server are executed on the first one, of course failing.
I think the best thing is to raise an issue against the node author. So assuming you are using node-red-contrib-mssql-plus this is https://github.com/bestlong/node-red-contrib-mssql-plus/issues
Dear all, I have witnessed the same problem and although I can recreate it on one system, I can't on another. I'll check versions later. It may be related to the required version of "mssql" in the nodes package. It permits very old version.
Another thing that bothers me about that node is it still triggers an output upon error.
One more issue, you only get the first result if multiple queries are made.
And lastly, you get no real confirmation of success of an insert or update.
So...
I have forked the repo, fixed all of the above (in a backwards compatible manor), added some options, updated the package.JSON to use v5 of mssql and hope to make a pull request in the coming days.
I'd appreciate it if someone was willing to beta test? Let me know.
I did some more research on this. Apparently the problem is due to making connections vs. using the connection pool. If you make new connections on each query, or each node trigger, the connection configuration(s) gets out of sync.
I’m thinking the author didn’t write this node to fully and completely work with “mssql”. Just enough to do what he needed. I have a need to get this working better for multiple SQL database connections. I’ll certainly work with you on this.
hi @tree-frog, I have updated my fork.
I have tested with multiple SQL Server (2000, 2008 and 2012). The wrong config issue doesn't occur.
I have fed it bad SQL, bad SQL Address, and other deliberate errors, chained nodes of same and different servers, triggered parallel nodes at same time with differing servers, multiple SELECTs in one node, multiple selects where one query is bad etc - so far so good 
If you wish to test it, you will need to do the following...
- remove node-red-contrib-mssql-plus
1.1 This can be done from the pallete (if all instances of it are deleted first)
or to avoid deleting your nodes...
1.2 Stop node-red
1.3cd ~/node-red
1.4npm uninstall node-red-contrib-mssql-plus - Install from github...
2.1cd ~/node-red
2.2npm install Steve-Mcl/node-red-contrib-mssql-plus
NOTE: This version of node-red-contrib-mssql-plus uses the same internal names so any nodes you have already configured should just work
Let me know if you have success / failure please.
Some screen shots...
Loaded your version of "node-red-contrib-mssql-plus" and it's WORKING!.. I cannot thank you enough.
Thanks for updating the documentation!
Kind Regards,
Kyle
Thanks for the feedback.
I'd like to give it a wee while in case something odd crops up.
Please keep me posted.
Cheers.
