Snippet Name: AI_Agent_for_SQL

Tags:

Created Date: 2025-06-16 13:12:42
Last Modified Date: 2025-05-31 10:32:06

Dependencies:

DBA Notes:

Sample Output:

Title: AI Agent for SQL
Author: user
I have a large collection of SQL Server scripts based on decades of experience. can you help me create an AI Agent that runs a given script on any collection of SLQ Servers, and produces an email if the scritp reports any data, which we can assume is an exception that was found

Author: ChatGPT
Lowell wants to create an AI Agent that runs a given SQL Server script on multiple SQL Servers and sends an email if the script returns any data, assuming the data represents an exception.

Author: ChatGPT
Model set context updated.

Author: ChatGPT
Yes, I can help you design and implement this AI Agent! Here???s a high-level breakdown of what we need:

### **1. Components of the AI Agent**
- **SQL Server Connection Manager**: Reads a list of SQL Servers to connect to.
- **Script Execution Module**: Runs the given SQL script on each server.
- **Exception Detection**: If the script returns data, consider it an exception.
- **Email Notification System**: Sends an email with results if exceptions are found.
- **Logging & Error Handling**: Tracks execution results and errors.

### **2. Implementation Approach**
- **PowerShell + SQLCMD** (Lightweight and can run on Windows servers)
- **Python + pyodbc** (Cross-platform and extensible for future AI integrations)
- **C# Console App** (If you want a compiled, deployable solution)

Would you prefer PowerShell (for easy integration with SQL Server Agent), Python (for AI integration later), or C# for a structured application? Let me know your preferred tech stack, and we can start with a proof of concept!

Author: user
Lets go with PowerShell, as I am very strong in that.

Author: ChatGPT
Lowell prefers to implement the AI Agent in PowerShell.

Author: ChatGPT
Model set context updated.

Author: ChatGPT
Great! Since you're strong in PowerShell, we'll design the AI Agent as a PowerShell script or module that:
1. Reads a list of SQL Servers from a configuration file or database.
2. Connects to each server using `Invoke-Sqlcmd`.
3. Runs a given SQL script on each server.
4. Captures and evaluates the results.
5. Sends an email if any exceptions are detected.

---

### **Step 1: Define the Configuration**
We'll store the list of SQL Servers in a file (e.g., `servers.txt` or a JSON/CSV file). Example `servers.txt`:
```
Server1\Instance1
Server2\Instance2
Server3
```

Alternatively, if we want a JSON configuration:
```json
{
  "Servers": ["Server1\\Instance1", "Server2\\Instance2", "Server3"],
  "Database": "master",
  "EmailSettings": {
    "SMTPServer": "smtp.yourdomain.com",
    "From": "alerts@yourdomain.com",
    "To": "admin@yourdomain.com"
  }
}
```

---

### **Step 2: PowerShell Script**
Here???s a PowerShell script that:
- Reads the server list
- Executes a given SQL script
- Sends an email if there???s any result

```powershell
# Configuration
$ConfigPath = "C:\Path\To\config.json"
$SQLScriptPath = "C:\Path\To\query.sql"

# Read JSON Configuration
$Config = Get-Content -Path $ConfigPath | ConvertFrom-Json
$Servers = $Config.Servers
$Database = $Config.Database
$SMTPServer = $Config.EmailSettings.SMTPServer
$FromEmail = $Config.EmailSettings.From
$ToEmail = $Config.EmailSettings.To

# Function to Execute SQL Query
function Run-SQLQuery {
    param (
        [string]$Server,
        [string]$Database,
        [string]$Query
    )
    try {
        $Result = Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Query $Query -ErrorAction Stop
        return $Result
    }
    catch {
        Write-Host "Error connecting to $Server : $_"
        return $null
    }
}

# Read SQL Script
$SQLQuery = Get-Content -Path $SQLScriptPath -Raw

# Store Results
$Exceptions = @()

# Run Query on Each Server
foreach ($Server in $Servers) {
    Write-Host "Running query on $Server..."
    $Result = Run-SQLQuery -Server $Server -Database $Database -Query $SQLQuery
    
    if ($Result -and $Result.Count -gt 0) {
        Write-Host "Exception detected on $Server!"
        $Exceptions += @{
            Server = $Server
            Data   = $Result | Out-String
        }
    }
}

# Send Email if Exceptions Found
if ($Exceptions.Count -gt 0) {
    $EmailBody = "SQL Exception Report`n`n"
    foreach ($Ex in $Exceptions) {
        $EmailBody += "Server: $($Ex.Server)`n"
        $EmailBody += "Results:`n$($Ex.Data)`n`n"
    }

    Send-MailMessage -SmtpServer $SMTPServer -From $FromEmail -To $ToEmail -Subject "SQL Exception Detected" -Body $EmailBody
    Write-Host "Email sent to $ToEmail"
} else {
    Write-Host "No exceptions found."
}
```

---

### **Step 3: How to Use**
1. Update `config.json` with your servers and email settings.
2. Save your SQL script in `query.sql`.
3. Run the PowerShell script:
   ```powershell
   .\SQLAgent.ps1
   ```

---

### **Next Steps**
- Store exceptions in a log file.
- Add error handling for network timeouts.
- Run the script on a schedule via **SQL Server Agent** or **Task Scheduler**.
- Extend it to support **multiple queries** dynamically.

Does this structure work for you? Would you like any customizations? ????