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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.