Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
jpanzeca
Advocate I
Advocate I

specifying linked server

Can I specify a linked server in the data source settings?

 

I am able to write queries in SSMS by specifiying the linked server in the query.

 

    SELECT * FROM [linked server name].[data base name].[table name]

 

I would like to use this same linked server to access data but can't seem to figure out how to tell Power BI to do this.

6 REPLIES 6
DamianDavies
Frequent Visitor

You can create a view in your "SomeDatabase" and then query the view in Power BI via a Data Gateway.

 

d;)

netboyz
Regular Visitor

I see this question was asked long ago and probably everybody's solved it by now, but I couldn't find any direct answers posted. Here are several M snippets that pull data through SQL Server via Linked Servers with Joins in M. In the examples, Linked Server LS_Proto is targeted to an Excel Workbook, and I'm pulling data from named ranges but of course these can be hacked to pull from different sources. I'm illustrating use of LS with several M data functions. And while we're pulling data thru SQL Server from external sources, showed use of OPENROWSET too.

 

Using OleDb.DataSource, with Linked Server Query, to get and join from Excel Named Ranges
let
xlAlloc=OleDb.DataSource("Provider=MSOLEDBSQL; initial catalog=ExcelProto; data source=MYSERVER\SQLEXPRESS", [Query="SELECT * FROM LS_Proto...rngAlloc"]),
xlBOM=OleDb.DataSource("Provider=MSOLEDBSQL; initial catalog=ExcelProto; data source=MYSERVER\SQLEXPRESS", [Query="SELECT * FROM LS_Proto...rngBOM"]),
AB=Table.Join(xlAlloc, {"akAllocRevKey"},xlBOM,{"bmBOMItemKeyIn"},JoinKind.LeftOuter),
in
AB

 

Using OleDb.Query, with Linked Server Query, to get and join Excel Named Ranges
let
FileSource = "Provider=MSOLEDBSQL; initial catalog=ExcelProto; data source=MYSERVER\SQLEXPRESS",
xlAlloc = OleDb.Query(FileSource,"SELECT * FROM LS_Proto...rngAlloc"),
xlBom = OleDb.Query(FileSource,"SELECT * FROM LS_Proto...rngBOM"),
AB=Table.Join(xlAlloc, {"akAllocRevKey"},xlBOM,{"bmBOMItemKeyIn"},JoinKind.LeftOuter),
in
AB


Using OleDb.DataSource, with OPENROWSET Query, to get and join Excel Named Ranges
let
xlAlloc=OleDb.DataSource("Provider=MSOLEDBSQL; initial catalog=ExcelProto; data source=MYSERVER\SQLEXPRESS", [Query="SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; Database=C:\filepath\File.xlsm;HDR=YES', rngAlloc)"]),
xlBOM=OleDb.DataSource("Provider=MSOLEDBSQL; initial catalog=ExcelProto; data source=MYSERVER\SQLEXPRESS", [Query="SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; Database=C:\filepath\File.xlsm;HDR=YES', rngBOM)"]),
AB=Table.Join(xlAlloc, {"akAllocRevKey"},xlBOM,{"bmBOMItemKeyIn"},JoinKind.LeftOuter),
in
AB

Anonymous
Not applicable

You should be able to do that with a raw query in the Advanced options of the SQL Server prompt.

 

See screenshot:

PBIDesktop_2017-11-01_10-49-10.png

Thanks Sonny,

 

I had tried that before and received the following error...

    The key didn't match any rows in the table.

@jpanzeca,

 

I had tried that before and received the following error...

    The key didn't match any rows in the table.

This error indicates that we cannot query linked server directly in Power BI. And to work around this issue, we need to create an ODBC connection and then changing each query to use the ODBC connection and the sql query as you sauid.

 

If you have any concern on this feature, you can submit your idea on the link below. If this feature was mentioned by mulitple users, product team will consider to add this feature to next release.
https://ideas.powerbi.com/forums/265200-power-bi-ideas

 

Thank you for your understanding.

 

Regards,

Charlie Liao

I did find a work around by creating an ODBC connection and then changing each query to use the ODBC connection and the sql query.  This was a bit time consuming as I had over 20 queries but it works for now.

 

    = Odbc.Query("dsn=ODBC Connection", "SELECT * FROM [Link Server].[Database].[Table]")

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.