Microsoft SQL and multiple items

Hello again!

Sorry for bothering once again today. I have a pertty straight forward question/problem: Is the Microsoft SQL node not capable of being called multiple times?

{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        250,
        300
      ]
    },
    {
      "parameters": {
        "functionCode": "return [{ json: { productNumber: 'DA01-0042'}}, { json: { productNumber: 'DA01-0070'}}];"
      },
      "name": "Function",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        450,
        300
      ]
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "=SELECT ProductId  FROM Products WHERE ProductNumber = '{{$json[\"productNumber\"]}}'"
      },
      "name": "Microsoft SQL",
      "type": "n8n-nodes-base.microsoftSql",
      "typeVersion": 1,
      "position": [
        650,
        300
      ],
      "credentials": {
        "microsoftSql": "EMCOM-GO-DB2 ERP"
      }
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Function",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Function": {
      "main": [
        [
          {
            "node": "Microsoft SQL",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

I have this very simple Function that generates 2 items. But the Microsoft SQL only returns a result for the first item. It is not called the second time. Is this my fault or is the node having some issue?

Regards Sebastian

Ah yes you are sadly correct. It looks like it got implemented wrongly and it executes only once. We have to fix that in a future version of the node (changing it now could break the workflows of people). So what you would have to do for now is to use the SplitInBatches node set to 1 and then iterate with it over the items.

Can I merge the Batches back together?

Yes, that is possible. You can find an example here:

thanks Jan once again.

The noItemsLeft is a neat trick! But it is a it tricky to find (https://github.com/n8n-io/n8n/blob/master/packages/nodes-base/nodes/SplitInBatches.node.ts#L85). This should be added to the documentation (the `$node.context) or even be made obviously available in the node as an Output or something imho.

Thanks anyway for saving the day once again!

Yes agree. We will add it to the documentation.