Getting Error in Exporting the data from power Bi using Direct Query into Sql Server

[Anonymous]

Hello, 

I have a request to export the data from Power BI into SQL server. The report of Power Bi which I am using is the PBIT template  & working on the Direct Query mode to fetch the data from the Source System. I have googled some solutions and found out one method where the data can be exported through the R script mentioned in below link. 

https://www.biinsight.com/exporting-power-bi-data-to-sql-server/#:~:text=You%20can%20export%20Power%....

I have tried the steps mentioned in the above link through R Script and successfully installed the RODBC package but getting an error while exporting the data. Attaching the code snippet and the error. 

Code for R Script: 

library(RODBC)
conn <- odbcDriverConnect("Driver=SQL Server Native Client 11.0; server=GDQKVPRRPWV01P,58082; Database=DWH")
odbcClearError(conn)
sqlSave(conn, dataset, tablename="CQD",rownames=FALSE, safer=FALSE, append=TRUE)
close(conn)

Error 

DataSource.Error: ADO.NET: R script error.
Warning messages:
1: In odbcDriverConnect("Driver=SQL Server Native Client 11.0; server=GDQKVPRRPWV01P,58082; Database=DWH") :
[RODBC] ERROR: state 28000, code 18456, message [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user ''.
2: In odbcDriverConnect("Driver=SQL Server Native Client 11.0; server=GDQKVPRRPWV01P,58082; Database=DWH") :
ODBC connection failed
Error in odbcClearError(conn) :
first argument is not an open RODBC channel
Execution halted

Details:
DataSourceKind=R
DataSourcePath=R
Message=R script error.
Warning messages:
1: In odbcDriverConnect("Driver=SQL Server Native Client 11.0; server=GDQKVPRRPWV01P,58082; Database=DWH") :
[RODBC] ERROR: state 28000, code 18456, message [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user ''.
2: In odbcDriverConnect("Driver=SQL Server Native Client 11.0; server=GDQKVPRRPWV01P,58082; Database=DWH") :
ODBC connection failed
Error in odbcClearError(conn) :
first argument is not an open RODBC channel
Execution halted

ErrorCode=-2147467259
ExceptionType=Microsoft.PowerBI.Scripting.R.Exceptions.RScriptRuntimeException

Please help me in resolving the error 

[Anonymous]

Hi @Anonymous,

According to your error messages, it seems like your script functions failed to login into the internal data source.
I'd like to suggest you double-check the R script and correspond library package versions to confirm if they are compatible.

In addition, have you tested with the other connection modes? AFAIK, power bi uses SSAS tabular instance to handle data mode tables, but direct query connection mode seems not to include the data model tables.  (these scripts may not find out the hidden instance to execute the functions) 

You can try to switch to import mode to ensure this.

BTW, please remove sensitive data from your error messages.

Regards,

Xiaoxin Sheng