topic Re: R Script in direct query in Report Server

R Script in direct query

SwethaSatya — Wed, 07 Jul 2021 11:59:49 GMT

I am using R Script in Direct Query to retrieve data from SQL server using SP by passing logged-in user id.

In Power Query Editor window, the script runs fine and retrieves data. But on applying changes to the report, it is throwing below error:

Microsoft SQL: Incorrect syntax near the keyword 'EXEC'.
Incorrect syntax near ')'.

Sample code:

let
RScript = R.Execute("output <- read.table(text=system2(""whoami"", stdout=TRUE))"),
output = Record.FieldValues(RScript{[Name="output"]}[Value]{0}){0},

Source = Sql.Database("SQLServerName", "DBName", [Query="EXEC SP_Name '"&output&"'"])
in
Source

Re: R Script in direct query

d_gosbell — Thu, 08 Jul 2021 00:49:35 GMT

R script is not supported as a direct query source see https://docs.microsoft.com/en-us/power-bi/connect-data/power-bi-data-sources#data-sources-p-r

You might want to look into using either CURRENT_USER or SUSER_NAME or something like that inside your SQL query and then just use a SQL Server data source in Power Query

Re: R Script in direct query

SwethaSatya — Thu, 08 Jul 2021 08:42:31 GMT

We are using SQL server authentication to connect to DB. In this scenario, how do I pass LoggedIn user as parameter to SP?

Re: R Script in direct query

d_gosbell — Fri, 09 Jul 2021 06:36:28 GMT

SwethaSatya wrote:

We are using SQL server authentication to connect to DB. In this scenario, how do I pass LoggedIn user as parameter to SP?

You can't you would need to use Windows Auth to use either of those functions.