# MSSQL-PLUS, Same query, different response
**URL:** https://discourse.nodered.org/t/mssql-plus-same-query-different-response/43340
**Category:** General
**Created:** [29 March 2021 07:54 UTC](https://discourse.nodered.org/t/mssql-plus-same-query-different-response/43340 "2021-03-29T07:54:34Z")
**Posts on this page:** 11
**Page:** 1
### Author:  [@JonasH](https://discourse.nodered.org/u/JonasH)
#### Post date: [29 March 2021 07:54 UTC](https://discourse.nodered.org/t/mssql-plus-same-query-different-response/43340/1 "2021-03-29T07:54:34Z")
Hi all,
I´m using MSSQL-PLUS to read out a value from MS SQL DB.
I created the Query manually, than I get the correct response from the DB.
I create the Query automatically, than I get an "empty" response from DB.
What I´m doing wrong?
Thank you in advance!
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [29 March 2021 09:39 UTC](https://discourse.nodered.org/t/mssql-plus-same-query-different-response/43340/2 "2021-03-29T09:39:19Z")
Please connect the debug node to the function and the inject and show what they give.
Also experiment with the query and find which bit is failing. If you remove the where clause (in the function) do you get records? If you mis-spell the database name in the function what happens?
---
### Author:  [@JonasH](https://discourse.nodered.org/u/JonasH)
#### Post date: [29 March 2021 09:52 UTC](https://discourse.nodered.org/t/mssql-plus-same-query-different-response/43340/3 "2021-03-29T09:52:21Z")
The msg.payload is the same in both ways.
What is the difference between string [22] and [9] ?
Removing the WHERE Clause, I get the data of the DB with both versions.
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [29 March 2021 09:55 UTC](https://discourse.nodered.org/t/mssql-plus-same-query-different-response/43340/4 "2021-03-29T09:55:38Z")
You are including the single quotes in the where clause but the database does not have single quotes in the data.
Export your flow and I will show you how to simplify this using parameters.
---
### Author:  [@JonasH](https://discourse.nodered.org/u/JonasH)
#### Post date: [29 March 2021 09:58 UTC](https://discourse.nodered.org/t/mssql-plus-same-query-different-response/43340/5 "2021-03-29T09:58:04Z")
The flow...
```auto
[{"id":"6bab9a99.9c35b4","type":"tab","label":"Flow 4","disabled":false,"info":""},{"id":"6daabca1.3208b4","type":"inject","z":"6bab9a99.9c35b4","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":140,"wires":[["9b57933b.1214a"]]},{"id":"9b57933b.1214a","type":"modbus-getter","z":"6bab9a99.9c35b4","name":"Lese Auftragsnummer","showStatusActivities":false,"showErrors":false,"logIOActivities":false,"unitid":"1","dataType":"HoldingRegister","adr":"34950","quantity":"20","server":"b80bda77.541c78","useIOFile":false,"ioFile":"","useIOForPayload":false,"emptyMsgOnFail":false,"keepMsgProperties":false,"x":240,"y":220,"wires":[["3fdf7993.047b26"],[]]},{"id":"3fdf7993.047b26","type":"function","z":"6bab9a99.9c35b4","name":"WORD to STRING","func":"msg.payload = Buffer.from(msg.payload);\nmsg.payload = msg.payload.toString();\nmsg.payload = \"'\"+msg.payload+\"'\";\nreturn msg;","outputs":1,"noerr":0,"x":330,"y":320,"wires":[["c7c7b3c9.47d44","803cb449.e7dc98"]]},{"id":"803cb449.e7dc98","type":"MSSQL","z":"6bab9a99.9c35b4","mssqlCN":"b95d3c1d.51301","name":"Get Pruefauftrag","outField":"payload","returnType":"1","throwErrors":"0","query":"SELECT * FROM Pruefauftrag;","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"none","params":[{"output":false,"name":"aNr","type":"VarChar(20)","valueType":"global","value":"Auftrag"}],"x":650,"y":420,"wires":[["f2b6fa59.ba56e8"]]},{"id":"5e5ca7c4.200ee8","type":"inject","z":"6bab9a99.9c35b4","name":"","topic":"","payload":"'PA-1212'","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":560,"wires":[["c7c7b3c9.47d44","803cb449.e7dc98"]]},{"id":"f2b6fa59.ba56e8","type":"debug","z":"6bab9a99.9c35b4","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":790,"y":500,"wires":[]},{"id":"c7c7b3c9.47d44","type":"debug","z":"6bab9a99.9c35b4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":430,"y":420,"wires":[]},{"id":"b80bda77.541c78","type":"modbus-client","z":"","name":"mTRON T CPU","clienttype":"tcp","bufferCommands":true,"stateLogEnabled":false,"queueLogEnabled":false,"tcpHost":"192.168.178.46","tcpPort":"502","tcpType":"DEFAULT","serialPort":"/dev/ttyUSB","serialType":"RTU-BUFFERD","serialBaudrate":"9600","serialDatabits":"8","serialStopbits":"1","serialParity":"none","serialConnectionDelay":"100","unit_id":"1","commandDelay":"1","clientTimeout":"1000","reconnectOnTimeout":true,"reconnectTimeout":"2000","parallelUnitIdsAllowed":true},{"id":"b95d3c1d.51301","type":"MSSQL-CN","z":"","tdsVersion":"7_4","name":"MS Azure SQL-DB","server":"test-server-eng.database.windows.net","port":"1433","encyption":true,"trustServerCertificate":true,"database":"TestDatenbank","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5","parseJSON":false,"enableArithAbort":true}]
```
Admin edit - surround flow with backticks to make it importable\*
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [29 March 2021 10:07 UTC](https://discourse.nodered.org/t/mssql-plus-same-query-different-response/43340/6 "2021-03-29T10:07:32Z")
Dont add more quotes
Use parameters
```auto
[{"id":"6daabca1.3208b4","type":"inject","z":"6bab9a99.9c35b4","name":"","repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":188,"y":128,"wires":[["9b57933b.1214a"]]},{"id":"9b57933b.1214a","type":"modbus-getter","z":"6bab9a99.9c35b4","name":"Lese Auftragsnummer","showStatusActivities":false,"showErrors":false,"logIOActivities":false,"unitid":"1","dataType":"HoldingRegister","adr":"34950","quantity":"20","server":"b80bda77.541c78","useIOFile":false,"ioFile":"","useIOForPayload":false,"emptyMsgOnFail":false,"keepMsgProperties":false,"x":388,"y":128,"wires":[["3fdf7993.047b26"],[]]},{"id":"3fdf7993.047b26","type":"function","z":"6bab9a99.9c35b4","name":"WORD to STRING","func":"msg.payload = Buffer.from(msg.payload);\nmsg.payload = msg.payload.toString();\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":218,"y":208,"wires":[["803cb449.e7dc98","3dcb9e6a.6848c2"]]},{"id":"803cb449.e7dc98","type":"MSSQL","z":"6bab9a99.9c35b4","mssqlCN":"b95d3c1d.51301","name":"Get Pruefauftrag","outField":"payload","returnType":"1","throwErrors":"0","query":"SELECT * FROM Pruefauftrag \nwhere Pruefauftrag = @Pruefauftrag;","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","params":[{"output":false,"name":"Pruefauftrag","type":"VarChar(20)","valueType":"msg","value":"payload"}],"x":480,"y":208,"wires":[["f2b6fa59.ba56e8"]]},{"id":"5e5ca7c4.200ee8","type":"inject","z":"6bab9a99.9c35b4","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"PA-1212","payloadType":"str","x":252,"y":320,"wires":[["803cb449.e7dc98"]]},{"id":"f2b6fa59.ba56e8","type":"debug","z":"6bab9a99.9c35b4","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":642,"y":208,"wires":[]},{"id":"3dcb9e6a.6848c2","type":"debug","z":"6bab9a99.9c35b4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":230,"y":256,"wires":[]},{"id":"b80bda77.541c78","type":"modbus-client","name":"mTRON T CPU","clienttype":"tcp","bufferCommands":true,"stateLogEnabled":false,"queueLogEnabled":false,"tcpHost":"192.168.178.46","tcpPort":"502","tcpType":"DEFAULT","serialPort":"/dev/ttyUSB","serialType":"RTU-BUFFERD","serialBaudrate":"9600","serialDatabits":"8","serialStopbits":"1","serialParity":"none","serialConnectionDelay":"100","unit_id":"1","commandDelay":"1","clientTimeout":"1000","reconnectOnTimeout":true,"reconnectTimeout":"2000","parallelUnitIdsAllowed":true},{"id":"b95d3c1d.51301","type":"MSSQL-CN","tdsVersion":"7_4","name":"MS Azure SQL-DB","server":"test-server-eng.database.windows.net","port":"1433","encyption":true,"trustServerCertificate":true,"database":"TestDatenbank","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5","parseJSON":false,"enableArithAbort":true}]
```
PS, to simplify modbus data conversion to usable types, you might find [node-red-contrib-buffer-parser](https://flows.nodered.org/node/node-red-contrib-buffer-parser) useful (it was made for this type of operation)
---
### Author:  [@JonasH](https://discourse.nodered.org/u/JonasH)
#### Post date: [29 March 2021 10:16 UTC](https://discourse.nodered.org/t/mssql-plus-same-query-different-response/43340/7 "2021-03-29T10:16:20Z")
Thank you for your explanations!
But I still have the same problem.
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [29 March 2021 10:24 UTC](https://discourse.nodered.org/t/mssql-plus-same-query-different-response/43340/8 "2021-03-29T10:24:44Z")
Am i right in thinking the bottom (inject) works?
As for the modbus version - your string conversion might have un-printable characters.
thats why i suggested buffer-tools.
Try adding .trim() call to your function node.
PS, expand the "queryParams" on the failed query & show me what is in there?
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [29 March 2021 10:35 UTC](https://discourse.nodered.org/t/mssql-plus-same-query-different-response/43340/9 "2021-03-29T10:35:04Z")
> [@JonasH](#):
>
> What is the difference between string [22] and [9] ?
I don't know if it is still relevant, but the difference is that the long one must have some unprintable characters in it.
---
### Author:  [@JonasH](https://discourse.nodered.org/u/JonasH)
#### Post date: [29 March 2021 10:46 UTC](https://discourse.nodered.org/t/mssql-plus-same-query-different-response/43340/10 "2021-03-29T10:46:49Z")
I changed the parsing and now I have a string without un-printable characters.
Now it works!
Thank you so much!
---
### Author:  [@system](https://discourse.nodered.org/u/system)
#### Post date: [12 April 2021 10:47 UTC](https://discourse.nodered.org/t/mssql-plus-same-query-different-response/43340/11 "2021-04-12T10:47:48Z")
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.