Steve-Mcl
commented
3 years ago I', not sure what you mean by msg_id however you can catch errors and see the result of the RAISEERROR
Here is a demo flow...
[{"id":"bd03aae5804ea8e8","type":"MSSQL","z":"30e2e9e0.eeaab6","mssqlCN":"8d9d212a.cea03","name":"create sproc_severity10","outField":"payload","returnType":"1","throwErrors":1,"query":"\r\ncreate PROCEDURE sproc_severity10\r\nas\r\nSET NOCOUNT ON; \r\n select getdate()\r\n RAISERROR (N'This is from inside sproc_severity10 - %s %d.', -- Message text. \r\n 10, -- Severity, \r\n 1, -- State, \r\n N'number', -- First argument. \r\n 2); -- Second argument. \r\n\r\n\r\n\r\n","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"none","rows":"","rowsType":"msg","params":[{"output":false,"name":"INV_IP","type":"NVarChar(200)","valueType":"str","value":"1.2.3.4","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"INV_Name","type":"NVarChar(200)","valueType":"str","value":"fake_vertor","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"ts","type":"NVarChar(200)","valueType":"datetime","value":"0","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"Param_KKS_Name","type":"NVarChar(200)","valueType":"msg","value":"payload.pname","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"Param_ID","type":"NVarChar(200)","valueType":"msg","value":"payload.pid","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"Param_Value","type":"NVarChar(200)","valueType":"msg","value":"payload.pval","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":1650,"y":1020,"wires":[["9ca5a3a8cb4f0740"]]},{"id":"ec89ea322ab2fe51","type":"inject","z":"30e2e9e0.eeaab6","name":"","props":[{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":1430,"y":1020,"wires":[["bd03aae5804ea8e8"]]},{"id":"0f45460dedc8f981","type":"debug","z":"30e2e9e0.eeaab6","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1870,"y":1080,"wires":[]},{"id":"9a7022126ad06714","type":"MSSQL","z":"30e2e9e0.eeaab6","mssqlCN":"8d9d212a.cea03","name":"exec sproc_severity10","outField":"payload","returnType":"1","throwErrors":"0","query":"sproc_severity10","modeOpt":"","modeOptType":"execute","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"none","rows":"","rowsType":"msg","params":[{"output":false,"name":"INV_IP","type":"NVarChar(200)","valueType":"str","value":"1.2.3.4","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"INV_Name","type":"NVarChar(200)","valueType":"str","value":"fake_vertor","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"ts","type":"NVarChar(200)","valueType":"datetime","value":"0","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"Param_KKS_Name","type":"NVarChar(200)","valueType":"msg","value":"payload.pname","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"Param_ID","type":"NVarChar(200)","valueType":"msg","value":"payload.pid","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"Param_Value","type":"NVarChar(200)","valueType":"msg","value":"payload.pval","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":1640,"y":1140,"wires":[["c8c756fba89f9987"]]},{"id":"c8c756fba89f9987","type":"debug","z":"30e2e9e0.eeaab6","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"sqlInfo[0]","targetType":"msg","statusVal":"","statusType":"auto","x":1880,"y":1140,"wires":[]},{"id":"cc653254d4fe2b84","type":"catch","z":"30e2e9e0.eeaab6","name":"","scope":["9a7022126ad06714","9dc9e180189f0126"],"uncaught":false,"x":1610,"y":1280,"wires":[["a7540f939610547b"]]},{"id":"a7540f939610547b","type":"debug","z":"30e2e9e0.eeaab6","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"_error","targetType":"msg","statusVal":"","statusType":"auto","x":1870,"y":1280,"wires":[]},{"id":"b6bd41a99134f8bf","type":"inject","z":"30e2e9e0.eeaab6","name":"","props":[{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":1430,"y":1140,"wires":[["9a7022126ad06714"]]},{"id":"9dc9e180189f0126","type":"MSSQL","z":"30e2e9e0.eeaab6","mssqlCN":"8d9d212a.cea03","name":"exec sproc_severity11","outField":"payload","returnType":"1","throwErrors":"1","query":"sproc_severity11","modeOpt":"","modeOptType":"execute","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"none","rows":"","rowsType":"msg","params":[{"output":false,"name":"INV_IP","type":"NVarChar(200)","valueType":"str","value":"1.2.3.4","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"INV_Name","type":"NVarChar(200)","valueType":"str","value":"fake_vertor","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"ts","type":"NVarChar(200)","valueType":"datetime","value":"0","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"Param_KKS_Name","type":"NVarChar(200)","valueType":"msg","value":"payload.pname","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"Param_ID","type":"NVarChar(200)","valueType":"msg","value":"payload.pid","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"Param_Value","type":"NVarChar(200)","valueType":"msg","value":"payload.pval","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":1640,"y":1200,"wires":[["71c78c6b3166c099"]]},{"id":"44463bb916c817ba","type":"inject","z":"30e2e9e0.eeaab6","name":"","props":[{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":1430,"y":1200,"wires":[["9dc9e180189f0126"]]},{"id":"9ca5a3a8cb4f0740","type":"MSSQL","z":"30e2e9e0.eeaab6","mssqlCN":"8d9d212a.cea03","name":"create sproc_severity11","outField":"payload","returnType":"1","throwErrors":1,"query":"create PROCEDURE sproc_severity11\r\nas\r\nSET NOCOUNT ON; \r\n select getdate()\r\n RAISERROR (N'This is from inside sproc_severity11 - %s %d.', -- Message text. \r\n 11, -- Severity, \r\n 2, -- State, \r\n N'number', -- First argument. \r\n 3); -- Second argument. \r\n\r\n","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"none","rows":"","rowsType":"msg","params":[{"output":false,"name":"INV_IP","type":"NVarChar(200)","valueType":"str","value":"1.2.3.4","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"INV_Name","type":"NVarChar(200)","valueType":"str","value":"fake_vertor","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"ts","type":"NVarChar(200)","valueType":"datetime","value":"0","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"Param_KKS_Name","type":"NVarChar(200)","valueType":"msg","value":"payload.pname","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"Param_ID","type":"NVarChar(200)","valueType":"msg","value":"payload.pid","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"Param_Value","type":"NVarChar(200)","valueType":"msg","value":"payload.pval","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":1650,"y":1080,"wires":[["0f45460dedc8f981"]]},{"id":"71c78c6b3166c099","type":"debug","z":"30e2e9e0.eeaab6","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1870,"y":1200,"wires":[]},{"id":"8d9d212a.cea03","type":"MSSQL-CN","tdsVersion":"7_4","name":"My DB","server":"192.168.1.38","port":"1433","encyption":false,"trustServerCertificate":true,"database":"testdb","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5","parseJSON":false,"enableArithAbort":true}]
negalaz
Is there a way to get the msg_id returned when an error happened?
I would like to valitade the msg_id specially when using RAISERROR on SP for error handling on node-red
Thanks!