Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am trying to execute Stored procedure and fetch results, I am getting error can any one help?
I'm trying to execute a simple stored procedure and getting error: "the EXECUTE permission was denied on object 'stored proc name', database 'database name', schema 'dbo'."
Stored proc is just this:
USE [DB name]
GO
CREATE PROCEDURE [dbo].P_x AS
SELECT 'x' as name
go
I am able to use views from this same db no problem and have created a number of reports, dashboards, etc. however for the life of me can't figure out how to use a stored proc instead of a view..
To connect inside PowerBI I use the following syntax:
SELECT *
FROM OPENQUERY ([server_x],
'EXEC db_x.dbo.P_x');
@arelf27 What is the reasoning for using the OPENQUERY?
You are all ready setting the connection to the database, if you just use "EXECUTE dbo.P_x" it should run without any issues.
I believe I need to do openquery as I'm using DirectQuery not Import option. From what I read openquery should work for either.. I did try just execute dbo.p_x and it worked to where I could see the resultset of my stored proc and even the sequel statement appeared fine and said there are no syntax errors, however strangely enough, when I pressed close & apply it says there's a syntax error....so had to go back to query window and delete the query.. I think it's because of the DirectQuery.. I mean otherwise what would be the mechanism of re-running the stored proc for updated resultset? (in other project where I'm using Import I am able to execute the stored proc per the method you mention and it works fine...I just really want to stick to DirectQuery for this project)
What is the error that you are getting? Is it something along the lines of "not authorized for execution"?