I am getting error "Can not execute query" while using Database block

Description

While using a database block, the user may get an Error Message "Can not execute query". There can be multiple reasons for this error message. Let's have a look at below scenarios and their resolution.

Resolution

Scenario 1: User has not configured DSN properly.

This can happen if the DSN is not configured as recommended. Please follow the guidelines in the below articles for working with different data sources/database.

Excel - https://support.leapwork.com/hc/en-us/articles/360000704592-Accessing-Excel-via-ODBC-driver-in-LEAPWORK

SQL - https://support.leapwork.com/hc/en-us/articles/360000006451-Access-SQL-Database-via-ODBC-driver-in-LEAPWORK

Oracle - https://support.leapwork.com/hc/en-us/articles/360005115111-Accessing-an-Oracle-database-with-LEAPWORK-using-ODBC

Scenario 2: User has specified whitespace between part of names in DSN.

LEAPWORK don't support DSN names with whitespace in between so, please remove whitespace if any. Please refer below

Scenario 3: User has not used the same version of applications.

Please note that LEAPWORK Studio, ODBC Driver and Database all 3 software should run under the same bit version. i.e if you have 64-bit LEAPWORK please install ODBC Driver and database also of the 64-bit version. And if LEAPWORK Studio is 32-bit, install the 32-bit version of ODBC Driver and database. 

Basically, an ODBC driver is, typically, a dynamic link library (DLL) which is loaded by its parent application. You absolutely cannot mix 32bit application and 64bit driver (or vice-versa). All executables, DLLs, etc that share the same process space must be the same bit'ness.

Scenario 4: Incorrect query

If the query written in the Database block is incorrect then also it will display the error message as "Could not execute query" once you click on the Refresh button in the Database block.

To get a detailed explanation of the error you can navigate to "C:\Program Files\LEAPWORK\Logs" folder and open studio.log file, navigate to the bottom of the file. There you will see the detailed execution error message being displayed.

For example:- 

"Could not execute query" as error message being displayed if we click on the Refresh button in Database block having an incorrect query.

Studio Log message error displaying error message:-

[8868] LeapTest.AutomationStudio.ViewModels.BuildingBlocks.FlowProperties.DatabaseBlockFlowPropertyViewModel 2019-07-04 20:18:11.8583 - Error
Message: Could not execute query.
System.Data.Odbc.OdbcException (0x80131937): ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'Where'.

For any clarification, please contact our Priority Support.