change server connection

• mtz676

  SSCrazy Eights

  Points: 9261

  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 months ago by mtz676.

• pietlinden

  SSC Guru

  Points: 63575

  More actions

  December 5, 2024 at 3:38 pm

  #4493710

  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: 9261

  More actions

  December 5, 2024 at 5:34 pm

  #4493769

  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

  Default port

  Points: 1411

  More actions

  December 5, 2024 at 7:26 pm

  #4493820

  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