October 17, 2006 at 5:18 pm
My environment consists of one database per customer, each database is a mirror image of the next. We have about 300 databases spread across 5 SQL Clusters and 1 "Pointer" server that points the web app to the correct server/database (user).
My company would like to start using Reporting Services, however I'm not sure what the best practice is for our type of environment.
Every database has about 40 report sprocs....which are the exact same from database to database. Is it better to install reporting services on each cluster and build a datasource for each server/database? I really don't even know where to start!
Is there anyone else out there with a similar environment that has run into this issue? Any suggestions?
October 19, 2006 at 4:10 pm
Use a parameter to hold the Database name and Use EXEC sp_executesql ...
Make your data source as follows....
DECLARE @strSQL nvarchar(1000)
SELECT @strSQL = 'select * from ' + @dataSource + '.dbo.yourQuery where x = ' + @param1
EXEC sp_executesql @strSQL
OR
DECLARE @strSQL nvarchar(1000)
SELECT @strSQL = exec ' + @dataSource + '.dbo.yourStoredProc ' + @param1
EXEC sp_executesql @strSQL
But you get the idea
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 19, 2006 at 4:12 pm
Also, if you need appostrophies in your query, you'll need to replace then with ' + char(39) + '
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 19, 2006 at 10:18 pm
Hi
Not sure if your data needs to be real time. But if not I think you could create a ODS (Operational Data Store) a mini data warehouse then use some ETL tool like DTS to get the data into the tables you require. Then you could run your report/s of the new ODS.
Mike
October 20, 2006 at 9:58 am
Thanks for the suggestions...I think our final solution is to create one data source directed at our "pointer" server (which is linked to all other servers) and force the web app to call stored procedures by their FQN.
I'll report back if we run into any major performance bottle necks...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy