Automating Database Queries and Record Updates from Power Automate Desktop: Connecting to SQL Server, Executing Queries, and Writing Results to Windows Applications

Introduction

Picture this: your operations team runs a daily reconciliation process that involves querying an order management database, identifying records where shipment status doesn't match the ERP system, and manually entering corrections into a legacy Windows application that has no API and no import feature. Two analysts spend four hours every morning doing this. The data is in SQL Server. The corrections go into a form-based Windows app from 2009. There's a clipboard, a lot of copy-pasting, and an uncomfortable number of transcription errors.

This is exactly the kind of problem Power Automate Desktop was built to solve — and database connectivity is the backbone of making it work at production quality. When you can query SQL Server directly from a desktop flow, you stop relying on exported CSVs, manual lookups, or brittle screen-scraping of other applications to get your source data. You get the real data, at the moment you need it, filtered and shaped exactly as your automation requires. Combined with PAD's ability to drive Windows UI, you have a complete end-to-end automation that reads from the source of truth and writes to whatever system needs updating.

By the end of this lesson, you'll know how to connect to SQL Server from a desktop flow, execute parameterized queries safely, iterate over result sets, handle connection lifecycle properly, push query results into Windows applications, and write updates back to the database — all with the error handling discipline that production automation demands.

What you'll learn:

• How to establish and manage SQL Server connections using PAD's database actions, including both Windows Authentication and SQL Authentication patterns
• How to execute SELECT queries and work with the resulting DataTable, including column access patterns and row iteration
• How to parameterize queries to prevent SQL injection and handle dynamic runtime values safely
• How to execute INSERT, UPDATE, and DELETE statements and interpret their results
• How to write database query results into Windows application fields reliably, including handling timing, field navigation, and form submission
• How to structure error handling around database operations so failures are logged and recoverable

---

Prerequisites

This lesson assumes you're comfortable with Power Automate Desktop beyond the basics. Specifically, you should already understand how desktop flows are structured, how variables and data tables work, and how to interact with Windows UI elements using selectors. If any of those feel shaky, spend time with Variables, Lists, and Data Tables in Power Automate Desktop: A Complete Practitioner's Guide and UI Elements and Selectors in Power Automate Desktop: Building Automations That Don't Break before continuing.

You'll also need:

• Power Automate Desktop installed on a machine with network access to your SQL Server instance
• SQL Server Management Studio or equivalent to validate your queries independently
• A test database — we'll use a fictional OrderManagement database throughout this lesson, but you should adapt examples to your own environment
• Appropriate database permissions (at minimum SELECT on the tables you're querying, INSERT/UPDATE on tables you're writing to)

---

Understanding How PAD Connects to Databases

Power Automate Desktop uses ADO.NET under the hood for its database actions. This is important to understand because it means your connection string syntax follows the standard ADO.NET / OLE DB conventions, and the same drivers you'd use in a .NET application are available here. PAD supports connections via:

• SQL Server Native Client / OLE DB Provider for SQL Server — the most common path for on-premises SQL Server
• ODBC — useful for older systems or when a DSN is already configured on the machine
• OLE DB providers for other databases — Oracle, Access, etc., though this lesson focuses on SQL Server

The three primary database actions you'll use are:

1. Open SQL Connection — establishes a connection and stores a connection handle
2. Execute SQL Statement — runs a query against an open connection; returns a DataTable for SELECT statements
3. Close SQL Connection — explicitly releases the connection

This explicit open/close model is deliberate. Unlike some higher-level ORMs that manage connection pooling transparently, PAD gives you direct control over when connections are opened and closed. This matters for unattended automation running against a shared database server — you don't want connections sitting open while the bot is driving a Windows UI for ten minutes between queries.

---

Configuring Your Connection String

The connection string is where most first-time issues occur, so let's be thorough here.

Windows Authentication (Most Common for Corporate Environments)

Provider=SQLOLEDB;Data Source=YourServerName\InstanceName;Initial Catalog=OrderManagement;Integrated Security=SSPI;

This uses the Windows identity of the account running the desktop flow. For unattended automation, that's the service account configured on the machine. For attended flows, it's the logged-in user. The key advantage is that no credentials are embedded in the flow — Windows handles authentication transparently.

SQL Server Authentication

Provider=SQLOLEDB;Data Source=YourServerName\InstanceName;Initial Catalog=OrderManagement;User ID=svc_pad_bot;Password=YourPasswordHere;

If you're using SQL Authentication, the password should never be hardcoded in the connection string as a literal. Store it in a sensitive variable and construct the connection string dynamically:

Provider=SQLOLEDB;Data Source=%ServerName%;Initial Catalog=%DatabaseName%;User ID=%DBUsername%;Password=%DBPassword%;

Where %DBPassword% is a sensitive (masked) variable populated from a secure credential store. See Handling Credentials Securely in Desktop Flows: Sensitive Variables and Azure Key Vault for the full treatment of this pattern. The short version: never paste a password into a connection string as plain text in your flow.

Named Instance vs. Default Instance

If SQL Server is running as a named instance (e.g., SQLEXPRESS), use the backslash notation: MyServer\SQLEXPRESS. If it's the default instance, just use the server name or IP. For remote servers, make sure TCP/IP is enabled in SQL Server Configuration Manager and port 1433 is open in any firewall between the bot machine and the database server.

Using the Connection String Builder in PAD

When you drag the "Open SQL Connection" action into your flow, you'll see a "Connection String" field with a small "Build Connection String" button. Clicking it opens a Windows Data Link Properties dialog — the classic OLE DB connection wizard. This is genuinely useful for first-time setup: you can select your provider (Microsoft OLE DB Provider for SQL Server), enter your server details, test the connection, and then copy the resulting connection string back into your flow. The test button in that dialog is the fastest way to validate that your driver, network path, and credentials are all correct before you write a single line of flow logic.

---

Executing SELECT Queries and Working with Results

Once you have a connection open, executing a SELECT query looks like this in the PAD action configuration:

Action: Execute SQL Statement

• SQL Connection: %SQLConnection%
• SQL Statement: SELECT OrderID, CustomerName, ShipmentStatus, ERPStatus FROM dbo.Orders WHERE ShipmentStatus <> ERPStatus AND OrderDate >= DATEADD(day, -7, GETDATE())
• Output variable: %QueryResults% (this will be a DataTable)

The returned DataTable variable behaves identically to what you'd get from reading an Excel sheet or a CSV — rows accessed by index, columns accessed by name or index. This is intentional design, and it means all the DataTable manipulation techniques you already know transfer directly.

Iterating Over Query Results

The standard pattern is a For Each loop over the DataTable rows:

For Each CurrentRow In %QueryResults%
    Set Variable > OrderID = CurrentRow['OrderID']
    Set Variable > CustomerName = CurrentRow['CustomerName']
    Set Variable > ShipmentStatus = CurrentRow['ShipmentStatus']
    Set Variable > ERPStatus = CurrentRow['ERPStatus']
    
    # ... do something with these values
End

Column names in the DataTable are case-sensitive and match exactly what your query returns — including any aliases you define. If your query uses SELECT o.OrderID AS ID, your column reference in PAD should be CurrentRow['ID'], not CurrentRow['OrderID']. This trips people up constantly, especially when they inherit queries from someone else and the aliases aren't obvious.

Accessing Row Count

You can get the number of rows returned using %QueryResults.RowsCount%. This is useful both for logging ("Processing 47 orders today") and for conditional logic ("If zero rows returned, skip the rest of the flow and send a notification").

If %QueryResults.RowsCount% = 0
    # Log and exit cleanly
    Display Message > "No reconciliation needed today. Query returned 0 rows."
    Stop Flow
End

Handling NULL Values

SQL NULLs deserve special attention. When a column value is NULL in the database, PAD represents it in the DataTable as an empty string for text columns, or as a specific null-type object for numeric columns. Before you use a column value in a string operation or numeric comparison, check whether it's empty:

If %CurrentRow['ShipmentStatus']% = ''
    Set Variable > ShipmentStatus = 'UNKNOWN'
Else
    Set Variable > ShipmentStatus = CurrentRow['ShipmentStatus']
End

Failing to handle NULLs is one of the most common causes of mid-flow crashes in database automation. A NULL date column fed into a "Format DateTime" action will cause an exception that's hard to diagnose after the fact.

---

Parameterizing Queries: Doing It Right

Here's a pattern that appears constantly in desktop flows written by people who came from an Excel background, and it's dangerous:

# DON'T DO THIS
Set Variable > SQLStatement = 'SELECT * FROM Customers WHERE CustomerName = ''' + %InputCustomerName% + ''''

If %InputCustomerName% ever contains a single quote — a customer named "O'Brien," for example — this query will fail. If your automation takes input from a user dialog or external source, you have a potential SQL injection vector. The classic attack — '; DROP TABLE Customers; -- — is not hypothetical.

PAD's Execute SQL Statement action doesn't support native parameterized queries in the way ADO.NET's SqlCommand with Parameters.Add() does. You can't use @CustomerName placeholders the way you would in code. The practical mitigations are:

Option 1: Escape Single Quotes in Inputs

For string inputs that you're embedding in SQL, replace single quotes with doubled single quotes before constructing the query:

Set Variable > SafeCustomerName = %InputCustomerName% with Replace(old_value="'", new_value="''")
Set Variable > SQLStatement = 'SELECT * FROM Customers WHERE CustomerName = ''' + %SafeCustomerName% + ''''

This prevents the most common injection vectors and query-breaking inputs. It's not a full security solution, but it handles the practical cases in internal enterprise automation.

Option 2: Use Stored Procedures

This is the architecturally correct solution for anything beyond simple queries. Create a stored procedure in SQL Server:

CREATE PROCEDURE dbo.GetOrdersByStatus
    @StatusFilter NVARCHAR(50),
    @LookbackDays INT
AS
BEGIN
    SELECT OrderID, CustomerName, ShipmentStatus, ERPStatus
    FROM dbo.Orders
    WHERE ShipmentStatus = @StatusFilter
      AND OrderDate >= DATEADD(day, -@LookbackDays, GETDATE())
END

Then call it from PAD:

Set Variable > SQLStatement = 'EXEC dbo.GetOrdersByStatus @StatusFilter = ''' + %SafeStatusFilter% + ''', @LookbackDays = ' + %LookbackDays%

Stored procedures let your database team own the query logic, apply proper permissions (EXECUTE on the proc only, not SELECT on the underlying tables), and change the SQL without touching the desktop flow. They're the right architectural boundary between your automation layer and your data layer.

Option 3: Run PowerShell for True Parameterization

If you need genuine parameterized queries without stored procedures, you can use PAD's "Run PowerShell Script" action to execute a properly parameterized ADO.NET call and return results. This is more complex to set up but gives you the full safety of parameterized queries. See Scripting Inside Desktop Flows: Running PowerShell, Python, and VBScript Actions for the mechanics of passing variables into and out of PowerShell from PAD.

---

Executing UPDATE, INSERT, and DELETE Statements

For data modification statements, the Execute SQL Statement action works similarly — but instead of a DataTable, it returns a row count (the number of rows affected). Technically, PAD's action returns a DataTable regardless, but for non-SELECT statements it will be empty; what matters is that the action succeeds without error.

UPDATE Pattern

Here's a realistic UPDATE scenario: after your bot has confirmed a correction in the Windows application, you want to write an audit record back to the database marking the record as processed.

UPDATE dbo.Orders
SET ReconciliationStatus = 'PROCESSED',
    ProcessedByBot = 1,
    ProcessedAt = GETDATE(),
    BotRunID = @RunID
WHERE OrderID = @OrderID
  AND ReconciliationStatus = 'PENDING'

In PAD, after escaping your inputs:

Set Variable > UpdateSQL = 'UPDATE dbo.Orders SET ReconciliationStatus = ''PROCESSED'', ProcessedByBot = 1, ProcessedAt = GETDATE(), BotRunID = ' + %BotRunID% + ' WHERE OrderID = ' + %CurrentOrderID% + ' AND ReconciliationStatus = ''PENDING'''
Execute SQL Statement > Connection: %SQLConnection%, SQL: %UpdateSQL%

The AND ReconciliationStatus = 'PENDING' clause is a concurrency guard. If another process has already handled this record between the time you queried it and the time you're updating it, the WHERE clause won't match and zero rows will be affected — which is the safe outcome. This is an optimistic concurrency pattern and it's worth building into your UPDATE statements wherever records might be touched by multiple processes.

INSERT Pattern for Audit Logging

It's good practice to INSERT an audit log record for every record your automation touches, regardless of outcome:

INSERT INTO dbo.BotAuditLog 
    (RunID, OrderID, Action, OldValue, NewValue, Timestamp, BotMachine)
VALUES 
    (@RunID, @OrderID, 'STATUS_UPDATE', @OldStatus, @NewStatus, GETDATE(), @MachineName)

Log what the bot saw, what it did, and when. When something goes wrong in production three weeks from now, this table is what lets you reconstruct exactly what happened.

Transactions Across Multiple Statements

PAD doesn't expose native transaction management (BEGIN TRANSACTION / COMMIT / ROLLBACK) through its database actions directly. If you need atomic multi-statement operations, your best options are:

1. Wrap it in a stored procedure that handles the transaction internally — the cleanest solution
2. Use PowerShell to execute a SqlTransaction via .NET, giving you full control
3. Design for idempotency — structure your operations so that if the flow fails halfway through, re-running it produces the correct result without double-processing

Option 3 is often underestimated. If your UPDATE includes WHERE ReconciliationStatus = 'PENDING' and you INSERT with a unique constraint on (RunID, OrderID), a retry that re-processes the same record will either update nothing (record already marked PROCESSED) or fail with a constraint violation (audit record already exists) — both of which are safe, detectable outcomes.

---

Managing Connection Lifecycle in Production Flows

Connection management separates production-quality flows from fragile ones. Here's the pattern you should follow:

Open Once, Query Many, Close Once

For a flow that processes a batch of records, open the connection once at the start, use it for all queries throughout the flow, and close it once at the end. Don't open and close the connection inside your processing loop — that's unnecessary overhead and risks hitting connection limits on busy servers.

[Start of flow]
Open SQL Connection > ConnectionString: %ConnectionString%, Output: SQLConnection

[Middle of flow - your processing loop]
For Each row...
    Execute SQL Statement > various queries using %SQLConnection%
End

[End of flow]
Close SQL Connection > %SQLConnection%

Use Error Handling to Guarantee Connection Closure

The most common production bug is a flow that errors mid-execution and leaves the connection open. Wrap your entire flow logic in an error handler that closes the connection regardless of how the flow exits.

In PAD, you implement this using the "On Block Error" mechanism. Structure your flow like this:

Open SQL Connection ...

On Block Error (for the processing section):
    Close SQL Connection > %SQLConnection%
    Log error details
    Re-raise or exit

[Protected block]
    ... all your processing logic ...
[End block]

Close SQL Connection > %SQLConnection%

This guarantees that whether the flow succeeds or fails, the connection gets closed. See Error Handling in Desktop Flows: On Block Error, Retry Policies, and Recovery Screenshots for the full mechanics of this pattern — it applies to database connections exactly as described there.

---

Writing Query Results to Windows Applications

This is where database automation becomes genuinely powerful — and where the complexity increases significantly. You've got your data from SQL Server in a DataTable. Now you need to drive a Windows application to enter or update that data.

The general architecture looks like this:

1. Query database → get DataTable of records to process
2. Open target Windows application (or confirm it's already open)
3. For each row in DataTable: a. Navigate to the relevant record in the Windows app b. Enter field values from the current DataTable row c. Submit/save the record d. Update the database to mark the record as processed
4. Close connection, log results

Launching and Preparing the Windows Application

Before your processing loop, ensure the target application is in a known state. If it needs to be launched, use "Launch Application" or "Run Application." If it's already running, use "Get Running Processes" to verify it's active, then bring it to focus with "Focus Window."

For the actual field interaction mechanics — finding controls, setting values, navigating forms — the techniques in Automating Data Entry into Windows Desktop Applications with Power Automate Desktop: Launching Apps, Navigating Forms, and Submitting Records Reliably cover this in depth. We'll focus here on the database integration patterns rather than repeating selector fundamentals.

The Record-by-Record Processing Loop

Here's the complete pattern for a reconciliation automation:

[After opening SQL connection and querying records]
[After launching/focusing the Windows application]

For Each CurrentRow In %QueryResults%
    Set Variable > OrderID = CurrentRow['OrderID']
    Set Variable > CustomerName = CurrentRow['CustomerName']
    Set Variable > CorrectStatus = CurrentRow['ERPStatus']
    
    On Block Error (record-level):
        [Log failure for this record]
        [Execute SQL: INSERT into BotAuditLog with Status='FAILED']
        [Continue to next record - don't abort the whole flow]
    
    [Protected block for this record]
        # Navigate to the record in the Windows app
        Set Text Box Value > OrderSearchField, Text: %OrderID%
        Press Key > Enter
        Wait For Window to Contain Element > StatusDropdown
        
        # Read current value for audit logging
        Get DropDown List Selected Value > StatusDropdown, Output: CurrentAppStatus
        
        # Only update if needed (defensive check)
        If %CurrentAppStatus% <> %CorrectStatus%
            Select DropDown List Value > StatusDropdown, Value: %CorrectStatus%
            Click > SaveButton
            Wait For Element > ConfirmationMessage, Timeout: 10s
        End
        
        # Write audit record back to DB regardless
        Set Variable > AuditSQL = 'INSERT INTO dbo.BotAuditLog (OrderID, OldStatus, NewStatus, ProcessedAt) VALUES (' + %OrderID% + ', ''' + %CurrentAppStatus% + ''', ''' + %CorrectStatus% + ''', GETDATE())'
        Execute SQL Statement > %SQLConnection%, %AuditSQL%
        
        # Mark record as processed in source table
        Set Variable > UpdateSQL = 'UPDATE dbo.Orders SET ReconciliationStatus = ''PROCESSED'' WHERE OrderID = ' + %OrderID%
        Execute SQL Statement > %SQLConnection%, %UpdateSQL%
    [End protected block]
    
    # Brief pause between records to avoid overwhelming the app
    Wait > 0.5 seconds

End For Each

Handling Application State Between Records

One of the trickiest aspects of record-by-record automation is ensuring the Windows application is in a known, clean state at the start of each iteration. After saving record N, is the application showing a confirmation dialog? Is it on the search screen? Is it on a detail screen? You need explicit navigation logic to return to a consistent starting point.

The safest pattern is to explicitly navigate to "new search" state at the top of each loop iteration, rather than relying on what the previous iteration left behind:

[Top of For Each loop]
# Navigate back to search screen - explicit, not assumed
Click > NewSearchButton
Wait For Element > SearchField, Timeout: 5s
Clear Text > SearchField

This adds a fraction of a second per record but eliminates an entire class of state-corruption bugs that are extraordinarily difficult to diagnose in production logs.

---

Advanced Pattern: Batch Processing with Database-Side Locking

For unattended automation running across multiple machines simultaneously, you need to coordinate which bot processes which records. The naive approach — each bot queries the same table and processes whatever it finds — results in multiple bots processing the same record simultaneously, which causes duplicate updates and audit log conflicts.

The solution is database-side locking using an UPDATE with OUTPUT or a status field claim pattern.

The Claim Pattern

Before your main query, have each bot "claim" a batch of records by setting a status field:

-- Each bot runs this at the start, with its own unique BotID
UPDATE TOP (50) dbo.Orders
SET ReconciliationStatus = 'IN_PROGRESS',
    ClaimedByBot = @BotID,
    ClaimedAt = GETDATE()
OUTPUT INSERTED.OrderID, INSERTED.CustomerName, INSERTED.ShipmentStatus, INSERTED.ERPStatus
WHERE ReconciliationStatus = 'PENDING'
  AND ClaimedAt IS NULL

The UPDATE ... OUTPUT pattern atomically marks the records and returns them in a single operation. No other bot can claim those same records because by the time a second bot runs the same statement, those rows no longer have ReconciliationStatus = 'PENDING'.

In PAD, you'd execute this as your initial query and the results flow directly into your DataTable for processing. This works only if your OLE DB driver supports it (SQL Server does, natively).

Claiming in PAD

Since PAD's Execute SQL Statement runs whatever SQL you give it, you can run this UPDATE...OUTPUT directly:

Set Variable > ClaimSQL = 'UPDATE TOP (50) dbo.Orders SET ReconciliationStatus = ''IN_PROGRESS'', ClaimedByBot = ''' + %MachineName% + ''', ClaimedAt = GETDATE() OUTPUT INSERTED.OrderID, INSERTED.CustomerName, INSERTED.ShipmentStatus, INSERTED.ERPStatus WHERE ReconciliationStatus = ''PENDING'' AND ClaimedAt IS NULL'

Execute SQL Statement > %SQLConnection%, %ClaimSQL%, Output: %ClaimedRecords%

Now %ClaimedRecords% contains only the rows this bot has exclusively claimed. Process them, mark them PROCESSED, and any records left as IN_PROGRESS (due to bot failure) can be reclaimed by a cleanup job after a timeout period.

This pattern is foundational to running unattended RPA at scale. If you're building towards enterprise multi-bot deployment, see Building a Resilient Unattended RPA Orchestration Framework in Power Automate Desktop for how this fits into a broader orchestration architecture.

---

Writing Results to Excel as an Intermediate Layer

Sometimes your target isn't a Windows application but a report or output file. If you're querying SQL Server and writing results to Excel rather than a Windows app, the DataTable from your query maps directly to Excel ranges.

[After Execute SQL Statement returning %QueryResults%]

Launch Excel > OrderReconciliationReport.xlsx
Set Active Excel Worksheet > Sheet1
Write DataTable to Excel Worksheet > DataTable: %QueryResults%, Starting Cell: A2

This writes the entire DataTable in one action. You can also iterate the DataTable and write selectively, add formatting, or combine SQL results with other data sources. The Automating Excel with Power Automate Desktop: Reading, Writing, and Running Macros lesson covers the Excel layer in detail; the database-to-Excel pipeline is really just "get the DataTable from SQL instead of from a file."

The more interesting multi-application pattern — read from SQL, process in a Windows app, write summary to Excel — is the kind of end-to-end flow covered in Automating Multi-Application Workflows in Power Automate Desktop: Transferring Data Between Windows Apps, Web Browsers, and Excel in a Single Desktop Flow.

---

Structuring Your Flow for Maintainability

At production scale, monolithic flows that do everything in one giant sequence become impossible to maintain. Break your database automation into subflows:

• GetPendingOrders subflow — opens connection, queries, returns DataTable
• ProcessOrder subflow — takes a single order row, drives the Windows app, returns success/failure status
• UpdateOrderStatus subflow — takes OrderID and status, writes to DB
• LogAuditRecord subflow — writes to audit log table

This decomposition has real benefits: you can test each subflow independently, you can add a new data source by swapping out GetPendingOrders without touching anything else, and the main flow reads like a readable workflow spec rather than a wall of actions.

See Subflows and Reusable Logic in Power Automate Desktop for the mechanics of passing variables between subflows and returning values — it's the right architectural layer for this kind of decomposition.

---

Hands-On Exercise

Build a complete database-to-Windows-app automation using the following scenario. This exercise is designed to take 90–120 minutes if you work through all the steps.

Scenario: You have a ContactDirectory SQL Server database with a table dbo.Contacts containing columns: ContactID (int), FirstName, LastName, Email, Department, LastUpdated (datetime), SyncStatus (varchar: 'PENDING'/'SYNCED'/'FAILED'). A legacy Windows HR application called "HRDirect" (you can substitute Notepad or any simple form-based app for testing) allows you to search for contacts by ID and update their department.

Part 1: Database Setup

Create the test database and table:

CREATE DATABASE ContactDirectory;
GO
USE ContactDirectory;
GO

CREATE TABLE dbo.Contacts (
    ContactID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(100),
    LastName NVARCHAR(100),
    Email NVARCHAR(200),
    Department NVARCHAR(100),
    NewDepartment NVARCHAR(100),  -- what the bot should set
    LastUpdated DATETIME DEFAULT GETDATE(),
    SyncStatus NVARCHAR(20) DEFAULT 'PENDING'
);

INSERT INTO dbo.Contacts (FirstName, LastName, Email, Department, NewDepartment)
VALUES 
    ('Sarah', 'Chen', 'schen@corp.com', 'Finance', 'Operations'),
    ('Marcus', 'Rivera', 'mrivera@corp.com', 'IT', 'Engineering'),
    ('Priya', 'Patel', 'ppatel@corp.com', 'Sales', 'Revenue Operations'),
    ('James', 'Okonkwo', 'jokonkwo@corp.com', 'Marketing', 'Growth'),
    ('Elena', 'Vasquez', 'evasquez@corp.com', 'HR', 'People Operations');

CREATE TABLE dbo.ContactSyncLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    ContactID INT,
    OldDepartment NVARCHAR(100),
    NewDepartment NVARCHAR(100),
    SyncResult NVARCHAR(20),
    ProcessedAt DATETIME DEFAULT GETDATE(),
    MachineName NVARCHAR(100)
);

Part 2: Build the Desktop Flow

Build a flow with the following structure:

1. Initialize variables:

   • ConnectionString = your server connection string
   • MachineName = %ComputerName% (use the system variable)
   • SuccessCount = 0
   • FailureCount = 0

2. Open SQL Connection using %ConnectionString%

3. Query pending contacts:

SELECT ContactID, FirstName, LastName, Department, NewDepartment 
FROM dbo.Contacts 
WHERE SyncStatus = 'PENDING'
ORDER BY ContactID

4. If zero rows returned: Display a message "No contacts to sync today" and stop.

5. For each row in results:

   • Extract ContactID, names, old and new department to variables
   • Implement an On Block Error handler that: logs to ContactSyncLog with SyncResult = 'FAILED', increments FailureCount, continues to next record
   • [Drive your Windows app to find the contact and update their department — simulate this with a Write to File action writing "Updated ContactID X from Y to Z" if you don't have a real target app]
   • Execute INSERT to ContactSyncLog with SyncResult = 'SUCCESS'
   • Execute UPDATE on dbo.Contacts setting SyncStatus = 'SYNCED', LastUpdated = GETDATE()
   • Increment SuccessCount

6. Close SQL Connection

7. Display summary: "Sync complete. Success: X, Failed: Y"

Part 3: Validation

After running the flow, execute these validation queries in SSMS:

SELECT SyncStatus, COUNT(*) as RecordCount FROM dbo.Contacts GROUP BY SyncStatus;
SELECT * FROM dbo.ContactSyncLog ORDER BY ProcessedAt;

All records should show SYNCED, and the log table should have one entry per contact.

Stretch goal: Modify the flow to claim records in batches using an UPDATE statement before querying, so it would be safe to run on multiple machines simultaneously.

---

Common Mistakes and Troubleshooting

"Connection Failed" at Flow Start

Symptom: The Open SQL Connection action fails immediately with a generic connection error.

Diagnosis checklist:

1. Test connectivity with Test-NetConnection -ComputerName YourServer -Port 1433 from the bot machine
2. Verify the OLE DB provider is installed — SQL Server Native Client isn't installed by default on all Windows machines; you may need to install Microsoft OLE DB Driver for SQL Server separately
3. Check that SQL Server Browser service is running on the database server (required for named instances)
4. Confirm the account running the flow has login rights in SQL Server — a Windows account can exist on the machine but not be a SQL Server login

"Column Does Not Exist" Errors in DataTable Access

Symptom: CurrentRow['ColumnName'] throws an error, even though you're sure the column exists.

Cause: Column name mismatch. Check for: trailing spaces in column names (rare but it happens), aliases in your SELECT that differ from the base column name, case sensitivity issues.

Fix: Early in your flow, add a Display Message action that shows %QueryResults.ColumnCount% and the name of the first row/first column to verify what PAD actually received.

Records Being Processed Multiple Times

Symptom: Your audit log shows duplicate entries for the same records.

Cause: Your UPDATE that marks records as PROCESSED is running, but the WHERE clause is not restrictive enough, or the flow is restarting after a partial failure and re-querying records that were partially processed.

Fix: Add the status check to your UPDATE WHERE clause (WHERE OrderID = X AND SyncStatus = 'PENDING'). If zero rows are affected, log it as a warning rather than treating it as success.

Windows App State Errors Mid-Loop

Symptom: The first few records succeed, then the automation starts failing because it's interacting with the wrong screen.

Cause: One record triggered an unexpected dialog or error in the Windows application that the flow didn't handle, leaving the app in an unexpected state. All subsequent records fail because the bot is operating on the wrong screen.

Fix: At the top of each loop iteration, add an explicit navigation to the app's home/search screen. For legacy applications that throw modal dialogs on validation errors, add dialog detection and dismissal logic before each navigation.

Long-Running Queries Timing Out

Symptom: Execute SQL Statement fails with a timeout error on queries that run fine in SSMS.

Cause: PAD's Execute SQL Statement has a default command timeout (typically 30 seconds). Complex queries, table scans on large tables, or queries that encounter locking can exceed this.

Fixes:

1. Add query hints or indexes to make the query faster — fix the root cause, not the timeout
2. Add WITH (NOLOCK) to your SELECT if dirty reads are acceptable for your use case (reconciliation queries often tolerate this)
3. Use a stored procedure and set a longer timeout explicitly in the connection string: Command Timeout=120;
4. Break large queries into smaller batches using TOP with ORDER BY and keeping track of the last processed ID

Connection Timeout During Long-Running Windows App Operations

Symptom: The flow works for the first batch of records, then starts failing with connection errors mid-loop.

Cause: The SQL connection times out while the bot is spending several seconds per record driving the Windows application. Default Connection Timeout in connection strings is often 15-30 seconds of idle time.

Fix: Add Connection Timeout=0; to your connection string to disable idle timeout, or restructure the flow to close and re-open the connection periodically. Adding a lightweight keepalive query (SELECT 1) at the start of each loop iteration prevents idle timeout by ensuring the connection stays active.

---

Summary and Next Steps

You now have a complete framework for database-driven automation in Power Automate Desktop. The key capabilities you've built:

• Connection management: Opening SQL connections with both Windows and SQL Authentication, structuring flows to guarantee connections are closed even when errors occur
• Data retrieval: Executing SELECT queries, working with DataTable results, handling NULL values and column name precision
• Safe query construction: Escaping string inputs, using stored procedures as the proper architectural boundary, and understanding when PowerShell parameterization is warranted
• Data modification: Writing UPDATE, INSERT, and DELETE statements, using optimistic concurrency patterns, and designing for idempotency
• Application integration: The record-by-record loop pattern with per-record error handling, state management between iterations, and database audit logging
• Scale patterns: Batch claiming for multi-bot deployments, connection lifecycle optimization, and subflow decomposition for maintainable flows

The natural next steps from here branch in two directions. If you're building towards unattended production deployment, understanding how these flows run at scale — machine configuration, scheduling, and monitoring — is essential. Start with Attended vs Unattended RPA: Choosing a Run Mode and Configuring Machines in Power Automate and then work through the operational side with Monitoring and Troubleshooting Desktop Flow Runs at Scale.

If you're extending the flow itself — perhaps the Windows application you're writing to is particularly complex, involves legacy UI patterns, or requires more sophisticated navigation — the techniques in Automating Legacy Windows Applications with UI Automation in Power Automate Desktop will take you further.

The pattern you've learned here — query the database, drive the UI, write back to confirm — is the core pattern of the vast majority of enterprise RPA. Everything else is variation on that theme.