Skip to content
+44 (0) 330 99 88 700
Sign In Register
Ready to Talk?

Database Query: RowCount property not reflecting the amount of rows produced by a recordset

Table of Contents

Symptom #

The Database query step’s RowCount property does not reflect the number of rows returned by the query. It may contain a value such as -1, or 0 (zero) in place of the expected number of rows.

Please follow the below link for a similar symptom encountered with the RowsProcessed property of the Filter Data:

Filter Data Rows Processed Property

Cause #

Many ODBC drivers and OLEDB provider do not return a row count to the task environment. Instead, they return a value to indicate the presences or otherwise of rows. For example, the ODBC driver may use the value -1 to indicate rows and 0 (zero) to indicate no rows.

Solution #

The solution is to add a counter logic to the task. The task design to implement the custom counter logic will have the following features:

  1. Task variable to store the counter
  2. Run VBScript step to initialise the counter to 0 (zero)
  3. Run VBScript step to increment the counter value

Continue below for instructions on how to add a counter logic to a task.

Design Implementation #

1.0 Add a task variable to store the counter

A parameter type variable must first be created, this will be utilised by both VB Script steps.

  1. Under the Environment tab of the task browser, right-click the Variables node and select New. This will display the variable properties.
  2. Within the General tab name the variable RowCount.
  3. Within the Details tab tick the Parameter box and set the Scope to Task Instance.
    2

2.0 Add a Run VB Script step to initialise the counter to 0 (zero)

This VB Script step must be placed at the beginning of the task. It’s purpose is to write a value of 0 (zero) to the variable.

  1. Under the Tools tab of the task browser, locate the Run VBScript icon.
  2. Drag the tool onto the ‘Task Planner’. This will automatically open the tool for configuration.
  3. Within the General tab provide a name for the step, for example Initialise Counter.
  4. Within the Main tab key in the following: [Variable] = 0
    Capture 1

3.0 Run VB Script step to increment the counter value

This VB Script step will consume a recordset and must be placed after the Database Query step. It will be writing the number of rows to the variable.

  1. Create a second VB Script step and provide an appropriate name, for example Increment Counter.
  2. Main tab. Key in the following: [Variable] = [Variable] + 1
    Capture 2
  3. Enter a carriage return to begin a new line. Under the Environment tab of the task browser and This Step selected in the Scope field, drag & drop the property LogInfo in to the script.
    Capture3
  4. Between the double quotes of the LogInfo property enter the text: RowCount:
  5. Complete the script line by entering the following: & [Variable]The completed script should now look as follows:
    Capture 4(1)
  6. Click Check Syntax to verify that the script is correct.

Summary #

A parameter type variable is created. The first VB Script step is placed at the beginning of the task and writes a value of 0 to the variable. This will initialise the counter ensuring it starts at 0 when it begins counting the rows.

The second VB Script step is the counter, it is placed after the Database Query and consumes a recordset. It executes once per row and will write the number of rows to the same variable in increments of 1 with each successful execution.

Below is an example of a task which utilises the counter in-line with a Database Query step.

1(1)

Was this article useful?