Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
I have created a basic stored procedure without parameters in SQL Server, when I use the store procedure in Power Bi Desktop the return is ok in design but when I click on Save and Close in datasource edit the Power Bi gives me an errormessage like below, I'm trying to use this procedure as Direct Query.
Microsoft SQL: Incorrect syntax near the keyword 'EXECUTE'. Incorrect syntax near ')'.
I tried to change the statement but I got nothing.
The sql statement to execute the procedure is:
EXECUTE [Person].[SelectpersonByType]
I'm using the AdventureWorks database sample from Microsoft and my procedure statement is:
CREATE PROCEDURE [Person].[SelectpersonByType]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select * from Person.Person
END
My customer is testing Power BI and are facing the same error, thanks in advanced.
I tried the same way to execute one of the stored procedure inside my Oracle package but failed
-- create a variable to hold SQL command to run
DECLARE @SqlCommand varchar(8000)
-- set the command to run
SET @SqlCommand = 'INTG_PA_BAL_DASHBOARD_PUB.populate_balance_db'
-- run it!
EXEC (@sqlCommand @parameter1=529,@parameter2="ALL",@parameter3="Feb-17",@parameter4=" ",@parameter=" ")
I tried the same way to execute one of the stored procedure inside my Oracle package but failed
-- create a variable to hold SQL command to run
DECLARE @SqlCommand varchar(8000)
-- set the command to run
SET @SqlCommand = 'INTG_PA_BAL_DASHBOARD_PUB.populate_balance_db'
-- run it!
EXEC (@sqlCommand @parameter1=529,@parameter2="ALL",@parameter3="Feb-17",@parameter4=" ",@parameter=" ")
@dsilveira Nice! Thanks for sharing.
@Seth_C_Bauer I tried proposed solution but I am getting below error in Direct Query mode against Azure SQL data source.
Details: "EvaluateNativeQueryUnpermitted failure: the query 'DECLARE @sqlCommand varchar(1000)
SET @sqlCommand = 'dbo.uspGetSyncEventSchedulePB'
EXEC (@sqlCommand)
' isn't approved for execution."
I am not sure if I am missing something as datasource is Azure Sql.
@Vivs18 If you note, I make reference in my solution that it only works with Import. Not when using Direct Query.
If you want to continue to use DQ, could you create a View and reference that?
@Seth_C_Bauer I missed import in provided solution. But I tried with the proposed solution with Import but still it resulted in same error. I am not sure if I am missing anything here.
@Vivs18 It could be a limitation in Azure SQL DB... There are a bunch of differences - Reference
Small query over here,If we need to pass parameters to the procedure then how can we use this here?Could you share the syntax so that it would be helpful for beginners.
Thanks.
you can use below syntax:
EXEC IP_sel_OrderIntakeRS 'Mazlan','2020-11-04','2020-11-04'
Check out @v-yuezhe-msft solution in this link - http://community.powerbi.com/t5/Integrations-with-Files-and/parameters-to-SP-or-Sql-query/m-p/58337/...
@vineetmodi@Zak2815@anakoom Letting the cat out of the bag a little early here as I'm going to write up a blog around this, but rather than use a stored procedure you can use a Table Valued Function. This function allows for input parameters to be passed in. For example: if I were to query the AdventureWorks2014 database and a function in there looks like this:
CREATE FUNCTION [dbo].[ufnGetContactInformation](@PersonID int) RETURNS @retContactInformation TABLE ( -- Columns returned by the function [PersonID] int NOT NULL, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [JobTitle] [nvarchar](50) NULL, [BusinessEntityType] [nvarchar](50) NULL ) AS -- Returns the first name, last name, job title and business entity type for the specified contact. -- Since a contact can serve multiple roles, more than one row may be returned. BEGIN IF @PersonID IS NOT NULL BEGIN IF EXISTS(SELECT * FROM [HumanResources].[Employee] e WHERE e.[BusinessEntityID] = @PersonID) INSERT INTO @retContactInformation SELECT @PersonID, p.FirstName, p.LastName, e.[JobTitle], 'Employee' FROM [HumanResources].[Employee] AS e INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID] WHERE e.[BusinessEntityID] = @PersonID; IF EXISTS(SELECT * FROM [Purchasing].[Vendor] AS v INNER JOIN [Person].[BusinessEntityContact] bec ON bec.[BusinessEntityID] = v.[BusinessEntityID] WHERE bec.[PersonID] = @PersonID) INSERT INTO @retContactInformation SELECT @PersonID, p.FirstName, p.LastName, ct.[Name], 'Vendor Contact' FROM [Purchasing].[Vendor] AS v INNER JOIN [Person].[BusinessEntityContact] bec ON bec.[BusinessEntityID] = v.[BusinessEntityID] INNER JOIN [Person].ContactType ct ON ct.[ContactTypeID] = bec.[ContactTypeID] INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = bec.[PersonID] WHERE bec.[PersonID] = @PersonID; IF EXISTS(SELECT * FROM [Sales].[Store] AS s INNER JOIN [Person].[BusinessEntityContact] bec ON bec.[BusinessEntityID] = s.[BusinessEntityID] WHERE bec.[PersonID] = @PersonID) INSERT INTO @retContactInformation SELECT @PersonID, p.FirstName, p.LastName, ct.[Name], 'Store Contact' FROM [Sales].[Store] AS s INNER JOIN [Person].[BusinessEntityContact] bec ON bec.[BusinessEntityID] = s.[BusinessEntityID] INNER JOIN [Person].ContactType ct ON ct.[ContactTypeID] = bec.[ContactTypeID] INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = bec.[PersonID] WHERE bec.[PersonID] = @PersonID; IF EXISTS(SELECT * FROM [Person].[Person] AS p INNER JOIN [Sales].[Customer] AS c ON c.[PersonID] = p.[BusinessEntityID] WHERE p.[BusinessEntityID] = @PersonID AND c.[StoreID] IS NULL) INSERT INTO @retContactInformation SELECT @PersonID, p.FirstName, p.LastName, NULL, 'Consumer' FROM [Person].[Person] AS p INNER JOIN [Sales].[Customer] AS c ON c.[PersonID] = p.[BusinessEntityID] WHERE p.[BusinessEntityID] = @PersonID AND c.[StoreID] IS NULL; END RETURN; END;
In my advanced options - SQL Query in Power BI I can call it just executing the select like this:
select * from [dbo].[ufnGetContactInformation] (73)
and the output is returned as expected. This also allows you to do any CTE's or temp tables within the function.
Is there another way to do this? In your example, you put the input parameters in the query. If I extract all the distinct values, in your case, all the PersonID as a table. And created a slicer, is there a way for the PBI used the value selected in the slicer and call the function to return the output?
Thanks!
@anakoom You can't. If you watch what is actually sent to the SQL server you will see that an added syntax is added to the command which invalidates the statement. In Import, it just executes the SQL statement. In Direct Query it changes it to something like Select * from () - this is off the top of my head. In either case, that is why the openquery works, or the table valued function because both of those wouldn't create un-executable code.
I'm giving you an alternative to a SP because that method doesn't work, you can transform whatever you are doing in that SP to the TVP.
@anakoom Probably not in the way you are thinking...
Power BI is best when you pull in the larger dataset and add slicers and filters to the report.
I'm not aware of any way to pass a parameter all the way downstream from a deployed report in the Power BI Service. You can do this in the desktop, but that would be stuck in that mode from my understanding.
Hi,
table value function is just fine, but not work if you call it with parameter which is not constant 😞
I tried to send any info about userid, dax expresion about userid, or anything similar which is not constant, didn't successed.
select * from TableFunction('abc') run fine, but
select * from TableFunction(USERPRINCIPALNAME()) fail...
Really It is awesome.It could help multiple users.As most of them not aware of this solution.If we might initimate in forum it would be great.
1. tried to change from Direct query to import but the Power BI doesnt give me that option to change.
It is most likely because you already have a connection to a SQL source in Direct Query mode. If that is the case, I believe this gets greyed out.
2. Also tried the OPENQUERY OPTION and I am now getting the below message:
Details: "Microsoft SQL: Server 'sv375002\lt1201' is not configured for DATA ACCESS."
You would need to enable direct access on your SQL Server. You can check what it is currently set at by running this:
select server_id, name, is_data_access_enabled from sys.servers
But I would definitely check with your DB team (if you aren't) before switching anything.
To enable you would execute EXEC sp_serveroption '<Your ServerName>', 'DATA ACCESS', TRUE
After enabling that feature you will be able to successfully execute the OpenQuery.
As noted, be sure you understand what you are enabling and what it is doing before executing anything.
User | Count |
---|---|
94 | |
92 | |
84 | |
83 | |
49 |
User | Count |
---|---|
150 | |
146 | |
112 | |
73 | |
56 |