Query : Running a job/step in a loop for ALL databases

Hi,

I've written a job to export user and database permissions for all
d/b's on a server. As you can see below, the T-SQL commands are the
same for each d/b. Can anyone assist with regard to re-writing this so
that any new d/b's added do not require ammending the job (loop)?

Thx,

GC.

use master
go
SELECT db_name()
EXEC sp_helpuser
EXEC sp_helprotect NULL, NULL, NULL, 'o s'
use msdb
go
SELECT db_name()
EXEC sp_helpuser
EXEC sp_helprotect NULL, NULL, NULL, 'o s'
use test1
go
SELECT db_name()
EXEC sp_helpuser
EXEC sp_helprotect NULL, NULL, NULL, 'o s'
use test2
go
SELECT db_name()
EXEC sp_helpuser
EXEC sp_helprotect NULL, NULL, NULL, 'o s'"Garry Clarke" <gclarke@.euro.banta.com> wrote in message
news:fed38413.0310240324.77f4ce60@.posting.google.c om...
> Hi,
> I've written a job to export user and database permissions for all
> d/b's on a server. As you can see below, the T-SQL commands are the
> same for each d/b. Can anyone assist with regard to re-writing this so
> that any new d/b's added do not require ammending the job (loop)?
> Thx,
> GC.
> use master
> go
> SELECT db_name()
> EXEC sp_helpuser
> EXEC sp_helprotect NULL, NULL, NULL, 'o s'
> use msdb
> go
> SELECT db_name()
> EXEC sp_helpuser
> EXEC sp_helprotect NULL, NULL, NULL, 'o s'
> use test1
> go
> SELECT db_name()
> EXEC sp_helpuser
> EXEC sp_helprotect NULL, NULL, NULL, 'o s'
> use test2
> go
> SELECT db_name()
> EXEC sp_helpuser
> EXEC sp_helprotect NULL, NULL, NULL, 'o s'

A cursor is one way to do this (cursors are usually a bad idea in
application code, but can be useful for admin scripts):

declare @.db sysname

declare cur_dbs cursor fast_forward
for select name from master..sysdatabases
order by name

open cur_dbs

fetch next from cur_dbs into @.db

while @.@.fetch_status = 0
begin
select @.db
exec('exec ' + @.db + '..sp_helpuser')
exec('exec ' +@.db + '..sp_helprotect NULL, NULL, NULL, ''os''')
fetch next from cur_dbs into @.db
end

close cur_dbs
deallocate cur_dbs

Simon

query - which came last?

Hi,
Query puzzler here.
I have a table of PEOPLE.
Besides the Person ID, each record has five Date fields- Married ,
Graduated, Started Job, Got Famous, Got Rich
I want to write a query that shows what the current status of each person
is.
In other words, show which event has happened most recently for each person
Any help would be , and might keep me from getting fired.No datefields in the table? Can you show one or two rows as sample in your
table?
Thanks,
Sree
"Tales Mein" wrote:

> Hi,
> Query puzzler here.
> I have a table of PEOPLE.
> Besides the Person ID, each record has five Date fields- Married ,
> Graduated, Started Job, Got Famous, Got Rich
> I want to write a query that shows what the current status of each person
> is.
> In other words, show which event has happened most recently for each perso
n
> Any help would be , and might keep me from getting fired.
>
>
>
>|||Hi, no-one wants to see you get fired...
SELECT PersonID, max(Married) FROM (
select PersonID, Married from PEOPLE
union
select PersonID, Graduated from PEOPLE
union
select PersonID, [Started Job] from PEOPLE
union
select PersonID, [Got Famous] from PEOPLE
union
select PersonID, [Got Rich] from PEOPLE) as Derived
GROUP BY PersonID
HTH. Ryan
"Tales Mein" <toddough@.doughpdu.com> wrote in message
news:IuednZ9bsJhDO0neRVn-oQ@.comcast.com...
> Hi,
> Query puzzler here.
> I have a table of PEOPLE.
> Besides the Person ID, each record has five Date fields- Married ,
> Graduated, Started Job, Got Famous, Got Rich
> I want to write a query that shows what the current status of each person
> is.
> In other words, show which event has happened most recently for each
> person
> Any help would be , and might keep me from getting fired.
>
>
>
>|||you can use this code:
SELECT ID,
Case When Married IS NOT NULL
And Married >= Coalesce (Graduated,'19000101')
And Married >= Coalesce ([Started Job],'19000101')
And Married >= Coalesce ([Got Famous],'19000101')
And Married >= Coalesce ([Got Rich],'19000101')
Then 'Married'
When Graduated IS NOT NULL
And Graduated >= Coalesce (Married,'19000101')
And Graduated >= Coalesce ([Started Job],'19000101')
And Graduated >= Coalesce ([Got Famous],'19000101')
And Graduated >= Coalesce ([Got Rich],'19000101')
Then 'Graduated'
When [Started Job] IS NOT NULL
And [Started Job] >= Coalesce (Graduated,'19000101')
And [Started Job] >= Coalesce (Married,'19000101')
And [Started Job] >= Coalesce ([Got Famous],'19000101')
And [Started Job] >= Coalesce ([Got Rich],'19000101')
Then '[Started Job]'
When [Got Famous] IS NOT NULL
And [Got Famous] >= Coalesce (Graduated,'19000101')
And [Got Famous] >= Coalesce ([Started Job],'19000101')
And [Got Famous] >= Coalesce (Married,'19000101')
And [Got Famous] >= Coalesce ([Got Rich],'19000101')
Then '[Got Famous]'
When [Got Rich] IS NOT NULL
And [Got Rich] >= Coalesce (Graduated,'19000101')
And [Got Rich] >= Coalesce ([Started Job],'19000101')
And [Got Rich] >= Coalesce ([Got Famous],'19000101')
And [Got Rich] >= Coalesce (Married,'19000101')
Then '[Got Rich]'
Else 'nothing happend'
End
As LastEvent,
Case When Married IS NOT NULL
And Married >= Coalesce (Graduated,'19000101')
And Married >= Coalesce ([Started Job],'19000101')
And Married >= Coalesce ([Got Famous],'19000101')
And Married >= Coalesce ([Got Rich],'19000101')
Then Married
When Graduated IS NOT NULL
And Graduated >= Coalesce (Married,'19000101')
And Graduated >= Coalesce ([Started Job],'19000101')
And Graduated >= Coalesce ([Got Famous],'19000101')
And Graduated >= Coalesce ([Got Rich],'19000101')
Then Graduated
When [Started Job] IS NOT NULL
And [Started Job] >= Coalesce (Graduated,'19000101')
And [Started Job] >= Coalesce (Married,'19000101')
And [Started Job] >= Coalesce ([Got Famous],'19000101')
And [Started Job] >= Coalesce ([Got Rich],'19000101')
Then [Started Job]
When [Got Famous] IS NOT NULL
And [Got Famous] >= Coalesce (Graduated,'19000101')
And [Got Famous] >= Coalesce ([Started Job],'19000101')
And [Got Famous] >= Coalesce (Married,'19000101')
And [Got Famous] >= Coalesce ([Got Rich],'19000101')
Then [Got Famous]
When [Got Rich] IS NOT NULL
And [Got Rich] >= Coalesce (Graduated,'19000101')
And [Got Rich] >= Coalesce ([Started Job],'19000101')
And [Got Rich] >= Coalesce ([Got Famous],'19000101')
And [Got Rich] >= Coalesce (Married,'19000101')
Then [Got Rich]
End
As LastEventDate
FROM PEOPLE
"Tales Mein" wrote:

> Hi,
> Query puzzler here.
> I have a table of PEOPLE.
> Besides the Person ID, each record has five Date fields- Married ,
> Graduated, Started Job, Got Famous, Got Rich
> I want to write a query that shows what the current status of each person
> is.
> In other words, show which event has happened most recently for each perso
n
> Any help would be , and might keep me from getting fired.
>
>
>
>|||Thanks - this seems to be the shorter of the two, saving typing, allowing
more time to beg for my job.
Both are instructive.
thnanks a lot
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%23iR7usAIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hi, no-one wants to see you get fired...
> SELECT PersonID, max(Married) FROM (
> select PersonID, Married from PEOPLE
> union
> select PersonID, Graduated from PEOPLE
> union
> select PersonID, [Started Job] from PEOPLE
> union
> select PersonID, [Got Famous] from PEOPLE
> union
> select PersonID, [Got Rich] from PEOPLE) as Derived
> GROUP BY PersonID
> --
> HTH. Ryan
> "Tales Mein" <toddough@.doughpdu.com> wrote in message
> news:IuednZ9bsJhDO0neRVn-oQ@.comcast.com...
>|||Hi this is almost there, but it returns dates. I need the column name
returened.
Like: Married
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%23iR7usAIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hi, no-one wants to see you get fired...
> SELECT PersonID, max(Married) FROM (
> select PersonID, Married from PEOPLE
> union
> select PersonID, Graduated from PEOPLE
> union
> select PersonID, [Started Job] from PEOPLE
> union
> select PersonID, [Got Famous] from PEOPLE
> union
> select PersonID, [Got Rich] from PEOPLE) as Derived
> GROUP BY PersonID
> --
> HTH. Ryan
> "Tales Mein" <toddough@.doughpdu.com> wrote in message
> news:IuednZ9bsJhDO0neRVn-oQ@.comcast.com...
>|||I need the status, not the date
OK I could use both.
I may get fired after all
"Tales Mein" <toddough@.doughpdu.com> wrote in message
news:IuednZ9bsJhDO0neRVn-oQ@.comcast.com...
> Hi,
> Query puzzler here.
> I have a table of PEOPLE.
> Besides the Person ID, each record has five Date fields- Married ,
> Graduated, Started Job, Got Famous, Got Rich
> I want to write a query that shows what the current status of each person
> is.
> In other words, show which event has happened most recently for each
> person
> Any help would be , and might keep me from getting fired.
>
>
>
>|||On Mon, 23 Jan 2006 02:50:28 -0700, Tales Mein wrote:

>Hi,
>Query puzzler here.
>I have a table of PEOPLE.
>Besides the Person ID, each record has five Date fields- Married ,
>Graduated, Started Job, Got Famous, Got Rich
>I want to write a query that shows what the current status of each person
>is.
>In other words, show which event has happened most recently for each person
>Any help would be , and might keep me from getting fired.
Hi Tales,
The person who designed that table should get fired, not you.
First, let's create a view to get a better normalised version of the
data:
CREATE VIEW Normalized
AS
SELECT PersonID, Married AS EventDate, 'Married' AS Event
FROM PEOPLE
UNION ALL
SELECT PersonID, Graduated, 'Graduated'
FROM PEOPLE
UNION ALL
SELECT PersonID, "Started Job", 'Started Job'
FROM PEOPLE
UNION ALL
SELECT PersonID, "Got Famous", 'Got Famous'
FROM PEOPLE
UNION ALL
SELECT PersonID, "Got Rich", 'Got Rich'
FROM PEOPLE
go
With this, getting the required information becomes a lot easier:
SELECT a.PersonID, a.EventDate, a.Event
FROM Normalized AS a
WHERE a.EventDate = (SELECT MAX(b.EventDate)
FROM Normalized AS b
WHERE b.PeopleID = a.PeopleID)
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP|||thanks a lot|||Hugo,
Is it just me, or are the columns Married, Graduated, StartedJob, GotFamous,
and GotRich unlikely columns in any database?
Unlikely, of course, unless the database is academic only.
Maybe Tales simply made up these column names because they couldn't tell us
the actual columns for proprietary reasons, but I am willing to bet this is
a classroom example, and no one's job is at risk.
I believe Tales just got this newsgroup to do his/her homework.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info> wrote in message
news:r3adt15gapiv1bvvkcitvvrfq33rg850d8@.
4ax.com...
> On Mon, 23 Jan 2006 02:50:28 -0700, Tales Mein wrote:
> Hi Tales,
> The person who designed that table should get fired, not you.
>

query

Imagine we have 2 tables
personal table with 3 fields:
personalid int
jobid int
oldjobid int

and table job with 2 fields :
jobid int
jobname varchar

now I want write a query with this result :
personalid,jobname,oldjobname
which jobname means the name in job table related with jobid in personal table
and oldjobname means the name in job table related with oldjobid in personal table
please help me

SELECT
personalid,
jobname,
(SELECT jobname FROM job INNER JOIN personal on personal.oldjobid=job.jobid) AS Oldjobname
FROM
personal
INNER JOIN job ON personal.jobid = job.jobid
|||

tanks for you answer
but when I run this query this error occure:
"subquery returned more than 1 value. This is not permitted when the subquery follows =,!=,<,<=,<,<= or when the subquery is used as an expression"

|||Try this:
SELECT personalid, j1.jobName, j2.jobName as oldJobName
FROM personal p
INNER JOIN job j1 ON p.jobID = j1.jobID
INNER JOIN job j2 ON p.oldJobID = j2.jobID

Nick|||

tank you

exactly true

queries and reporting from corporative database non IT staff

Hi! we have no Business Intelligence solution in my job and the general manager asked for a query and reports system where he (and other section managers and non IT staff) could query the corporative database (we work in healthcare, so it's mostly about patients, clinic histories, treatments) so I will propose them SQL SERVER 2005. I'll tell you my general idea and maybe you could tell me if I'm right: Through Integration Services we will load our data from the corporative database (it's Borland's InterBase) and generate a datawarehouse (now we don't have a datawarehouse) then through Reporting Services we can query this datawarehouse to generate different reports (this reports can be generated for non-technical staff, but staff who knows quite a lot about for example the different treatments that different groups of patients follow, so this staff may want to query about which patients followed which treatment in a period of time and generate by himself a report about that). And through Analysis Services we could (in a future) generate some OLAP solutions, Data Mining etc

But for the query and report system it could be enough to start with Integration Services and Reporting Services? it's very important for this system to work efficiently in terms of time: the non-technical staff generates the query and in seconds receives the report result... some of this non IT staff who will use the system knows exactly which information we house in each InterBase table, so through this system they would like to generate their own queries and reports (this is a different level of non-IT staff who is in between a Medical doctor and a Software engineer, this staff does have some technical background specially in SQL.

So my idea is to have an SQL SERVER 2005 to which this staff gets connected via LAN (all this system runs locally in one corporative place, will not run through Internet) feeded by the InterBase corporative database and delivering efficiently queries and reports... is this possible? could I test this using the trial free 6 months version? and after that if everything's working fine how much will I have to pay for 15 persons to access the SQL SERVER 2005 for keeping using the system?

This sounds like a good solution. I believe there are three stages of data presentation at a company: Data storage and retrieval, a good reporting strategy, and then Business Intelligence. SQL Server 2005 contains all the tools you need to do all three. The engine (and good application programming) handles number one, SSIS and Reporting Services handles number two, and the new Analysis Services handles number three.

You can read more about what I think on this here:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=159&rl=1

If this answers your question, make sure to mark it "answered" so that others can quickly locate it.

Buck Woody
http://www.buckwoody.com

|||

Thanks!! just one question to get the general idea: in the Data storage and retrieval part is when you create the datawarehouse? so the original data is somehow "rearranged" for the Reporting Services to query the data more efficiently..

I don't understand how the data is queried efficiently

|||

Good question - if you'll check out my series of articles I referenced in the last post, you'll see that the data warehouse is a different structure than your data store. I think you can get the reports you're looking for out of the ODS layer I talk about. Check out those articles and I think you'll find what you're looking for.

Buck