The current ms sql node 1.1 in n8n 1.47.1 has a problem with executing none query commands.
I want to execute backup commands and the result type is not handled correctly.
Command:
BACKUP LOG mydb
TO URL = ‘s3://gateway.storjshare.io/my-bucket/mydb_20240717_02.trn’
WITH FORMAT, MAXTRANSFERSIZE = 10485760, STATS = 10, COMPRESSION, ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = mydb_2024);
Result:
{
"errorMessage": "Internal error",
"errorDetails": {},
"n8nDetails": {
"n8nVersion": "1.47.1 (Self Hosted)",
"binaryDataMode": "default",
"stackTrace": [
"TypeError: result is not iterable",
" at Object.execute (/usr/local/lib/node_modules/n8n/node_modules/n8n-nodes-base/dist/nodes/Microsoft/Sql/MicrosoftSql.node.js:218:41)",
" at processTicksAndRejections (node:internal/process/task_queues:95:5)",
" at Workflow.runNode (/usr/local/lib/node_modules/n8n/node_modules/n8n-workflow/dist/Workflow.js:728:19)",
" at /usr/local/lib/node_modules/n8n/node_modules/n8n-core/dist/WorkflowExecute.js:664:51",
" at /usr/local/lib/node_modules/n8n/node_modules/n8n-core/dist/WorkflowExecute.js:1076:20"
]
}
}
SQL Studio Result is composed of only message output:
100 percent processed.
Processed 8 pages for database ‘mydb’, file ‘mydb_log’ on file 1.
BACKUP LOG successfully processed 8 pages in 0.584 seconds (0.100 MB/sec).
Here here error output, the error message is the same, would be nice to be able to execute no query operations for maintenance and other things.
n8n version
1.50.1 (Self Hosted)
Stack trace
TypeError: result is not iterable at Object.execute (/usr/local/lib/node_modules/n8n/node_modules/n8n-nodes-base/dist/nodes/Microsoft/Sql/MicrosoftSql.node.js:218:41) at processTicksAndRejections (node:internal/process/task_queues:95:5) at Workflow.runNode (/usr/local/lib/node_modules/n8n/node_modules/n8n-workflow/dist/Workflow.js:725:19) at /usr/local/lib/node_modules/n8n/node_modules/n8n-core/dist/WorkflowExecute.js:673:51 at /usr/local/lib/node_modules/n8n/node_modules/n8n-core/dist/WorkflowExecute.js:1085:20
Instead of making a new docker image you can now just add your certificates to a volume mounted at /opt/custom-certificates and it should make life a bit easier (more info on that here: Configure n8n to use your own certificate authority | n8n Docs)
I suspect the issue here could be that the underlying package we use doesn’t support running commands or the response is not being handled correctly. I will see if we can set up SQL locally to do some testing as well.
That doesn’t work for me. The only way to fix this, is to set the setting for ‘On Error’ on ‘Continue’. This is for me the only way to let the workflow work without it to stop.
@Jon thanks for the certificate tip, n8n starts with `
Trusting custom certificates from /opt/custom-certificates.
ERROR: Access denied '/opt/custom-certificates'
User settings loaded from: /home/node/.n8n/config
Initializing n8n process
n8n ready on 0.0.0.0, port 5678
...
I exec into the container and checked the permissions (644), i could list the file and read the pem content from the directory
The issue is still persisting in 1.52.2, the command is executed successfully but the node simply does not handle the result type.
@nineforty5 the workaround with a second command does work for sql backup command. It will error on failure and succeed on completion.
{
"errorMessage": "Internal error",
"errorDetails": {},
"n8nDetails": {
"n8nVersion": "1.52.2 (Self Hosted)",
"binaryDataMode": "default",
"stackTrace": [
"TypeError: result is not iterable",
" at Object.execute (/usr/local/lib/node_modules/n8n/node_modules/n8n-nodes-base/dist/nodes/Microsoft/Sql/MicrosoftSql.node.js:218:41)",
" at processTicksAndRejections (node:internal/process/task_queues:95:5)",
" at Workflow.runNode (/usr/local/lib/node_modules/n8n/node_modules/n8n-workflow/dist/Workflow.js:728:19)",
" at /usr/local/lib/node_modules/n8n/node_modules/n8n-core/dist/WorkflowExecute.js:673:51",
" at /usr/local/lib/node_modules/n8n/node_modules/n8n-core/dist/WorkflowExecute.js:1085:20"
]
}
}
I had the same issue with the permissions for certificates, but it turned out that the directory itself (/opt/custom-certificates) that did not have the correct permissions (for me the permissions were ‘root:root’). Please make sure that the mapped volume directory (the ./pki directory in the example) has the correct permissions.