Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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

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."

nirajsevalkar_0-1638533620142.png

 

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

 

nirajsevalkar_1-1638533783685.png

 

Regards,

Niraj Sevalkar

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

 

nirajsevalkar_0-1638972130646.png

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

nirajsevalkar_1-1638972315234.png

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

 

 

 

 

 

View solution in original post

11 REPLIES 11
stayingdusty
Frequent Visitor

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. 

Anonymous
Not applicable

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.

 

nirajsevalkar_0-1638972130646.png

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

nirajsevalkar_1-1638972315234.png

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

 

 

 

 

 

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

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
Super User
Super User

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



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

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

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

Anonymous
Not applicable

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

 

Regards,

Niraj Sevallkar

v-easonf-msft
Community Support
Community Support

Hi. @Anonymous 

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

Anonymous
Not applicable

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.

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"

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors