We have a setup with a web server and multiple databases, and a live, stage, and dev environment. We use SQL Server standard 2005 and use the ASP.NET ReportView control. I have spent countless hours now trying to get this to work and am about to give this up and go back to Crystal.
First I wanted a report that would work from dev to stage to live without modification, so we set up shared data sources on each environment to point to the appropriate database. No problem, I can publish it to each environment and it works, though sometimes I have to go into Report Manager and fix the data source.
Next I wanted to be able to work with multiple databases, identical in structure. For this we did a hidden parameter with the database name and used a formula for the query string. This works pretty well.
Next I wanted to be able to run against multiple database servers from a single web server. This has been nearly impossible. I've read a million posts about this, and nothing seems to work well. I've tried a dynamic connection string, and passing the server in as a parameter, but this doesn't work, because I can't get the credentials set on the ReportViewer.ServerReport, so it doesn't work from dev to stage. You can't programatically change the shared data source - that would make it too easy. Linked servers are not an option.
I guess I need to either publish a copy of the report for each database server, or set up an instance of SQL on the web server for each database server.
Any other reasonable options out there. I just can't imagine my setup is all that unique.
Have you explored the possibility of using Synonyms?
(Even dyanmically creating them when necessary.)
|||Whats a Synonym?
No comments:
Post a Comment