Microsoft SQL Action Block

What it does: Execute queries and perform data operations against Microsoft SQL Server databases directly from your workflow automation.

When to Use This

Use the Microsoft SQL action when you need to:

• ✅ Query a SQL Server database for data to use in workflow logic
• ✅ Insert records into SQL tables based on workflow events
• ✅ Update existing database records when data changes
• ✅ Delete records as part of data cleanup or archival workflows
• ✅ Run custom SQL queries for reporting or data transformation

Example: When a new customer signs up through your chatbot, insert their information into the Customers table in your SQL Server database and query their account to confirm creation.

Features

• SQL Server Authentication: Connect with host, port, username, and password
• Query Execution: Run arbitrary SELECT queries with parameterized inputs
• Insert Operations: Add new records to any table
• Update Operations: Modify existing records based on conditions
• Delete Operations: Remove records matching specified criteria
• Parameterized Queries: Built-in SQL injection protection
• Variable Support: Use workflow variables in queries and parameters

Setup

1. Gather SQL Server Credentials

You need the following connection details for your SQL Server instance:

• Host: Server hostname or IP address (e.g., myserver.database.windows.net)
• Port: SQL Server port (default: 1433)
• Database: The database name to connect to
• Username: SQL Server login username
• Password: SQL Server login password

2. Connect in Indite

1. Open the Microsoft SQL block settings
2. Enter the Host address
3. Enter the Port (default 1433)
4. Enter the Database name
5. Enter your Username and Password

3. Configure the Action

1. Select the desired Action from the dropdown
2. Write your query or specify the table and fields
3. Map response values to workflow variables

Supported Actions

Using Variables

You can use workflow variables in all SQL operations. Variables are automatically parameterized for security.

Execute Query:

SELECT OrderId, CustomerName, Total, Status
FROM Orders
WHERE CustomerId = {{customer.id}}
  AND OrderDate >= {{filter.startDate}}
ORDER BY OrderDate DESC

Insert:

Table: Customers
Fields:
  Name: {{form.customerName}}
  Email: {{form.email}}
  Phone: {{form.phone}}
  CreatedAt: {{date.now}}

Update:

Table: Orders
Set:
  Status: {{order.newStatus}}
  UpdatedAt: {{date.now}}
Where:
  OrderId: {{order.id}}

Delete:

Table: TempSessions
Where:
  ExpiresAt < {{date.now}}

Response Mapping

Map results from SQL actions to workflow variables:

Available Values (Execute Query):

• Rows: Array of result rows, each as a key-value object
• Row Count: Number of rows returned
• Column Names: List of column names in the result set

Available Values (Insert):

• Rows Affected: Number of rows inserted
• Inserted ID: Auto-generated identity value (if applicable)

Available Values (Update/Delete):

• Rows Affected: Number of rows modified or deleted

Example Mapping:

Rows → {{sql.rows}}
First Row Name → {{sql.rows[0].CustomerName}}
Row Count → {{sql.rowCount}}
Rows Affected → {{sql.rowsAffected}}

Common Use Cases

1. Customer Lookup During Chat

Trigger: Chatbot user provides email SQL Action: Execute Query

SELECT CustomerId, Name, AccountType, LastOrderDate
FROM Customers
WHERE Email = {{user.email}}

Then personalize the chatbot response with {{sql.rows[0].Name}} and {{sql.rows[0].AccountType}}.

2. Order Creation

Trigger: Order confirmed in workflow SQL Action: Insert

Table: Orders
Fields:
  CustomerId: {{customer.id}}
  ProductId: {{product.id}}
  Quantity: {{order.quantity}}
  UnitPrice: {{product.price}}
  Total: {{order.total}}
  Status: Pending
  CreatedAt: {{date.now}}

3. Status Update Pipeline

Trigger: External system webhook (e.g., shipping confirmation) SQL Action: Update

Table: Orders
Set:
  Status: Shipped
  ShippedAt: {{shipping.timestamp}}
  TrackingNumber: {{shipping.trackingNumber}}
Where:
  OrderId: {{webhook.orderId}}

4. Data Cleanup Job

Trigger: Schedule (Daily at 3 AM) SQL Action: Delete

Table: SessionLogs
Where:
  CreatedAt < {{date.daysAgo(90)}}

Then log the number of deleted rows: Cleaned up {{sql.rowsAffected}} expired sessions.

Best Practices

Query Safety

• ✅ Always use the block's parameterized query mechanism -- never build SQL strings manually
• ✅ Limit SELECT queries with WHERE clauses and TOP/LIMIT to avoid returning massive result sets
• ✅ Use specific column names in SELECT instead of SELECT *
• ✅ Test queries with a small dataset before deploying to production

Performance

• ✅ Ensure frequently queried columns have appropriate indexes
• ✅ Avoid running complex queries in time-sensitive workflow paths
• ✅ Use connection pooling (handled by the workflow engine) for high-frequency operations
• ✅ Set reasonable timeouts for long-running queries

Data Integrity

• ✅ Validate data types and formats before inserting or updating
• ✅ Use unique constraints and primary keys to prevent duplicate records
• ✅ Consider using transactions for multi-step operations (execute multiple related queries in sequence)
• ✅ Always include a WHERE clause in UPDATE and DELETE operations

Security

• ✅ Use a dedicated SQL login with minimum required permissions (e.g., only SELECT on read-only workflows)
• ✅ Never expose database credentials in workflow outputs or logs
• ✅ Restrict the SQL user to specific tables and operations
• ✅ Enable SSL/TLS encryption for database connections
• ✅ Regularly rotate database passwords

Troubleshooting

Connection Failed

Check:

• The host address is correct and reachable from the workflow engine
• The port (default 1433) is open in the firewall
• SQL Server is configured to accept TCP/IP connections
• For Azure SQL, your workflow engine IP is in the allowed firewall rules
• Username and password are correct

Query Returns No Results

Solutions:

• Verify the WHERE clause values match existing data
• Check data types -- string comparisons are case-sensitive by default in some collations
• Ensure the database and table names are correct
• Test the query directly in SQL Server Management Studio

Insert/Update Fails

Check:

• All required (NOT NULL) columns have values provided
• Data types match column definitions (e.g., dates in proper format, numbers as numeric values)
• Foreign key constraints are satisfied
• Unique constraints are not being violated
• The SQL user has INSERT/UPDATE permissions on the target table

Timeout Error

Solutions:

• Optimize the query (add indexes, reduce result set size)
• Increase the workflow timeout if the query is legitimately long-running
• Break large operations into smaller batches
• Check if the database server is under heavy load

Limitations

• Stored Procedures: Executing stored procedures is not directly supported -- use Execute Query with EXEC statements
• Transactions: Multi-statement transactions within a single block execution are not supported
• Binary Data: Large binary (BLOB) column operations may be limited
• Bulk Operations: No native bulk insert -- use loops for multiple row inserts
• Connection Encryption: SSL/TLS settings depend on the SQL Server configuration
• Result Set Size: Very large result sets may cause workflow memory issues -- use TOP or pagination

Related Blocks

• Azure Cosmos DB: For NoSQL document storage needs
• Excel 365: Export SQL query results to spreadsheets
• Condition: Add logic based on query results
• Loop: Iterate over query result rows
• Set Variable: Prepare query parameters dynamically