Using Microsoft SQL Server Database with Node-Red

README.md

This Example shows how to use node-red-contrib-mssql to SELECT, UPDATE, and INSERT data into Microsoft SQL Server. Blog post and video walkthrough found at http://www.electronhacks.com/2017/04/using-microsoft-sql-server-with-node-red-and-mqtt/

flow.json

[{"id":"15237651.20281a","type":"debug","z":"31ff4407.c5ea7c","name":"","active":true,"console":"false","complete":"payload","x":855,"y":159,"wires":[]},{"id":"84df0cd4.58462","type":"inject","z":"31ff4407.c5ea7c","name":"Select","topic":"","payload":"SELECT TOP (1) [Topic]       ,[Payload]   FROM [Dev].[dbo].[MQTTData]","payloadType":"str","repeat":"","crontab":"","once":false,"x":256.00000381469727,"y":92.99999809265137,"wires":[["eac3e9b1.b2f0e8"]]},{"id":"eac3e9b1.b2f0e8","type":"MSSQL","z":"31ff4407.c5ea7c","mssqlCN":"df8c0b88.91b0a8","name":"MSSQL","query":"","outField":"payload","x":624,"y":160,"wires":[["15237651.20281a"]]},{"id":"ca03c5dc.709628","type":"inject","z":"31ff4407.c5ea7c","name":"Insert","topic":"","payload":"INSERT INTO [Dev].[dbo].[MQTTData] (Topic, Payload) VALUES ('Cardinal', 'Tom B. Erichsen' )","payloadType":"str","repeat":"","crontab":"","once":false,"x":258,"y":144,"wires":[["eac3e9b1.b2f0e8"]]},{"id":"57a3215a.12b8d","type":"inject","z":"31ff4407.c5ea7c","name":"Update","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":255,"y":195,"wires":[["fc1afd38.1ad0f"]]},{"id":"fc1afd38.1ad0f","type":"function","z":"31ff4407.c5ea7c","name":"Function","func":"d = new Date,\ndformat = [d.getMonth()+1,\n    d.getDate(),\n    d.getFullYear()].join('/')+' '+\n    [d.getHours(),\n    d.getMinutes(),\n    d.getSeconds()].join(':');\n\ndtstmp = new Date().toString();\npld =       \"UPDATE [Dev].[dbo].[MQTTData] \"\npld = pld + \"Set Timestamp = '\" + dformat + \"' \"\npld = pld + \"WHERE id = 1\"\n\nmsg.payload = pld\nreturn msg;\n\n\n\n\n","outputs":1,"noerr":0,"x":395,"y":195,"wires":[["eac3e9b1.b2f0e8"]]},{"id":"35b4a3e.f64c05c","type":"inject","z":"31ff4407.c5ea7c","name":"Select","topic":"","payload":"2","payloadType":"num","repeat":"","crontab":"","once":false,"x":256,"y":251,"wires":[["1528d6ba.7db449"]]},{"id":"1528d6ba.7db449","type":"function","z":"31ff4407.c5ea7c","name":"Function","func":"pld =       \"SELECT ID, Topic, Payload, Timestamp \"\npld = pld + \"FROM [Dev].[dbo].[MQTTData] \"\npld = pld + \"WHERE id = \" + msg.payload\n\nmsg.payload = pld\nreturn msg;\n\n\n\n\n","outputs":1,"noerr":0,"x":396,"y":251,"wires":[["eac3e9b1.b2f0e8"]]},{"id":"38bb7577.af1c3a","type":"mqtt in","z":"31ff4407.c5ea7c","name":"","topic":"SQLTest/#","qos":"0","broker":"712b53e5.990dfc","x":247,"y":301,"wires":[["7eafa2f8.041ebc"]]},{"id":"7eafa2f8.041ebc","type":"function","z":"31ff4407.c5ea7c","name":"Function","func":"d = new Date(),\ndformat = [d.getMonth()+1,\n    d.getDate(),\n    d.getFullYear()].join('/')+' '+\n    [d.getHours(),\n    d.getMinutes(),\n    d.getSeconds()].join(':');\n\npld =       \"INSERT INTO [Dev].[dbo].[MQTTData] \"\npld = pld + \"(Topic, Payload, Timestamp) \"\npld = pld + \"VALUES ('\" + msg.topic + \"', '\" + msg.payload + \"', '\" + dformat + \"')\"\n\nmsg.topic = ''\nmsg.payload = pld\nreturn msg;\n\n\n\n\n","outputs":1,"noerr":0,"x":389,"y":301,"wires":[["eac3e9b1.b2f0e8","15237651.20281a"]]},{"id":"df8c0b88.91b0a8","type":"MSSQL-CN","z":"","name":"Dev","server":"localhost","encyption":false,"database":"Dev"},{"id":"712b53e5.990dfc","type":"mqtt-broker","z":"","broker":"localhost","port":"1883","clientid":"NodeRedSQLClient","usetls":false,"compatmode":true,"keepalive":"15","cleansession":true,"willTopic":"","willQos":"0","willPayload":"","birthTopic":"","birthQos":"0","birthPayload":""}]

I am trying to get this to work. If I use encryption, I get nothing. If I dont, node-red crashes every time I press the "Select" Injector.
Tested my select string on the sql server (no probs). Server credentials are OK. Not sure about the domain though. Is it required?
SQL server version is 8.0.760.
Any ideas that might help me?