SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Report to have servername as parameter display data


Report to have servername as parameter display data

Author
Message
Minaz Amin
Minaz Amin
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2770 Visits: 1750
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"
Abu Dina
Abu Dina
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3051 Visits: 3325
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
Minaz Amin
Minaz Amin
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2770 Visits: 1750
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"
Abu Dina
Abu Dina
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3051 Visits: 3325
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
Minaz Amin
Minaz Amin
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2770 Visits: 1750
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"
Abu Dina
Abu Dina
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3051 Visits: 3325
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
Minaz Amin
Minaz Amin
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2770 Visits: 1750
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"
Abu Dina
Abu Dina
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3051 Visits: 3325
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

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search