MS SQL - The JDBC storage plugin failed while trying setup the SQL query

1

I got this error when query MS SQL server from Power BI - Dremio connector.
The below SQL script is auto-generated by PowerBI…

select “FIELD_NAME”
from "MSSQL.“DBTABLE”
group by “FIELD_NAME”
order by “FIELD_NAME”
OFFSET 0 ROWS FETCH FIRST 501 ROWS ONLY

As a temp fix, I tried it works if i create reflection for the table. and there will not be an error with syntax like OFFSET. However it is not a long term solution as i can’t create reflection for every single table…Please advise whether it can be fixed?

2

Actually currently Dremio does support the OFFSET syntax from PowerBI. Can you share the job profile?

3

Below please find the profile from 2 similar queries as mentioned, but with error "The JDBC storage plugin failed while trying setup the SQL query. "

6efbc161-5128-4d25-838e-a58513281c52.zip (5.4 KB)
ae03c72f-3a2e-417c-8592-0a7fd8a89f79.zip (5.5 KB)

4

Hi, i found another case that will give me JDBC error.

We have a MS SQL view with column EM_ID (type = uniqueidentifier). We can do select * of the table. but we cannot query the EM_ID field directly from the table.

i.e. we can Select * from TABLE:

image
image.png972×571 27.7 KB

4073a9ac-f57a-4dfe-b487-3439c79e5045.zip (7.3 KB)

but we cannot select EM_ID from TABLE.

image
image.png963×654 24.2 KB

1c796a70-ea82-464e-b9e0-7f24f4c373a6.zip (5.4 KB)

It’s strange…

5
  1. What version of MSSQL are you using? I just tested basically same query on my MSSQL and works fine.
  2. Can you also test directly on MSSQL (take Dremio out the picture)? The query Dremio pushing down is

SELECT [ABBREVIATION] COLLATE LATIN1_GENERAL_BIN2 AS [ABBREVIATION]
FROM [MESDB].[dbo].[GEN_FACTORY]
GROUP BY [ABBREVIATION] COLLATE LATIN1_GENERAL_BIN2
ORDER BY [ABBREVIATION] COLLATE LATIN1_GENERAL_BIN2
OFFSET 0 ROWS
FETCH NEXT 501 ROWS ONLY

  1. You attached select EM_ID from TABLE profile is wrong, you attached another query. Can you re-attach select EM_ID from TABLE please?
6

  1. The MSSQL is Standard version 2k8 R2.

  2. the SQL works in the MSSQL directly.

    image
    image.png616×803 17.8 KB

  3. Sorry for wrong attachment. here you are:
    63596744-52bf-441d-ae6f-e5e5409195d1.zip (7.4 KB)

7

One more information about (2) is that…the total number of row in the table is just 49. but the PowerBI is querying next 501 rows in the SQL script. I don’t know whether there is any relationship…
But i tried to change 501 to number less than 49, it does not work too…

8

Can you specify column types? EM_ID error is because it’s of a UNIQUEIDENTIFIER type, which is a known issue I’m looking into. How about CIPMS_COMBINE_PART_FLAG & ABBREVIATION?