# Query data source returns no rows but looks fine in Database Query Browser

**URL:** <https://forum.inductiveautomation.com/t/query-data-source-returns-no-rows-but-looks-fine-in-database-query-browser/117590>\
**Category:** Ignition\
**Tags:** reporting, database\
**Created:** [October 23, 2026, 3:00pm UTC](https://forum.inductiveautomation.com/t/query-data-source-returns-no-rows-but-looks-fine-in-database-query-browser/117590 "2026-10-23T15:00:56Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [October 23, 2026, 3:00pm UTC](https://forum.inductiveautomation.com/t/query-data-source-returns-no-rows-but-looks-fine-in-database-query-browser/117590/1 "2026-10-23T15:00:56Z")

</div>

This query works in Database Query Browser and returns one row and two columns as expected.

```sql
DECLARE @BatchId AS VARCHAR(12) = '000101240433';

SELECT -- TOP 1 
	VALUE,
	RIGHT(CAST([VALUE] AS VARCHAR(max)), 7) AS Value7Char 
FROM MyTable 
WHERE BATCH_NUM = @BatchId
  AND [NAME] = 'INNER_LABEL'

```

It also works in SQL Server Management Studio.

In a report's query data source it returns no rows. The query name appears in the reporting Preview as an empty XML element.

Both the Data Source and the query browser are using `<Default>` database.

I've spent way too long trying to figure it out - so I'm guessing it's something really obvious!

Thanks.

---

<div class="post-metadata">

**Author:** ![Nol](https://avatars.discourse-cdn.com/v4/letter/n/8e8cbc/32.png) [@Nol](https://forum.inductiveautomation.com/u/Nol)\
**Post date:** [October 23, 2026, 3:07pm UTC](https://forum.inductiveautomation.com/t/query-data-source-returns-no-rows-but-looks-fine-in-database-query-browser/117590/2 "2026-10-23T15:07:38Z")

</div>

I think the report data source only picks up the first result which is coming from the DECLARE statement rather than the SELECT while SSMS returns all the results so you see all of it.

Try adding this line above Declare. Or move the batchId into a parameter and remove the declare line.  
`SET NOCOUNT ON;`

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [October 23, 2026, 3:17pm UTC](https://forum.inductiveautomation.com/t/query-data-source-returns-no-rows-but-looks-fine-in-database-query-browser/117590/3 "2026-10-23T15:17:18Z")

</div>

Thanks, Nol.  
I'm aware of `NOCOUNT` and spent an embarrassing amount of time realising its significance while working on a PHP web server project many years ago - enough that I learned the lesson really well!

The query is one of more than twenty that otherwise all run fine without NOCOUNT being set, so I don't think that's the problem (and adding it doesn't solve it).

Next?

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [October 23, 2026, 3:47pm UTC](https://forum.inductiveautomation.com/t/query-data-source-returns-no-rows-but-looks-fine-in-database-query-browser/117590/4 "2026-10-23T15:47:42Z")

</div>

Try removing the DECLARE statement and just setting the Batch ID to the string. I believe technically this is a SQL script and the JDBC driver may not be playing nice with it.

Might also remove the comment, as I blieve I remember there being potential issues with that as well.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [October 23, 2026, 4:08pm UTC](https://forum.inductiveautomation.com/t/query-data-source-returns-no-rows-but-looks-fine-in-database-query-browser/117590/5 "2026-10-23T16:08:43Z")

</div>

> [@lrose](#):
>
> technically this is a SQL script

Exactly. Vanilla JDBC doesn't claim to support SQL scripts, just **statements**. Microsoft's JDBC driver will accept SQL scripts if handed off cleanly, like the DB browser does. Named Queries and Report Queries do parameter stuff that is easily broken.

> [@Transistor](#):
>
> works in SQL Server Management Studio

SSMS doesn't use JDBC, so a totally meaningless test.