Mssql not executing properly and how to write the result of query into csv file and send email

Hi,
The MSSQL node is not executing consistently. Sometimes, it returns the response: ‘It appears that no active employees were found who match the criteria specified for the tasks required for the project.’ Other times, it responds with employee details and their cost calculations. Below is the chatInput I sent to the webhook trigger node:

"Employee information is saved in the table ‘dbo.Employee’. The employee’s first name is in the column ‘emp_firstName’ and id in emp_ID. Employee status is the column ‘emp_status’ and a value of ‘Active’ indicates the employee is currently active.
Skill information is saved in the table ‘dbo.Skill’. The Skill ID column is ‘skl_ID’ and the Skill name column is ‘skl_desc’.

Skills of each employee are saved in the table ‘dbo.vw_EmployeeSkill’. This table is related to the Employee table by the column ‘esk_employeeID’ and to the Skill table by the column ‘esk_SkillID’. This also contains years of experience in the skill in column esk_yearsExp, skill level in the column sklLvl_Name, skill name in the column skl_Desc.

Rates of employees are saved in the table ‘dbo.EmployeeBillRate’. This table is related to the employee table by the column ‘ebr_employeeID.Rate’ information is the column ‘ebr_billRate’

The employee schedule is in ‘dbo.Schedule’ with employee id in the column ‘sch_employeeID’. Their schedule date range is between columns ‘sch_fromDateTime’ and ‘sch_toDateTime’.

Given the above information, can you recommend all employees for the following project with a budget of $6000? . Tasks for the project are listed below:

Task 1: Auditing; Skills required: SAP and Spanish; Hours: 40
Task 2: Tax Filing; QuickBooks: Hours 12

Steps to recommend employees:
Step 1: Get details of all employees who are active and available based on their current schedule for the specified project duration with Spanish and SAP skills. Include employee ID, employee name, list of skills along level of each skill, employee bill rate and employee level in the results.
Step 2: Analyze employee details and recommend employees for each task. Ensure that the project is within budget with the selected employees. Show details of recommended employees including employee name, skill level, rate for each recommended employee.
Step 3: Calculate and show the total cost of the project using employee bill rates."

Respond received when it executes properly :
"Step 1: Employee Details for the Project

Based on the provided information and queries to the database, here are the available active employees with the required skills (SAP and Spanish) for the project:

Employee: JUAN
    Employee ID: 1000
    Skills:
        SAP: Novice (Rates: 245, 290, 370)
        Spanish: Native/Bilingual (Rates: 245, 290, 370)

Employee: SAMANTHA
    Employee ID: 1001
    Skills:
        Spanish: Professional Working Proficiency (Rates: 270, 295, 285)

Employee: MARK
    Employee ID: 11002
    Skills:
        SAP: Novice (Rates: 300, 330, 450)

Employee: ANGEL
    Employee ID: 1008
    Skills:
        SAP: Beginner (Rates: 100, 175)
        Spanish: Professional Working Proficiency (Rates: 215, 220)

Employee: NANCY
    Employee ID: 11098
    Skills:
        SAP: Novice (Rates: 200, 205, 210)
        Spanish: Professional Working Proficiency (Rates: 200, 210)

Other candidates with appropriate skills can be similarly listed.

Step 2: Analyzing Employee Details and Recommendations for Each Task

For the project, we will allocate resources based on the required skills and the budget constraint of $6000.
Task Breakdown:

Task 1: Auditing (SAP and Spanish); Hours: 40
Task 2: Tax Filing (QuickBooks); Hours: 12

Budget Analysis

We need to ensure the total cost does not exceed the budget during allocation.
Recommendations:
Task 1: Auditing

Select 1 Employee for SAP (Mark) and 1 Employee for Spanish (Samantha)
    Employee: JUAN: SAP (Rate: 290, Work Hours 20)
    Employee: SAMANTHA: Spanish (Rate: 295, Work Hours 20)

Total Cost for Auditing:
    JUAN: 20 hours * 290 = $5800
    SAMANTHA: 20 hours * 295 = $5900 Total for Task 1: $5800 + $5900 = $11700 (Over Budget)

Alternative for Auditing (Change Employee Rate)

Recommendation:
    Replace SAMANTHA from using $5900 options, try using a lower rate with compatible employees.
    Select other candidates from the list to fill these roles under budget.

Task 2: Tax Filing

Employee: NANCY (Best Option for Quickbooks)
    Bill Rate: 210
    Work Hours: 12

Total Cost for Tax Filing:

NANCY: 12 hours * 210 = $2520

Overall Cost Summary:

Final Calculations should consider:
Total Cost = Cost for Auditing + Cost for Tax Filing.
Compare against budget limits.

Conclusion

Once you finalize the employees and their rates, provide the modules (skills) offered, hours they can work on and bill rates acceptable towards building the correct tax filing strategy and retain long mentorship values on task sustainability. Make adjustments accordingly to ensure you meet a total under the project budget of $6000.

Let me know how you’d like to proceed or any adjustments to the employee list if necessary! "

My workflow is -
{
“nodes”: [
{
“parameters”: {
“httpMethod”: “POST”,
“path”: “invoke_ai_agent”,
“responseMode”: “responseNode”,
“options”: {}
},
“type”: “n8n-nodes-base.webhook”,
“typeVersion”: 2,
“position”: [
-760,
-120
],
“id”: “99455199-69ab-4fd8-b363-a0bc0d605f2a”,
“name”: “Webhook”,
“webhookId”: “93d6d920-53f9-4017-aac8-f2297ecab0b8”
},
{
“parameters”: {
“promptType”: “define”,
“text”: “={{ $json.body.chatInput }}”,
“options”: {
“systemMessage”: “”
}
},
“type”: “@n8n/n8n-nodes-langchain.agent”,
“typeVersion”: 1.7,
“position”: [
-280,
-120
],
“id”: “45cc0fed-e27c-49d1-ba28-860756496434”,
“name”: “AI Agent”
},
{
“parameters”: {
“respondWith”: “json”,
“options”: {}
},
“type”: “n8n-nodes-base.respondToWebhook”,
“typeVersion”: 1.1,
“position”: [
320,
-80
],
“id”: “7a94e8f8-32fc-4c39-af76-3b5312b2ddb6”,
“name”: “Respond to Webhook”
},
{
“parameters”: {
“options”: {}
},
“type”: “@n8n/n8n-nodes-langchain.chatTrigger”,
“typeVersion”: 1.1,
“position”: [
-780,
180
],
“id”: “4370a9d9-2af4-423b-bfc3-f421603c7b74”,
“name”: “When chat message received”,
“webhookId”: “27d6b9a3-5086-4737-9b8a-0f8fe7960a31”
},
{
“parameters”: {
“options”: {}
},
“type”: “@n8n/n8n-nodes-langchain.lmChatOpenAi”,
“typeVersion”: 1.1,
“position”: [
-320,
180
],
“id”: “70245dc9-d903-41f1-9ef4-461617b0abb6”,
“name”: “OpenAI Chat Model”,
“credentials”: {
“openAiApi”: {
“id”: “nMBfO26KykOtXOwu”,
“name”: “OpenAi account MGO”
}
}
},
{
“parameters”: {
“operation”: “executeQuery”,
“query”: "{{ $fromAI(‘data’) }} "
},
“type”: “n8n-nodes-base.microsoftSqlTool”,
“typeVersion”: 1.1,
“position”: [
-20,
240
],
“id”: “d60a5f02-5e79-4a79-80e6-e4bb066b365e”,
“name”: “Microsoft SQL”,
“credentials”: {
“microsoftSql”: {
“id”: “fNtkWs9KKG43upoH”,
“name”: “Microsoft SQL account 3”
}
}
},
{
“parameters”: {
“sessionIdType”: “customKey”,
“sessionKey”: “={{ $json.body.sessionId }}”
},
“type”: “@n8n/n8n-nodes-langchain.memoryBufferWindow”,
“typeVersion”: 1.3,
“position”: [
-180,
240
],
“id”: “08e78e29-6300-4cfe-b769-4c614586dc6a”,
“name”: “Window Buffer Memory”
},
{
“parameters”: {
“assignments”: {
“assignments”: [
{
“id”: “6d192932-1676-479d-97c9-92eba82876e3”,
“name”: “body.chatInput”,
“value”: “={{ $json.chatInput }}”,
“type”: “string”
},
{
“id”: “83d271a6-07f3-414f-a633-fa7f0d7f8c12”,
“name”: “body.sessionId”,
“value”: “={{ $json.sessionId }}”,
“type”: “string”
}
]
},
“options”: {}
},
“type”: “n8n-nodes-base.set”,
“typeVersion”: 3.4,
“position”: [
-500,
80
],
“id”: “c17ee814-3621-4696-a6b8-07a909b17380”,
“name”: “Edit Fields”
}
],
“connections”: {
“Webhook”: {
“main”: [
[
{
“node”: “AI Agent”,
“type”: “main”,
“index”: 0
}
]
]
},
“AI Agent”: {
“main”: [
[
{
“node”: “Respond to Webhook”,
“type”: “main”,
“index”: 0
}
]
]
},
“When chat message received”: {
“main”: [
[
{
“node”: “Edit Fields”,
“type”: “main”,
“index”: 0
}
]
]
},
“OpenAI Chat Model”: {
“ai_languageModel”: [
[
{
“node”: “AI Agent”,
“type”: “ai_languageModel”,
“index”: 0
}
]
]
},
“Microsoft SQL”: {
“ai_tool”: [
[
{
“node”: “AI Agent”,
“type”: “ai_tool”,
“index”: 0
}
]
]
},
“Window Buffer Memory”: {
“ai_memory”: [
[
{
“node”: “AI Agent”,
“type”: “ai_memory”,
“index”: 0
}
]
]
},
“Edit Fields”: {
“main”: [
[
{
“node”: “AI Agent”,
“type”: “main”,
“index”: 0
}
]
]
}
},
“pinData”: {},
“meta”: {
“templateCredsSetupCompleted”: true,
“instanceId”: “42cf5de39f6d42709d74f11d3b88dee52d654b6bf492be9c7760ea3bf63aa81f”
}
}

Additionally, I need to:
->Generate a CSV file after executing the query.
->Attach and send this CSV file via email.(csv format screenshot attached)

could anyone help troubleshoot the MSSQL inconsistency and suggest the best approach for automating the CSV creation and email sending

(Select the nodes on your canvas and use the keyboard shortcuts CMD+C/CTRL+C and CMD+V/CTRL+V to copy and paste the workflow.)

Share the output returned by the last node

Information on your n8n setup

  • n8n version:
  • Database (default: SQLite):
  • n8n EXECUTIONS_PROCESS setting (default: own, main):
  • Running n8n via (Docker, npm, n8n cloud, desktop app):
  • Operating system:

It looks like your topic is missing some important information. Could you provide the following if applicable.

  • n8n version:
  • Database (default: SQLite):
  • n8n EXECUTIONS_PROCESS setting (default: own, main):
  • Running n8n via (Docker, npm, n8n cloud, desktop app):
  • Operating system:

nRunnung n8n via n8n cloud

executions -own

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.