Friday, March 30, 2012

Multiple Join Clause

I have a table "Users" like this:

GroupId
CompanyId
UserId

I need to query the users getting the company's and group's names, but I only know how to join one table. Example:

Select UserId, GroupId, Groups.Name, CompanyId, Companies.Name
From Users JOIN Groups ON Users.GroupId = Groups.Id

Hon can I add the companies table in the Join ?

Thanks,
MosheYou can have multiple JOIN clauses. I find it helpful to put them on separate lines, plus I always indicate the JOIN type since I never can remember which type is the default, and it is liable to change on someone's whim in the future. Plus normally I'd want to return the User's information whether or not I was able to successfully find the corresponding Group or Company -- which means OUTER JOINs:


SELECT
Users.UserId,
Users.GroupId,
ISNULL(Groups.Name,'**unknown**'),
Users.CompanyId,
ISNULL(Companies.Name,'**unknown**')
FROM
Users
LEFT OUTER JOIN
Groups ON Users.GroupId = Groups.Id
LEFT OUTER JOIN
Companies ON Users.CompanyID = Companies.CompanyID

Terri|||Thanks,
It was helpfull

Multiple jobs on one schedule

Hi
I am wondering if it's possible to run multiple backup jobs from one shedule
in SQL Server 2000, so all backups are triggered simultaneously at a given
time.
Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
they have a 1 to 1 relationship and this leads me to believe this is not
possible. Has anyone else ever achieved this, and if so, how?
Thanks in advance
Brin
bin
The second backup will wait till the first one completed.
What is the purpose?
"brin" <brin_z{nospam}@.hotmail.com> wrote in message
news:uZV$LObSGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am wondering if it's possible to run multiple backup jobs from one
> shedule in SQL Server 2000, so all backups are triggered simultaneously at
> a given time.
> Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
> they have a 1 to 1 relationship and this leads me to believe this is not
> possible. Has anyone else ever achieved this, and if so, how?
> Thanks in advance
> Brin
>
|||brin wrote:
> Hi
> I am wondering if it's possible to run multiple backup jobs from one shedule
> in SQL Server 2000, so all backups are triggered simultaneously at a given
> time.
> Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
> they have a 1 to 1 relationship and this leads me to believe this is not
> possible. Has anyone else ever achieved this, and if so, how?
> Thanks in advance
> Brin
>
I think the closest you can get, it to create multiple jobs that starts
at the same time. I don't quite see the need for it though, but I assume
you have a reason for it?
Regards
Steen
|||It just seemed a bit more organised to use the one schedule as all we are
doing is repeating data in sysjobschedules, this is for around 25 databases
each with 2 backup schedules. We are about to rescheule the time they run
anyway so thought it may be a worthwhile excercise.
Thanks again
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23TM$8QbSGHA.5780@.TK2MSFTNGP10.phx.gbl...
> bin
> The second backup will wait till the first one completed.
> What is the purpose?
>
>
> "brin" <brin_z{nospam}@.hotmail.com> wrote in message
> news:uZV$LObSGHA.1204@.TK2MSFTNGP12.phx.gbl...
>
|||Hi
If you schedule mutiple jobs that hit the same discs you may see a
performance bottleneck. You may want to look at having one job with multiple
steps!
John
"brin" wrote:

> It just seemed a bit more organised to use the one schedule as all we are
> doing is repeating data in sysjobschedules, this is for around 25 databases
> each with 2 backup schedules. We are about to rescheule the time they run
> anyway so thought it may be a worthwhile excercise.
> Thanks again
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23TM$8QbSGHA.5780@.TK2MSFTNGP10.phx.gbl...
>
>

Multiple jobs on one schedule

Hi
I am wondering if it's possible to run multiple backup jobs from one shedule
in SQL Server 2000, so all backups are triggered simultaneously at a given
time.
Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
they have a 1 to 1 relationship and this leads me to believe this is not
possible. Has anyone else ever achieved this, and if so, how?
Thanks in advance
Brinbin
The second backup will wait till the first one completed.
What is the purpose?
"brin" <brin_z{nospam}@.hotmail.com> wrote in message
news:uZV$LObSGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am wondering if it's possible to run multiple backup jobs from one
> shedule in SQL Server 2000, so all backups are triggered simultaneously at
> a given time.
> Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
> they have a 1 to 1 relationship and this leads me to believe this is not
> possible. Has anyone else ever achieved this, and if so, how?
> Thanks in advance
> Brin
>|||brin wrote:
> Hi
> I am wondering if it's possible to run multiple backup jobs from one shedu
le
> in SQL Server 2000, so all backups are triggered simultaneously at a given
> time.
> Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
> they have a 1 to 1 relationship and this leads me to believe this is not
> possible. Has anyone else ever achieved this, and if so, how?
> Thanks in advance
> Brin
>
I think the closest you can get, it to create multiple jobs that starts
at the same time. I don't quite see the need for it though, but I assume
you have a reason for it?
Regards
Steen|||It just seemed a bit more organised to use the one schedule as all we are
doing is repeating data in sysjobschedules, this is for around 25 databases
each with 2 backup schedules. We are about to rescheule the time they run
anyway so thought it may be a worthwhile excercise.
Thanks again
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23TM$8QbSGHA.5780@.TK2MSFTNGP10.phx.gbl...
> bin
> The second backup will wait till the first one completed.
> What is the purpose?
>
>
> "brin" <brin_z{nospam}@.hotmail.com> wrote in message
> news:uZV$LObSGHA.1204@.TK2MSFTNGP12.phx.gbl...
>|||Hi
If you schedule mutiple jobs that hit the same discs you may see a
performance bottleneck. You may want to look at having one job with multiple
steps!
John
"brin" wrote:

> It just seemed a bit more organised to use the one schedule as all we are
> doing is repeating data in sysjobschedules, this is for around 25 database
s
> each with 2 backup schedules. We are about to rescheule the time they run
> anyway so thought it may be a worthwhile excercise.
> Thanks again
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23TM$8QbSGHA.5780@.TK2MSFTNGP10.phx.gbl...
>
>sql

Multiple jobs on one schedule

Hi
I am wondering if it's possible to run multiple backup jobs from one shedule
in SQL Server 2000, so all backups are triggered simultaneously at a given
time.
Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
they have a 1 to 1 relationship and this leads me to believe this is not
possible. Has anyone else ever achieved this, and if so, how?
Thanks in advance
Brinbin
The second backup will wait till the first one completed.
What is the purpose?
"brin" <brin_z{nospam}@.hotmail.com> wrote in message
news:uZV$LObSGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am wondering if it's possible to run multiple backup jobs from one
> shedule in SQL Server 2000, so all backups are triggered simultaneously at
> a given time.
> Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
> they have a 1 to 1 relationship and this leads me to believe this is not
> possible. Has anyone else ever achieved this, and if so, how?
> Thanks in advance
> Brin
>|||brin wrote:
> Hi
> I am wondering if it's possible to run multiple backup jobs from one shedule
> in SQL Server 2000, so all backups are triggered simultaneously at a given
> time.
> Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
> they have a 1 to 1 relationship and this leads me to believe this is not
> possible. Has anyone else ever achieved this, and if so, how?
> Thanks in advance
> Brin
>
I think the closest you can get, it to create multiple jobs that starts
at the same time. I don't quite see the need for it though, but I assume
you have a reason for it?
Regards
Steen|||It just seemed a bit more organised to use the one schedule as all we are
doing is repeating data in sysjobschedules, this is for around 25 databases
each with 2 backup schedules. We are about to rescheule the time they run
anyway so thought it may be a worthwhile excercise.
Thanks again
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23TM$8QbSGHA.5780@.TK2MSFTNGP10.phx.gbl...
> bin
> The second backup will wait till the first one completed.
> What is the purpose?
>
>
> "brin" <brin_z{nospam}@.hotmail.com> wrote in message
> news:uZV$LObSGHA.1204@.TK2MSFTNGP12.phx.gbl...
>> Hi
>> I am wondering if it's possible to run multiple backup jobs from one
>> shedule in SQL Server 2000, so all backups are triggered simultaneously
>> at a given time.
>> Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
>> they have a 1 to 1 relationship and this leads me to believe this is not
>> possible. Has anyone else ever achieved this, and if so, how?
>> Thanks in advance
>> Brin
>|||Hi
If you schedule mutiple jobs that hit the same discs you may see a
performance bottleneck. You may want to look at having one job with multiple
steps!
John
"brin" wrote:
> It just seemed a bit more organised to use the one schedule as all we are
> doing is repeating data in sysjobschedules, this is for around 25 databases
> each with 2 backup schedules. We are about to rescheule the time they run
> anyway so thought it may be a worthwhile excercise.
> Thanks again
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23TM$8QbSGHA.5780@.TK2MSFTNGP10.phx.gbl...
> > bin
> >
> > The second backup will wait till the first one completed.
> > What is the purpose?
> >
> >
> >
> >
> > "brin" <brin_z{nospam}@.hotmail.com> wrote in message
> > news:uZV$LObSGHA.1204@.TK2MSFTNGP12.phx.gbl...
> >> Hi
> >>
> >> I am wondering if it's possible to run multiple backup jobs from one
> >> shedule in SQL Server 2000, so all backups are triggered simultaneously
> >> at a given time.
> >>
> >> Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
> >> they have a 1 to 1 relationship and this leads me to believe this is not
> >> possible. Has anyone else ever achieved this, and if so, how?
> >>
> >> Thanks in advance
> >>
> >> Brin
> >>
> >
> >
>
>

Multiple Job steps using same variable or one temp table

I need a constant to be available in multiple steps of a job (more
specifically, getdate() from step one). I tried storing it in a variable or
temp table but when I get to step two of the job and try to retrieve the
value, I get "must declare variable..." or "table does not exist" (depending
on which method I am testing).
I did a little test and the SPID in each one of the steps is the same so
shouldn't the temp table be accessible from all steps?
A profiler trace reveals that Agent does a logout and login. What you are seeing is the same spid
number being reused. As Agent won't execute the same job simultaneously, why not use a permanent
table?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:0B9550DB-C93F-42FD-AB5D-670049929375@.microsoft.com...
>I need a constant to be available in multiple steps of a job (more
> specifically, getdate() from step one). I tried storing it in a variable or
> temp table but when I get to step two of the job and try to retrieve the
> value, I get "must declare variable..." or "table does not exist" (depending
> on which method I am testing).
> I did a little test and the SPID in each one of the steps is the same so
> shouldn't the temp table be accessible from all steps?
|||Initially, my main reason for not using a permanent table is that I will need
to drop it at the end and if step 2 fails and the table gets dropped in step
3 then it won't get dropped.
I could modify step 2 to continue on to step 3 even if it fails but then
the job will show as completed succesfully even if step two failed.
Maybe I will just use a permanent table and keep it so that I don't need to
drop it at the end...
"Tibor Karaszi" wrote:

> A profiler trace reveals that Agent does a logout and login. What you are seeing is the same spid
> number being reused. As Agent won't execute the same job simultaneously, why not use a permanent
> table?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DBA72" <DBA72@.discussions.microsoft.com> wrote in message
> news:0B9550DB-C93F-42FD-AB5D-670049929375@.microsoft.com...
>
>
|||> Maybe I will just use a permanent table and keep it so that I don't need to
> drop it at the end...
That is what I would do.

> Initially, my main reason for not using a permanent table is that I will need
> to drop it at the end and if step 2 fails and the table gets dropped in step
> 3 then it won't get dropped.
> I could modify step 2 to continue on to step 3 even if it fails but then
> the job will show as completed successfully even if step two failed.
Have two finishing steps. If step 2 fails, goto step 4 which drops the table and returns fails
status whether step 4 is OK or not...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:FBFC6287-C3C4-47C3-A58F-9AF3B373E3D7@.microsoft.com...[vbcol=seagreen]
> Initially, my main reason for not using a permanent table is that I will need
> to drop it at the end and if step 2 fails and the table gets dropped in step
> 3 then it won't get dropped.
> I could modify step 2 to continue on to step 3 even if it fails but then
> the job will show as completed succesfully even if step two failed.
> Maybe I will just use a permanent table and keep it so that I don't need to
> drop it at the end...
> "Tibor Karaszi" wrote:

Multiple Job steps using same variable or one temp table

I need a constant to be available in multiple steps of a job (more
specifically, getdate() from step one). I tried storing it in a variable or
temp table but when I get to step two of the job and try to retrieve the
value, I get "must declare variable..." or "table does not exist" (depending
on which method I am testing).
I did a little test and the SPID in each one of the steps is the same so
shouldn't the temp table be accessible from all steps?A profiler trace reveals that Agent does a logout and login. What you are seeing is the same spid
number being reused. As Agent won't execute the same job simultaneously, why not use a permanent
table?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:0B9550DB-C93F-42FD-AB5D-670049929375@.microsoft.com...
>I need a constant to be available in multiple steps of a job (more
> specifically, getdate() from step one). I tried storing it in a variable or
> temp table but when I get to step two of the job and try to retrieve the
> value, I get "must declare variable..." or "table does not exist" (depending
> on which method I am testing).
> I did a little test and the SPID in each one of the steps is the same so
> shouldn't the temp table be accessible from all steps?|||Initially, my main reason for not using a permanent table is that I will need
to drop it at the end and if step 2 fails and the table gets dropped in step
3 then it won't get dropped.
I could modify step 2 to continue on to step 3 even if it fails but then
the job will show as completed succesfully even if step two failed.
Maybe I will just use a permanent table and keep it so that I don't need to
drop it at the end...
"Tibor Karaszi" wrote:
> A profiler trace reveals that Agent does a logout and login. What you are seeing is the same spid
> number being reused. As Agent won't execute the same job simultaneously, why not use a permanent
> table?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DBA72" <DBA72@.discussions.microsoft.com> wrote in message
> news:0B9550DB-C93F-42FD-AB5D-670049929375@.microsoft.com...
> >I need a constant to be available in multiple steps of a job (more
> > specifically, getdate() from step one). I tried storing it in a variable or
> > temp table but when I get to step two of the job and try to retrieve the
> > value, I get "must declare variable..." or "table does not exist" (depending
> > on which method I am testing).
> >
> > I did a little test and the SPID in each one of the steps is the same so
> > shouldn't the temp table be accessible from all steps?
>
>|||> Maybe I will just use a permanent table and keep it so that I don't need to
> drop it at the end...
That is what I would do.
> Initially, my main reason for not using a permanent table is that I will need
> to drop it at the end and if step 2 fails and the table gets dropped in step
> 3 then it won't get dropped.
> I could modify step 2 to continue on to step 3 even if it fails but then
> the job will show as completed successfully even if step two failed.
Have two finishing steps. If step 2 fails, goto step 4 which drops the table and returns fails
status whether step 4 is OK or not...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:FBFC6287-C3C4-47C3-A58F-9AF3B373E3D7@.microsoft.com...
> Initially, my main reason for not using a permanent table is that I will need
> to drop it at the end and if step 2 fails and the table gets dropped in step
> 3 then it won't get dropped.
> I could modify step 2 to continue on to step 3 even if it fails but then
> the job will show as completed succesfully even if step two failed.
> Maybe I will just use a permanent table and keep it so that I don't need to
> drop it at the end...
> "Tibor Karaszi" wrote:
>> A profiler trace reveals that Agent does a logout and login. What you are seeing is the same spid
>> number being reused. As Agent won't execute the same job simultaneously, why not use a permanent
>> table?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "DBA72" <DBA72@.discussions.microsoft.com> wrote in message
>> news:0B9550DB-C93F-42FD-AB5D-670049929375@.microsoft.com...
>> >I need a constant to be available in multiple steps of a job (more
>> > specifically, getdate() from step one). I tried storing it in a variable or
>> > temp table but when I get to step two of the job and try to retrieve the
>> > value, I get "must declare variable..." or "table does not exist" (depending
>> > on which method I am testing).
>> >
>> > I did a little test and the SPID in each one of the steps is the same so
>> > shouldn't the temp table be accessible from all steps?
>>

Multiple Job Steps SQL 2000

Hi,
I have 3 DTS packages I want to run after each other, in no particular
order.
I have x3 jobs, each running one of the DTS using CmdExec.
I don't want to run these jobs on individual schedules. I also don't want a
single DTS package with x3 steps to achieve the same thing.
I want visibility of Status etc available in the SQL EM Job view for each of
the jobs.
I created a single multi-step job, with each step invoking a DTS package
using CmdExec. This works but then the individual jobs are redundant and the
level of job status visibility is lost.
I created a single multi-step job, with each step using T-SQL and
sp_start_job. Now this ran the jobs and gave visibility of their individual
status but the jobs did not run synchronously. They all started together.
How can I write a single 'wrapper' job to start one job and wait for it to
complete before starting the next job? This allows me to maintain a single
schedule and yet see x3 status indicators?
ThanksCreate an another package , with three Execute Sql Task, and use work flow to
execute each task only after success so that it wont run synchronously.
Thanks,
Sree
"Gramps" wrote:
> Hi,
> I have 3 DTS packages I want to run after each other, in no particular
> order.
> I have x3 jobs, each running one of the DTS using CmdExec.
> I don't want to run these jobs on individual schedules. I also don't want a
> single DTS package with x3 steps to achieve the same thing.
> I want visibility of Status etc available in the SQL EM Job view for each of
> the jobs.
>
> I created a single multi-step job, with each step invoking a DTS package
> using CmdExec. This works but then the individual jobs are redundant and the
> level of job status visibility is lost.
> I created a single multi-step job, with each step using T-SQL and
> sp_start_job. Now this ran the jobs and gave visibility of their individual
> status but the jobs did not run synchronously. They all started together.
>
> How can I write a single 'wrapper' job to start one job and wait for it to
> complete before starting the next job? This allows me to maintain a single
> schedule and yet see x3 status indicators?
> Thanks
>
>