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...
This code executes as intended in Desktop, but fails in Power BI cloud.
DECLARE @CustomerNumberIDList dbo.IdList
Declare @Success int
INSERT INTO @CustomerNumberIDList
SELECT Distinct CustomerNumberId
EXEC @Success=[dbo].[LogCustomerView] @CustomerNumberIDList
My requirement is to log the key values anytime a customer is used in a report for privacy resason. The portion that does the logging: 'EXEC @Success=[dbo].[LogCustomerView] @CustomerNumberIDList' works like a charm whenever I refreesh in Power BI desktop, but doesn't log the key values when I run the exact same report after uploading it to the cloud. It does still get the dataset though.
An y ideas on why that call to log the keys only works in desktop?
Openquery did not work for us as we have an SP that utilized a temp table.
but we have had some success using "with result sets" option of exec when that issue comes up.
SELECT * FROM OPENQUERY ("snapserver", 'EXEC specialprojects.dbo.CFE_DASHBOARD_Summary_BugTrendX WITH RESULT SETS ( ( [rel] nvarchar(16) NOT NULL, [value] int NOT NULL, [year] int NOT NULL, [series] nvarchar(16) NOT NULL, [sortorder] int NOT NULL ))')
This seems to work without issue. Not always ideal as it requires changing the result set definition if the query changes but as these are bi reports they are likely using the same output scheme for most queries.
the second half of this is trying to get parameters to work.
i think that this would work if the declare statement is removed. bi doesnt seem to like thing like declare or with unless part of the dynamic sql which wont work in this case.
I assume the declare statement can be removed and @team replaced with a bi parameter. but i have not tried it yet.
declare @team nvarchar(max)= 'winet' exec (' SELECT * FROM OPENQUERY ("snapserver", ''EXEC specialprojects.dbo.CFE_DASHBOARD_Summary_BugTrendX @team='+@team+' WITH RESULT SETS ( ( [rel] nvarchar(16) NOT NULL, [value] int NOT NULL, [year] int NOT NULL, [series] nvarchar(16) NOT NULL, [sortorder] int NOT NULL ))'')')
I know this post might have been closed as the solutions provided here is working for many.
However when I am trying to follow same steps getting different kinds of errors.
We were fetching the data from remote database(ORACLE) through VPN in Power BI using Server Name and Connection String.
Using below mentioned syntax, writing the query in Import section and calling the stored procedure which is created in remote database. But when we try to run the report getting the error as follows. To be double sure the stored procedure is not having syntax error or such, executed the same procedure in sql developer and it works as expected. Any help or lead on this is highly appreciated.
FROM OPENQUERY ([server name],
'EXEC dbname.dbo.spname @parametername = ''R1''');
For the above query, the error msg is:
DECLARE @sqlCommand varchar(1000) ==> DECLARE @Variable varchar(1000)
SET @sqlCommand = 'dbo.Testproc' ==> SET @Variable = 'dbo.nid_poc_test1'
EXEC (@sqlCommand) ==> EXEC (@variable)
For this query the error msg is:
Unable to connect
We encountered an error While trying to connect.
Details: "OracleRA-06550:line 3,column 5: PLS-00488:'variable' must be a type ORA-06550:line 3,column 5:
PL/SQL: Item ignored
ORA-06550:line 5,column 1:
PLS-00221:'variable'is not a procedure or is undefined
ORA-06550:line 5,column 1:
PL/SQL: "Statement ignored"
Error2 while using semicolon at the end:
DECLARE @Variable varchar(1000);
SET @Variable = 'dbo.nid_poc_test1';
Here VPN is very much connected and stored procedure was pretty much running fine in remote DB. We have also tried giving the command time out in Power BI.
I know this is an old post, but what's the use/benefit of using:
DECLARE @sqlCommand varchar(1000)
SET @sqlCommand = 'dbo.Testproc'
Opposed to a plain and simple:
@nirvana_moksh At the time of the previous post when you ran a stored procedure against the database in Direct Query mode it woud fail. This work around got it to work, I have not tested it in awhile, so I don't know if that behaviour has changed. If you use "import" then you can just use a straight execute statement.