Activities - Best practices

activities

latest

false

Developer activities

Best practices

Best practices for Database activities, covering connection strings for SQL Server, Oracle, MySQL, and other databases, stored procedures, and troubleshooting.

Using a stored procedure with OracleRefCursor

When using stored procedures in Oracle, ensure that the REF CURSOR is correctly bound with the Oracle.ManagedDataAccess.Types.OracleRefCursor variable.

To do so, you need to make sure the number of parameters and their type match the ones setup in the Parameters property of the Run Query activity.

You can get the content of the cursor using the Invoke Code activity or you can pass it to another database query as an input parameter. Here is a sample invoke code to convert it to a data table:

Oracle.ManagedDataAccess.Client.OracleDataReader reader2 = myRefCursor.GetDataReader();
                dt = new DataTable();
                dt.Load(reader2);
Oracle.ManagedDataAccess.Client.OracleDataReader reader2 = myRefCursor.GetDataReader();
                dt = new DataTable();
                dt.Load(reader2);

You should dispose the cursor when you are done with it. You can do it either with Invoke Code activity (myRefCursor.Dispose), with Invoke Method activity from the System activity package or via an SQL command that you run.

Running stored procedures with parameters

The Run Query and Run Command activities can call a stored procedure instead of a plain SQL statement. Use Run Query when the procedure returns a result set and Run Command when it does not.

To call a stored procedure:

Set the Command type property to Stored Procedure.
Enter the procedure name in the SQL field, without an EXEC or CALL wrapper.
Add one entry to the Parameters collection for each procedure parameter.

Each Parameters entry has the following attributes:

A name.
A direction (In, Out, or In/Out).
A type.
A value.

Out and In/Out values are read back after the call. How the parameter names and order are interpreted depends on the database provider, so the guidance differs per database in the following sections.

Database	Provider	How to call	Parameter binding
Microsoft SQL Server	`Microsoft.Data.SqlClient`	Command type = Stored Procedure, SQL = procedure name	By name (`@name`); order does not matter
Oracle	`Oracle.ManagedDataAccess.Client`	Command type = Stored Procedure, SQL = procedure name	By position; add parameters in the procedure's declared order
MySQL / MariaDB	Open Database Connectivity (ODBC)	Command type = Text, SQL = `CALL procedure_name(?, ?)`	Positional `?` placeholders
PostgreSQL	ODBC	Command type = Text, `SELECT * FROM function_name(?)` or `CALL procedure_name(?)`	Positional `?` placeholders
SQLite	`Microsoft.Data.Sqlite`	Not applicable	SQLite has no stored procedures

Microsoft SQL Server

SQL Server binds parameters by name, so the Parameters entry names must match the procedure's @-prefixed parameters, and their order does not matter. Output parameters are returned when you set the entry direction to Out.

CREATE PROCEDURE dbo.usp_CountCustomers @Country NVARCHAR(5), @Total INT OUTPUT AS
BEGIN
    SET NOCOUNT ON;
    SELECT @Total = COUNT(*) FROM dbo.customer WHERE country = @Country;
END;
CREATE PROCEDURE dbo.usp_CountCustomers @Country NVARCHAR(5), @Total INT OUTPUT AS
BEGIN
    SET NOCOUNT ON;
    SELECT @Total = COUNT(*) FROM dbo.customer WHERE country = @Country;
END;

Configure Run Command with Command type = Stored Procedure and SQL = dbo.usp_CountCustomers, then add the parameters:

Name	Direction	Type	Value
`@Country`	In	`String`	`US`
`@Total`	Out	`Int32`	—

After the run, @Total holds the count. For a procedure that returns rows, use Run Query instead; the result is available in the Data table output.

Oracle

Oracle binds parameters by position, not by name, so you must add the Parameters entries in the same order that the procedure declares them. A procedure returns a result set through an OUT SYS_REFCURSOR parameter; declare that entry with the OracleRefCursor type and the Out direction, and the rows are returned in the Data table output of Run Query. See also Using a stored procedure with OracleRefCursor.

CREATE OR REPLACE PROCEDURE get_customers(p_country IN VARCHAR2, p_cursor OUT SYS_REFCURSOR) AS
BEGIN
    OPEN p_cursor FOR SELECT id, name FROM customer WHERE country = p_country ORDER BY id;
END;
CREATE OR REPLACE PROCEDURE get_customers(p_country IN VARCHAR2, p_cursor OUT SYS_REFCURSOR) AS
BEGIN
    OPEN p_cursor FOR SELECT id, name FROM customer WHERE country = p_country ORDER BY id;
END;

Configure Run Query with Command type = Stored Procedure and SQL = get_customers, then add the parameters in declaration order:

Name	Direction	Type	Value
`p_country`	In	`String`	`US`
`p_cursor`	Out	`OracleRefCursor`	—

Warning:

Oracle binds parameters by position. If the Parameters entries are not in the procedure's declared order, values are assigned to the wrong parameters and the call fails or returns empty output.

MySQL and MariaDB

When you connect to MySQL or MariaDB through ODBC, call the procedure with the Text command type and a CALL statement that uses positional ? placeholders. A SELECT inside the procedure is returned in the Data table output of Run Query.

CREATE PROCEDURE list_customers(IN p_country VARCHAR(5))
BEGIN
    SELECT id, name FROM customer WHERE country = p_country ORDER BY id;
END
CREATE PROCEDURE list_customers(IN p_country VARCHAR(5))
BEGIN
    SELECT id, name FROM customer WHERE country = p_country ORDER BY id;
END

Configure Run Query with Command type = Text and SQL query = CALL list_customers(?), then add one In parameter for the placeholder.

Note:

For MySQL procedures with OUT parameters, read the value from a returned result set or use the CALL procedure_name(?, @out); SELECT @out; pattern, because OUT values are not always mapped back to the parameter entry through ODBC.

PostgreSQL

PostgreSQL exposes two kinds of routines: functions, called with SELECT, and procedures, called with CALL. When you connect through ODBC, use the Text command type with positional ? placeholders. Use Run Query for a function that returns rows.

CREATE FUNCTION get_customers_by_country(p_country text)
RETURNS TABLE(id int, name text) LANGUAGE sql AS $$
    SELECT id, name FROM customer WHERE country = p_country ORDER BY id;
$$;
CREATE FUNCTION get_customers_by_country(p_country text)
RETURNS TABLE(id int, name text) LANGUAGE sql AS $$
    SELECT id, name FROM customer WHERE country = p_country ORDER BY id;
$$;

Configure Run Query with Command type = Text and SQL query = SELECT * FROM get_customers_by_country(?), then add one In parameter. To invoke a procedure created with CREATE PROCEDURE, use CALL procedure_name(?) instead.

SQLite

SQLite does not support stored procedures. Keep the logic in your workflow using parameterized Run Query and Run Command statements, or define a reusable view.

Connection strings for different database systems

This guide provides sample connection strings for the Connect to Database activity, enabling you to connect to various databases using native and ODBC drivers. It includes connection string examples for:

Microsoft SQL Server (including Microsoft Entra ID authentication)
Oracle
PostgreSQL
MySQL
Amazon Aurora
Microsoft Access
SAP HANA
IBM Db2
SQLite

A troubleshooting section for common connection errors follows the examples. Follow best practices to ensure secure and efficient database connectivity.

Microsoft SQL Server

Common connection string formats for Microsoft SQL Server when using Microsoft.Data.SqlClient.

SQL Server and ODBC authentication

Using SQL Server authentication:

Data Source=SERVER_NAME;Initial Catalog=DATABASE_NAME;User ID=USERNAME;Password=<your-password>;
With a specific port:

Data Source=SERVER_NAME,PORT_NUMBER;Initial Catalog=DATABASE_NAME;User ID=USERNAME;Password=<your-password>;
Using the ODBC driver:

Driver={ODBC Driver 18 for SQL Server};Server=SERVER_NAME;Database=DATABASE_NAME;Uid=USERNAME;Pwd=<your-password>;Encrypt=yes;TrustServerCertificate=no;

Important:

By default, these activities connect with Encrypt=True. A connection to a server with a self-signed or otherwise untrusted certificate fails unless you add TrustServerCertificate=True (test or development only) or install a trusted certificate. For predictable behavior, set both keywords explicitly, for example Encrypt=True;TrustServerCertificate=False;.

Microsoft Entra ID authentication

Using password authentication (deprecated by Microsoft and incompatible with multifactor authentication (MFA); prefer interactive, service principal, or managed identity):

Server=tcp:SERVER.database.windows.net,1433;Initial Catalog=DATABASE_NAME;Authentication=Active Directory Password;User ID=user@yourtenant.onmicrosoft.com;Password=<your-password>;Encrypt=True;
Using service principal authentication (for unattended robots):

Server=tcp:SERVER.database.windows.net,1433;Initial Catalog=DATABASE_NAME;Authentication=Active Directory Service Principal;User ID=APP_CLIENT_ID;Password=<your-client-secret>;Encrypt=True;
Using managed identity authentication (omit User ID for a system-assigned identity; for a user-assigned identity, set it to the identity's client ID):

Server=tcp:SERVER.database.windows.net,1433;Initial Catalog=DATABASE_NAME;Authentication=Active Directory Managed Identity;User ID=USER_ASSIGNED_MI_CLIENT_ID;Encrypt=True;
Using integrated authentication (uses Integrated Windows Authentication with a federated on-premises Active Directory identity):

Server=tcp:SERVER.database.windows.net,1433;Initial Catalog=DATABASE_NAME;Authentication=Active Directory Integrated;Encrypt=True;
Using interactive authentication (opens a browser with an MFA prompt; for attended or development use only, not for unattended robots):

Server=tcp:SERVER.database.windows.net,1433;Initial Catalog=DATABASE_NAME;Authentication=Active Directory Interactive;User ID=user@yourtenant.onmicrosoft.com;Encrypt=True;

For the ODBC driver, the equivalent keyword values have no spaces: ActiveDirectoryPassword, ActiveDirectoryServicePrincipal, ActiveDirectoryMsi (managed identity), ActiveDirectoryIntegrated, and ActiveDirectoryInteractive. ActiveDirectoryInteractive is available on Windows only.

Tip:

If the same workflow runs on both a robot and a development machine, use Authentication=Active Directory Default. It uses DefaultAzureCredential, which tries environment variables, workload identity, and managed identity before falling back to developer-tool credentials.

You can learn more about connection string syntax in the official Microsoft documentation and about Microsoft Entra authentication in the Microsoft Entra authentication documentation.

Excel file

Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\full\path\to\the\sampleFile.xlsx;

Oracle Managed Data Access

Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XEPDB1)));User Id=system;Password=<your-password>;

You can also use the Easy Connect short form: Data Source=//HOST_NAME:1521/SERVICE_NAME;User Id=USERNAME;Password=<your-password>;.

For more information, see the Oracle Data Provider for .NET documentation.

MySQL ODBC 8.3 Unicode Driver

Driver={MySQL ODBC 8.3 Unicode Driver};Server=SERVER_NAME;Database=DATABASE_NAME;User=USERNAME;Password=<your-password>;Option=3;

You can learn more about it via the official MySQL documentation page here.

MySQL ODBC 8.3 ANSI Driver

Driver={MySQL ODBC 8.3 ANSI Driver};Server=SERVER_NAME;Database=DATABASE_NAME;User=USERNAME;Password=<your-password>;Option=3;

Replace the version in the driver name with the MySQL Connector/ODBC version installed on the machine (for example, MySQL ODBC 9.4 Unicode Driver); the name must match the registered driver exactly. Prefer the Unicode driver over the ANSI driver to avoid corrupting non-ASCII data. The legacy Option=3 flag is optional and not required for typical use.

PostgreSQL

Driver={PostgreSQL Unicode};Server=SERVER_NAME;Port=5432;Database=DATABASE_NAME;Uid=USERNAME;Pwd=<your-password>;

Use the ANSI variant Driver={PostgreSQL ANSI};… only for legacy single-byte encodings; prefer the Unicode driver for UTF-8 databases. Add sslmode=require; (lowercase) to enforce TLS.

Important:

The Driver={...} name must match the driver registered on the machine that runs the robot. Depending on the installer, the 64-bit psqlODBC driver is registered as either PostgreSQL Unicode or PostgreSQL Unicode(x64) (and the ANSI equivalents). If the name does not match a registered driver, the connection fails with "data source name not found."

Verify the exact registered name in the 64-bit ODBC Data Source Administrator (C:\Windows\System32\odbcad32.exe).

You can learn more via the psqlODBC documentation.

Amazon Aurora (MySQL-compatible)

Amazon Aurora MySQL-Compatible Edition uses the MySQL wire protocol, so you connect with the MySQL Connector/ODBC driver. Point Server at the Aurora cluster (writer) endpoint:

Driver={MySQL ODBC 9.4 Unicode Driver};Server=CLUSTER.cluster-xxxx.REGION.rds.amazonaws.com;Port=3306;Database=DATABASE_NAME;User=USERNAME;Password=<your-password>;

Oracle Autonomous Database (ATP/ADW) with wallet

To connect to Oracle Autonomous Database (Autonomous Transaction Processing or Autonomous Data Warehouse):

Download the instance wallet (Wallet_DBNAME.zip) from Oracle Cloud Infrastructure.
Extract all files from the wallet archive into a folder on the robot machine.
Set the TNS_ADMIN environment variable to that folder.
Edit the WALLET_LOCATION directory in the wallet's sqlnet.ora to that same folder. The wallet ships with a ?/network/admin placeholder that you must replace, because TNS_ADMIN alone is not sufficient.

With the auto-login wallet (cwallet.sso), no wallet password is needed. Otherwise, supply the wallet password so the driver can open the password-protected ewallet.p12.

Then use a service alias from tnsnames.ora (DBNAME_high, DBNAME_medium, or DBNAME_low, plus DBNAME_tp and DBNAME_tpurgent on transaction-processing databases):

Data Source=DBNAME_low;User Id=USERNAME;Password=<your-password>;

Tip:

You can also connect without a wallet using one-way TLS, provided the Autonomous Database instance is configured to allow TLS connections. Instances configured for mutual TLS (mTLS) only still require the wallet.

You can learn more via the Oracle ODP.NET configuration documentation.

Microsoft Access

Using the ODBC driver:

Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\full\path\to\database.accdb;Uid=Admin;Pwd=;

Using the OLE DB provider (System.Data.OleDb, available on Windows only):

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\full\path\to\database.accdb;Persist Security Info=False;

For a password-protected file, append Jet OLEDB:Database Password=<your-password>; to the OLE DB string.

Important:

Both the ODBC driver and the ACE OLE DB provider come from the Microsoft Access Database Engine redistributable, whose architecture (x86 or x64) must match the robot and project architecture. A mismatch results in a "provider is not registered on the local machine" error.

SAP HANA

Driver={HDBODBC};ServerNode=HOST_NAME:3NN15;UID=USERNAME;PWD=<your-password>;

Use HDBODBC for 64-bit robots (HDBODBC32 only for a 32-bit process). For a single-container database or the first tenant, the port is 3NN15, where NN is the two-digit instance number — for example, 30015 for instance 00, or 30515 for instance 05.

For a multitenant database container (MDC) system, connect through the system-database port 3NN13 and name the tenant, which is the form SAP recommends:

Driver={HDBODBC};ServerNode=HOST_NAME:30013;DATABASENAME=TENANT_DB;UID=USERNAME;PWD=<your-password>;

Important:

For SAP HANA Cloud (as opposed to on-premises SAP HANA), the host is a UUID-style endpoint on port 443, and the connection is always encrypted. Set encrypt=TRUE explicitly for SAP HANA client 2.5 and earlier; newer clients infer it on port 443. The 3NN15 port scheme applies to on-premises systems only.

IBM Db2

Driver={IBM DB2 ODBC DRIVER};Database=DATABASE_NAME;Hostname=HOST_NAME;Port=50000;Protocol=TCPIP;Uid=USERNAME;Pwd=<your-password>;

The registered driver name can be installation-specific — with multiple Db2 copies, it might be IBM DB2 ODBC DRIVER - DB2COPY1. Verify the exact name in the ODBC Data Source Administrator on Windows, or in odbcinst.ini on Linux. Port 50000 is the common default, but the actual port is set per instance by SVCENAME.

SQLite

The Database activities use Microsoft.Data.Sqlite. Provide the path to the database file:

Data Source=C:\full\path\to\database.db;

Optional keywords include Mode=ReadOnly (or ReadWrite / ReadWriteCreate), Foreign Keys=True to enforce referential integrity, and Cache=Shared. The Version=3 keyword is not valid here — it belongs to the older System.Data.SQLite provider and results in an error.

You can learn more via the Microsoft.Data.Sqlite documentation.

JDBC

The Database activities run on .NET and do not use JDBC drivers directly. To reach a database that you would normally access through JDBC, use its ODBC driver with the ODBC connection string for that database in this guide, or implement the connection in a coded workflow using the database vendor's .NET or ADO.NET driver.

Troubleshooting common connection issues

Data source name not found (error IM002)

ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found, and no default driver specified

The ODBC driver named in Driver={...} is not installed, or is registered under a different name or architecture. To fix it:

Install the matching ODBC driver.
Confirm that the driver appears in the ODBC Data Source Administrator that matches the robot's architecture. There are separate 32-bit and 64-bit administrators.
Copy the exact driver name from the administrator into Driver={...}. The name is often version- or installation-specific (for example, PostgreSQL Unicode(x64), MySQL ODBC 9.4 Unicode Driver, or IBM DB2 ODBC DRIVER - DB2COPY1).

Driver could not be loaded (error IM003)

ERROR [IM003] Specified driver could not be loaded due to system error 1114

System error 1114 means the driver library was found but its initialization routine failed — typically a missing dependency (such as a required runtime), or an architecture or version mismatch. It is common with Oracle Instant Client (SQORA32.dll) and IBM Db2 (DB2CLIO.DLL). To fix it:

Ensure the driver architecture matches the runtime.
Install any prerequisites the driver requires.
Reinstall or update the driver.

Driver architecture and platform support

Install the database driver that matches the project's runtime architecture. Windows projects run as 64-bit processes and need the 64-bit ODBC or OLE DB driver. Windows-Legacy projects run as 32-bit processes and need the 32-bit driver.

OLE DB (System.Data.OleDb, used for Access and Excel) is available on Windows only. ODBC works on Windows, and on Linux or macOS robots when unixODBC 2.3.1 or later and the target driver are installed on that machine.

Tip:

After you install a driver, restart Studio or the robot so that it detects the driver.

Was this page helpful?

PREVIOUSProject compatibility

NEXTActivities

Using a stored procedure with OracleRefCursor​

Running stored procedures with parameters​

Microsoft SQL Server​

Oracle​

MySQL and MariaDB​

PostgreSQL​

SQLite​

Connection strings for different database systems​

Microsoft SQL Server​

SQL Server and ODBC authentication​

Microsoft Entra ID authentication​

Excel file​

Oracle Managed Data Access​

MySQL ODBC 8.3 Unicode Driver​

MySQL ODBC 8.3 ANSI Driver​

PostgreSQL​

Amazon Aurora (MySQL-compatible)​

Oracle Autonomous Database (ATP/ADW) with wallet​

Microsoft Access​

SAP HANA​

IBM Db2​

SQLite​

JDBC​

Troubleshooting common connection issues​

Data source name not found (error IM002)​

Driver could not be loaded (error IM003)​

Driver architecture and platform support​

Was this page helpful?

Using a stored procedure with OracleRefCursor

Running stored procedures with parameters

Microsoft SQL Server

Oracle

MySQL and MariaDB

PostgreSQL

SQLite

Connection strings for different database systems

Microsoft SQL Server

SQL Server and ODBC authentication

Microsoft Entra ID authentication

Excel file

Oracle Managed Data Access

MySQL ODBC 8.3 Unicode Driver

MySQL ODBC 8.3 ANSI Driver

PostgreSQL

Amazon Aurora (MySQL-compatible)

Oracle Autonomous Database (ATP/ADW) with wallet

Microsoft Access

SAP HANA

IBM Db2

SQLite

JDBC

Troubleshooting common connection issues

Data source name not found (error IM002)

Driver could not be loaded (error IM003)

Driver architecture and platform support