Hey folks, I’m working on an n8n workflow to create an AI assistant for querying a Microsoft SQL database. I’ve got the connection set up, but I’m stuck on how to make the AI generate the SQL query.
I’ve tried a bunch of things in the ‘Query’ parameter of the Microsoft SQL tool, like leaving it blank, using empty quotes, and even trying some expressions like {{ $fromAI('query') }}. Nothing seems to work.
Does anyone know the correct syntax or approach for this? I’m guessing it should be something simple, but I can’t find any clear documentation on it.
For context, I’ve already got this working with Supabase, which doesn’t need a query parameter. But with MS-SQL, I’m totally lost.
Having worked extensively with n8n and MS SQL, I can offer some insights. The key is to use a combination of nodes to achieve your goal. Start with an AI node to generate the query, then use a Function node to process and sanitize it if needed. Finally, in the Microsoft SQL node, reference the processed query using an expression like {{ $node['Function'].json['sqlQuery'] }}.
This method provides better control and error handling. It’s crucial to ensure your AI model generates T-SQL compatible with MS SQL Server. Also, consider implementing query validation checks in your Function node to prevent potential SQL injection risks.
Remember to test thoroughly with various query types to ensure robustness. If you encounter any specific errors, the n8n community forum is an excellent resource for troubleshooting.
hey there! i’ve been messing with n8n and MS SQL too. what worked 4 me was using a seperate node to make the AI query, then linking it in the MS SQL node. like {{ $node['AINode'].json['query'] }} in the Query field. make sure ur AI knows T-SQL tho, or it’ll mess up. good luck!
I’ve actually tackled a similar challenge with n8n and MS SQL recently. Here’s what worked for me:
Instead of trying to put the AI-generated query directly in the Microsoft SQL node, I used a Function node before it. In the Function node, I called the AI to generate the SQL query and stored it in a variable.
Then, in the Microsoft SQL node, I referenced that variable in the Query field like this: {{ $node['Function'].json['generatedQuery'] }}
This approach gives you more control and lets you sanitize or modify the query if needed before executing it. It also makes debugging easier since you can see the generated query separately.
One caveat: make sure your AI is trained to generate valid T-SQL syntax for Microsoft SQL Server. Some AIs might default to more generic SQL that won’t work correctly.
Hope this helps! Let me know if you need any clarification on the setup.