Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Report to have servername as parameter display data Expand / Collapse
Author
Message
Posted Wednesday, December 12, 2012 3:32 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 3:34 AM
Points: 549, Visits: 1,554
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"
Post #1395554
Posted Wednesday, December 12, 2012 5:28 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 26, 2014 4:08 AM
Points: 708, Visits: 3,286
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

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
Post #1395602
Posted Wednesday, December 12, 2012 9:19 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 3:34 AM
Points: 549, Visits: 1,554
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"
Post #1395968
Posted Thursday, December 13, 2012 5:04 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 26, 2014 4:08 AM
Points: 708, Visits: 3,286
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

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
Post #1396127
Posted Thursday, December 13, 2012 5:43 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 3:34 AM
Points: 549, Visits: 1,554
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"
Post #1396137
Posted Friday, December 14, 2012 2:15 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 26, 2014 4:08 AM
Points: 708, Visits: 3,286
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

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
Post #1396525
Posted Friday, December 14, 2012 2:45 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 3:34 AM
Points: 549, Visits: 1,554
Thanks.

I will check the link.
Also Iam trying to using paramter in the datasource but it failed with the following error. Almost broken 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"
Post #1396533
Posted Friday, December 14, 2012 3:14 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 26, 2014 4:08 AM
Points: 708, Visits: 3,286
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 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

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
Post #1396546
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse