Exec stored procedure with Openrowset in direct query mode

[quinnie]

Hi everyone,

I'm trying to exec store procedure in direct query mode with openrowset:
let
from_date= DateTime.ToText(from_date, "yyyy-MM-dd"),
to_date= DateTime.ToText(to_date, "yyyy-MM-dd"),
country_id= country_id,
sales_channel_id= sales_channel_id,
Source = Sql.Database( test_server, sea_database,
[ Query = "SELECT * FROM OPENROWSET('SQLNCLI','trusted_connection=yes','exec [rpt_PB_Daily]
@from_date =''" & from_date & "'',@to_date=''" & to_date & "'',@country_id=''" & country_id & "'',@sales_channel_id=''" & sales_channel_id &"''')"])
in Source

But got this error message:
DataSource.Error: Microsoft SQL: The metadata could not be determined because every code path results in an error; see previous errors for some of these.
Could not find stored procedure 'rpt_PB_Daily'.

I did some research and tried to modify my query to:
let
from_date= DateTime.ToText(from_date, "yyyy-MM-dd"),
to_date= DateTime.ToText(to_date, "yyyy-MM-dd"),
country_id= country_id,
sales_channel_id= sales_channel_id,
Source = Sql.Database( test_server, sea_database,
[ Query = "SELECT * FROM OPENROWSET('SQLNCLI','trusted_connection=yes','exec [rpt_PB_Daily]
WITH RESULT SETS
(
(from_date datetime NOT NULL,
to_date datetime NOT NULL,
country_id nvarchar(50) NOT NULL,
sales_channel_id nvarchar(250) NOT NULL
))')", CreateNavigationProperties=false ] )
in Source

But still got an error:

DataSource.Error: Microsoft SQL: Could not find stored procedure 'rpt_PB_Daily'.
Even though my stored procedure exists

How can i fix this issue? Thank you for your help

[Anonymous]

Hi @quinnie ,

Firstly, the error message The metadata could not be determined because every code path results in an error; see previous errors for some of these. typically occurs when SQL Server is unable to determine the shape (i.e., the column names and data types) of the result set returned by the query. This can happen with OPENROWSET when executing a stored procedure because the result set of the stored procedure is not known at compile time. Using WITH RESULT SETS is a correct approach to define the metadata explicitly, but it seems there's an issue with the syntax or the visibility of the stored procedure rpt_PB_Daily.
Please ensure that the stored procedure rpt_PB_Daily exists in the specified database and that the user under which the query is executed has sufficient permissions to execute it. You can do this by trying to execute the stored procedure directly in SSMS (SQL Server Management Studio) with the same user credentials.
And please try this:

SELECT * FROM OPENROWSET(
    'SQLNCLI',
    'Server=test_server;Trusted_Connection=yes;',
    'EXEC sea_database.dbo.rpt_PB_Daily @from_date = '''+ @from_date +''', @to_date = '''+ @to_date +''', @country_id = '''+ @country_id +''', @sales_channel_id = '''+ @sales_channel_id +''' WITH RESULT SETS ((from_date datetime NOT NULL, to_date datetime NOT NULL, country_id nvarchar(50) NOT NULL, sales_channel_id nvarchar(250) NOT NULL))'
)

If you continue to face issues, consider whether using a linked server or another method of executing the stored procedure might be more straightforward or reliable for your scenario. Linked servers, for example, can simplify the syntax and make the procedure call more transparent.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.