"Native queries aren't supported by this value." When trying to run Stored Proc Call in Power BI

[nirajsevalkar]

Hi Team,

 We are trying to connect SnowFlakes server and calling stored proc in SQL command, we are getting following error

 "Native queries aren't supported by this value."

We have used following parameters for connecting this. could you please help me on this if we are doing anything wrong.

Regards,

Niraj Sevalkar

[stayingdusty]

just to add here... this same error appear when there was a error in my sql... I have a comma (",") after my last column selected, right before the FROM statement. this is just invalid sql, so it could not find the table. 

[nirajsevalkar · Accepted solution]

Hi Team,

After hunting few of things, I am able to solve the issue.

Solution: 

I have used ODBC connector (We need to install Snowflakes ODBC driver in advance) to connect SnowFlakes server and call the stored proc inside the ODBC.Query() method.

After selecting Snowflakes ODBC driver we need to call procedure in SQL statement.

This has solved my problem, hope this will help others to resolve same.

[prachik]

Hi Niraj,I am also able to call SP using the driver, but can we convert it into direct query mode?

[luiscabello]

Hello,

I was facing the same problem. In my case, the problem was solved by using " when declaring target table:
"Database"."SCHEMA"."TABLE. 

Eg. SELECT * FROM "ALPHA_DATABASE"."PUBLIC"."CLIENTS"

[bcdobbs]

I think I read in another post a while ago that you have to give a fully qualified name with the snow flake connector.  

eg database.schema.objectname

[LeonD52]

Hi all.

When using the PBI included snowflake driver, query folding is enabled by default for "select" queries and may error when calling snowflake procedures.
Try turning query folding off by going into M advanced editor on your query and adjusting last parameter on source line holding snowflake connect information.

E.g.

[EnableFolding=true]

Change to

[EnableFolding=false]

Or remove the parameter altogether.

This may be a better option as you can then update your dataset from cloud service without an extra ODBC driver which could require a gateway.

Good luck

Leon

[sandyteenan]

This worked for me and allowed a stored proc to be called with NativeQuery 👍

[nirajsevalkar]

Thank you for your response, I have tried with fully qualified but no luck.

Regards,

Niraj Sevallkar

[v-easonf-msft]

Hi. @nirajsevalkar 

You may need to recheck the call of sql statement (stored procedure).

Did you pass any parameters in stored procedure?

You can check the M code in this related thread.

parameterized-sql-statement-expression-error-this-native 

sql-stored-procedures-in-power-bi 

Best Regards,
Community Support Team _ Eason

[nirajsevalkar]

Thank you for your reply Eason, but I am not able to follow the links. I am just calling stored proc with two paramters and those are hardcoded value.

Could you please give some more example if I am doing anything wrong here.

[milad_bahari]

In my case it was solved by specifying Qutation mark (") before and after of DB, shcema and table as follows:

"DEV_BT_XXXX"."COMMON"."DIM_COUNTRY"