Script to add groups and register servers in those groups

We have several different environemnts with several servers in each.

We now want to share SQL Server Admin around the group.

I would like to create a script that would add each server group and register each server in the appropriate group

Something Like

exec sp_addservergroup 'Dev'

exec sp_registerserver 'Dev', 'AccountingDev'

exec sp_registerserver 'Dev', 'MarketingDev'

exec sp_addservergroup 'Test'

exec sp_registerserver 'Dev', 'AccountingTest'

exec sp_registerserver 'Dev', 'MarketingTest'

exec sp_addservergroup 'Prod'

exec sp_registerserver 'Dev', 'AccountingProd'

exec sp_registerserver 'Dev', 'MarketingProd'

I've looked in books online and googled but can't seem to lay my finger on the TSQL solution, if there is one. Thanks In Advance for any help

MPM

Apologies if this gets reposted, tech probs first time round

Moving the thread to Tools forum since this is not a TSQL feature.|||I don't believe there is a T-SQL solution to this however you should be able to right click on the registered servers tree in SSMS and export the registered servers from there.

Script to add groups and register servers in those groups

We have several different environemnts with several servers in each.

We now want to share SQL Server Admin around the group.

I would like to create a script that would add each server group and register each server in the appropriate group

Something Like

exec sp_addservergroup 'Dev'

exec sp_registerserver 'Dev', 'AccountingDev'

exec sp_registerserver 'Dev', 'MarketingDev'

exec sp_addservergroup 'Test'

exec sp_registerserver 'Dev', 'AccountingTest'

exec sp_registerserver 'Dev', 'MarketingTest'

exec sp_addservergroup 'Prod'

exec sp_registerserver 'Dev', 'AccountingProd'

exec sp_registerserver 'Dev', 'MarketingProd'

I've looked in books online and googled but can't seem to lay my finger on the TSQL solution, if there is one. Thanks In Advance for any help

MPM

Hi,

first as I saw the names of that procedured I was puzzled though they looked like procedures in SQL Server but I wasn′t aware of. I overread you "something like" statement. I assume that you want to add these server to the Enterprise manager for adminstering them. But EM is a client application, though executing these scripts on the server wouldn′t affect the client at all (beside that they don′t exists :-) ) What you can do is to open up MMC, add a snapin (The SQL Server Managment Console) and add the appropiate servers, afterwards you can just save (as..) the msc file as a administrative template which can be shared with your peers / colleagues.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks, Jens, that is a winner.|||Is there any way out to do the same thing but via T-SQL script only ?
More-over in which table the entries go?

Thks|||No, those registrations are part of the client tool and NOT part of the database so there is no T-SQl or any other command (except the GUI) to create server groups and register servers. As such, they are also NOT stored in the database at all, but in a snap-in file on the client machine that has the tool installed.|||

In such a case, then what would be the windows script or VBA script who can read and list the available groups and database servers available in it.

thks

Kuljit

Script to add groups and register servers in those groups

We have several different environemnts with several servers in each.

We now want to share SQL Server Admin around the group.

I would like to create a script that would add each server group and register each server in the appropriate group

Something Like

exec sp_addservergroup 'Dev'

exec sp_registerserver 'Dev', 'AccountingDev'

exec sp_registerserver 'Dev', 'MarketingDev'

exec sp_addservergroup 'Test'

exec sp_registerserver 'Dev', 'AccountingTest'

exec sp_registerserver 'Dev', 'MarketingTest'

exec sp_addservergroup 'Prod'

exec sp_registerserver 'Dev', 'AccountingProd'

exec sp_registerserver 'Dev', 'MarketingProd'

I've looked in books online and googled but can't seem to lay my finger on the TSQL solution, if there is one. Thanks In Advance for any help

MPM

Hi,

first as I saw the names of that procedured I was puzzled though they looked like procedures in SQL Server but I wasn′t aware of. I overread you "something like" statement. I assume that you want to add these server to the Enterprise manager for adminstering them. But EM is a client application, though executing these scripts on the server wouldn′t affect the client at all (beside that they don′t exists :-) ) What you can do is to open up MMC, add a snapin (The SQL Server Managment Console) and add the appropiate servers, afterwards you can just save (as..) the msc file as a administrative template which can be shared with your peers / colleagues.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks, Jens, that is a winner.|||Is there any way out to do the same thing but via T-SQL script only ?
More-over in which table the entries go?

Thks|||No, those registrations are part of the client tool and NOT part of the database so there is no T-SQl or any other command (except the GUI) to create server groups and register servers. As such, they are also NOT stored in the database at all, but in a snap-in file on the client machine that has the tool installed.|||

In such a case, then what would be the windows script or VBA script who can read and list the available groups and database servers available in it.

thks

Kuljit

sql

scouts, badges, and groups of badges (was "Please help")

Hi Guys,:rolleyes:

I am designing a database for scouts and i am stuck on the ERD(relationships design)...The situation is this: I have an entity 'scout' and entity 'badge'.
A scout can have many badges as obvious. However there are different classification(groups) of badges(main badges, activity badges,Staged Activity badges...). Each of these group of badges consists of different types of badges and finally a badge type has required tasks to accomplish to qualify for it!!

Is there anyone with a suggestion how to model these entities please??

migarich@.yahoo.frWhen you post your homework, please either provide the URL where the assignment was given, or at least scan in the handout you got in class that describes the assignment. It makes life so much simpler if we can do your homework for you in one pass, instead of completing what you've posted so far, only to have you realize that there is another part you need for full credit!

-PatP|||This is not actually an assignment but a system i am designing as a voluntary action for my local scout troup...and there is no specifications as such...the descriptions i have made are from my head and not some handouts as you may think...If there is any part of the description you do not understand i would be grateful to hear...as i said it is only part of the problem that i am having issues with.

Regards|||Ok, I see three detail level tables: Scouts, Requirements, Badges.

I see three linking (many to many) tables, Scout to requirement (showing that the scout has met a requirement), Badge to requirement (showing that the requirement is a pre-requisite for the badge), and Scout to badge (showing that the scout has earned the badge).

I see one "decorative" table, showing that a badge is of a given badge type.

-PatP|||Here is a sample model using natural keys. You can substitue surrogate keys if you'd like:
Table: Scouts
(ScoutName, [pkey]
etc...)

Table: BadgeClasses
(BadgeClass, [pkey]
etc...)

Table: Badges
(Badge, [pkey]
BadgeClass, [fkey to BadgeClasses]
etc...)

Table: ScoutBadges [implements many-to-many relationship between scouts and badges]
(ScoutName, [fkey to Scouts]
Badge, [fkey to Badges]
etc...)|||Hi Blindman,

This definetely makes sense...I was thinking more of the supertypes and subtypes...Would you say it is feasible?...Thanks

Here is a sample model using natural keys. You can substitue surrogate keys if you'd like:
Table: Scouts
(ScoutName, [pkey]
etc...)

Table: BadgeClasses
(BadgeClass, [pkey]
etc...)

Table: Badges
(Badge, [pkey]
BadgeClass, [fkey to BadgeClasses]
etc...)

Table: ScoutBadges [implements many-to-many relationship between scouts and badges]
(ScoutName, [fkey to Scouts]
Badge, [fkey to Badges]
etc...)|||Thank you very much...It is now clearer...How about replacing the requirement table by 'Scout_badge' which has 2 key fields as from scout and badge??

Thanks for you help|||Table: Requirements
(Requirement, [pkey]
Badge, [fkey to badges]
Description,
etc...)

Table: ScoutRequirementsCompleted
(ScoutName, [fkey to scouts]
Requirement, [fkey to Requirements]
DateCompleted,
etc...)

When a scout has completed all the requirements for a badge, grant him the badge and add a record to ScoutBadges|||But a Scout can only earn a badge once, and if I remember correctly, aren;t some badges prerequsities to other badges?|||But a Scout can only earn a badge once, and if I remember correctly, aren;t some badges prerequsities to other badges?A UNIQUE CONSTRAINT will take care of a scout only earning a badge once. Making the possesion of a badge a requirement for another badge takes care of the prerequisite.

-PatP|||Hi again,

there are various tasks/activities that for a badge requirement...
Thanks again|||Hi guys, I have a table called camp, Scout and Medical_File...i need to include the details of a scout medical file when the scout is booking a camp so we know what we need to take care of in different activities...does anyone know how to model this. The medical File is not currently linked to the camp at the moment!!
Thanks|||If there is only one medical file for each scout, then consider simplifying your schema by including the medical information in the scouts table.|||That is actually a good idea...i did that before and decided to move it to a separate entity as the enity scout seemed of a big table...

Yeah i have another prob...sometimes scouts go to a camp by patrol but also individually depending on who organised the camp...at the moment i had scout entity linked to camp via payment(camp payment)...do u think i should link patrol as well?

thanks|||Is a scout in only one Patrol?

You should probably store "snapshot" information about the scout and the patrol the belong to in your camp/scout attendance table, as some of the data may change over time (they may join a different patrol), but you would still want to know that they were in the original patrol when they attended the camp.

scopeing problem in an aggregate function

I have a matrix with two column groups. I need to be able have the user
select from my boolean parameter and change the scope on my aggregate
function. I already have my boolean parameter setup. the two groups are
called matrix1_Year and matrix1_Hub. i used the following expression in my
textbox
=Count(Fields!DeliveryNumber.Value)/Count(Fields!DeliveryNumber.Value,IIF(Parameters!Scope1.Value,"matrix1_Year","matrix1_hub"))
when i preview the report it gives me the following error
The Value expression for the textbox â'textbox5â' has a scope parameter that
is not valid for an aggregate function. The scope parameter must be set to a
string constant that is equal to either the name of a containing group, the
name of a containing data region, or the name of a data set.
If i take the IIF statement out and put in either of the group names, the
report works and i get the values i would expect, but i need to be able to do
this by passing in a parameter, i don't want to create two report just for
this one problem.Have you tried adding the IIF to the outside... ie
=IIf (Parameters@.Scope1.Value,Big Expression1, Big Expression2)
I do not know if this will help, but it is possible that the problem is
related to WHEN the expression is evaluated..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"gacar101" wrote:
> I have a matrix with two column groups. I need to be able have the user
> select from my boolean parameter and change the scope on my aggregate
> function. I already have my boolean parameter setup. the two groups are
> called matrix1_Year and matrix1_Hub. i used the following expression in my
> textbox
> =Count(Fields!DeliveryNumber.Value)/Count(Fields!DeliveryNumber.Value,IIF(Parameters!Scope1.Value,"matrix1_Year","matrix1_hub"))
> when i preview the report it gives me the following error
> The Value expression for the textbox â'textbox5â' has a scope parameter that
> is not valid for an aggregate function. The scope parameter must be set to a
> string constant that is equal to either the name of a containing group, the
> name of a containing data region, or the name of a data set.
> If i take the IIF statement out and put in either of the group names, the
> report works and i get the values i would expect, but i need to be able to do
> this by passing in a parameter, i don't want to create two report just for
> this one problem.

Scope problem

Hello,
I have a single table with 1 detail and 2 groups.
The detail contains data per year, the first group is the card which sums
all years, and the second the family of the card.
Year Objective Realized
2006 100
2007 200
2008 0
TOTAL CARD 1 1000 300
TOTAL FAMILY A 3000 (!!) 300
But the "objective" column isn't a detail data. I would like to return TOTAL
FAMILY A = 1000, but it returns 3000 (it sums 1000 for each year).
I try to use scope for the sum objective of family, but it doesn't seems to
work fine...
If someone can help me...
Thanks,
Drix.Since you want 1000 it sounds like you dont want Sum(). You want
First() or Max() or Min().|||No, if I take my example, the 1000 isn't linked to a year, but to a card.
There is nothing on the year lines for objective, the value is on the line
card, and the sum in the family line.
So, I would like to sum all the objectives cards for the family.
"FatOaf" wrote:
> Since you want 1000 it sounds like you dont want Sum(). You want
> First() or Max() or Min().
>|||I understand what you want now.
Is your resultset returning a row per year and then grouping on card
and family?
and the objective is repeated for each year in the same card?
I dont think you can do what you are asking with that resultset since
the aggregate functions operate on the underlying rows in the resultset
(years) - the scoping just determines which years are included in the
aggregate.
Maybe you can try altering the resultset so each year contains only the
average objective per card or only the first year in a card contains
the objective. Then Sum() would work at any level. The SQL to do this
would be a bit hairy but possible.
Another approach would be to use UNION to have 2 row types in the
resultset - one for years and one for cards. Then display cards from
the second row type instead of by grouping.