Forum Discussion

jpanzeca's avatar
jpanzeca
Advocate I
8 years ago

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 ...
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

 

See screenshot:

jpanzeca's avatar
jpanzeca
Advocate I
8 years ago

Thanks Sonny,

 

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

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

  • jpanzeca's avatar
    jpanzeca
    Advocate I
    8 years ago

    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]")

  • v-caliao-msft's avatar
    v-caliao-msft
    Microsoft Employee
    8 years ago

    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