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

[Anonymous]

Hello,

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]

[Greg_Deckler]

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.