Showing results for 
Search instead for 
Did you mean: 
caseyd Frequent Visitor
Frequent Visitor

Changing SQL Server Connections for Multiple Reports with a Since Stored Procedure



I am trying to engineer a solution for changing the SQL Server connection for all of the SQL Server connections between 30 reports. I have read @GilbertQ 's post on using parameters and love the solution; however, it does require a user to change the server name in each report. I feel like I came up with a great solution but am getting an error in the Service when I try to establish a refresh schedule. Below are the steps that I took and the error message:

1.  I created a table with two columns [Server],[Status] (status being a true/false identifier to choose which server to use -  only one server can be active at a time)

2. I created a stored procedure to change the [Status] between different servers

3. I connect to the table in all reports, filter for [Status] = 1, then drill down on the [Server] to get the output of the server name

4. I then use the name of the query in step 3 as the source for all of my SQL Server connections. 

I can get the report to refresh in the desktop after toggling between servers but when I publish to the web I cannot get the refresh schedule to work. Each sql source shows the same error: 

Query contains unknown or unsupported data sources. SQL, [actual servername].[actual domain];[actual database]




Super User
Super User

Re: Changing SQL Server Connections for Multiple Reports with a Since Stored Procedure

Why not just create a DNS entry for your SQL server. Then you can point it to any SQL Server you want at any time. If you are using a gateway, you could even use local host file entries in c:\Windows\System32\Drivers\etc\hosts. Then you wouldn't even need to get the DNS folks involved.

I have book! Learn Power BI from Packt

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Helpful resources

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 391 members 4,051 guests
Please welcome our newest community members: