Dataset Refresh error - Null Value

[Iamnvt]

hi,

Since upgraded to Gateway version Sep, 2020. I got this error:

• Underlying error code-2147467259
• Underlying error messageMicrosoft SQL: Warning: Null value is eliminated by an aggregate or other SET operation.

I tried to:

SET ANSI_WARNINGS OFF
GO

however, it is not working.

Any suggestions please?

[Iamnvt · Accepted solution]

hi,

Seems like some updates from query folding. I pushed back some of the operations when creating VIEW in SQL server, instead of using query folding feature in Power BI, and it works again.

[Anonymous]

Hi @Iamnvt,

If you download the pbix file and test on the desktop side, did this issue appears again?
According to your error message, It seems like you are work with SQL data source, any advanced t-SQL statement append in the connector? I'd like to suggest you use SQL profiler to trace the requests to confirm if any issue appears in it.

BTW, you can also take a look at the following link about similar issues if it helps to your scenario:

Warning: Null value is eliminated by an aggregate or other SET operation in Aqua Data Studio 

Regards,

Xiaoxin Sheng

[Iamnvt]

I dont have any count operations, and it was good with previous Gateway.

Only since I upgraded to version Sep, 2020 (or afterward), it occurred the issue.

[Anonymous]

Probably you are doing a count over a column that contains null values.

Try replacing you SQL statement with 

SELECT COUNT(ISNULL(<fieldname>,0)) if you want to count NULL rows

OR

SELECT SUM(CASE WHEN <fieldname> IS NULL,1,0) if you do not want to count