Connect to ORACLE Database in Blueprism

The OLEDB object can be configured to support connections to Oracle databases. The OLEDB object uses the Jet OLE DB 4.0 connection capability, and so supports a number of OLEDB standard databases. By design the Provider String uses a generic format which is designed to cater for basic OLEDB connections. Oracle, however, requires slightly different parameters to the standard connection string.Read More »

Connection to a Microsoft Access database?

You can retrieve data using an SQL query connected to an MS Access database by using the Blue Prism Business Object called “Data – OLEDB” found within the Business Object called “BPA Object – Data – OLEDB.xml”. This is one of the processes that comes with the product, and can be imported from the VBO directory beneath the installation folder for Blue Prism (by default ‘C:\Program Files\Blue Prism Limited\Blue Prism Automate\VBO’).

OLEDB support and the JET 4.0 Provider

The OLEDB object uses the Jet 4.0 OLEDB interface to connect to MS Access by default. The OLEDB provider information can be modified to suit whichever provider you have installed and wish to use – it is not restricted to Microsoft Access. The Jet 4.0 provider string is provided by default, and this will support a large number of OLEDB connections, including Access databases up until Access 2007, when the Jet 4 provider was deprecated, and the ACE engine was used instead (.ACCDB files).

The existing connection string looks like this:-

“Provider=” & [Provider] & “; Data Source=” & [Database] & “;”  – no password required

“Provider=” & [Provider] & “; Data Source=” & [Database] & “; Jet OLEDB:Database Password=” & [Password] &”;”  – where a password is required

In the Calculation Stage where the password is required the JET 4.0 OLEDB provider has been coded in. You are free to change this.

Changing the Connection String

The Connection String can be modified to create whatever connection you need to an OLEDB compliant database. The Initialise page of the “Data – OLEDB” Business Object allows for the setting of  the most common Connection String parameters. You may add whichever ones you need to create your custom string.

The Set Connection page is the part of the process which uses the Connection parameters to form the connection string depending n whether a password is required or not. You are free to change these Calculation Stages to set whatever parameters you need.

For a list of the different Connection Strings used by most OLEDB compliant data structures please refer to the web site


Set Connection


  • Database – supply the full file path to the Access .mdb database file
  • User – the name of an authorized Access user
  • Password – the password for the Access user account

Get Collection


  • SQL – a string containing the SQL query to retrieve data e.g. “SELECT * FROM myAccessTable”


  • Results – create a Collection Data Item in your process and use the collection in the ‘Store In’ parameter.


When the standard JET 4.0 OLEDB Provider has been used to try to access a database that was created using Microsoft Access 2007 or later, then the following error message may be seen:

“Internal: Could not execute code stage because exception thrown by code stage: Unrecognized database format ‘< path to database file>’.”

This can be resolved by changing the OLEDB provider used in the Connection String to be one that is suitable for the version oof Access that the database was created in.

Read More »

Find and resolve process deadlocks?

Deadlocks occur when query X locks records A then B and query Y locks B then A. Process Y can’t get record A until Process X releases it, and Process X can’t release it until it gets record B, hence they are deadlocked. The only way out is for SQL Server to kill one of the queries.

SQL Server normally kills the cheapest query, the one it will have the least effort in rolling back. This means the deadlock victim as reported in the error is only one side of the story – to get the full story we need a deadlock graph from the database server, which shows which two transactions were involved and on which objects they were deadlocked. The trace flags described in the resolution below will give us this information.

These don’t have to be records in a table necessarily – they can be locking indexes for update or select in different orders.

Deadlocks are usually exacerbated by slow running queries, so first step is to get the worst query plans from the DBA and start to look for big table or index scans or CPU intensive loops and joins.

The steps below talk about methods to reduce the amount of data in the database – a query won’t show up as being inefficient until there is lots of data to trawl through, so generally they occur in large databases where this is lots of contention from a high number of resource PCs – before reducing the data it’s worth getting the info from the database so we can improve the product (the related article on database timeouts describes how this can be done in more detail).

There are a few things that can be tried, depending on version. For older versions of Blue Prism, you can run a script to add an index for the Work Queue Items tables to the database. In addition, the following SQL script can be used to delete any unused Work Queue tags:-

delete t
from BPATag t
left join BPAWorkQueueItemTag it on = it.tagid
where it.tagid is null;

(See also the related document on database maintenance, with some useful scripts for reducing the size of various tables within the database).

Finally if deadlocks are still occurring, ask the Database Administrator to set trace flags 1204 and 1222 on the database and send us the error logs, which will tell us which indexes/tables are deadlocking.

The deadlock is caused by the statuses “locked”, “exception”, “deferred”, and “finished” being accessed in a range lookup index and also being attempted to be updated later in the transaction which causes the cross over.

In 4.2.42 and above, these status fields are removed from the index and the queries are optimised for better performance

How do I add a time interval to a DateTime?

To add a number of hours, minutes or seconds to a DateTime data value, set up a data item with type ‘Timespan‘. Using the function MakeTimeSpan() use the product of this function (its return value) to generate a time difference (positive or negative). Once you have the difference value, use a Calculation stage to add the difference value to your DateTime data item.

For example, to calculate the time two minutes prior to any supplied date/time value (using a variable called “myDateTime”), use this notation in a Calculation stage:

[myDateTime] – (MakeTimeSpan(0, 0, 2, 0))