Changing Old SQL Data Source to New SQL Data Source

[tsutton9]

We are currently sourcing 20 different tables for a particular report from an old SQL server which has since been rebuilt as a new SQL server with new table+column names (simplifying and combining data and pulling raw data from SalesForce, GreatPlains, etc.). I need some advice to figure out the best method for replacing the old SQL data source with the new one when table names and column names no longer match. (Have to do this for about 20 reports total with multiple tables/data sources per report).

[lbendlin]

Short term you can recreate the old table format as views on the new server. That way you would "only" have to change the server name. Still a big thing as soon as gateways are involved.

Long term you have to consider a rewrite. Painful but necessary.