December 12, 2012 at 3:32 am
Hi all,
Iam designing report to display the index stat of the server we choose from the dropdown list box. I need your input and guideance to build this.
"More Green More Oxygen !! Plant a tree today"
December 12, 2012 at 5:28 am
So you want to have a report to allow you to select a SQL instance name from a drop-down box then display all index stats on all databases on the instance?
Can you confirm please?
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
December 12, 2012 at 9:19 pm
Yes that is right.
I have script to display index stats , My report should have parameter which will list all the servers I have and then depending on the server selection it should display the index stat of those DBs in that server.
Currently I have planned to have a table which will have all all the server name and the associated connections string depending on the selection the datasource connection string will also change and fetch the report.
I am not sure how to put that into execution as Iam new to SSRS.
"More Green More Oxygen !! Plant a tree today"
December 13, 2012 at 5:04 am
There are two parts to your process.
1) Information gathering
2) Report generation.
For the information gathering you could implement this in
a) Scheduled T-SQL script if you're able to use linked servers.
b) SSIS package which uses a servers table(where your server name and login details are stored) to generate the connection string dynamically then gather the required index info to be stored in a central database.
c) Scheduled PowerShell script.
Once you have all your index stats from all instances and all databases, it should be straight forward to generate the SSRS report.
See which option you can use to gather he info and if you need help with implementing an SSIS solution then let me know and I will help you get this to work as I have recently done a similar exercise.
Good luck!
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
December 13, 2012 at 5:43 am
Dina, thank you for information and yes if could help me out I will help me a lot. I am not at all good i SSIS and SSRS.
🙂
"More Green More Oxygen !! Plant a tree today"
December 14, 2012 at 2:15 am
I assume that you can't use linked servers as you mentioned storing SQL logins in a table. Is this the case or are you able to setup linked servers to access the index stats?
This is important as I don't want to go down the SSIS route if there is an easier alternative to implement.
By the way, can I suggest you Google for Rodney Landrum's DBA Repository and SQL-uation DBA Repository. These are two great solutions for gathering information from SQL instances in a trusted/non-trusted domain.
Although I didn't use their solution, I studied the code and SSIS packages and was able to customise my own solution.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
December 14, 2012 at 2:45 am
Thanks.
I will check the link.
Also Iam trying to using paramter in the datasource but it failed with the following error. Almost broken :w00t: but every run the same error popups
Any suggestions
Parameterized datasource with server and database as parameter text type.
=”datasource=” &Parameters!Server.Value&;initialcatalog=” &Parameters!Database.Value
error:
The ConnectString expression for the data source ‘DataSource1’ contains an error: [BC30037] Character is not valid.
"More Green More Oxygen !! Plant a tree today"
December 14, 2012 at 3:14 am
Minaz Amin (12/14/2012)
Thanks.I will check the link.
Also Iam trying to using paramter in the datasource but it failed with the following error. Almost broken :w00t: but every run the same error popups
Any suggestions
Parameterized datasource with server and database as parameter text type.
=”datasource=” &Parameters!Server.Value&;initialcatalog=” &Parameters!Database.Value
error:
The ConnectString expression for the data source ‘DataSource1’ contains an error: [BC30037] Character is not valid.
The way I've implemented my solution is like this:
1) I used a dedicated utility server which has SQL Server Database Engine, SSIS and SSRS installed on it.
2) Then I created a DBA Repository database (this just hold information about jobs, backups, users, db sizes etc....)
3) The SSIS package runs every day in the morning and uses a servers table in my repository database to loop through the servers. There is logic to handle domain and non-domain servers.
4) I then created my SSRS reports with my data source pointing at the repository database.
So if you can work out the following for me then we'll take it from there:
1) How many SQL instances are you going to monitor?
2) Are they all in the same domain?
3) Where do you plan to store the gathered information?
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply