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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Kudoed Authors