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

Ignition
,
1

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

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.

2

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;

3

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?

4

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.

5

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.

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