change server connection

Question

change server connection

mtz676

SSCrazy Eights

Points: 9217
More actions
December 5, 2024 at 12:32 pm

#4493634
I have a one database and one table in that database. This is on 100 servers.
Same db and table name
1.Can I build a report which will change server connection based on the server selected in a drop down list of 100 servers.
Can the select data from multiple servers based on a check box list of 100 servers ?
2.Like if I select 10 servers I need data from 10 servers in the report.
Thanks
- This topic was modified 6 days, 11 hours ago by mtz676.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63506 More actions · Answer 1

Sounds like a hideous design.

Why not just put all the data in the same database with a foreign key column to the server name? Then you don't need any dynamic SQL or anything even remotely complicated.

"I'm [not] Kent Brockman, [but] that's My Two Cents"

mtz676 SSCrazy Eights Points: 9217 More actions · Answer 2

Thank you. But I already know that way. Trying to see if I can avoid centralizing the data into 1 server. No good reason for this.

But wanted to know if it can be done the way I am currently looking for

ratbak Ten Centuries Points: 1380 More actions · Answer 3

You could use a parameter to let user choose server, & programmatically derive the connection string & dataset. See links below

Or -- not recommended -- you could embed all 100 connections & datasets in the report & choose the correct ones using selected parameter.

https://stackoverflow.com/questions/24065148/dynamically-set-the-data-source-for-ssrs-reports-without-unattended-execution-ac

https://stackoverflow.com/questions/19827625/ssrs-report-dynamic-connection-string-and-parameters

https://stackoverflow.com/questions/14546125/change-ssrs-data-source-of-report-programmatically-in-server-side