MSSQL node-red-contrib-mssql-plus behavior

#1

"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.

0 Likes

#2

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.

0 Likes

#3

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
0 Likes

#4

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.

0 Likes

#5

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.

0 Likes

#6

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

0 Likes