Changing Server Names

[CaseyD]

My SQL server connections will be changing over the next few months and I have 27 reports and probably hundreds of connections to my SQL server. I am trying to switch all of my connections in all of my reports by simply changing to source to the output of a new query. I created a table with two columns ServerName and Source, where source is a true false depending on if it is the source.
SQL Server Connections.xlsx (9.2 KB)

I then change the data types around in M, filter Source to TRUE and then extract the ServerName value.

let
Source = Excel.Workbook(File.Contents("\\data-analytics\c$\Dashboard\SQL Server Connections.xlsx"), null, true),
Servers_Table = Source{[Item="Servers",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Servers_Table,{{"ServerName", type text}, {"Source", type logical}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Source] = true)),
server = #"Filtered Rows"{0}[ServerName]
in
server

This all works great but I cannot figure out how to connect my SQL Server data sources to the output of this query.
The name of this query is called Servers and I have tried:
Sql.Databases("Servers")
and
Sql.Databases(#"Servers")

but I cannot connect to the server.

Does anyone have a great way of using the output of an M query as the source of another SQL Server query?

[Nick_M]

@CaseyD,
I gave this a shot, and here’s what I got so far. I tried using parameters and for some reason it would not work. So went at it a different way. Going on the theory that only one server will be active at a time we can drill down into that server name: (had to add in my local server name to see if it would connect)

Here’s our table of servers:

Load that into PQ and

1. Filter the Source Column = 1
2. Assuming that is only one server, drill down into that value
3. Name the query as Servers
   

Then in your existing connections, go into the applied setting and change out existing server name, and type in Server:

You might run into the Query Firewall issue, but hard to tell without actually seeing. An easy fix of that is select “Ignore privacy settings” in the workbook.

Hope that helps
-Nick

[CaseyD]

Hi @Nick_M!

Thank you so much for the detailed response. I was able to get as far as the firewall warning but then unable to resolve the issue. I went into options-> Privacy and then checked Always ignore Privacy Level settings but the error was not resolved. Do you know of a way to get around this error?

Thank you again so much!

[Nick_M]

I think you were in the Global Settings and not the workbook settings?

[CaseyD]

Perfect! I got it to work. Now I just need to replace the excel table with a SQL table for less vulnerability.

Thank you!

[CaseyD]

@Nick_M
I created the table and a stored procedures for simply switching back and forth between servers (updates the status). I updated the sources for all of my SQL server connections in the report and am able to switch servers easily with the refresh and stored procedure. However, when I push the report to the Service I get an unsupported data source error when trying to schedule a refresh.
Do you have any means of getting around this error?
Query contains unknown or unsupported data sources.
SQL, tims-sql-v7.norco.com;DataAnalytics

[Nick_M]

@CaseyD
Not off the top of my head. I will try to make some time to see if I can figure something out

[CaseyD]

Thanks @Nick_M!

[Nick_M]

@CaseyD
Came across the same error, still trying to figure out a work-around…