Stored Procedures in Direct Query mode

[karthikaganesh]

Hai, 

I am working in powerbi for 1(1/2) months.

My client requirement is to use parameters in the report. I want to make it happen using direct query.

Via import it is possible...

For direct query, I'm searching many resources to learn since 3 days. But none is working with me......

Method 1:

Exec SP Parameters..

[[[[obviously it wont work in direct query]]]]

ERROR: SYNTAX ERROR

Method 2:
SELECT * FROM
OPENROWSET('SQLOLEDB','SERVER=SERVERNAME;Trusted_Connection=yes;',exec DATABASE.Scheme.SPNAME @parametrers)

[[[[It is not working... Showing different types of error..]]]]

ERROR:  Microsoft SQL: Named Pipes Provider: Could not open a connection to SQL Server [2]. 

Method 3

SELECT * FROM
OPENROWSET('SQLNCLI','SERVER=SERVERNAME;Trusted_Connection=yes;',exec DATABASE.Scheme.SPNAME @parametrers)

[[[[Not working]]]]]

ERROR" the ole db provider "msoledbsql" for linked server "(null)" reported an error. authentication failed. cannot initialize the data source object of ole db provider "msoledbsql" for linked server "(null)".

Method 4

SELECT *  FROM OPENQUERY ([server name], 'EXEC dbname.dbo.spname @parametername = ''R1''');

[[[[[Not Working]]]]

ERROR: SYNTAX ERROR

I also enabled adhoc query disrtibution..

Can you any help me with this? Is it possible to work in direct query mode with stored procedure?

Thanks and Regards,

Karthika

[Anonymous]

"First connect to SQL using the Direct Query mode, then change the query to the following format in the Advanced editor.

SELECT * FROM OPENROWSET('SQLNCLI','server=<SQLservername>;Persist Security Info=True;UID=<SQLlogin>;PWD=<yourPassword>','EXEC <dbname>.<schema>.<storedprocedure>')"

Does this suggestion means that I cannot use the windows credentials as authentication method when connecting to SQL Server?

[v-kkf-msft]

Hi @karthikaganesh ,

First connect to SQL using the Direct Query mode, then change the query to the following format in the Advanced editor.

SELECT * FROM OPENROWSET('SQLNCLI','server=<SQLservername>;Persist Security Info=True;UID=<SQLlogin>;PWD=<yourPassword>','EXEC <dbname>.<schema>.<storedprocedure>')

For example:

let
    Source = Sql.Database(".", "CaseTest", [Query="SELECT * FROM OPENROWSET('SQLNCLI','server=<SQLservername>;trusted_connection=yes;','EXEC CaseTest.dbo.GetProductDesc_withparameters @PID=" & Parameter1 & "')"  ])
in
    Source

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[karthikaganesh]

Hi @v-kkf-msft 

Thank you for your answer. But the problem is still not resolved. 

ERROR:

My Expected Solution is:

In import method whenever we use EXEC storeprocedure parameters, the query automatically get refresh and fetch data according the parameter values.

For example:

If I use select * from Animals

It will result in some data with some columns like color, type, age. For this table, when am using slicer in desktop, resulting as empty table for some particular value.

But instead of using select * if we use EXEC animals color,type,age it will refresh the table and give the expected answer.

Like this am expecting via direct query.

Hope this am clearly explained my requirement. 

Regards,

Karthika

[miguel]

Hey!

Please check the link below for more information on how to use Direct Query with Dynamic M query parameters:

Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Docs