# Ncd device string data to sql using mssql plus node
**URL:** https://discourse.nodered.org/t/ncd-device-string-data-to-sql-using-mssql-plus-node/14325
**Category:** General
**Created:** [14 August 2019 18:33 UTC](https://discourse.nodered.org/t/ncd-device-string-data-to-sql-using-mssql-plus-node/14325 "2019-08-14T18:33:55Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@Pitmaster](https://discourse.nodered.org/u/Pitmaster)
#### Post date: [14 August 2019 18:33 UTC](https://discourse.nodered.org/t/ncd-device-string-data-to-sql-using-mssql-plus-node/14325/1 "2019-08-14T18:33:55Z")
Not certain if I have the right area for this. I am having a heck of time trying to get a string value from an ncd device to a mssql database. The specific value I am attempting to get into the db is data.sensor\_type. I am running the latest supported versions of node-red, node.js, and mssql plus node.
The error I am receiving from the node is calling it an invalid column, but I believe the sql syntax is correct.
I can force a string value (when entered directly into the sql syntax) and it works just fine getting into the db. All numerical values go into the db just fine. I have tried sensor\_type (as string value) and it gives me the same type of error from the node.
Sql Syntax in the MSSQL node:
INSERT INTO [temperaturehumidity] (temp,humidity,nodeID,voltage,SensorName) VALUES ({{{payload.temperature}}}\*1.8+32,{{{payload.humidity}}},{{{data.nodeId}}},{{{data.battery}}},{{{data.sensor\_name}}})
Flow:
```auto
[{"id":"24a7e6c1.bbd94a","type":"ncd-wireless-node","z":"a41e8b1b.0dadc","name":"Room 1 Encapsulation Sensor (Semi)","connection":"99bc8983.06925","config_comm":"99bc8983.06925","addr":"00:13:a2:00:41:a3:d1:cd","sensor_type":"1","auto_config":true,"node_id":0,"delay":"300","destination":"0000FFFF","power":4,"retries":10,"pan_id":"7FFF","change_enabled":"","change_pr":"0","change_interval":"0","cm_calibration":"60.6","bp_altitude":"0","bp_pressure":"0","bp_temp_prec":"0","bp_press_prec":"0","amgt_accel":"0","amgt_mag":"0","amgt_gyro":"0","impact_accel":"0","impact_data_rate":"4","impact_threshold":25,"impact_duration":1,"activ_interr_x":1,"activ_interr_y":2,"activ_interr_z":4,"activ_interr_op":8,"filtering":0,"data_rate":5,"time_series":0,"reading_type":1,"x":300,"y":300,"wires":[["df6ab3eb.c0147","93e2f5e.0e0a108","6b76aa4d.37758c","86b55769.fca548","febbac70.a19a2"]]},{"id":"86b55769.fca548","type":"MSSQL","z":"a41e8b1b.0dadc","mssqlCN":"48d548c2.d378f","name":"MSSQL","query":"INSERT INTO [temperaturehumidity] (temp,humidity,nodeID,voltage,SensorName) VALUES ({{{payload.temperature}}}*1.8+32,{{{payload.humidity}}},{{{data.nodeId}}},{{{data.battery}}},'again a test')","outField":"payload","returnType":"0","throwErrors":"0","x":840,"y":300,"wires":[[]]},{"id":"99bc8983.06925","type":"ncd-gateway-config","z":"","name":"Wireless Modem","comm_type":"tcp","ip_address":"sanitized","tcp_port":"sanitized","port":"","baudRate":"115200","pan_id":"7FFF","rssi":false},{"id":"48d548c2.d378f","type":"MSSQL-CN","z":"","tdsVersion":"7_4","name":"sanitized","server":"sanitized","port":"1433","encyption":false,"database":"EnvironmentMonitoring","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5"}]
```
Output area
Sql data types
---
### Author:  [@zenofmud](https://discourse.nodered.org/u/zenofmud)
#### Post date: [14 August 2019 18:52 UTC](https://discourse.nodered.org/t/ncd-device-string-data-to-sql-using-mssql-plus-node/14325/2 "2019-08-14T18:52:14Z")
The error in the `mSSQL` node says `invalid column name "Humidity"`
the insert statement you show used `humidity` are the column names case sensitive??
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [14 August 2019 19:08 UTC](https://discourse.nodered.org/t/ncd-device-string-data-to-sql-using-mssql-plus-node/14325/3 "2019-08-14T19:08:03Z")
Can you post the full object output from MSSQL node? I added a property`msg.query` that is the final rendered SQL that is executed against the SQL server. It is for this kind of debugging exactly. You can then try running that in your SQL application if need be but I suspect you will spot your issue.
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [14 August 2019 19:34 UTC](https://discourse.nodered.org/t/ncd-device-string-data-to-sql-using-mssql-plus-node/14325/4 "2019-08-14T19:34:54Z")
> [@Pitmaster](#):
>
> INSERT INTO [temperaturehumidity] (temp,humidity,nodeID,voltage,SensorName) VALUES ({{{payload.temperature}}}\*1.8+32,{{{payload.humidity}}},{{{data.nodeId}}},{{{data.battery}}},{{{data.sensor\_name}}})
Look again at your screen shot, shouldn't you be accessing temperature and humidity from `{{{data.sensor_data.temperature}}}` and `{{{data.sensor_data.humidity}}}`?
You know there is a copy button appears to the right of each property in the debug sidebar to ensure no typos?
---
### Author:  [@Pitmaster](https://discourse.nodered.org/u/Pitmaster)
#### Post date: [14 August 2019 19:47 UTC](https://discourse.nodered.org/t/ncd-device-string-data-to-sql-using-mssql-plus-node/14325/5 "2019-08-14T19:47:08Z")
Steve-Mcl,
I noticed I may have not clarified the item I am trying to get into the db. data.sensor\_name (Temperature/Humidity) is the sensor\_name value.
Thanks for the msg.query info.
Is this what you are referring to for 'full output'?
INSERT INTO [temperaturehumidity] (temp,humidity,nodeID,voltage,SensorName) VALUES (18.86\*1.8+32,29.59,0,3.2940600000000004,Temperature/Humidity)
After looking at it it appears that Temperature/Humidity (the sensor name) is missing ' before and after the value. So it should be 'Temperature/Humidity' to properly go into the sql db. I am assuming I need to append the value to include the apostrophes.
On the separate note you pointed out about temp and humidity data.....
It appears I can get it from either data.sensor\_data.temperature (humidity) or payload.temperature (humidity)
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [14 August 2019 19:54 UTC](https://discourse.nodered.org/t/ncd-device-string-data-to-sql-using-mssql-plus-node/14325/6 "2019-08-14T19:54:41Z")
> [@Pitmaster](#):
>
> After looking at it it appears that Temperature/Humidity (the sensor name) is missing ' before and after the value. So it should be 'Temperature/Humidity' to properly go into the sql db
That'll be it. Put them in the MSSQL node string e.g.
```sql
INSERT INTO [temperaturehumidity]
(temp,humidity,nodeID,voltage,SensorName)
VALUES ({{{payload.temperature}}}*1.8+32,
{{{payload.humidity}}},
{{{data.nodeId}}},
{{{data.battery}}},
'{{{data.sensor_name}}}')
```
---
### Author:  [@Pitmaster](https://discourse.nodered.org/u/Pitmaster)
#### Post date: [14 August 2019 19:56 UTC](https://discourse.nodered.org/t/ncd-device-string-data-to-sql-using-mssql-plus-node/14325/7 "2019-08-14T19:56:31Z")
hah! Thank you. Just learning some sql syntax 🙂